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. If this is the first time you’ve pulled up this panel, it will look like this. Type CREATE to create a new filter:
Enable Filtering:
Enable or disable filters for the current display. Valid values are Y (filters are active for the current display) and N (filters are not active for the current display).
Exclude Filtered Data:
Indicates whether the data you define in the filter is to be excluded from your view. Valid values are Y (excludes data matching your filter criteria from display) and N (does not exclude data matching your filter criteria from display; matching data is displayed).
Mixed case:
Indicates whether the data you define in the filter contains mixed case characters. Valid values are Y (data contains mixed case) and N (data does not contain mixed case). Mixed case set to N does not alter the value you enter, it simply controls whether or not data is translated to upper case prior to display. However, if you set Mixed Case to N and you modify a value, it will be stored in upper case.
Match case:
Indicates whether the filter applies as entered or whether all values are treated as if they were entirely upper case. Valid values are Y (match values as entered) and N (treat all values as upper case). Match Case set to N does not alter the value you enter, it simply controls whether or not a copy of the data values to be compared are translated to upper case prior to comparison.
Display Defined Filters:
Indicates whether or not to display existing filters. Valid values are Y (displays existing filters) and N (does not display existing filters). If you specify Y in the Display Defined Filters field, you must also specify the name of the data set that contains defined filters in the Filter Dataset Name field.
Filter Dataset Name
The name of the data set you created to hold Db2 Query Monitor filters. If you specify a fullyqualified data set name, the name must be enclosed in single-quotes, for example: 'TWUSER.CQM.FILTERS.' NOTE: This dataset is not required to keep a filter. If you leave FILTER DATASET NAME field blank, Db2 Query Monitor will keep this filter. You can logoff and log back and the filter still exists. But, if you CREATE a new filter and provide a 80 byte PDS data set name, this information will be stored in the dataset. This allows you to send this file to other lpars and when you login to that lpar and provide this dataset name with DISPLAY DEFINED FILTERS set to Y, you will see the filters.
AND/OR between columns
Indicates whether line items for a filter are joined by the AND or the OR operator. Valid values are
AND
Joins line items by the AND operator. Db2 Query Monitor data must meet the criteria of all defined filter lines for it to be displayed.
OR
Joins line items by the OR operator. Db2 Query Monitor data must meet the criteria of at least one defined filter line for it to be displayed.
Once you enter “create” (the ENABLE field can be Y or N), the following screen will be displayed:
In this example, we want to see all SQL statements issued by authid “ts4430.”
OPERATOR:
o EQ (equal to)
o GE (greater than or equal to)
o GT (greater than)
o LE (less than or equal to)
o LT (less than)
o NE (not equal to)
The case of Column values is handled according to the Mixed Case and Match Case settings previously described. Column values can also be specified using special wildcard characters. The percent sign (%) is a fill character and it will match any value in that position. For example, a Column Value of ab%d will match abcd. The asterisk(*) matches any number of characters beyond the minimum required. For example, abc* will match abcd, abcde, abcdef, etc. If you specify a column name of BUFPOOL, the column value B* is invalid, BP* must instead be used with the asterisk. There is also a special wildcard, N/A. This value can be specified in any case regardless of the setting of Mixed Case. During data capture, some data might be unspecified or unavailable. These fields show as N/A or blank on a display. You can filter these lines using the column value of N/A. Only the EQ and NE operators can be specified with any column value specified with a wildcard (* or %) or set to N/A.
If field “ENABLE FILTERING” is set to Y, your SQL statement screen will display the data along with a comment about the percentage of records filtered:
Once your filter is created, you can simply type FILTER ON to see the filtered data. To turn if off, type FILTER OFF.
FIND:
Finds a unique string within a panel of data where “abc” is the string for which you are searching. If the specified string is found, the cursor moves to the first position of the found string. If the specified string is not found a message displays to indicate that is the case. You should be as specific as possible when using the FIND command to ensure the correct return.
The FIND command can be issued with the following keywords:
- NEXT Finds the next instance of the search string.
- PREV Finds the previous instance of the search string.
- FIRST Finds the first instance of the search string.
- LAST Finds the last instance of the search string.
- ALL Finds all instances of the search string.
The syntax is:
FIND <string> <keyword>
OR
FIND <keyword> <string>
where <string> is the text you want to find and <keyword> is a valid for the FIND command (NEXT, PREV, FIRST, LAST, or ALL). If none of the these keywords is explicitly specified, the default behavior is for the next instance of the search term to be found. If a keyword is the only parameter, it is treated as a search string. Two keyword parameters can coexist as long as one of them is identified by surrounding quotes as the search string. Otherwise, the occurrence of multiple instances of keywords causes an error. RFIND is used if you continue to hit PF5 to repeat.
FORM:
Reformats the display of a selected line item on a report panel such that each column becomes a row and values display in list format. To use the FORM command, type FORM in the option line, place your cursor on the line item of interest, and press Enter. The data for the selected line item will be displayed in list format showing column names and their associated values.
- To return to the original view from FORM view, press PF3.
- CSETUP functions are not accessible when in FORM view. Exit FORM view to access CSETUP functionality.
NROW n
- Displays the report for a subsequent row of interest where n is the number of rows (after to the currently displayed row) that you want to scroll forward (when viewing reports in FORM view). The default value of n is 1.
Note: The NROW command is only valid when viewing a report in FORM view.
PROW n
- Displays the report for a previous row of interest where n is the number of rows (prior to the currently displayed row) that you want to scroll back (when viewing reports in FORM view). The default value of n is 1.
Note: The PROW command is only valid when viewing a report in FORM view.
Normal View:
FORM View:
To view the next record, type NROW. To view the previous row, type PROW. To jump 3 rows, type PROW 3
INTERVAL/INTV:
An interval to Db2 Query Monitor is a slice of time that was monitored. Summary data is performance data that is summarized for each unique SQL statement that is executed in a Db2 Query Monitor interval. The values collected are totals and averages, accounting information, object information, Db2 commands, SQL/CA, etc.
Definition: A unit into which Db2 Query Monitor divides and stores data. Intervals have a start time, an end time, an interval number, and other information that uniquely identifies the interval. Data collected by Db2 Query Monitor is stored in performance history files on the mainframe on a per-interval basis.
The underlying data sets are VSAM files. The INTERVAL or INTV command allows you to navigate around the intervals. You can view one interval, you can merge intervals, you can go forward and backwards.
Interval navigation commands: Use these interval navigation commands to browse query activity in previous and current intervals when viewing activity summaries, exceptions, SQLCODES, or Db2 command activity.
o PREV Show the previous interval (the interval before the interval that is currently shown).
o NEXT Show the next interval (the interval after the interval that is currently shown).
o CUR Show the current interval.
Changing the PF keys for interval navigation:
Db2 Query Monitor sets the following PF keys by default:
• PF4 - PREV command
• PF6 - NEXT command
To modify these PF key settings, type KEYS in the option line of any Db2 Query Monitor window, press Enter, and change the PF key settings as appropriate.
You can view information about intervals and perform a variety of tasks such as selecting, keeping, or releasing intervals. When viewing activity summaries, exceptions, SQLCODES, or Db2 commands, type INTV in the command line and press Enter to access the Interval Selection panel. As you can see in the following example, this screen shows all Db2 packages sorted by CPU in descending order. Notice the interval start and end time? This started April 27, 2023 at midnight and shows us information related for the day.
Upon typing INTV and hitting enter, you will see all the intervals for Db2 Query Monitor. From this screen, you can select a different interval or select multiple interviews.
You can select a different interval or select a block of intervals (SS to SS). Using the example above, upon hitting enter will join all 5 intervals together.
![]()
Typing PREV will take you to the previous interval, typing NEXT will take you to the next interval. If you need to jump to the current interval, type CUR. While on this screen, you can (K) KEEP intervals so that they are not removed and you can (R) RELEASE them as well. If you want to see all the VSAM files being used for that interval, type D for DATASET.
PRINTX:
The PRINTX command takes a screen shot of a report and sends it to an output queue. The default output destination is the default output queue for your site. For example, if your site's default output class is configured to send output to the hold queue, the PRINTX command sends the currently displayed report to the hold queue. You can then view the output using SDSF.
You can change the output class designation for the PRINTX command by entering PRINTX S in the command line and pressing Enter.
Enter the desired output class in the New Output Class field and press Enter. The new output class is saved across sessions and remains in effect unless you change it. For appropriate output classes available at your site, check with your systems programmer. To change the class back to the default output message class, blank out the value in the New Output Class field. For a snapshot of the current display (print screen), the ISPF Print command can be used. The ISPF Print command writes output to the ISPF LIST data set. Following is an example of the output in SDSF:
RANGE:
The Filter Record Ranges panel enables you to select a start and end record number to display only records that fall within the specified range. The fields available on the Filter Record Ranges panel include:
Record Starting Number:
The record number at which you want the filter range to begin. Records before the Record Starting Number will not be included in the display.
Record Ending Number:
The record number at which you want the filter range to end. Records after the Record Ending Number will not be included in the display.
NOTE: Ranges are not saved across QM sessions.
The RANGE command is only valid for:
o View Current Activity (Option 3)
o View DB2 Command Activity (Option 4)
o View Exceptions (Option 5)
As an example, let’s look at Exceptions (Home Panel, option 5). Type RANGE to bring up the RANGE options panel:
If you only want to see the first 2000 records, type 2000 in the ENDING field and hit enter. When you hit enter, you’ll get a notice that the range has been set:
Hit PF3 one more time to see the updated screen of the first 2000 records.
SORT:
Sorting data is a bit complex with Db2 Query Monitor. There are a lot of options. Let’s start with basics.
SORT column_number direction:
Sorts data (on panels of scrollable or tabular data) by column where column_number is the number of the column by which you want to sort and direction can be either A (to sort data in ascending order) or D (to sort data in descending order). You can refer to columns only by the column number (not the column name). Column numbers are not displayed on the panel. The CMD column is column 1 and columns to the right are incremented sequentially.
Data can be sorted in ascending (A) or descending (D) order. To specify sort order, append the A or D to the end of the SORT command. The default is ascending (A). For example, to sort column 2 in descending order, type:
SORT 3 D (You can also type SORT 3 DESC)
… in the command line and press Enter. Data will be sorted by column 2 in descending order.
All of the following examples are possible:
SORT NAME
Sorts display data in ascending order based on the value in the Name column (when no dir value is
specified, the default sort order is ascending, thus SORT NAME and SORT NAME A are synonymous).
SORT NAME D
Sorts display data in descending order based on the value in the Name column.
SORT NAME DESC
Sorts display data in descending order based on the value in the Name column.
SORT NAME A CREATOR D
Sorts display data first in ascending order based on the value in the Name column and then sorts data
in descending order based on the value in the Creator column.
SORT NAME ASC CREATOR DESC
Sorts display data first in ascending order based on the value in the Name column and then sorts data
in descending order based on the value in the Creator column.
SORT 1 A
Sorts display data in ascending order based on the value in the Name column.
SORT 1 A CREATOR D
Sorts display data first in ascending order based on the value in the Name column and then sorts data
in descending order based on the value in the Creator column.
SORT 3 2 1
Sorts the display data first in ascending order based on the value in the Status column, then in
ascending order based on the value in the Creator column, and finally in ascending order based on the
value in the Name column.
Note: When you specify a column name using any of the above formats, you may enclose it in single
quotes, double quotes, or be without any quotes.
SORT/CSORT:
The SORT (or CSORT) primary command without any following text will bring up the main SORT panel:
CSORT and SORT are synonymous. The following fields appear on the Define Sort Columns panel:
Column Function
Enables you to jump to any of the CSET functions by typing in the appropriate number. The number
corresponding to the current option displays in this field.
Permanent View
Indicate whether the view you define is permanent or temporary. Valid values are:
• Y–View customizations are permanent.
• N–View customizations are temporary.
Stop Sorting
Indicates whether to stop sorting as specified. Valid values are:
• Y–Stop sorting.
• N–Continue sorting.
Cmd
Field where you specify the sort order.
Dir
Specifies the lexicographic order for the column. Valid values are:
• A–(Default) Values are listed in ascending order, smallest to largest.
• D–Values are listed in descending order, largest to smallest.
New
Displays the new CSORT view settings.
Old
Displays the previous CSORT view settings.
Column_Name
Shows the name of the column.
- Type A or D in the Cmd field next to the columns on which you want to base your sort.
- Press Enter. The new sort preferences are displayed in the New column.
- Press PF3 to return to the display panel.
Column display functions
Column display functions (CSETUP functions) enable you to rearrange report columns, change the width
of individual columns, and control the vertical ordering of columns.
CSETUP functionality enables you to:
• Rearrange report columns horizontally using the CFIX and CORDER options.
• Change the width of individual columns using the CSIZE option.
• Control the vertical ordering of columns using the CSORT option.
Additional column display functions enable you to:
• Scroll horizontally between columns, in both left and right directions.
• Scroll horizontally within a single report column while other report columns remain stationary on the
screen.
• Insert column numbers above each display column.
• Generate a ruler at the top of the report columns beneath the headings.
• Display an entire row-column data element.
The customizations, or views, you configure using CFIX, CORDER, CSIZE, and CSORT can be saved across
sessions.
The following syntax restrictions apply to the use of CSETUP functionality:
• Underlined text indicates the minimum acceptable abbreviation for each keyword.
• Variables are shown in italicized lowercase type.
• Keyword options are separated by vertical lines ( | ).
You will notice many optional sort commands like CFIX, CLEFT,CORDER,CREMOVE,CRESET,CRIGHT,CSIZE,CSETUP. These are all various option under SORT. For me, simply typing SORT will bring up the main sort panel. From there, you drill into all the various sorting subcommands. Here’s a quick listing of these subcommands:
CSETUP: Listing of functions
CFIX: Select columns to anchor on the left side of the report
CORDER: Modify the horizontal placement of unfixed columns
CSIZE: Customize the width and starting point of columns
CSORT: Select report columns to sort
CRESET: Restore to "as shipped"
CREMOVE: Remove all customizations, including original defaults
CLEFT: Scroll to the left
CRIGHT: Scroll to the right
Your sorting characteristics are stored in a file named “userid.cqm+version-number.isptlib.” After defining the way you want your panels sorted, send this file to another LPAR. When you activate Db2 Query Monitor, this will bring up the panels sorting in your new format.
In Summary: As you can see, there are many IPSF Primary Commands created for Db2 Query Monitor to assist you with your daily tasks. Many of these are also on the Db2 Query Monitor Web Client interface, but in a different format, such as an ICON. If you have ideas on ways to make Db2 Query Monitor better, you can submit an Aha!
#IBMChampion