Blog
DB2utor

Categories

June 12, 2012

DB2 10 Conversion to Packages

A reader recently e-mailed me, explaining that his company is looking to convert all its plans that contain a DBRM to plans that contain only a package list. I pointed him to an article I wrote on this subject, "Converting to Packages."

In that article I reference an IBM Redbook. That Redbook, "DB2 9 for z/OS: Packages Revisited," was updated early this year, and it's a good resource for anyone undertaking a conversion to packages.

Chapter 4 is dedicated to converting to packages. To see how many plans in your system need to be converted, run this SQL statement:

SELECT COUNT(DISTINCT PLNAME) AS NUMBER_OF_PLANS FROM SYSIBM.SYSDBRM

The good news is that you don’t have to go and bind every DBRM into a package and then come back and rebind the plan. DB2 10 introduced the COLLID option as part of the rebind plan command. The Redbook provides a description of this option:

"To convert DBRMs bound within plans into packages, a new REBIND PLAN option COLLID (collection name or *) is introduced to perform the DBRM to package conversion. During the REBIND PLAN process, DB2 reads the SYSDBRM table to retrieve the DBRMs bound with the plan, binds them into packages and binds the packages into the plans. Each package is listed separately in the PKLIST, not as a collection *. Finally, the REBIND deletes the DBRMs bound into the plans. After the rebind, there are no longer any SYSDBRM or SYSSTMT records associated with the plan. The SYSPACKAGE and SYSPACKSTMT tables are populated with new package statement data."

If you've already migrated to DB2 10 and want to see which plans are using a default collection name, use this statement:

SELECT SUBSTR(NAME,1,20) AS NAME   

     , SUBSTR(COLLID,1,30) AS COLLID

FROM SYSIBM.SYSPACKAGE             

WHERE COLLID LIKE 'DSN_DEFAULT%'

Incidentally, while COLLID is a DB2 10 feature, DB2 8 and DB2 9 users can make use of it by downloading this APAR.