Sample library members

The Db2 Query Monitor sample library (SCQMSAMP) includes the following sample jobs.

Table 1. SCQMSAMP library members
SCQMSAMP member Description
CQM The CLIST that allocates the libraries used for Db2 Query Monitor.
CQM#CTLF Creates the Db2 control file.
CQM#RDSC Provides sample source code for the batch reporting process for CPU utilization by dynamic SQL. CQM#RDSC is optional and can be used to modify existing batch reports. The reports can be executed from the .QUERY data set (SCQMQRY) without having to alter the source programs.
CQM#RDSE Provides sample source code for the batch reporting process for dynamic SQL statement elapsed. CQM#RDSE is optional and can be used to modify existing batch reports. The reports can be executed from the .QUERY data set (SCQMQRY) without having to alter the source programs.
CQM#RDSG Provides sample source code for the batch reporting process for dynamic SQL statement getpage. CQM#RDSG is optional and can be used to modify existing batch reports. The reports can be executed from the .QUERY data set (SCQMQRY) without having to alter the source programs.
CQM#RSSC Provides sample source code for the batch reporting process for CPU utilization by static SQL. CQM#RSSC is optional and can be used to modify existing batch reports. The reports can be executed from the .QUERY data set (SCQMQRY) without having to alter the source programs.
CQM#RSSE Provides sample source code for the batch reporting process for static SQL statement elapsed. CQM#RSSE is optional and can be used to modify existing batch reports. The reports can be executed from the .QUERY data set (SCQMQRY) without having to alter the source programs.
CQM#RSSG Provides sample source code for the batch reporting process for static SQL statement getpage. CQM#RSSG is optional and can be used to modify existing batch reports. The reports can be executed from the .QUERY data set (SCQMQRY) without having to alter the source programs.
CQM#RSSO Provides sample source code for the batch reporting process for summary of objects. CQM#RSSG is optional and can be used to modify existing batch reports. The reports can be executed from the .QUERY data set (SCQMQRY) without having to alter the source programs.
CQM@CNVT A LOAD utility job that loads the data from the Db2 Query Monitor Version 3.2.0 Performance History Database to the Db2 Query Monitor Version 3.3.0 tables. The utility statements in this job use the cross-loader function of the LOAD utility to copy data directly from the old tables to the new tables. This job must be run first and may be run repeatedly if necessary. If a failure occurs on one of the LOAD utility statements, rerunning the job will allow DSNUTILB to restart the utility at the proper place in the utility stream.
CQM@LDB2 Specifies parameters and DDNAMES needed to run the offload process.
Important: The CQM@LDB2 job is provided as an example. You must edit this job to suit your needs. In the example CQM@LDB2 job, the REPLACE STATISTICS parameter, which is passed to the Db2 LOAD utility, is run. You might want to use the RESUME parameter so the data is appended in the tables. The offload process closes the file at the end of each interval offload. If you offload multiple intervals, the offload process closes the file and reopens it with extend between each interval. If you are offloading more than one interval per run, we recommend that you customize the CQM@LDB2 job with a larger secondary quantity or without the RLSE option.
CQM@LIDA Loads performance history data into IBM® Db2 Analytics Accelerator.
Important: The CQM@LIDA job is provided as an example. You must edit this job to suit your needs.
CQM@LSTM Updates the statement type table (CQM_STMT_TYPES). The inputs to the CQM@LSTM program are updated via the PTF maintenance stream when IBM adds SQL statement types.
CQM@NOCP Sets the offload table spaces to NOCOPYPEND.
CQM@NRPB Provides a sample of a Db2 bind and grant that gives Db2 access to use Db2 Query Monitor batch reporting for z/OS®.
CQM@NRPT Executes batch reports.
CQM@WDB2 This program performs the Db2 Query Monitor data offload. This program verifies that all ofDb2 Query Monitor's Db2 tables exist in the Db2 catalog with the proper columns names and types. The CQM@LSTM program verifies that the statement type table exists but does not perform verification of the columns within the statement type table. If a space error is encountered on any of the output data sets for the CQM@WDB2 program, a return code will be generated. Data will still be off-loaded for other data types regardless of the out-of-space condition.
CQMADBI Provides a sample EXEC to add Db2 Query Monitor to Db2 Administration Tool Launch Pad.
CQMBIND Binds the CQM plan.
CQMCAE The CAE Agent started task.
CQMCAESV Starts the CAE Server on USS.
CQMCAEWD Starts the Watchdog process.
CQMCCERT Creates a new certificate inside the certs directory of the directory defined by CQM_VAR_HOME. After you run CQMCCERT, you must then run CQMICERT on the newly created certificate to import the certificate. This enables the CAE Server to use the certificate you created with CQMCCERT.
CQMCHMOD

Sets the file permissions for the CAE Server on USS.
CQMCLIST The CLIST that allocates the libraries used for Db2 Query Monitor.
CQMCMDS Provides a sample CLIST that holds the commands processed by the Interval Data Sets panel.
CQMCMGRT Migrates user customizations for the CAE Server from the previous version to the current version.
CQMCNTFL Builds the VSAM control file used for holding Db2 subsystem ID information.
CQMCOMM Adds table and column descriptions to the Db2 catalog.
CQMCPRMS Provides a sample CQMCPRMS for CAE Agent startup.
CQMCRDB Creates the Performance History Database, the Db2 database that is used for offloading data from Db2 Query Monitor.
CQMCSDDL Creates the staging tables used by the integration between Db2 Query Monitor and IBM query workload tuning tools.
CQMCSMGT Migrates the Db2 Query Monitor staging tables.
CQMCUNPX Unpaxes the configuration and data files to the CQM_VAR_HOME directory.
CQMCUPPT Extracts updates from CQMCPXPT to the configuration and data files that were installed to the CQM_VAR_HOME directory during the original Db2 Query Monitor 3.3.0 installation. To apply the updates, edit and run CQMCUPPT according to the instructions in the member.
Note: The CQMCUPPT job is cumulative and only needs to be run once to install the current maintenance level.
CQMDDL Creates the Performance History Database objects.
CQMDDLST Creates additional Performance History Database objects for statement types and descriptions.
CQMDROP Drops the database objects associated with the Performance History Database.
Important: Run this only if you want to re-create the Db2 Query Monitor objects. If you want to drop the Performance History Database, you must uncomment the DROP DATABASE command in this member and comment out all of the DROP TABLESPACE commands. Before dropping the Performance History Database, you should be aware that such action will affect other Db2 tools that use this database. Verify that dropping the Performance History Database will not adversely affect any other Db2 tools at your site that you do not intend to affect by this action.
CQMGRTB Grants the appropriate privileges to authorization IDs to access the Performance History Database.
CQMCCERT Creates a new certificate inside the certs directory defined by CQM_VAR_HOME.
CQMECERT Exports certificates from the certs directory inside the directory defined by CQM_VAR_HOME.
CQMHVBLD This JCL assembles and links a user-defined function that translates numeric host variable text into readable format. After the CQMHVBLD job completes and before the WLM job starts, the following message is displayed: CONCATENATE CQM AND FEC LOADLIB TO THE STEPLIB OF WLM JOB.
CQMICERT Imports certificates from the certs directory inside the directory defined by CQM_VAR_HOME. The name of the file in the certs directory is used as the host name in the certificates store for the CAE Server.
CQMIADD A REXX program to define accelerator shadow tables. Before running CQMIADD, you must first have an existing Db2 table. This REXX program is used by the CQMIDAD1, CQMIDAM1, CQMIDAM2, and CQMIDAM3 scripts.
CQMIDAA
CQMIDAA is an ISPF edit macro that modifies CQMDDL to be compatible with Analytics Accelerator. When you run CQMIDAA without parameters, the resulting DDL creates accelerator shadow tables. When you run CQMIDAA with parameters, the resulting DDL creates accelerator-only tables. To define accelerator-only tables, the macro requires the database name and the Analytics Accelerator name. The edit macro modifies the Performance History Database in the following ways:
  • Removes all WITH DEFAULT clauses
  • Converts SQL statement text tables CQM_SUMM_TEXT, CQM_EXCP_TEXT, and CQM_SQLCODE_TEXT tables to the EBCDIC encoding
  • Changes the SQLTEXT column to VARCHAR(32000)
  • Changes the ROW_ID column to SMALLINT
  • Changes the buffer pool for the CQMMT1, CQMET1, and CQMST1 table spaces to BP32K
  • For accelerator-only tables, removes all CREATE TABLESPACE statements and add the IN ACCELERATOR clause to all tables
The EBCDIC code page does not support all characters available in the Unicode code page. The conversion of the Unicode SQL Statement text collected from Db2 to EBCDIC might result in data loss. Dynamic SQL texts that are more than 32K are truncated if you load using Analytics Accelerator.
CQMIDAAL Loads data from the Db2 Query Monitor 3.3.0 Performance History Database to the Db2 Query Monitor 3.3.0 accelerator shadow tables.
Important: When migrating from 3.2.0 accelerator shadow tables to 3.3.0 accelerator shadow tables, run CQMMIDAA before you run CQMIDAAL.
CQMIDAD1 Drops accelerator shadow tables and database. Use this any time you want to drop accelerator objects.
CQMIDAD2 Drops accelerator only tables and database. Use this any time you want to drop accelerator-only objects.
CQMIDAM1 Moves data from old accelerator schema to new accelerator schema (accelerator shadow or accelerator-only tables) as long as the number and sequence of columns in old and new are the same. Before running CQMIDAM1, make sure the new target database (#DBNEW#) and offload tables have been created using CQMDDL.
  • If the target tables are accelerator-only tables, submit only the first step.
  • If the target tables are accelerator shadow tables, submit the entire job. In this case, new accelerator shadow tables are loaded in ONLY mode (only accelerator tables are populated, Db2 tables remain empty).
  • This job does not drop old tables. If you want to drop old tables and they are Db2 / accelerator-shadow tables, then use CQMIDAD1. If the tables are accelerator-only tables, use CQMIDAD2.
CQMIDAM2 Moves offloaded data from Db2 schema to accelerator schema.
CQMIDAM3 Migrates offloaded data from accelerator schema created prior to PH17430 (#DBOLD#) to newly created accelerator schema (#DBNEW#) for support of anomaly detection and data collection.
Note: Db2 Query Monitor does not currently support altering the table structure of accelerator tables. CQMIDAM3 must be used to create the new database with offload tables in the new column format and to migrate your data.
CQMIDEL A REXX program to drop accelerator shadow tables. This REXX program is used by the CQMIDAD1, CQMIDAM1, CQMIDAM2, and CQMIDAM3 scripts.
CQMIENBL A REXX program to enable query acceleration for the accelerator shadow table by changing its state. This REXX program is used by the CQMIDAD1, CQMIDAM1, CQMIDAM2, and CQMIDAM3 scripts.
CQMINDEX
Important: The indexes documented in SCQMSAMP library member CQMINDEX are as of Db2 Query Monitor V3.2. No further development has been made to them.
Defines indexes for tables in the offload database, to help to speed up the queries you run against the Performance History Database. These indexes identify the relationships between the Performance History Database tables. You can run test queries on your Performance History Database to identify performance optimization requirements. You can then modify and run CQMINDEX to ensure that the indexes you define are best suited to your environment.
CQMINTER Allocates the intervals data set.
CQMLDTAS A REXX program to load data from the source tables in Db2 into the corresponding accelerator shadow tables.
CQMMIDAA Migrates data from Db2 Query Monitor V3.2 accelerator-only tables to Db2 Query Monitor V3.3 accelerator-only tables. When you run CQMMIDAA and then CQMIDAAL, migrates data from Db2 Query Monitor V3.2 accelerator shadow tables to Db2 Query Monitor V3.3 accelerator shadow tables.
Important: Before submitting CQMMIDAA, make sure that the V3.3 offload Db2 tables are empty.
CQMMSTR Stops the Master Address Space. The Master Address Space can only be stopped if all products that use it have been stopped. If products are using the Master Address Space, it will not be stopped and Db2 Query Monitor issues the message CQM1044E.
CQMPARMS Provides a sample of CQMPARMS for Db2 Query Monitor startup.
CQMPROC The Db2 Query Monitor subsystem started task.
CQMPROFS Allocates the CQMPROFS data set for Db2 Query Monitor.
CQMPYOM Populates Db2 Query Monitor source files in the OMEGAMON® distribution.
CQMQRY01 Verifies Performance History Database tables.
CQMUDDDL Creates a stored procedure for Db2 Query Monitor.
CQMUDFHV The source for the assembler program that implements the user-defined function to format the contents of the HOSTV_TEXT column.
CQMUTS1 Migrates the following segmented table spaces for offload tables to partition-by-growth table spaces: CQMMET, CQMINT, CQMOBJ, CQMEXP, CQMEXC, CQMEXH, CQMEXO, CQMDBC, CQMSQS, CQMSQD.
CQMUTS2 Migrates the following segmented table spaces for statement type tables to partition-by-growth table spaces: CQMSTYPE.
CQMUTS3 Migrates the following segmented table spaces for staging tables to partition-by-growth table spaces: CAPTS, STMTTS, OBJTS.