IBM Support

WebSphere Data Interchange for z/OS, Converting from a DB2 DBRM based Plan to a DB2 Package based Plan

Product Documentation


Abstract

This document will describe a process that will allow a WDI for z/OS customer to convert a current installation using IBM DB2 v9 or above that uses the product provided DB2 Plan based on DBRMs to use a DB2 Plan based on Packages.

Customers using a DB2 release lower than DB2 V10 are not required to use DB2 Packages. WDI will provide tools to support both techniques for BINDing DB2 Plans

Content


Role of DB2 Packages

Releases of DB2 for z/OS after Version 9.1 do not support Database Request Module (DBRM)-based plans. Its installation guide notes the following: “A future release of DB2 might not handle plans that contain DBRMs. In preparation, ensure that all of your plans contain only package lists.”

From the DB2 10 for z/OS, Installation and Migration Guide (G19-2974-01) , "Binding DBRMs directly into plans is no longer supported. For pre-existing plans that are bound from DBRMs, you can use the COLLID parameter of the REBIND PLAN command to create packages. If you execute a plan that is bound from DBRMs, DB2 performs an automatic rebind that creates packages from the DBRMs and binds those packages into a plan. However, the recommendation is to use REBIND with the COLLID option so that you can specify bind options and receive more diagnostic information. If the installation uses the RACF access control module, owners of plans with DBRMs need to explicitly rebind the plans to convert the DBRMs to packages."

History of Package Availability and Use

The use of packages in a DB2 plan is nothing new, however many systems were built with DBRM-based plans before packages became available. IBM first introduced plans with packages in DB2 V2R3, which became generally available October 1991. IBM began delivering packages to solve several problems with DBRM-based plans.

The biggest problem was related to change management. If a program was changed, then it had to be recompiled – which produces a new DBRM and requires a rebind to every plan using that DBRM. This isn’t really a problem with batch programs, since most plans contained only one DBRM. The batch program name and DBRM name and plan name was usually the same. It became a big problem for CICS environments. If a CICS transaction was going to call any other CICS transaction, then that DBRM had to be included in the plan that was allocated for the initial transaction. This became a change management nightmare. If transaction TRN1 called TRN2 or TRN3 then DBRMs for TRN1 and TRN2 and TRN3 must be included in the TRN1plan. These three DBRMs must also be in plan TRN2 and in plan TRN3. When a change was made to any one of these DBRMs. a rebind had to occur for each plan. First you’d have to rebind plan TRN1 with all three DBRMs, then plan TRN2 and finally plan TRN3. This not only took a long time to process–especially if the PLAN had 20 or 30 DBRMs in it–but it also became error prone. For example, if a change was made to program TRN1, it required a rebind of TRN1, and as was mentioned above also to TRN2. However, you could easily forget to rebind plan TRN3, which can also call TRN1. Everything would work fine at first, but a few days later someone would try to call TRN1 from TRN3 and fail, because the DBRM in bound in TRN3 is not the most current DBRM.

Planning for a Conversion from Plan to Packages, in general

The conversion from DBRM-based to package-based plans may seem like a simple task and technically it is.
The BIND command for DBRM based PLAN:


    BIND PLAN planname MEMBER(pgm01, pgm02, pgm03)

The BIND command for package based PLAN:

    BIND PLAN planname PKLIST(collid.pgm01, collid.pgm02,collid.pgm03)

The first thing you should do when planning a project is to put together a checklist to help you prepare for the conversion effort. I’ve started a list to help get things going:
    Who should be a part of the planning team?
    What needs to change in the current development procedures?
    What needs to change in the current production change-control procedures?
    What needs to change with your current DB2 standards?
      use of Plans
      use of Collections
      implementation of Package versions

    How many plans need to be converted?
      List plans and DBRM names
      List DBRM names found in more than one plan
      List Online Plan names
      List Batch Plan names
      Identify Third-party products
        Note: If your vendor products aren’t using packages, you should find out what their
        plans are for converting to packages.

    How are reports generated?

    Define a conversion and test strategy
      Change online compile panels
      Modify change control procedures
      Setup CICS region with dynamic plan switching turned off
      Convert plans and cutovers
      Test online CICS
      Test batch
      Progress Reports and Notifications

(above are excerpts from IBM Systems Magazine, May / June 2009, by Troy Coleman,
http://www.ibmsystemsmag.com/mainframe/mayjune09/webexclusive/25085p1.aspx)


Procedure for Converting the WDI DB2 Plan to DB2 Packages

With the implementation of WDI APAR PM26227 in WDI v3.3 for z/OS (PTF UK63010), WDI will provide objects that can be used to utilize DB2 Packages. This PTF provides no new DBRMs. It merely includes new sample JCL to accommodate package-based binding, as will be discussed below. While not absolutely necessary, we strongly recommend that customers running on DB2 V10 convert to using DB2 packages because automatic rebind is costly from a performance stand-point. Each execution of WDI will cause DB2 v10 to internally convert DBRM-based plans into package-based plans for each runtime until the Plan is rebound using packages .

From the IBM Redbook publication, section 4.3.1: “DB2 9 for z/OS: Packages Revisited”
(SG24-7688-00, http://www.redbooks.ibm.com/abstracts/sg247688.html?Open)

"Included via APAR PK62876 in DB2 Version 8 for z/OS and DB2 9 for z/OS is a new option of the REBIND PLAN command that converts DBRM-based plans to packaged-based plans. There are choices you can make when determining your conversion strategy. You need to be aware of their consequences.

To convert DBRMs bound within plans into packages, a new REBIND PLAN option COLLID (i.e. collection name) 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."

An example of REBIND for WDI plan is
    REBIND PLAN (EDIENU33) COLLID(EDIENU33) -
      OWNER (EDIENU33)
The steps to perform the conversion are:

1) Convert the existing WDI DB2 Plan to a DB2 Collection of Packages by using the DB2 REBIND
command. The Collection name used is EDIENU33.
See member EDIJRBND in EDI.V3R3M0.SEDIINS1.
The REBIND will create the initial set of packages from DBRMs.
REBIND will require DB2 permissions to GRANT CREATE IN COLLECTION EDIENU33 and to
GRANT EXECUTE ON PACKAGE EDIENU33.* to EDIENU33

2) Test the new PLAN with the sample program or other WDI translation.
Note - After the initial bind, any new DBRMs would need to be included with the
ACTION(REPLACE) for individual packages in the collection.

Procedure for Converting the WDI DB2 Plan to DB2 Packages, for CICS

1) Convert the existing WDI DB2 Plan to a DB2 Collection of Packages by using the DB2 REBIND
command. The Collection name used is EDIENU33.
See member FXXJRBND in EDI.V3R3M0.SEDIINS2.

2) Test the new PLAN with the sample CICS transaction or other WDI translation
Note - After the initial bind, any new DBRMs would need to be included with the
ACTION(REPLACE) for individual packages in the collection.

Maintaining the WDI DB2 PLAN

While the REBIND will create and BIND the initial DB2 packages, it is also necessary to maintain the packages after applying SMP maintenance to the WDI product. Customers may also wish to create additional DB2 Plans to support testing or for quality assurance aspects of their WDI implementation.

In the future, when PTFs are applied to WDI, a PTF HOLD action will let the customer know that DB2 changes have occurred. For those using DB2 DBRM based Plans, the traditional EDIJBIND or FXXJBIND JCL should be executed to BIND the Plan. For customers migrating to DB2 Packages, only the affected packages need be bound. The PTF HOLD action will identify the individual package names. BIND each of the individual packages using the BIND commands provided in the EDI.V3R3M0.SEDISQL1 library. Use the JCL provided in EDI.V3R3M0.SEDIINS1(EDIJBNDK). And comment-out all package names not specified in the PTF HOLD action. An example of how EDIJBNDK would look if the package named EDIELOG was identified in the HOLD action is:

//EDIJBNDK JOB (INSTALLATION DEPENDENCIES)
//*
//BINDPKG PROC MEMBER=
//*********************************************************************
//* BINDS PACKAGE NAMED IN VARIABLE MEMBER *
//*********************************************************************
//STEP0100 EXEC PGM=IKJEFT01,DYNAMNBR=20,REGION=2000K
//STEPLIB DD DSN=DB2.SDSNLOAD,DISP=SHR <- DB2 LOAD LIBRARY
// DD DSN=DB2.SDSNEXIT,DISP=SHR <- DB2 LOAD LIBRARY
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//LISTING DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//* BIND Package
//SYSTSIN DD DSN=EDI.V3R3M0.SEDISQL1(EDISYST),DISP=SHR
// DD DSN=EDI.V3R3M0.SEDISQL1(&MEMBER),DISP=SHR
// PEND
//*ELAS EXEC BINDPKG,MEMBER=EDIELAS
//ELOG EXEC BINDPKG,MEMBER=EDIELOG
//*MSGS EXEC BINDPKG,MEMBER=EDIMSGS
//*EVIDB EXEC BINDPKG,MEMBER=EDIEVIDB
//*EVUDB EXEC BINDPKG,MEMBER=EDIEVUDB
...

Note - make sure to change the installation based parameters in the JCL and the BIND
statements before submitting the JCL.


If an additional DB2 Plan is desired, member EDIJBIN2 in EDI.V3.R3M0.SEDIINS1 is provided. This will allow an additional Plan to be created using the distribution library objects. BIND the DB2 PLAN used by WDI using Packages (parameter PKLIST) rather than specifying DBRMs (parameter LIBRARY in EDIJBIND).

An example of the BIND statement using DB2 packages is
    DSN SYSTEM(DB2)
    BIND PLAN (EDIENU33)
      OWNER (EDIENU33)
      PKLIST (EDIENU33.*)
      ACTION (REP)
      RETAIN
      VALIDATE (BIND)
      ENCODING (UNICODE)
      ISOLATION (CS)
      ACQUIRE (USE)
      RELEASE (COMMIT)
      CURRENTDATA (NO)
      EXPLAIN (NO)
    END

The Collection ID specified in the PKLIST parameter should be the same one used during the REBIND when the DB2 Packages were initially created.
Note - make sure to change the installation based parameters in the JCL and the BIND
statements before submitting the JCL.

For CICS, the JCL used to BIND each of the individual packages identified in the PTF HOLD
action is provided in EDI.V3R3M0.SEDIINS2(FXXJBNDK).
The BIND command statements are provided in the EDI.V3R3M0.SEDISQL2 library.
Comment out all package names not specified in the PTF HOLD action.
Note - make sure to change the installation based parameters in the JCL and the BIND
statements before submitting the JCL.

To BIND an additional DB2 Plan for CICS, see member FXXJBIN2 in EDI.V3.R3M0.SEDIINS2.
Note - make sure to change the installation based parameters in the JCL and the BIND
statements before submitting the JCL.

Original Publication Date

13 October 2010

[{"Product":{"code":"SSFKTZ","label":"WebSphere Data Interchange"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"WDI 3.3 z\/OS","Platform":[{"code":"PF035","label":"z\/OS"}],"Version":"3.3","Edition":"Enterprise","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]

Document Information

Modified date:
19 July 2019

UID

swg27019377