Binding


The bind process establishes a relationship between an application program and its relational data. This step is necessary before a program can be executed. DB2 allows two basic ways of binding a program: to a package or directly to an application plan.

NOTE

If the application uses DRDA access to distribute data, you must use packages.


When a program is preprocessed for DB2, the two outputs are a modified source program to be passed to a compiler where the original SQL statements have been replaced by statements to invoke the appropriate DB2 interface functions, as well as information on how those SQL statements will be used by DB2. This second output, a DBRM, must be bound so that all the DB2 functions used in the program can be checked for validity and authorization and so that a proper access path can be determined. The bind process performs the following:

  • Checks SQL syntax

  • Checks security

  • Compares column and table names against the DB2 catalog

  • Builds an access path strategy for each SQL statement

These processes are discussed in more detail later in this chapter.

Packages and Plans

A DBRM can be bound in a package or directly into a plan. In addition, packages can be bound into logical groups called collections, which can then be bound into a plan. A package can be bound for only a single SQL statement, for a subset of the program, or for an entire program. The package will contain information representing optimized SQL, which may simply be an access path for the SQL to use to process the data required, or it could be information representing an SQL statement that was first rewritten by the optimizer.

A plan can contain multiple packages, collections, and/or DBRMs. Each package can be bound independently of a plan. If a list of DBRMs is bound into a plan and then the plan is rebound, all DBRMs are rebound as well. The output will contain the access path information for each SQL statement.

It is extremely difficult to justify the use of one plan containing all the packages for an installation or even for only all CICS transactions or batch jobs. Plans need to be granular. Large, cumbersome plans can cause performance degradation, buffer pool problems, and EDM pool problems. The number of packages put into a single plan needs to be based on functionality, such as all the packages supporting a particular function of an online application.

When determining the maximum size of a plan, you must consider several physical limitations, including the time required to bind the plan, the size of the environmental descriptor manager (EDM) pool, and fragmentation. Any number of DBRMs can be included in a plan. However, packages provide a more flexible method for handling large numbers of DBRMs within a plan.

Packages are database objects that contain executable forms of SQL statements. These packages contain statements that are contained in a DB2 application. A package corresponds to a program source module. Packages and their descriptions are stored in the DB2 catalog and directory tables. The packages contain the DB2 access plan that was selected by DB2 during the BIND or PREPARE process. This type of BIND is known as static binding, as it is performed prior to the execution of the SQL statement. Packages cannot be directly referenced in an SQL data manipulation statement.

Most applications that access a DB2 database will have a package or group of packages stored, or bound, in the system catalog and directory tables. Packages are input to the plan bind, using the PKLIST options. To be usable, a package must be bound into a plan. An example of a plan bind follows:

 BIND PLAN (certpln) PKLIST (col1.*) 

Packages need to be used based on the application design and objectives. Using packages provides a number of advantages:

  • Ease of maintenance. With packages, the entire plan does not need to be bound again when a change is made to one SQL statement. Only the package associated with the changed SQL statement needs to be bound.

  • Incremental development of a program. Binding packages into package collections, discussed later, allows adding packages to an existing application plan without having to bind the entire plan again.

  • Versioning. As discussed later, several versions of a plan can be maintained without using packages requiring a separate plan for each version and therefore separate plan names and RUN commands.

  • Flexibility in using bind options. The options of BIND PLAN apply to all DBRMs bound directly to the plan. The options of BIND PACKAGE apply only to the single DBRM bound to that package. The package options need not all be the same as the plan options and need not be the same as the options for other packages used by the same plan.

  • Flexibility in using name qualifiers. The bind option QUALIFIER can be used to name a qualifier for the unqualified object names in SQL statements in a plan or package. Using packages allows different qualifiers for SQL statements in different parts of an application. For example, in order to redirect SQL statements from a test table to a production table, all that is required is a rebind.

  • CICS flexibility. With packages, no dynamic plan selection and the associated exit routine are needed. A package listed within a plan is not accessed until it is executed. However, dynamic plan selection and packages can be used together. Doing so can reduce the number of plans in an application and hence require less effort to maintain the dynamic plan exit routine.

Collections

A collection is a group of associated packages. If a collection name is included in the package list when a plan is bound, any package in the collection becomes available to the plan. The collection can even be empty when the plan is first bound. Later, packages can be added to the collection, and existing packages can be dropped or replaced, without binding the plan again.

A collection is implicitly created at the first package bind that references a named collection. The name of the package is collection.packagename.

NOTE

Collections are used in order to group packages by application or function.


The CURRENT PACKAGESET special register allows package searches on specific collections and disregards any other DBRMs. The following example uses the special register to search only the certcol collection:

 EXEC SQL SET CURRENT PACKAGESET = 'certcol' 

Without the use of CURRENT PACKAGESET, all the DBRMs would be searched, and then collections would be searched.

NOTE

It is also possible to have two different copies of a program by binding them into two different collections.


The CURRENT PACKAGE PATH special register allows a list of collections to be specified as search locations when attempting to execute a package. The CURRENT PACKAGE PATH takes priority over the CURRENT PACKAGESET. Following is an example of using the special register to search the certcol and certcol2 collections:

 EXEC SQL SET CURRENT PACKAGE PATH='"certcol","certcol2"' 

Versioning

Maintaining several versions of a plan without using packages requires a separate plan for each version and therefore separate plan names and RUN commands. Isolating separate versions of a program into packages requires only one plan and helps to simplify program migration and fallback. At precompile time, the user can assign versions to the program, allowing multiple copies of the same package in the DB2 system. For example, separate development, test, and production levels of a program can be maintained by binding each level of the program as a separate version of a package, all within a single plan. The precompiler can also be set to automatically generate a version for the package that is based on a timestamp; using the VERSION(AUTO) option.

Binding and Rebinding

The BIND PACKAGE subcommand allows you to bind DBRMs individually. It provides the ability to test different versions of an application without extensive rebinding.

NOTE

Package binding is also the only method for binding applications at remote sites.


Even when DBRMs are bound into packages, all programs must be designated in an application plan for execution by local jobs. BIND PLAN establishes the relationship between DB2 and all DBRMs or packages in that plan. Plans can specify explicitly named DBRMs, packages, collections of packages, or a combination of these elements. The plan contains information about the designated DBRMs or packages and about the data the application program intends to use. The plan is stored in the DB2 catalog.

In addition to building packages and plans, the bind process

  • Validates the SQL statements, using the DB2 catalog. During the bind process, DB2 checks the SQL statements for valid table, view, and column names. Because the bind process occurs as a separate step before program execution, errors are detected and can be corrected before the program is executed.

  • Verifies that the process binding the program is authorized to perform the data-accessing operations requested by your program's SQL statements. When the BIND command is issued, an authorization ID can be specified as the owner of the plan or package. The owner can be any one of the authorization IDs of the process performing the bind. The bind process determines whether the owner of the plan or package is authorized to access the data the program requests.

  • Selects the access paths needed to access the DB2 data that the program wants to process. In selecting an access path, DB2 considers indexes, table sizes, and other factors. DB2 considers all indexes available to access the data and decides which ones, if any, to use when selecting a path to the data. BIND PLAN and BIND PACKAGE can be accomplished using DB2I panels, the DSNH CLIST, or the DSN subcommands BIND PLAN and BIND PACKAGE.

Depending on how the DB2 application was designed, all DBRMs could be bound in one operation, creating only a single application plan. Or, you might bind some or all of your DBRMs into separate packages in separate operations. After that, the entire application must still be bound as a single plan, listing the included packages or collections and binding any DBRMs not already bound into packages.

NOTE

Regardless of what the plan contains, it must be bound before the application can run on z/OS.


Binding or Rebinding a Package or Plan in Use

Packages and plans are locked during binding and execution. Packages that run under a plan are not locked until the plan uses them. If you execute a plan and some packages in the package list are never executed, those packages are never locked.

A package or a plan cannot be bound or rebound while it is running. However, a different version of a package that is running can be bound.

Options

A few methods are used for binding and rebinding packages and plans.

  • The BIND(ADD) option can be used for creating a new plan or package.

  • BIND(REPLACE) is used when the program has been changed.

  • REBIND is used when a program has not changed but perhaps an index has been added or RUNSTATS has been executed, and any access path changes need to be accounted for.

Several of the options of BIND PACKAGE and BIND PLAN can affect your program design. For example, a bind option can be used to ensure that a package or plan can run only from a particular CICS connection or a particular IMS region; this cannot be enforced in your code. An example follows:

 BIND PLAN(CICSONLY)-    MEMBER(CERTDBRM)-    ACTION(ADD)-    ISOLATION(CS)-    OWNER(DB2USER1)-    QUALIFIER(DB2USER1)-    CACHESIZE(0)-    ENABLE(CICS)CICS(CON1) 

Later chapters discuss several other options at length, particularly the ones that affect the program's use of locks, such as the option ISOLATION. Table 11-1 lists options for BIND/REBIND and what is valid for plans, packages, and trigger packages (REBIND only; trigger packages are discussed in Chapter 15). The options chosen are recorded in the SYSPLAN and SYSPACKAGE catalog tables.

Table 11-1. BIND/REBIND Options

Option

Function

Valid Values

Plan

Package

Trigger

ACQUIRE

Determines whether to acquire resources specified in the DBRM at first access or allocation

USE, ALLOCATE

X

X

 

ACTION

Determines whether the object (plan or package) replaces an existing object with same name or is new

REPLACE, ADD

X, BO

X, BO

 

REPLACE(RPLVER)

 

X, BO

 

REPLACE(RETAIN)

X, BO

  

CACHESIZE

Determines the size, in bytes, of the authorization cache acquired in the EDM pool for the plan

Value of PLAN AUTH CACHE; decimal value

X

  

COPY

Determines that you are copying an existing package and names the package

Collection-id, package-id, COPYVER

 

X, BO

 

CURRENTDATA

Determines whether to require data currency for RO (read-only) and ambiguous cursors when isolation level is CS (cursor stability)

YES, NO

X

X

X

CURRENTSERVER

Determines the location to connect to before running the plan

Location-name

X

  

DBPROTOCOL

Specifies which protocol to use when connecting to a remote site identified by a three-part name

DRDA, PRIVATE

X

X

 

DEFER

Whether to defer preparation of dynamic SQL that refers to remote objects or to prepare them immediately

DEFER(PREPARE), NODEFER (PREPARE)

X

X

 

DEGREE

Determines whether to attempt to run a query using parallel processing to maximize performance

1, ANY

X

X

 

DISCONNECT

Determines which remote connections to destroy during commit operations

EXPLICIT, AUTOMATIC, CONDITIONAL

X

  

DYNAMICRULES

Determines what values apply at runtime for dynamic SQL attributes

RUN, BIND

X

X

 

DEFINEBIND, DEFINERUN, INVOKEBIND, INVOKERUN

 

X

 

ENABLE/ DISABLE

Determines which connections can use the plan or package

BATCH, CICS, DB2CALL, DLIBATCH, IMS, IMSBMP, IMSMPP, RRSAF, [*]

X

X

 

REMOTE

 

X

 

ENCODING

Specifies the application encoding for all static statements in the plan or package (defaults to installed selection)

ASCII, EBCIDIC, UNICODE, ccsid

X

X

 

EXPLAIN

Determines whether to populate the PLAN_TABLE with information about the SQL statements

NO, YES

X

X

X

FLAG

Determines what messages to display

I, W, E, C

X

X

X

IMMEDIATE

Determines whether immediate writes will be done for updates made to GBP-dependent page sets/partitions

NO, PH1, YES

X

X

X

ISOLATION

Determines how far to isolate an application from the effects of other running applications

RR, RS, CS, UR, NC

X

X

X

KEEPDYNAMIC

Determines whether DB2 keeps dynamic SQL statements after commit points

NO, YES

X

X

 

LIBRARY

Determines what PDS to search for DBRMs listed in the member option

dbrm-pds-name (can be multiple for PLAN)

X, BO

X, BO

 

MEMBER

Determines what DBRMs to include in the plan or package

dbrm-member-name (can be multiple for PLAN)

X, BO

X, BO

 

OPTHINT

Controls whether query optimization hints are used for static SQL

Hint-id

X

X

 

OPTIONS

Specifies which bind options to use for the new package

COMPOSITE, COMMAND

 

X, BC

 

OWNER

Determines the authorization ID or the owner of the object (plan or package)

Authorization-id

X

X

 

PACKAGE

Determines what package or packages to bind or rebind

Location-name. collection-id. package-id (version-id)

 

X

 

REBIND ONLY

 

X, RO

 

PATH

Determines the SQL path that DB2 uses to resolve unqualified UDTs, functions, and stored procedure name

Schema-name, USER, (schema-name, (USER)…)

X

X

 

PKLIST or NOPKLIST

Determines what package to include for the package list in the plan

(Location-name. collection-id. package-id...), PKLIST only

X

  

PLAN

Determines what plan or plans to bind or rebind

Plan-name

X

  

[*]

X, RO

  

QUALIFIER

Determines the implicit qualifier for unqualified names of objects in the plan or package

Qualifier-name

X

X

 

RELEASE

Determines when to release resources that the program uses: either at commit or at termination

COMMIT, DEALLOCATE

X

X

X

REOPT

Determines whether access path should be determined at runtime with host variables, parameter markers, and special registers

ONCE, ALWAYS, NONE

X

X

 

SQLERROR

Determines whether to create a package if there is an SQL error in it

NOPACKAGE, CONTINUE

 

X

 

SQLRULES

Determines whether a type 2 connection can be made according to DB2 rules for an existing connection

DB2, STD

X

  

VALIDATE

Determines whether to recheck at runtime "not found" and "not authorized" errors found at bind time

RUN, BIND

X

X

 


[*] Bold=default; BO=BIND only; BC=BIND COPY; RO=REBIND only

NOTE

If the values in the BIND/REBIND PACKAGE are different from those in the BIND/REBIND PLAN, the values for the BIND/REBIND PACKAGE will prevail, unless the plan is more restrictive.


Preliminary Steps

Before performing a bind, consider the following.

  • Determine how the DBRMs should be bound: into packages, directly into plans, or a combination of both methods.

  • Develop a naming convention and strategy for the most effective and efficient use of the plans and packages.

  • Determine when the application should acquire locks on the objects it uses: on all objects when the plan is first allocated or on each object in turn when that object is first used. For more details on locking, refer to Chapter 16.

Invalidations

If an object that a package depends on is dropped, the following occur.

  • If the package is not appended to any running plan, the package becomes invalid.

  • If the package is appended to a running plan and the drop occurs outside of that plan, the object is not dropped, and the package does not become invalid.

  • If the package is appended to a running plan and the drop occurs within that plan, the package becomes invalid.

In all cases, the plan does not become invalid unless it has a DBRM referencing the dropped object. If the package or plan becomes invalid, automatic rebind occurs the next time the package or plan is allocated. If automatic rebind fails, the package or plan may be marked as inoperative and execution fails.

Rebinding a Package

The way in which the collection ID (coll-id), package ID (pkg-id), and version ID (ver-id) on the REBIND PACKAGE subcommand are specified determines which packages are bound. REBIND PACKAGE does not apply to packages for which you do not have the BIND privilege.

An asterisk (*) used as an identifier for collections, packages, or versions does not apply to packages at remote sites. The asterisk can be used on the REBIND subcommand for local packages but not for packages at remote sites. Any of the following commands rebinds all versions of all packages in all collections at the local DB2 system for which you have the BIND privilege:

 REBIND PACKAGE (*) REBIND PACKAGE (*.*) REBIND PACKAGE (*.*.(*)) 

Either of the following commands rebinds all versions of all packages in the local collection REGS for which you have the BIND privilege:

 REBIND PACKAGE (REGS.*) REBIND PACKAGE (REGS.*.(*)) 

Either of the following commands rebinds the empty string version of the package TESTPK in all collections at the local DB2 system for which you have the BIND privilege:

 REBIND PACKAGE (*.TESTPK) REBIND PACKAGE (*.TESTPK.()) 

Package Lists

Using the PKLIST keyword during rebind replaces any previously specified package list. Omitting the PKLIST keyword allows the use of the previous package list for rebinding. Using the NOPKLIST keyword deletes any package list specified when the plan was previously bound. The following example rebinds PLANTK and changes the package list:

 REBIND PLAN(PLANTK) PKLIST(GROUP1.*) MEMBER(TEST) 

The following example rebinds the plan and drops the entire package list:

 REBIND PLAN(PLANTK) NOPKLIST 

A list of REBIND subcommands can be generated for a set of plans or packages that cannot be described by using asterisks, using information in the DB2 catalog. You can then issue the list of subcommands through DSN.

One situation in which the technique is particularly useful is in completing a rebind operation that has terminated for lack of resources. A rebind for many objectssay, REBIND PACKAGE (*) for an ID with SYSADM authorityterminates if a needed resource becomes unavailable. As a result, some objects are successfully rebound and others are not. If you repeat the subcommand, DB2 attempts to rebind all the objects again. But if you generate a REBIND subcommand for each object that was not rebound and issue those, DB2 does not repeat any work already done and is not likely to run out of resources.

Automatic Rebinding

Automatic rebind might occur if an authorized user invokes a plan or package when the attributes of the data on which the plan or package depends have changed or if the environment in which the package executes changes. Whether the automatic rebind occurs depends on the value of the field AUTO BIND on installation panel DSNTIPO. The options used for an automatic rebind are those used during the most recent bind process.

In most cases, DB2 marks as invalid a plan or package that needs to be automatically rebound. Following are a few common situations in which DB2 marks a plan or package as invalid:

  • When a table, index, or view on which the plan or package depends is dropped

  • When the authorization of the owner to access any of those objects is revoked

  • When the authorization to execute a stored procedure is revoked from a plan or package owner, and the plan or package uses the CALL literal form to call the stored procedure

  • When a table on which the plan or package depends is altered to add a TIME, TIMESTAMP, or DATE column

  • When a created temporary table on which the plan or package depends is altered to add a column

  • When a user-defined function on which the plan or package depends is altered

  • Whether a plan or package is valid is recorded in column VALID of catalog tables SYSPLAN and SYSPACKAGE

  • When an index, table, or column definition changes via an ALTER

In the following cases, DB2 might automatically rebind a plan or package that has not been marked as invalid.

  • A plan or package is bound in a different release of DB2 from the release in which it was first used.

  • A plan or package has a location dependency and runs at a location other than the one at which it was bound. This can happen when members of a data sharing group are defined with location names, and a package runs on a different member from the one on which it was bound.

DB2 marks a plan or package as inoperative if an automatic rebind fails. Whether a plan or package is operative is recorded in column OPERATIVE of the SYSPLAN and SYSPACKAGE catalog tables.

Whether EXPLAIN runs during automatic rebind depends on the value of the field EXPLAIN PROCESSING on installation panel DSNTIPO and on whether you specified EXPLAIN(YES). Automatic rebind fails for all EXPLAIN errors except "PLAN_TABLE not found."

NOTE

The SQLCA is not available during automatic rebind; therefore, some error messages may not be available.


Removing a Plan or Package

One way to remove a plan or a package is to use the FREE command. The FREE command removes the object from the catalog tables, and it will no longer be available for use. The following example frees all the packages in the CERTCL collection:

 FREE PACKAGE (CERTCL.*) 

A package can also be dropped using the DROP PACKAGE SQL statement. The following is an example of dropping the DB2CERT package:

 DROP PACKAGE DB2USER.DB2CERT 



DB2 for z. OS Version 8 DBA Certification Guide
DB2 for z/OS Version 8 DBA Certification Guide
ISBN: 0131491202
EAN: 2147483647
Year: 2003
Pages: 175
Authors: Susan Lawson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net