How can I maximize performance when extracting Apptune data for my PADB tables?

Consider these points when establishing and maintaining Performance Analyzer Database (PADB) tables:

• Be aware that customers can create one or all of the PADB tables and/or any number in between. In addition, if the PADB Starter dialog is used, the customer is able to select all or only specific columns to be maintained in the tables. The selection of columns can reduce storage use since most columns are nullable. The PADB Starter Dialog is available from the Administration Menu.
• A nightly batch process is recommended, but select a run-cycle time frame that is reasonable for your shop's resources (CPU, DASD, memory, etc.). 
• Extract and load one table at a time. This approach can reduce the number of sorts (and the CPU consumed by them) performed in the DMDBMERG extract program as compared to extracting for multiple tables in a single step.
• The recommended input is archive files. There can be issues with using the active log files as described in Can the Apptune Performance Advisor Database (PADB) be loaded with SMF data? 

To ensure completeness of data for a nightly PADB process: 

1. After midnight, ensure that the Apptune unloads complete for each DB2.  The unloads will write the collected data from Apptune memory to the active log files.
2. After all unloads complete, then run the DOMBSWIT utility or issue the "SWITCH ALL" command to the DBC. The utility or command will start NGL archive tasks to copy records in the active log files to archive datasets.  All archive log tasks should complete before starting the nightly PADB batch cycle.  The default archive log started task name is NGLARCH. For more information on the Apptune SWITCH utility, refer to online Apptune documentation:  https://docs.bmc.com/docs/display/ASQ12100/DOMBSWIT+utility
3. Failure to allow the above processes to complete might result in data missing from the tables.

Consider deciding whether or not to maintain all three of the below tables. Resources can be saved if analysis needs can be met with fewer tables.

•   STMT_STATISTICS - The most granular of the three tables.
•   WKLD_STATISICS  -  The same as STMT_STATISTICS but aggregated by collection keys (workload identifiers such as Plan, Program, User, etc.)
•   STMT_SUMMARY  -  The same as STMT_STATISTICS but has fewer columns, therefore less detail.

Configure Output Groups to hold the Data Classes as suggested below:

• Assign APSTACC and APSTACCS together in an output group.
• Assign APOBJECT in an output group by itself.
• Assign APERROR  in an output group by itself.
• Assign APSTMT     in an output group by itself.

 
With Apptune Data Classes separated as indicated above, individual table extracts can be run in parallel. As shown below, the extract jobs or steps for the tables within each group should be run serially. The groups can run in parallel.
 

PADB sample schedule

Group 1 APSTACC/S	Group 2 APOBJECT	Group 3 APERROR	Group 4 APSTMT
STMT_STATISTICS	OBJ_STATISTICS	STMT_ERRORS	STMT_TEXT
DAILY_STMT_STATISTICS	DAILY_OBJ_STATISTICS	STMT_EXCEPTIONS
WEEKLY_STMT_STATISTICS	WEEKLY_OBJ_STATISTICS	STMT_EXCEPTIONS_HV
MONTHLY_STMT_STATISTICS	MONTHLY_OBJ_STATISTICS	STMT_EXCEPTIONS_OB
WKLD_STATISTICS	STMT_STATISTICS_OB
DAILY_WKLD_STATISTICS	DAILY_STMT_STATISTICS_OB
WEEKLY_WKLD_STATISTICS	WEEKLY_STMT_STATISTICS_OB
MONTHLY_WKLD_STATISTICS	MONTHLY_STMT_STATISTICS_OB
STMT_SUMMARY
DAILY_STMT_SUMMARY
WEEKLY_STMT_SUMMARY
MONTHLY_STMT_SUMMARY