This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.
APPTUNE for DB2
SQL Performance for DB2, BMC Performance for SQL
Converting from LOADxxx reporting to Performance Advisor Database
(1) How do I convert LOAD307 report (that collects top 500 CPU users) and LOAD007 to PADB process?
DMDBMERG utility is used to maintain Performance Advisor tables. You can use the PADB starter dialog to setup customized JCL (information in the link below) or use the DMDBMERP or DMDBMERN sample to customize your own.
Performance Advisor BBSAMP members
Setting up Performance Advisor database
Configuring automatic data collection
Loading data into the Performance Advisor database
The corresponding PADB tables to load are: (1) STMT_SUMMARY (interval based) for IFCID 307 (2) STMT_ERRORS for IFCID 007.
(2) Only top 5000 users are loaded into the DB2 table today. Can I restrict the output to only the top 5000 users using PADB?
The DB2LOAD output created by DMDBMERG is specific to the table being loaded. This means that the order and summarization are determined by each report's qualifiers. These qualifiers match the unique indexes that are created in sample member PASETUP.
Currently, DMDBMERG does not have a way to restrict the output to load the top 5000 users only.
Note: OUTLIM does work in DMDBMERG, but it is only limit the output after after all records have been ordered and summarized.
(3) Do I need to maintain daily, weekly and monthly tables?
The daily, weekly and monthly tables store aggregated data. These tables use less space than interval tables, depending on the interval duration. The way that summarization works in the PADB is that DMDBMERG takes the historical data from LOADIN and merges it with the 'new' (i.e. day -1) data to create the summary. Then a LOAD replace will load the rows. If you do not have the LOADIN, this prevents the aggregation from taking place and results in duplicate records.
(4) PADB generates a large number of records to be loaded and stored in a DB2 table. How can reduce the size of the tables?
We recommend using DAILY_STMT_STATISTICS etc. instead of the DAILY_STMT_SUMMARY tables. To further reduce the size of the DB2 tables, you can limit the columns that are maintained in these tables by using the PADB starter JCL dialog (from the Administration menu).
In the PADB starter JCL dialog, you can select the table(s) you want to maintain and see a list of columns in each table. From the column list, you can exclude any columns that you don’t want to populate in the DB2 table (you cannot exclude columns that are keys or being used by an advisor that you indicated was being used). After you have selected the tables and columns to maintain, JCL will be customized and placed into the starter JCL output data set. There will be separate members created for loading each table. The $$README member in the starter JCL data set contains information on the JCL members that were customized. When you run one of the customized members and you have excluded columns, it won’t reduce the DB2LOAD output, but it will reduce the DB2 table size since the excluded columns are loaded as nulls.
(5) I ran SQMCACTX (IFCID 307) report with DB2LOAD, SORT and OUTLIM to produce top n users. Is DB2LOAD supported with this report?
No. Apptune for DB2 User guide documents the following:
"The DB2LOAD statement is supported only when used with one of the LOADxxx reports described in Table 1. The content of the report determines the format of the data produced and the results are unpredictable when used with a report other than a LOADxxx report. It is recommended that the DB2LOAD statement and parameter be used only with the LOADxxx reports. "
"Use the recommended LOAD* reports to avoid generating duplicate column names. Duplicate column names cause an SQL error. See the table in DB2LOAD statement and parameter for a list of the LOAD* reports."