Report with Version

Products

• Plan Analyzer for DB2 for z/OS

Issue/Introduction

Creating reports using a specific version

We would like create reports with version, not only with ALL or LATEST. e.g. VERSION = (A11O000658) 

Environment

z/os DB2

Resolution

A way to get Plan Analyzer to do what you’re looking for and better still to do so using a batch based process. 
We can also generate and execute the FREE PACKAGE commands this way too. 

The key to the solution is to use an Extended Query Facility (EQF) in Plan Analyzer to have the Package List report 
include only the Package Versions you want to process. 
The following base Package report which includes 3 Package Versions 
(you could of course use any Search conditions to get the list of objects you’re looking for): 


20.0 ----------- PPA Package List ----------- 2018/12/31 13:36 
COMMAND ===> SCROLL ===> CSR 
LINE 1 OF 3 > 
Option ===> PK Report ===> L 
Collection ===> RDPCR190% Package ===> RAAZPOA Where ===> N 
----------------------------------------------------------------- XXXXX17 
Location ===> LOCAL DB2 SSID ===> XXXX Version ===> 121M500 


CMD PACKAGE COLLECTION VERSION COPYID OWNER 
____ ZZZZPOA RDPCR190_PDA CAD190_2016-10-22-22.48.18 CURRENT CALPE05 
____ ZZZZPOA RDPCR190_PDA CAD190_2016-11-15-08.25.30 CURRENT CALPE05 
____ ZZZZPOA RDPCR190_PDA CAD190_2017-02-28-09.16.05 CURRENT CALPE05 
******************************* BOTTOM OF DATA ********************************

Now I added an EQF to narrow this down to one Package, the oldest one. In my example I’m using the CONTOKEN as a 
search condition but you should be able to use something like a SUBSELECT to a table of your own so that you can 
list many Packages in your environment. This is my EQF: 

20.0 ----------------- SQL Selection Panel ----------------- 2018/12/31 13:40 
Command ==> SCROLL ===> PAGE 

Name ==> CONTOKEN Share => U Default => N 
Description => Panel : PPPKLL View SQL => N 
Confirm Replace ==> Y Userid : XXXXX17 
Where Clause: 
01 AND CONTOKEN = X'1A311F320491B112' 
02 
03 
04 
05 
06 
07 
08 
Order By: 
A.LOCATION A A.COLLID A A.NAME A 
A.CONTOKEN A 

SYSIBM.SYSPACKAGE Object Type: T (Correlation variable A 
1 LOCATION VAR> 2 COLLID VAR> 3 NAME VAR> 
4 CONTOKEN CHAR 5 OWNER VAR> 6 CREATOR VAR> 
7 TIMESTAMP TIM> 8 BINDTIME TIM> 9 QUALIFIER VAR> 

which when used generates the following report:


20.0 ----------- PPA Package List ----------- 2018/12/31 13:42 
COMMAND ===> SCROLL ===> CSR 
LINE 1 OF 1 > 
Option ===> PK Report ===> L 
Collection ===> RDPCR190% Package ===> RAAZPOA Where ===> S CONTOKEN 
----------------------------------------------------------------- XXXXX17 
Location ===> LOCAL DB2 SSID ===> ZZZZ Version ===> 121M500 


CMD PACKAGE COLLECTION VERSION COPYID OWNER 
____ RAAZPOA RDPCR190_PDA CAD190_2016-10-22-22.48.18 CURRENT CALPE05 
******************************* BOTTOM OF DATA ******************************** 

Now I type BATCH as a primary Command which displays the following screen: 

20.0 -------- PPA Batch Reporting Job Submission -------- 2018/12/31 13:4 
COMMAND ===> SCROLL ===> CSR 
LINE 1 OF 20 
Option ===> ( S - Submit, E - Edit, D - Write to Dataset ) 
Action ===> ( B - Bind, R - Rebind, F - Free ) 
----------------------------------------------------------------- XXXX17 
JOBCARD ===> //JOBCARDS 
===> 
===> 
===> 
SYSOUT ===> A (JES SYSOUT class to be used) 
******************************** REPORT INPUT ****************************** 
USERID = (XXXXX17) 
SSID = (ZZZZ) 
LOCATION = (LOCAL) 
ACM = (N,XXXXX17) 
LINES = (55) 
LINESZ = (132) 
PROCDDF = (N) 
OPTION = (PK) 
FLOATFMT = (SCI) 
PLANEXPL = (BOTH) 
Press ENTER to submit job Press END (PF3/15) to quit


Now using D to create to a dataset and either B for BIND or F for Free, then hitting PF3 generates me a batch job, 
which when executed generates the same report, along with the necessary BIND commands and also executes them. 

//JOBCARDS 
//* 
//STEP1 EXEC PGM=PTLDRIVM,REGION=4M,PARM='SUFFIX=00,EP=PPL@PRMB' 
//STEPLIB DD DISP=SHR,DSN=CADEMO.DB2TOOLS.R20.CDBALOAD 
//PTILIB DD DISP=SHR,DSN=CADEMO.DB2TOOLS.R20.CDBALOAD 
//PTIPARM DD DISP=SHR,DSN=CADEMO.DB2TOOLS.R20.CDBAPARM 
//* 
//SYSPRINT DD SYSOUT=A,COPIES=1, 
// DCB=(RECFM=FA,BLKSIZE=132) 
//SYSUDUMP DD SYSOUT=A 
//SYSIN DD * 
USERID = (XXXXX17) 
SSID = (XXXX) 
LOCATION = (LOCAL) 
ACM = (N,XXXXX17) 
LINES = (55) 
LINESZ = (132) 
PROCDDF = (N) 
OPTION = (PK) 
FLOATFMT = (SCI) 
PLANEXPL = (BOTH) 
DBOPT = (ALL) 
DBRANGE = (001) 
REPORT = (L) 
COLLID = (RDPCR190%) 
PACKAGE = (RAAZPOA) 
VERSION = (ALL) 
PKGCOPY = (N) 
REPVER = (N) 
EQF = (S,CONTOKEN) 
GENERATE 
BINDPACK 
END 
/* 
//