Problems can occur within DB2 V8 NFM when listing or explaining packages that were originally bound in DB2 V7 or V8 CM/ENFM modes. These problems can occur with any application or DB2 tool and not just Plan Analyzer for DB2 for z/OS (PPA).
Collid/Package names that were defined previously as CHAR are now VARCHAR blank padded. Because the VARCHAR format retains blanks, this does not match the original CHAR format as the blanks increase the length of the value.
- Trying to BIND/FREE/REBIND in Plan Analyzer (PPA) for a selected Collid/Package receives-
'PP169I NO ELIGIBLE DATA FOUND TO PROCESS'
- . Plan Analyzer batch explain job receives-
'No Explainable SQL statements to process. Check EXPLAIN object(s) for validity'
How can these problems be solved?
There are a few different solutions possible for this situation.
Certain PPA commands have been changed to overcome some of these problems. The solving PTFs are: UPOF094, UANF210, UCMF196, UPAF286 and USMF072.
- BIND all packages that were padded during the migration to DB2 V8 NFM eliminating trailing spaces and ensure predicates function properly for columns that have been reformatted from CHAR to VARCHAR. CA also recommends re-running the full bind package/plan job generated in 'highlvl.CONTROL(xxxx0002)'
- If BINDing of all packages that were padded during migration is impractical then you might consider using a shadow of SYSIBM.SYSPACKAGE defined in ACM (Alternate Catalog Mapping). Please refer to the Value Pack Reference Guide for details on ACM.
Use the SQL RTRIM function when inserting into the ACM selecting from SYSIBM.SYSPACKAGE. This will remove the trailing / padded collection-ids and packages. If needed, it is only necessary to populate the ACM for the collections and packages which are padded, but it might be easier to simply copying the entire SYSPACKAGE.
- Use Alternate Catalog Mapping Services (option M) to create an ACM-id.
- Use the MAKETAB command to create an ACM shadow table of SYSIBM.SYPACKAGES only.
- Change the generated job to use this INSERT statement rather than unload/load
INSERT INTO myacm.SYSPACKAGE
FROM SYSIBM.SYSPACKAGE ;
- On the CA DB2 Main menu specify ACM = ON and ACMID = myacm.
Enter PPA and using the PPA reporting function the BINDPACK commands should now function well.
DB2 V8 NFM uses a different format for its DBD's, packages and plans. Before DB2 can use a DBD, plan or package from an earlier release of DB2, it must first be expanded to the new Version 8 format. DB2 must also convert the DBD's, plans and packages to the old format before it can store them in the catalog. This is an extra overhead that exists while in DB2 V8 NFM using previously bound packages.
Reference: 'DB2 UDB for z/OS Version 8: Everything You Ever Wanted to Know'
Chapter 12. Installation and migration, pg. 967 (pg. 997 relative).
The following query can be used to identify the packages that were migrated from V7 or V8 CM/ENFM to V8 NFM containing trailing spaces.
WHERE HEX(COLLID) <> HEX(RTRIM(COLLID))
OR HEX(NAME) <> HEX(RTRIM(NAME))
You can execute this query using ISQL/SPUFI or alternatively the predicates can be specified as an EQF query in the Package Report (option 8) in Plan Analyzer. Once the list has been obtained and reviewed, the set of packages will need to have BIND cards manually generated. We would recommend using Plan Analyzer to generate a BIND card template using an existing package having similar BIND parameters. This can be done by generating a separate Package Report with the appropriate criteria specified to obtain your desired template package. DO NOT use one of the packages obtained from the initial query as your template package.
Use the B (for BIND) line command to generate the template BIND card syntax.
Select the edit option.
Update this generated BIND card template with the appropriate Collection and Package name as identified in the initial query.
Review the remaining options along with the DBRM library and submit. This process can be repeated for each package identified in the initial query.
Another workaround for a PPA batch explain is to change the collection ID for each SRCPACK statement to contain '%' as in the following example:
SRCPACK = (DB2U,LOCAL,BILLING_COLL_BTCH%,BEXBCDRX,VERS0,,)