How to BIND a DBRM into a PACKAGE and PACKAGE into a PLAN?

DB2DatabaseBig Data Analytics

A DBRM can be directly bound to a plan or we can first bind DBRM into a package and then bind that package into PLAN.

In case DBRM binds to a plan directly, if there is some change in the source code, the new DBRM has to be generated and then we have to bind the entire plan again. Since, plan contains multiple DBRMs, the system will process all the DBRMs again in order to bind that plan (even if the other DBRMs have not gone through any changes). This process takes a lot of resources like memory, processor and valuable time. Due to this reason many installations are now using DBRM, package and plan combination.

In DBRM, package and plan combination, the DBRM is first binded to a package. These packages are the DB2 optimized version of SQL queries but they are not executable by DB2. There is one package for every DBRM (one to one relationship). The package is then binded to a plan. Whenever there are any changes in the source code, we only need to bind the package again using newly generated DBRM. There is no need to bind the changed package again to a plan. This saves a lot of time, resources and effort.

Using below JCL steps we can bind DBRM into a package and package into a plan.

---To BIND DBRM into a package---
//BIND EXEC PGM=IKJEFT01
//STEPLIB DD DSN=DIS.TEST.LOADLIB,DISP=SHR
//SYSOUT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(TB3)
BIND PACKAGE(PACKA) -
MEMBER(DBRM1) -
LIB(‘DIS.TEST.DBRM’)
/*

---To BIND package into a plan---

//BIND EXEC PGM=IKJEFT01
//STEPLIB DD DSN=DIS.TEST.LOADLIB,DISP=SHR
//SYSOUT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(TB3)
BIND PLAN(PLANA) -
PKLIST(PACKA) -
/*

To bind a package, we will use the parameter BIND PACKAGE along with the DBRM name in the MEMBER parameter. On the other hand, to bind a plan we will use BIND PLAN parameter along with the package name in PKLIST parameter.

raja
Published on 11-Sep-2020 12:03:38
Advertisements