DB2 BIND OWNER, QUALIFIER, PATH, and related identity options

Identity options at a glance

Changing OWNER, QUALIFIER, or PATH on a REBIND that also specifies PLANMGMT(BASIC) or PLANMGMT(EXTENDED) is rejected. PLANMGMT(OFF) with those changes purges saved package copies. Keep identity options stable when you rely on previous and original copies.

1
2
3
4
5
6
7
8
DSN SYSTEM(DB2A)
BIND PACKAGE(PAYROLL) MEMBER(PAYUPD) -
  OWNER(PAYBIND) -
  QUALIFIER(PRODHR) -
  PATH(SYSIBM,SYSFUN,SYSPROC,SYSIBMADM,PRODHR) -
  APPLCOMPAT(V12R1M500) -
  ENCODING(EBCDIC)
END

OWNER

OWNER is the authorization ID of the plan, package, or REST service. That owner must hold the privileges needed to execute the SQL inside the object. If you omit OWNER, Db2 uses the primary authorization ID of the agent that runs the bind.

You can bind only objects for which the applicable ID has BIND privilege. If ownership changes on REPLACE, grants that the previous owner issued are rewritten so the new owner is the grantor. The new owner receives BIND and EXECUTE and grants those privileges back to the previous owner.

Trusted context and roles

In a trusted context with ROLE AS OBJECT OWNER:

• If you omit OWNER, the role associated with the binder becomes the owner.
• If you specify OWNER, it must be a role. The binder needs BINDAGENT from that role (or the binder’s own role must already hold BINDAGENT).

Without ROLE AS OBJECT OWNER, ordinary ownership rules apply, but a role attached to the trusted context still adds its privileges to the binder’s privilege set. OWNER is not valid for REBIND of function packages, native SQL procedures, or advanced triggers. For remote BIND or REBIND PACKAGE, the OWNER value is subject to translation when sent to the remote system.

Catalog: SYSPACKAGE.OWNER (packages), SYSPLAN.CREATOR (plans), and GRANTOR on SYSPACKAUTH.

QUALIFIER

QUALIFIER is the implicit qualifier for unqualified names of tables, views, indexes, aliases, and sequences in the plan or package. It is a naming default, not an authorization ID by itself—though the default value is the owner’s authorization ID if you omit QUALIFIER.

QUALIFIER is not translated when sent to a remote system for BIND or REBIND PACKAGE. Defaults: BIND SERVICE uses the service owner; BIND PLAN uses the plan owner; BIND PACKAGE uses the package owner; REBIND keeps the existing value.

Static SQL in COBOL often looks like this:

1
2
3
SELECT EMPNO, LASTNAME
  FROM EMPLOYEE
 WHERE WORKDEPT = :DEPT

There is no schema on EMPLOYEE. At bind time Db2 reads QUALIFIER and treats the table as QUALIFIER.EMPLOYEE. That is how one DBRM serves a test collection (QUALIFIER(TESTHR)) and a production collection (QUALIFIER(PRODHR)) without editing the program.

QUALIFIER does not automatically qualify functions or CALL procedure names—that is PATH. It also does not override an explicit schema in the SQL text. If the program writes FROM PRODHR.EMPLOYEE, QUALIFIER is ignored for that name.

For dynamic SQL, QUALIFIER is used as the default schema only when DYNAMICRULES bind behaviour is in effect. With DYNAMICRULES(RUN) (the usual default), dynamic SQL uses CURRENT SCHEMA instead. Do not assume a package QUALIFIER applies to SPUFI-like PREPARE inside the same package unless DYNAMICRULES says so.

PATH

PATH is the SQL path Db2 uses to resolve unqualified stored procedure names on CALL, user-defined distinct types, and functions. Schema names on PATH are not folded to uppercase. If you write path(myschema) in lowercase and the catalog has MYSCHEMA, resolution fails. Use uppercase unless you delimited the schema on CREATE.

• PATH(schema-name) or a comma-separated list — do not repeat the same schema. Maximum resulting path length is 2048 bytes (each name + 2 for delimiters + commas).
• PATH(USER) — placeholder for the USER special register. At bind time Db2 reserves 128 bytes in the length check. At run time it substitutes the primary authorization ID of the running process. Do not put quotes around USER.

You do not need to list SYSIBM, SYSFUN, SYSPROC, and SYSIBMADM; Db2 puts them at the front in that order and does not count them in the 2048-byte length if you omitted them. Do not specify SYSPUBLIC. PATH and PATHDEFAULT are mutually exclusive on REBIND.

Defaults: SYSIBM, SYSFUN, SYSPROC, SYSIBMADM, then the package, plan, or service qualifier. REBIND keeps the existing path. PATHDEFAULT on REBIND resets the path to that default list. Default paths are stored in the catalog as a zero-length string; SYSPACKAGE.PATHSCHEMAS holds an explicit PATH.

PATH is how overloaded functions in application schemas are found. If a shop creates HR.DAYS_BETWEEN and someone binds without HR on the path, Db2 never sees that function. Changing PATH on REBIND with PLANMGMT BASIC or EXTENDED is one of the option changes that Db2 rejects.

APPLCOMPAT

APPLCOMPAT is the application compatibility level for static SQL in the package. It also sets the initial value of the CURRENT APPLICATION COMPATIBILITY special register, which governs dynamic SQL in that package until the program issues SET CURRENT APPLICATION COMPATIBILITY.

If you omit APPLCOMPAT on BIND PACKAGE or BIND SERVICE, the APPLCOMPAT subsystem parameter is used. REBIND PACKAGE keeps the existing catalog value; if none is stored, the subsystem parameter is used. APPLCOMPAT on BIND does not block other packages from running at a higher level. Extra client/driver preparation can be required when you raise APPLCOMPAT for applications that connect through IBM data server clients.

APPLCOMPAT is not an optimizer switch. Rebinding to pick new access paths does not require a higher APPLCOMPAT. It is a SQL behaviour and feature gate: which built-in functions exist, how some data types behave, and whether newer syntax is accepted. APPLCOMPAT is not valid for REBIND of native SQL procedure packages or advanced triggers.

ENCODING

ENCODING is the application encoding scheme for host variables in static statements.

• ENCODING(ASCII) — ASCII (typical for Windows and many UNIX clients).
• ENCODING(EBCDIC) — the SYSTEM EBCDIC CCSID from DSNHDECP (usual for COBOL on z/OS).
• ENCODING(UNICODE) — Unicode. Required for BIND SERVICE (other values are invalid there). Catalog stores mixed CCSID 1208.
• ENCODING(ccsid) — a numeric CCSID when the terminal or client is not the system EBCDIC CCSID (example: emulator CCSID 1047 versus system 37).

Packages precompiled on DB2 version 6 or earlier may only use EBCDIC (or a CCSID that matches the system EBCDIC CCSID). ENCODING also affects DESCRIBE: column names and labels come back in the application encoding. The package does not inherit ENCODING from the plan. A package bound on a remote Db2 for z/OS system uses that server’s default; when a plan or package runs remotely, the specified ENCODING is ignored and the remote server’s scheme is used.

SET CURRENT PACKAGE SET / PATH statements that only move a host variable do not require the package to be in the plan; those host variables use the system default application encoding even if the application package was bound UNICODE. Defaults: installation application encoding scheme for BIND PLAN and BIND PACKAGE; UNICODE for BIND SERVICE; existing value on REBIND. Catalog: ENCODING_CCSID on SYSPLAN and SYSPACKAGE.

MEMBER

MEMBER names the DBRM that BIND PACKAGE reads. You specify a PDS member or a zFS file. With LIBRARY, an undelimited library name is a PDS; a name in double quotes is a zFS path. The member name becomes the package name. Names that start with DSN are reserved and produce a warning.

1
BIND PACKAGE(MYCOLL1) MEMBER("myMem1") LIBRARY("/u/mylib1")

BIND PACKAGE allows one MEMBER. If you do not use MEMBER, you must use COPY. You cannot specify both. MEMBER on BIND PLAN is deprecated: Db2 still binds those DBRMs into packages and puts them on the plan’s package list, but IBM’s current practice is BIND PACKAGE plus PKLIST.

VERSION

A package identity is location, collection, package name, and version. For BIND PACKAGE with MEMBER, the version ID comes from the DBRM. The Db2 precompiler VERSION option stamps that value (a timestamp, a release label, or a blank version). COPY uses COPYVER. ACTION(REPLACE) REPLVER(version-id) replaces one existing version; the new version-id still comes from the DBRM, and REPLVER names the version you are replacing.

Versioning lets one collection hold PAYUPD version PROD and PAYUPD version NEW without renaming the program. At run time Db2 matches the consistency token in the load module to the bound package version. That is the usual backout method: keep the old version bound, relink the old load module, and the old token matches again.

On BIND SERVICE, VERSION is a bind option after REST service versioning is enabled. The identifier may be up to 64 characters (letters, digits, underscore, @, #, $). Default is V1 when versioning is enabled, otherwise the empty string.

A practical bind for two environments

Same DBRM, two collections, different qualifier and owner:

1
2
3
4
5
6
7
BIND PACKAGE(COLLTEST) MEMBER(PAYUPD) -
  OWNER(TESTBIND) QUALIFIER(TESTHR) -
  APPLCOMPAT(V12R1M500) ENCODING(EBCDIC)
 
BIND PACKAGE(COLLPROD) MEMBER(PAYUPD) -
  OWNER(PRODBIND) QUALIFIER(PRODHR) -
  APPLCOMPAT(V12R1M500) ENCODING(EBCDIC)

Point the plan’s PKLIST at COLLPROD.* in production. Developers bind into COLLTEST without touching production OWNER privileges. Confirm SYSPACKAGE.OWNER, SYSPACKAGE.QUALIFIER, SYSPACKAGE.PATHSCHEMAS, SYSPACKAGE.APPLCOMPAT, SYSPACKAGE.ENCODING_CCSID, and SYSPACKAGE.VERSION after the job.

Explain It Like I'm Five

OWNER is whose backpack the homework lives in—that person is responsible for the work. QUALIFIER is the default last name you put on untitled worksheets so “EMPLOYEE” means “the PRODHR family’s EMPLOYEE,” not some other family’s. PATH is the list of toy boxes you search when someone says “get the DAYS_BETWEEN function” without saying which box. APPLCOMPAT is which year’s rulebook the teacher uses. ENCODING is whether the letters are written in the z/OS alphabet (EBCDIC), the PC alphabet (ASCII), or Unicode. MEMBER is which homework file you turn in. VERSION is the date stamp on that file so last week’s copy and this week’s copy can both sit in the same folder.

Exercises

1. Bind the same DBRM twice with QUALIFIER(TESTHR) and QUALIFIER(PRODHR). Predict the fully qualified table name for FROM EMP in each package.
2. Explain when OWNER and QUALIFIER should differ (for example a bind agent ID versus an application schema).
3. Write a PATH that searches an application schema MYFUNS after the IBM schemas. Why must MYFUNS be uppercase if it was created without delimiters?
4. Look up one package’s APPLCOMPAT in SYSPACKAGE. Does raising it require a new function level to be activated?
5. Find the precompiler VERSION option in your shop’s compile JCL and match it to SYSPACKAGE.VERSION for that program.