search cancel

Batch Package Compare


Article ID: 77707


Updated On:


Bind Analyzer for DB2 for z/OS SQL-Ease for DB2 for z/OS SYSVIEW Performance Management Option for DB2 for z/OS Plan Analyzer for DB2 for z/OS Subsystem Analyzer for DB2 for z/OS


After generating compare reports of all the packages in the current version vs their previous  version, the comparisons from previous executions are being reported in the current execution. The issue seems to be rows are staying in the following 3 tables after each execution, and the report is picking up all of them for subsequent executions: PTI.PTAN_STMT_1200, PTI.PTPA_ES_EXPL_1500, PTI.PTAN_SQL_1200. How to fix that?


Component: PPADB2


The current architecture for HVERSION method AUTO HVERSION treats each package version as separate object in the PPA historical database. When an explain takes, each package version is stored as a separate object in the auto hversion +0 level.
While  PPADB2 has an interface to remove auto hversion objects in the PPA historical database, it is only accessible online through option SD-Source Database Maintenance off the PPA main menu.
Minimally, enter the hversion, collid, package, DBOPTS=ALL, DBRANGE=10 and press enter.
Specify line command DEL next to each object that is to be deleted.
Here is example screen shot example (specify SETWIDTH VERSION 10 in command line to get scrollable version column into the display): 20.0 ---------- PPA DM Info. Summary --------- 2018/04/ COMMAND ===> SCROLL == LINE 1 OF HVersion ===> AUTOTST1 DBOPTS ===> ALL DBRANGE ===> 020 Collid ===> COMPARE_VERS_COLL Package ===> * STMTNO ===> Name ===> Creator ===> Type ===> ----------------------------------------------------------------- CARDO Location ===> LOCAL DB2 SSID ===> D12A Version ===> 121 HVERSION/LEVEL ARTCE CMD GROUP/OBJECT/STMT Type CVCCT SSID LOCATION VERSION ____ AUTOTST1 Auto ____ Level +0 ____ COMPARE_VERS_COLL (CO) D12A ____ FXCAVG (PK) C D12A T1TEST DEL_ FXCAVG (PK) C D12A T2TEST ____ FXCCNT (PK) C D12A T1TEST ____ FXCCNT (PK) C D12A T2TEST ____ FXCMAX (PK) C D12A T2TEST ******************************* BOTTOM OF DATA ************************
The recommendation is to try HVERSION method STRATEGY HVERSION instead of AUTO HVERSION, because it may be a better approach if packages are going to be changing on a regular basis. Based on some of the information supplied, an explain against the same collection or set of packages is performed on a weekly basis and a comparison is required to see what if any differences have been detected. Additionally, it sounds like only the latest package version is explained, if that is the case STRATEGY HVERSION can match up a different package version of the same package name.
When an explain strategy takes place, a STRATEGY HVERSION is created in the PPA history database using the strategy version name and a relationship is tied between the strategy version and STRATEGY HVERSION.
Deleting a strategy version automatically deletes the tied STRATEGY HVERSION in the PPA History database.
To enable HVERSION method STRATEGY HVERSION supply explain card DATABASE = (STRATEGY) inplace of DATABASE = (AUTO,HVER2,D01G).
In compare the OLDCOMP / NEWCOMP cards are generated a little differently for STRATEGY HVERSION versus AUTO HVERSION: OLDCOMP = (S,0000,,PK,,,,,Strategy Name,Strategy Creator,SSID,Strategy Vers, COLLID,,,'') NEWCOMP = (S,0000,,PK,,,,,Strategy Name,Strategy Creator,SSID,Strategy Vers, COLLID,,,'')