Bind statement for DB2 v10 and above
Article ID: 21396
Updated On:
Products
Issue/Introduction
With the introduction of DB2 v10 and above, a plan cannot be created from a DBRM directly.
A package must be created first from a DBRM, and a plan must be created by package(s).
Environment
Component:
Resolution
You can use packages with CA Easytrieve and CA Pan/SQL. Packages are known to DB2, not CA Easytrieve.
For CA Easytrieve Dynamic DB2 programs, this is done via the IJ3STGE1 job, which is part of the CA Pan/SQL installation.
Please see this example, modifying the ssid, package, and plan/member names:
...
DSN SYSTEM(ssid)
BIND PACKAGE(package) MEMBER(member) ACT(REPLACE) -
ISOLATION(CS) LIBRARY ('your.DBRMLIB') -
VALIDATE (BIND) SQLERROR(NOPACKAGE) -
DEFER(PREPARE) ENCODING(EBCDIC)
BIND PLAN(member) ACT(REPLACE) ISOLATION(CS) -
VALIDATE (RUN) -
PKLIST(*.package.member) DEFER(PREPARE) -
ENCODING(EBCDIC)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA10) PARM('RC0') -
LIB('ssid.RUNLIB.LOAD')END
...
The above BIND statements can either be copied into the DQBNDPLN member which is found in your CA Pan/SQL and is originally set to bind plans or this may be copied into a newly created DQBNDPAC member in your CA Pan/SQL CAIMAC.
The IJ3STGE1 JCL will then need to be changed to incorporate the correct member with the above BIND statements.
An example is as follows:
//SYSTSIN DD DSN=&CAI.CAIMAC(DQBNDPAC),
instead of
//SYSTSIN DD DSN=&CAI.CAIMAC(DQBNDPLN)
...
For a CA Easytrieve Statically bound program, where the Easytrieve PARM Statement has PLAN (PKGTST).
Again, please be sure and modify the specified libraries in addition to the name of your ssid, package, and plan/member:
...
//*
//* STEP 6: BIND AND GRANT AUTHORIZATION TO THE//* COMMAND PROGRAM'S APPLICATION PLAN
//*
//**********************************************************
//AUTH EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DISP=SHR,DSN=ssid.PRIVATE.SDSNEXIT
// DD DISP=SHR,DSN=DB2.SDSNLOAD
//DBRMLIB DD DISP=SHR,DSN=your.DBRMLIB
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//REPORT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(ssid)
BIND PACKAGE(package) MEMBER(member) VALIDATE(RUN) -
ACT(REPLACE) ISOLATION(CS)
BIND PLAN(member) PKLIST(package.*) -
ACT(REPLACE) ISOLATION(CS)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA10) -
LIB('ssid.RUNLIB.LOAD')END
/*
//SYSIN DD *
GRANT EXECUTE ON PACKAGE package.member TO PUBLIC;
/*(Note that IKJEFT01 is not a Pan/SQL program.)
Again, this is only a basic example. IBM documentation provides additional information.
Feedback
