Binding DBRM directly to a PLAN is NOT allowed (DB2 V9 onwards)

As you all know, you can Bind DBRM directly to a PLAN or to a Package but DB2 V9 onwards you will not be able to bind your dbrms to plan and you need to bind it to packages only and bind the packages into a plan.

DBRM-based plans are deprecated in DB2 9 for z/OS. That is, no enhancements are

expected to be implemented and, in a future release, these types of plans might no longer be supported.

Well then obvious question would arise that , what we will do with the existing PLANs which has DBRM directly bounded to it.


Here is the solution.

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.


If the plan that you specify already contains both DBRMs and package list, the newly converted package entries will be inserted into the front of the existing package list.

During the REBIND PLAN process, DB2 reads the SYSIBM.SYSDBRM catlog 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.

Let's see few example , how would you do it. ( As a programmer mostly you will not be the one , who will be doing  this task in your shop but this is just for you reference)


* Converting all PLAN

The following examples converts DBRMs that are bound with plan z into packages under the  collection ID mycollection.

     REBIND PLAN(z) COLLID('mycollection');

*  Rebinding multiple plans which may contain DBRMs

In the following example, BIND will traverse through each plan that is specified in the SYSPLAN table and will convert the DBRMs accordingly, and until none of the DBRMs are bound with plans.

        

REBIND PLAN (z1, z2, z3) COLLID (collection_id|*);

*  Specifying a package list

The following examples converts all DBRMs that are bound with plan z into packages under collection ID

        

 REBIND PLAN (z) COLLID (collection_id|*) PKLIST(z);

Additionally, releases of DB2 after Version 9.1 do not support the ACQUIRE(ALLOCATE) bind option for the BIND PLAN and REBIND PLAN commands.

And at last it make sense to not have any significance of the MEMBER parameter for BIND PLAN.