BIND commands in DB2 for z/OS

DSN BIND versus operator commands

BIND is not -BIND from SDSF. You start a DSN session (TSO command DSN SYSTEM(ssid)) or use DB2I, then enter the subcommand. Batch jobs use IKJEFT01 with DSN in SYSTSIN. Data sharing scope is group: the package/plan is available on every member. BIND PACKAGE also deletes phased-out package copies as part of plan management.

BIND PACKAGE

BIND PACKAGE builds an application package. Db2 records the description in catalog tables (SYSIBM.SYSPACKAGE and friends) and stores the executable form in the directory. Identify the package by collection and, from the DBRM, package-id and version. Optional location-name binds at a remote server.

1
2
3
4
5
6
DSN SYSTEM(DB2A)
BIND PACKAGE(PAYROLL) MEMBER(PAYCALC) -
  ACTION(REPLACE) OWNER(PAYBIND) QUALIFIER(HR) -
  ISOLATION(CS) RELEASE(COMMIT) EXPLAIN(YES) -
  VALIDATE(BIND) CURRENTDATA(NO)
END

• MEMBER(dbrm) — bind this DBRM from the DBRMLIB concatenation
• COPY — copy an existing package instead of a DBRM (remote bind / clone)
• ACTION(ADD | REPLACE) — add a new package/version or replace
• SQLERROR(NOPACKAGE | CONTINUE | CHECK) — how bind-time SQL errors are handled

The package owner must be authorized to execute the embedded SQL or the bind produces errors (SYSADM and DATAACCESS include that). VALIDATE(BIND) checks most authorization at bind time; LOCK TABLE and some DDL wait until run time. VALIDATE(RUN) retries failed checks at run time.

Adding a package depends on BINDNV (BIND NEW PACKAGE). The default BINDADD path needs BINDADD plus CREATE IN or PACKADM on the collection (or SYSADM/SYSCTRL/System DBADM). If BINDNV is BIND, PACKADM or BIND on the collection can add a new version of an existing package. REPLACE needs ownership, BIND on the package, or a system authority. BINDAGENT lets you bind on behalf of OWNER. In a trusted context with role ownership, OWNER is a role.

BIND PLAN

Every program still needs an application plan to allocate Db2 resources at run time. BIND PLAN builds it. Modern plans are thin: PKLIST names collections and packages. The MEMBER option still exists but is deprecated—it binds DBRMs into packages and stuffs them on the plan. Use BIND PACKAGE plus PKLIST unless you cannot.

1
2
3
4
5
BIND PLAN(PAYPLAN) -
  PKLIST(PAYROLL.*, COMMON.*) -
  ACTION(REPLACE) RETAIN -
  ISOLATION(CS) RELEASE(COMMIT) -
  CACHESIZE(1024) CURRENTSERVER(DB2A)

• ACTION(REPLACE) RETAIN — replace the plan but keep EXECUTE privileges
• ACTION(ADD) — needs BINDADD (or SYSADM/SYSCTRL/System DBADM)
• PKLIST — the binder needs EXECUTE on each package or PACKADM / SYSADM / DATAACCESS
• ACQUIRE(USE | ALLOCATE), DISCONNECT, CACHESIZE — plan-level resource options
• NODEFER / DEFER(PREPARE) — dynamic SQL prepare timing

Important bind options (packages, plans, services)

Isolation values: CS (cursor stability, the usual OLTP default), UR (uncommitted read), RS (read stability), RR (repeatable read). DEGREE(1 | ANY) controls query I/O parallelism. REOPT(NONE | ALWAYS | ONCE | AUTO) controls whether Db2 reoptimizes with host-variable values. OPTHINT names a package-level hint. PLANMGMT(OFF | BASIC | EXTENDED) keeps previous and original copies for SWITCH on REBIND. APREUSE / APCOMPARE reuse or compare access paths. Round out production binds with ENCODING, IMMEDWRITE, CONCURRENTACCESSRESOLUTION, and KEEPDYNAMIC as your shop standard requires.

BIND QUERY

BIND QUERY is not for COBOL DBRMs. It reads every row of DSN_USERQUERY_TABLE (statement text, default schema, bind options) plus correlated EXPLAIN / PLAN_TABLE hint rows. With LOOKUP(NO) (default), Db2 inserts SYSIBM.SYSQUERY, SYSQUERYPLAN, and SYSQUERYOPTS. That is how statement-level access path hints become catalog objects other packages can pick up.LOOKUP(YES) searches for matching catalog rows and reports DSNT280I/DSNT281I without inserting.

1
2
3
4
5
6
DSN SYSTEM(DB2A)
BIND QUERY
END
/* or */
BIND QUERY LOOKUP(YES)
BIND QUERY LOOKUP(NO) EXPLAININPUTSCHEMA(MYSCHEMA)

BIND QUERY is one of the DSN subcommands that does not run under DB2I the same way as BIND PACKAGE; it is typically batch DSN. You need the authority to insert those catalog rows (SYSADM or the documented bind-query privileges for your release). FREE QUERY removes the rows later.

BIND SERVICE

BIND SERVICE builds the package that implements a native REST service: one static SQL statement (SELECT, INSERT, UPDATE, DELETE, TRUNCATE, CALL, or WITH) invoked over HTTP(S) through DDF. Db2 records the service in SYSIBM.DSNSERVICE. After bind, that package is executed as a service, not as a normal application CALL.

1
2
3
4
5
BIND SERVICE(PAYROLL) NAME(getEmp) -
  SQLDDNAME(SQLIN) -
  DESCRIPTION('Employee by empno') -
  OWNER(PAYBIND) QUALIFIER(HR) -
  ISOLATION(CS) APPLCOMPAT(V12R1M500)

Options NAME, DESCRIPTION, VERSION, SQLDDNAME, and SQLENCODING are service-specific. Other bind options match BIND PACKAGE (ISOLATION, QUALIFIER, APPLCOMPAT, and so on). The createService REST API is an alternative to the DSN subcommand. You must be able to execute the embedded SQL and to bind into the collection. FREE SERVICE drops the active REST package; FREE PACKAGE with PLANMGMTSCOPE(INACTIVE) can still drop inactive copies.

Explain It Like I'm Five

A DBRM is a recipe card the precompiler wrote. BIND PACKAGE is the kitchen cooking that card into a labeled frozen meal (the package) and putting it on a shelf (the collection). BIND PLAN is the dinner ticket that lists which shelves the waiter may take meals from. BIND QUERY is pinning a sticky note on one recipe that says “always use this oven setting.” BIND SERVICE is wrapping one meal so a web app can order it by scanning a QR code (HTTP) instead of sitting in the dining room (COBOL).

Exercises

1. Write BIND PACKAGE for MEMBER(EMPINQ) in collection HRCOLL with ACTION(REPLACE), ISOLATION(CS), RELEASE(COMMIT), QUALIFIER(HR).
2. Explain what BINDADD, CREATE IN, and PACKADM each contribute when adding a first version to a new collection.
3. Rewrite an old BIND PLAN MEMBER(A,B,C) as BIND PACKAGE plus BIND PLAN PKLIST.
4. When would you BIND QUERY LOOKUP(YES) instead of LOOKUP(NO)?
5. Why must the REST service owner be able to execute the SQL inside BIND SERVICE, even though HTTP clients later call the service with their own credentials?