Verify that your system adheres to the following prerequisites before offloading data to the Performance History Database.

Supported Db2 versions

  • Db2 Version 10 or later

Encoding schemes and conversion services

  • CQMDDL does not support ASCII encoding.
  • Conversion services must be in place to convert between EBCDIC (CCSID 0500) and UNICODE (CCSID 1208). You must define a path from the CCSID of the collected SQL text to CCSID 1208. Db2 Query Monitor does not support the changing of encoding schemes in the supplied DDL files.

Table requirements and limitations

  • The tables in the Performance History Database are designed to be used in JOINs with other Performance History Database tables or with Db2 Catalog tables.
  • The tables in the Performance History Database do not have a defined codeset. The table spaces in the Performance History Database are defined in EBCDIC with the exception of the three text table spaces, which are defined in UNICODE.
  • The TEXT_TIMESTAMP column in SYSTOOLS.CQM_SUMM_TEXT is reserved for internal use only.

Recovery of offloaded data

  • The Performance History Database uses standard SQL tables. If you need to recover tables in the Performance History Database, use your site's standard recovery procedures.

Uniqueness of offload data

  • The SMFID and CQM_SUBSYSTEM columns are required to ensure the uniqueness of offloaded data across multiple Query Monitor Subsystems or multiple z/OS® systems.
  • The DB2_SUBSYSTEM column is required to ensure uniqueness of offloaded data for a single z/OS system.
  • Db2 Query Monitor uses one interval record for each interval, regardless of the number of Db2 subsystems that are monitored. To ensure uniqueness, the offload program expands the interval record and generates an interval record for each interval by Db2 subsystem. For example, if Db2 Query Monitor monitors three Db2 subsystems during an interval, the offload creates three rows in the interval table even though only one record exists in the Performance History File.
  • SCQMSAMP member CQMINDEX identifies the columns in the child tables that relate them to their parent table.

Other considerations

  • If you split your offload process into multiple steps, you must ensure that the tables in your Performance History Database are not altered or changed between offload steps.
  • Performance History Files are closed after each interval is offloaded. If you offload multiple intervals, Db2 Query Monitor opens, extends, and closes the Performance History File each time another interval is offloaded. If you offload more than one interval per run, you should customize CQM@LDB2 and use a larger secondary quantity or omit the RLSE option.
  • All data is in DB2 UNLOAD format with the exception of SQL text data.
  • Static SQL is not duplicated to Performance History Files. Static SQL text is stored in Db2 System Catalog tables and can be retrieved from there using SQL. Db2 Query Monitor offloads static SQL metrics but does not collect SQL text for SQL statements.
  • The Db2 offload table, CQM_EXCEPTIONS, includes the CLIENT_ENDUSER column, a 128-byte field that enables the data offloaded from Db2 Query Monitor to be joined with data in IBM® OMEGAMON for Db2 Performance Expert on z/OS tables.