Comparing dynamic SQL from different versions of Db2

In this task, you Explain and Compare access paths for dynamic SQL from earlier Db2 subsystems against the access path for that same SQL on the current version of Db2.

Collect dynamic SQL data on an earlier Db2 subsystem by activating a filter with SQL TEXT DATA = Y or D specified.
Execute the dynamic SQL on the earlier Db2 subsystem.
Create an archived trace data set:
1. Issue the SWITCH command for the output group containing the APSTMT class (or specify ALL output groups).
2. Examine the resulting archive task output to determine the name of the archive that was created (for use in the next step).

For detailed instructions, see the BMC AMI Apptune for Db2.

4. Run PSSEXTRJ on the earlier Db2 subsystem, using the archived trace data set that you created in Step 3.

PSSEXTRJ performs a dynamic Explain on all dynamic SQL captured from the earlier Db2 subsystem. The job requires you to enter the input parameters shown in PSSEXTRJ input parameters for an earlier version of Db2 subsystem.

Input parameter	Description
SSIDEX	Subsystem where the dynamic SQL is executed
SSID	Subsystem where Explain is performed
CREATOR	Value that you create to identify the dynamic SQL to be Explained Important You must specify the same value for CREATOR in Step 4, Step 7, and Step 8. 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

5. Run PSSHUNLD to unload the requested Explain records, which were created in PSSEXTRJ from history tables on the earlier Db2 version.

Before you submit PSSHUNLD, update member PSSHUNL1 to unload records from the appropriate history tables and where OBJOWNR is the same value that you entered for CREATOR in Step 4.

Important

The value that you enter for OBJOWNR must be identical to the value that you enter for CREATOR in the PSSEXTRJ and PSSCOMPJ jobs.

6. Load the history records from the earlier Db2 version into the appropriate history tables of the current Db2 version.

This step is required because Compare can read from only one set of history tables.

1. Update member PSSHLOD1 to load the history records from the earlier version of Db2 into the appropriate history tables of the current Db2 version.
2. Submit PSSHLOAD to load the records into the tables of the current Db2 version.

7. Run PSSEXTRJ on the current Db2 subsystem, using the same archived trace data set that you used when you ran the job on the earlier Db2 subsystem in Step 4.

PSSEXTRJ performs a dynamic Explain on all dynamic SQL that was captured from the earlier Db2 subsystem. The job requires you to enter the input parameters shown in PSSEXTRJ input parameters for the current Db2 subsystem.

Input parameter	Description
SSIDEX	Subsystem where dynamic SQL is executed
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 in Step 4, Step 7, and Step 8.}

8. Run PSSCOMPJ on the current Db2 subsystem to Compare the loaded history records from the earlier Db2 to the current Db2 history records.

The job requires you to enter the input parameters shown in PSSCOMPJ input parameters for the current Db2 subsystem.

Input parameter	Description
COMPNEW	Version of an Explain history (in this case, the Explain from the current Db2 subsystem) to be compared to an older history This value is typically set to 0.
COMPOLD	Version of an Explain history (in this case, the Explain from the earlier Db2 subsystem) to be compared to a newer 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 We recommend 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 We recommend 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 We recommend 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 in Step 4, Step 7, and Step 8.}
^{2 If your dynamic SQL comes from both packages and DBRMs, you must run PSSCOMPJ twice: the first time with OBJECT=DBRM and the second time with OBJECT=PACKAGE.}

Comparing dynamic SQL from different versions of Db2

On this page