By Tom Glaser
Db2 Query Monitor for z/OS ISPF Primary Commands
Date: Oct, 2023
Summary: IBM Db2 Query Monitor for z/OS is a tool used to monitor SQL statements running in Db2 for z/OS. It comes with two interfaces, TSO/ISPF and a Web Client. Accessing the various functions of Db2 Query Monitor is different, depending on the interface you use. For example, if you wanted to filter out data, in TSO, it’s a command. If in the Web Client, it’s an icon/button. This article is about the primary commands that are available for the TSO/ISPF interface.
Some primary commands can be used on any screen. For others, you need to be on a specific screen.
Primary commands are meant to provide the user with a quick way to achieve their end result. Do you want to page forward or backward in time? Do you want to print the information on the screen? Do you want to change the data on the screen to a different format. Let’s review the most used primary commands with some examples, listed in alphabetic order. As a side note, most of the primary commands work like standard TSO commands. You can issue PFSHOW or PFSHOW ON (to display PF Keys); they both mean the same thing. Issuing PFSHOW once more is the same as PFSHOW OFF.
The following Primary Commands will be reviewed in this order:
CEXPAND - Enables you to display an entire row-column data element.
CHELP - Provides help for context-sensitive columns.
CNUM - Enables you to toggle on/off the column numbers above each display column.
COLS ON/OFF - Displays a ruler.
CRIGHT/CLEFT - Scrolls column by column
EXP/EXPORT - Export SQL text of interest to a data set of your choice.
EXPORTLOG - Export the ISPF log to a dataset.
FILTER/FILT - Filters to remove unwanted data.
FIND - Finds a unique string within a panel of data.
FORM - Reformats the display of a selected line item on a report panel.
INTERVAL/INTV - An interval to Db2 Query Monitor is a slice of time that was monitored.
PRINTX - Takes a screen shot of a report and sends it to an output queue.
RANGE - Enables you to select a start and end record number that fall within the specified range.
SORT - Allows you to sort the data
CEXPAND:
This command enables you to display an entire row-column data element.
To invoke CEXPAND, place the cursor on a row-column element and issue the CEXPAND command. The cursor position determines the row-column that expands. The CEXPAND command can be issued by itself or with two parameters (row and column). The syntax is as follows:
CEXPAND (row column)
- Enables you to display an entire row-column data element where row is the number of the row and column is the number of the column (non-heading lines only) that you want to expand.
CEXPAND can be issued with no parameters and the cursor on a data field, or with two parameters. The two parameters are the row number, followed by the column number of the data element to be expanded. The row number is counted down from the top, starting with the first scrollable row (heading not counted) The column number is counted from left to right, starting with the left column in the current display window. This command cannot be used in the FORM mode.
In this example, we cannot see the whole trigger name. We can either change the length of the column (using CSIZE, which impacts every value in the column). Or, simply type CEXPAND to see the whole name.
If you do want to change the length of the column for all values, see SORT below.
CHELP:
Db2 Query Monitor supports context-sensitive column help. To access column help, type CHELP in the option line, place the cursor on the column of interest, and press Enter. A help panel displays a definition of the column. The CHELP command is valid on any Db2 Query Monitor ISPF panel that displays data in columns. If a panel does not contain columns (for example, a panel can contain a list of information or options) then the CHELP command is not valid for that panel. Note that you want to hit “ENTER,” not PF1.
CNUM:
Enables you to toggle on/off the column numbers above each display column. This command actually coincides with sorting column information on screen. While there are screens to assist with sorting (see further in this blog), typing CNUM provides a number associated with a column. This allows you to sort information in ascending or descending order by column number. Here’s an example. Notice there are 12 columns on this panel? If you wanted to sort by AvgX Delay, you can count over column by column. Or, type CNUM, hit enter, then you can sort by:
Option ===> SORT 10 D
COLS ON/OFF:
This is known as the “ruler display.”
The COLS command enables you to generate a ruler at the top of the report columns beneath the headings. This ruler tracks the current position within the column. The < > symbols indicate whether there is additional column data to the left or right of the displayed data. For example:
<-5----2----5->
In this example, positions 13 through 28 are displayed. There is data both to the left and right of the currently displayed area.
The COLS command can be issued by itself, as a toggle switch, or with one parameter (ON|OFF). The syntax is as follows:
COLS (ON|OFF) - enables you to generate a ruler at the top of the report columns to track the current position within the column.
![]()
Note: Just typing COLS is the same as typing COLS ON.
CRIGHT/CLEFT:
Column Scroll: Column scrolling enables you to scroll horizontally between columns, in both left and right directions.
Use the following commands when viewing any dynamic display panel to scroll horizontally between columns:
- CRIGHT n Enables you to scroll the left side of the display window n report columns to the right. Abbrev CR
- CLEFT n Enables you to scroll the left side of the display window n report columns to the left. Abbrev CL
EXP/EXPORT:
Db2 Query Monitor enables you to export SQL text of interest to a data set of your choice. The EXPORT command is valid only when viewing the entire SQL. It is not valid on the SQL Activities panel.
Procedure:
a) Access the Display SQL Statement Text panel from main menu option 1 (View Activity Summaries), 3 (View Current Activity), or 5 (View Exceptions). For example, to export SQL text using 1 (View Activity Summaries):
b) Select 1 (View Activity Summaries) from the IBM Db2 Query Monitor main menu.
c) Select 1 (Plan).
d) Locate the activity of interest and specify the S line command.
e) Locate the SQL text of interest and specify the V line command.
f) Type EXPORT in the Option line and press Enter. The Export SQL Text to DSN panel displays.
Next, specify the data set to which you want to export SQL text in the Export to Data Set field. If the data set is a PDS, then also specify the appropriate member in the Member field. Type Y in the Execute SQL PA against exported data set field if you would like to use IBM Db2 SQL Performance Analyzer for z/OS to analyze the data set containing the exported SQL text. Press Enter to process your request.
Note: SQL PA must be installed and configured to work with Db2 Query Monitor. If you specify Y to execute SQL PA against the query and it is not installed, you will get an error.
As you can see in the above SQL statement, this is not a query I would like to rewrite or even format. Exporting this query will format it to be in within columns 1-72.
EXPORTLOG:
This line command allows you to export the ISPF log to a dataset. Upon typing EXPORTLOG, you will get a message like the following:
I would recommend only using this command if IBM asks for it to help troubleshoot a Db2QM issue.
FILTER/FILT:
You can use filters to specify the type of information to display for an ISPF session.
For example, if a monitoring profile specifies the collection of only DSN% job names, you can define a filter to view only those jobs submitted by a specified user ID or job name. Then the data the user sees in Db2 Query Monitor is limited to only those DSN% jobs submitted by the user specified by the filter.
Notes:
1. Filters do not affect the data that is collected by Db2 Query Monitor, they only filter data for display purposes.
2. The FILTER command is valid for:
- The Object panel, available when viewing current activity
- The Calls panel, available when viewing exceptions
- View DB2 Command Activity panels
- View Activity Summaries panels
Enable and tailor your active filters as needed to show the data most important to you.
Type FILTER on the TSO command line to bring up the filter panel.