Plan Analyzer Package listing mismatch with RC/Query and SYSIBM.SYSPACKAGES.
Article ID: 64625
Updated On:
Products
Issue/Introduction
The PROFILE function in Plan Analyzer for DB2 for Z/OS(PPA) has an option called "Package version(s) default" where the setting can be "A" or "L" (A=All, L=Latest version). This can alter the information that is produced by the report. In particular if "L" or "the latest version only" is used only one record will be reported. If the requirement is to see all versions then this will be misleading unless the user understands that there is an option in play in the PROFILE to alter the records returned.
Environment
Release: R20
Component: PPADB2
Cause
When the PPA profile function (menu PF Profile or PROFILE command) , Package version default value, is set to "L" for LATEST, the generated report is limited to only the most recent version of a package.
Resolution
Setting the Package version default option to "A" for ALL will cause all packages to be listed as in RC/Query.
From the PPA PROFILE(or menu item PF Profile) , Option 2 panel:
--------------- PPA Profile Variables -------------- yyyy/mm/dd hh:mm
COMMAND ===>
General Execution Parameters:
Qualifier ON/OFF default ===> N (Y=ON or N=OFF)
Explode SQL ON/OFF default ===> Y (Y=Exploded or N=Shrunk)
Strategy List Mode default ===> L (L=List vers or N=Do not list vers)
Expert Mode default ===> Y (Y=Expert, N=Regular)
Display Batch Processor Input
Screen default ===> Y (Y or N)
Package version(s) default ===> A (A=All, L=Latest version) <-----------------------'A' is set for all packages
Package versioning method ===> A (N=None, A=Auto, O=Creation)
Package replace version default ===> Y (Y or N)
Host Language default ===> COB (ASM, COB, PLI, C, or SPF)
Update Statistics Qualifier ===> SYSIBM
Process Remote Packages ===> Y (Y or N)
Floating Point Format ===> ENG (SCI, ENG, or MET)
Use New Report Layouts ===> Y (Y or N)
Use Stored Proc. for Explain ===> N (Y or N)
Package Copies Display ===> N (Y=Show copies,N=Do not show copies)
Suppress Expl Prof Select panel ===> N (Y=Suppress, N=Do not suppress)
From the online help for this field:
Field: Package Version(s) default
Description: Indicate whether you want to see just the latest
package version displayed.
Values: L (Default) Display only the latest version for a
package.
A Display all versions for a package.
Note above that the default is "L" not "A"!!!
Sample of the PPA Package/List (PK/L) report with Package Version(s) default set to "A". All the versions are listed.
----------- PPA Package List ----------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
LINE 1 OF 3 >
Option ===> PK Report ===> L
Collection ===> TESTPRGA Package ===> * Where ===> N
----------------------------------------------------------------- authid1
Location ===> LOCAL DB2 SSID ===> ssid Version ===> 121M500
CMD PACKAGE COLLECTION VERSION COPYID OWNER
____ TESTPRGA TESTPRGA 2023-11-29-05.10.54.663986 CURRENT authid1
____ TESTPRGA TESTPRGA 2023-12-20-05.41.57.701514 CURRENT authid1
____ TESTPRGA TESTPRGA 2023-12-20-05.50.54.979999 CURRENT authid1 <----------this is the latest one based on the timestamp.
******************************* BOTTOM OF DATA ********************************
Set the Package Version(s) default setting to "L" and only the latest one is listed.
--------------- PPA Profile Variables -------------- yyyy/mm/dd hh:mm
COMMAND ===>
General Execution Parameters:
Qualifier ON/OFF default ===> N (Y=ON or N=OFF)
Explode SQL ON/OFF default ===> Y (Y=Exploded or N=Shrunk)
Strategy List Mode default ===> L (L=List vers or N=Do not list vers)
Expert Mode default ===> Y (Y=Expert, N=Regular)
Display Batch Processor Input
Screen default ===> Y (Y or N)
Package version(s) default ===> L (A=All, L=Latest version) <-----------------------'L' is set for latest package
Package versioning method ===> A (N=None, A=Auto, O=Creation)
Package replace version default ===> Y (Y or N)
Host Language default ===> COB (ASM, COB, PLI, C, or SPF)
Update Statistics Qualifier ===> SYSIBM
Process Remote Packages ===> Y (Y or N)
Floating Point Format ===> ENG (SCI, ENG, or MET)
Use New Report Layouts ===> Y (Y or N)
Use Stored Proc. for Explain ===> N (Y or N)
Package Copies Display ===> N (Y=Show copies,N=Do not show copies)
Suppress Expl Prof Select panel ===> N (Y=Suppress, N=Do not suppress)
The result is that only one is listed......the most recent version.
----------- PPA Package List ----------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
LINE 1 OF 3 >
Option ===> PK Report ===> L
Collection ===> TESTPRGA Package ===> * Where ===> N
----------------------------------------------------------------- authid1
Location ===> LOCAL DB2 SSID ===> ssid Version ===> 121M500
CMD PACKAGE COLLECTION VERSION COPYID OWNER
____ TESTPRGA TESTPRGA 2023-12-20-05.50.54.979999 CURRENT authid1
******************************* BOTTOM OF DATA ********************************
RC/Query for Db2 for Z/OS
The Package/List (PK/L) report in RC/Query does not have an option to obtain just the latest package version so all are listed.
This screen is two panels to the right(PF11) to show the package version.
RQPKL -------- RC/Q Application Package List -------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> PK Option ===> L Where => N
Package ===> * > Owner ===> * >
Collection ===> TESTPRGA > Grantor ===> * >
Version ===> * >
Loc: LOCAL ---------- SSID: ssid ----------authid1 - LINE 1 OF 3 < >
CMD NAME TYPE VERSION
________ TESTPRGA 2023-11-29-05.10.54.663986
________ TESTPRGA 2023-12-20-05.41.57.701514
________ TESTPRGA 2023-12-20-05.50.54.979999
******************************* BOTTOM OF DATA ********************************
In order to alter this result it is also possible to use an EQF(Extended Query).
In the Where => N field above place a "Y" and on the next screen enter something like this:
20.0.12 ---------------- SQL Selection Panel --------------- yyyy/mm/dd hh:mm
Command ==> SCROLL ===> CSR
Name ==> TEMP Share => N Default => N
Description => Panel : RQPKL View SQL => N
Confirm Replace ==> Y Userid : authid1
Where Clause:
01 AND A.VERSION = ( SELECT MAX ( A.VERSION ) FROM SYSIBM.SYSPACKAGE A
02 WHERE A.COLLID = 'TESTPRGA' AND UPPER ( A.VERSION ) LIKE '%' )
PF3 back and the result after PF11 two screens to the right is:
RQPKL -------- RC/Q Application Package List -------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> PK Option ===> L Where => S TEMP
Package ===> * > Owner ===> * >
Collection ===> TESTPRGA > Grantor ===> * >
Version ===> * >
Loc: LOCAL ---------- SSID: ssid ----------authid1 - LINE 1 OF 1 < >
CMD NAME TYPE VERSION
________ TESTPRGA 2019-12-20-05.50.54.979999
******************************* BOTTOM OF DATA ********************************
Additional Information
Feedback
