Loading Apptune data into Db2 tables

To load Apptune data into Db2 tables

1. Review the collection options for the filter that is active on your Db2 subsystems.The main data that you extract from the archived data sets affects the following collection options:
   • (optional) Accounting data
   • (optional) Object data
   • SQL text data
   • (optional) SQL error data

2. Review all Collection Key options set to Y. The settings for the collection keys determine how the product summarizes data. The resulting data that is collected and summarized serves as the input to the PSSBLOAD job.

   • If the Monitor SQL option is set to N for a set of filtering criteria, no data is extracted for that criteria.
   • If Ignore dynamic SQL text literals is set to Y, Apptune writes only the first SQL text record (BMC IFCID 005) in that interval to the data set. Metrics for all of the dynamic SQL statements containing the same text, but with different literal values, is summarized for the enabled collection keys. The SQL text record that is loaded to the SQLTXT table contains the literals from the first SQL text statement.
   • If Sample data with extrapolation is set to Y, the sampled data is loaded into the Db2 tables.
   • If User ID collection is set to Y, you can use the data loaded into the Db2 tables from PSSBLOAD for audit reporting.
   • If Dynamic SQL detail is set to Y, CURSOR statements have three IFCID 307 records: one each for the OPEN, FETCH, and CLOSE. When this information is loaded into the Db2 tables, it is displayed as three rows in the METRICS table. If Dynamic SQL detail is set to N, CURSOR statements have only one IFCID 307 record.

   Warning

   Note

   The data in the archived data sets reflects the summarization and collection options or filters that are in effect at the time the data is collected.

3. Run the PSSBLOAD job.The job requires you to customize the tokens listed in the following table:

   Input parameter	Description
   JOBNAME	Name of job
   ACCT	Job account number
   MSGCLASS	Job message class
   CLASS	Job class
   BMC_BBLINK	Name of load data set that contains the PSSBLOAD program
   TRACE	Name of archived data set
   TRACE2	Name of second archived data set You can concatenate as many data sets as you want.
   DB2LOAD1_DSN	Name of load data set for SORTIN
   DB2LOAD2_DSN	Name of load data set for SORTOUT
   METRICS_DSN	Name of load data set for metrics data
   SQLTXT_DSN	Name of load data set for SQL text data
   REF_DSN	Name of load data set for object reference data

4. Run the PSSBPROC job.The job requires you to customize the tokens listed in the following table. PSSBPROC uses the Db2 load facility, but you can customize the information to use any utility that loads Db2 tables.

   Input parameter	Description
   JOBNAME	Name of job
   ACCT	Job account number
   MSGCLASS	Job message class
   CLASS	Job class
   DSNLOAD	Name of the Db2 DSNLOAD data set
   SSID	Name of Db2 subsystem
   RESUME_SPEC	Whether tables are to be loaded into an empty or non-empty table Specify one of the following values: REPLACE REUSE resets table to be empty and then loads data into the empty table. RESUME YES loads data into a non-empty table. Success Tip BMC Software recommends that you use REPLACE REUSE in order to limit the size of your tables.
   METRICS_DSN	Name of SYSREC data set for metrics data
   SQLTXT_DSN	Name of SYSREC data set for SQL text data
   REF_DSN	Name of SYSREC data set for object reference data
   CREATOR	Name of table creator for DB2LOAD files
   Vvr	Name of version release number for tables
   DATABASE_NAME	Name of database
   TS1_NAME	Name of table space for metrics table
   TS2_NAME	Name of table space for SQL text table
   TS3_NAME	Name of table space for object reference data
   DSNEXIT	Name of Db2 DSNEXIT data set

5. Customize and run your own SQL queries on the performance data in the database.For more information, see the sample queries contained in the PSSBQURY member of the hlq.llqSAMPdata set.