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.
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:
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:
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.
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.
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"'
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.
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
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.
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.
A few methods are used for binding and rebinding packages and plans.
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.
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.
Before performing a bind, consider the following.
If an object that a package depends on is dropped, the following occur.
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.())
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 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:
In the following cases, DB2 might automatically rebind a plan or package that has not been marked as invalid.
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."
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