Comparing dynamic SQL from different periods of time

Use this procedure to Explain and Compare access paths for dynamic SQL that was collected for different periods of time from a single subsystem of your choice.

Collect dynamic SQL data on a Db2 subsystem by activating a filter with SQL TEXT DATA = Y or D specified.
Execute the dynamic SQL on a subsystem.
Issue the SWITCH command for the output group containing the APSTMT class (or specify ALL output groups). Examine the resulting archive task output to determine the name of the archive created. This archive is input to the PSSEXTRJ jobs.For detailed instructions, see the
BMC AMI Apptune for Db2

.

Run PSSEXTRJ on the subsystem, using the archived trace data set that was created in Step 3.PSSEXTRJ performs a dynamic Explain on all dynamic SQL that is captured from the subsystem you specified. The job requires you to enter the input parameters shown in Step 3.

Input parameter	Description
SSIDEX	Subsystem where dynamic SQL is run
SSID	Subsystem where Explain is performed
CREATOR¹	Value that you create to identify the dynamic SQL to be Explained This value can be 1 to 8 characters in length.
PLAN	Product plan; for example, DAAPRDD1
RETAIN	Number of history records to be retained for the PROGRAM

^{1 You must specify the same value for CREATOR for PSSEXTRJ in Step 4, Step 6, and for PSSCOMPJ in Step 7.}

After a period of time, statistics on the object are likely to have changed. Because of this, you might want to run the Explain again on the same trace data set, in order to Compare the access paths.
Run PSSEXTRJ on the specified subsystem, using the same archived trace data set that was used when you ran the job in Step 4.PSSEXTRJ performs a dynamic Explain on all dynamic SQL that was captured from the subsystem you specified. The job requires you to enter the input parameters shown in PSSEXTRJ input parameters.

Once the product history tables contain at least two entries, you can Compare the access paths from those periods of time. Run PSSCOMPJ on the specified subsystem. The job requires you to enter the input parameters shown in the following table.

Input parameter	Description
COMPNEW	Version of an Explain history (in this case, the most recent Explain) to be compared to a later history This value is typically set to 0.
COMPOLD	Version of an Explain history (in this case, the earlier Explain) to be compared to an earlier history This value is typically set to -1.
CREATOR¹	Value that you create to identify the dynamic SQL to be Explained This value can be 1 to 8 characters in length.
OBJECT²	Type of object to be Compared: PROGRAM containing the dynamic SQL
NAME	Object name of first PROGRAM BMC Software recommends that you specify % (percent sign) for this parameter, in order to process all dynamic SQL that was Explained from the trace data. This is because trace data sets usually have a large number of PROGRAMs that you would need to process.
COLLID	Collection ID of object BMC Software recommends that you specify % (percent sign) for this parameter, in order to process all dynamic SQL that was Explained from the trace data.
VERSION	Version of object BMC Software recommends that you specify % (percent sign) for this parameter, in order to process all dynamic SQL that was Explained from the trace data.

^{1 You must specify the same value for CREATOR for PSSEXTRJ in Step 4, Step 6, and for PSSCOMPJ in Step 7.}

^{2 If your dynamic SQL comes from both packages and DBRMs, you must run PSSCOMPJ twice: Once with OBJECT=DBRM and a second time with OBJECT=PACKAGE.}

Comparing dynamic SQL from different periods of time

On this page