Configuring the offload to the performance history database

Before you load Db2 Query Monitor data to your performance history database for the first time, you must configure this offload process.

To configure the offload to the Performance History Database:

1. Tailor and run SCQMSAMP library member CQMCRDB to create the performance history database database.
2. Tailor and run SCQMSAMP library member CQMDDL according to the instructions in the member.
3. Tailor and run SCQMSAMP library member CQMDDLST according to the instructions in the member.
4. Tailor and run SCQMSAMP library member CQMBIND according to the instructions in the member to rebind the CQM plan.
5. Tailor and run SCQMSAMP library member CQMGRTB according to the instructions in the member to grant the appropriate privileges to authorization IDs that access the performance history database.
6. In SCQMSAMP member CQM@LDB2, tailor the CQMLOADP DD statement to include the appropriate offload parameters. For more information, see Offload parameters.

   Table 1. Parameter DDNAME to specify in CQM@LDB2

   DDNAME	Description
   CQMLOADP	Specifies the parameters that control the offload process.

   Tips:

   • Use the STARTING_INTERVAL, ENDING_INTERVAL, and INTERVAL_UNITS parameters to specify the appropriate interval selection criteria.
   • Use the DATA parameter to specify the appropriate data selection criteria. The DATA parameter enables you to select specific data types that are to be loaded to Db2 tables, including summary metrics, summary objects, summary SQL text, exceptions, SQLCODEs, and Db2 commands.
   • Use the LOAD parameter to specify the appropriate load options and control whether or not existing data is deleted from Db2 tables prior to loading new data.
   • If you modify table names or creators, be sure to make the corresponding required changes using the offload parameters that control table creator and table names:

     • DB2_COMMANDS_TBNAME

     • EXCEPTION_CALLS_TBNAME

     • EXCEPTION_HOSTV_TBNAME

     • EXCEPTION_OBJS_TBNAME

     • EXCEPTION_TEXT_TBNAME

     • EXCEPTIONS_TBNAME

     • OBJECTS_TBNAME

     • SQLCODE_DETAIL_TBNAME

     • SQLCODE_TEXT_TBNAME

     • SQLCODES_TBNAME

     • SQLTEXT_TBNAME

     • STATEMENT_TYPE_TBNAME

     • TBCREATOR

7. In SCQMSAMP member CQM@LDB2, include any DD statements that are required by the values you specified for the DATA parameter in the CQMLOADP DD.
   The DD statements you might need to include are shown in the table below.

   Table 2. DATA parameter and corresponding required DDNAMES

   Specified DATA value	Required DDNAME
   DATA(METRICS)	METRDATA
   DATA(OBJECTS)	OBJSDATA
   DATA(SQLTEXT)	TEXTCLOB
   DATA(EXCEPTIONS)	EXCPINDX EXCPHSTV EXCPOBJS EXCPCALL EXCPCLOB
   DATA(DB2CMDS)	DB2CDATA
   DATA(SQLCODES)	SQLCDATA SQLDDATA SQLCCLOB
   DATA(INTERVAL)	INTVDATA

8. In SCQMSAMP member CQM@LDB2, tailor the LOADCARD, CQMERROR, and DB2PARMS DD statements as appropriate for your offload process.

   Table 3. Additional DDNAMES to specify in CQM@LDB2

   DDNAME	Description
   LOADCARD	Specifies the generated load cards for the offload process.
   CQMERROR	Specifies the error file that is required if SQL text is not available. For more information, see Special considerations for CQMERROR DD.
   DB2PARMS	Specifies Db2 connection information, STEPLIB data, plan names. Supports connection to Db2 by the CQM@WDB2 program.

9. In SCQMSAMP member CQM@LSTM, tailor the CQMLOADP and DB2PARMS DD statements as appropriate for your offload process.

   Table 4. DDNAMES to specify in CQM@LSTM

   DDNAME	Description
   CQMLOADP	Specifies the DB2_SUBSYSTEM parameter appropriate for offloading data to statement tables.
   DB2PARMS	Specifies Db2 connection information, STEPLIB data, plan names. Supports connection to Db2 by the CQM@WDB2 program.

10. Tailor SCQMSAMP member CQMCOMM according to the instructions provided in the member to add table and column descriptions to the system catalog.