Precompile and Bind DB2 application programs include SQL statements. In order to compile these programs, the SQL statements must be converted into a language recognized by the compiler or assembler. The DB2 precompiler or a host-language compiler needs to be used to
After the source program has been precompiled, a load module is created, and possibly one or more packages and an application plan are created. Creating a load module involves compiling and link-editing the modified source code that is produced by the precompiler. Creating a package or an application plan, a process unique to DB2, involves binding one or more DBRMs. See Figure 6-1. Figure 6-1. Program preparation.
PrecompileThe precompiler "prepares" the source program for compilation by replacing EXEC SQL by a CALL and by putting the SQL in comment. The precompiler performs the following:
NOTE
The DBRM that is created contains extracted, somewhat modified, parsed SQL source code. It can be stored as a member in a PDS. One DBRM is created for each precompile, and it will then become the input to the BIND process. The SQL statements are replaced during the precompile process with a call to the DSNHLI module. This call will contain the necessary parameters (DBRM name, timestamp, statement number, address of host variables , and address of SQLCA) in order to locate the access path needed to execute the SQL statement associated with the call when the DBRM and modified source are used together at execution time. BindingThe 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
When a program is preprocessed for DB2, there are normally two outputs: 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 and another output containing 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 a proper access path can be determined. The bind process performs the following:
These processes are discussed in more detail later in this chapter. Plans and PackagesA DBRM can be bound in a package or directly into a plan. In addition, packages can be bound into logical groups called collections. These packages or collections 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, and it will contain information representing optimized SQL, which may just be an access path for the SQL to use to process the data required, or it could be information representing a SQL statement that was first rewritten by the optimizer. A plan can be bound for multiple packages, collections, and/or DBRMs. Each package can be bound independently of a plan. If a DBRM is bound to a plan and then is rebound, all DBRMs are rebound as well. The output will contain the access path information for each SQL statement. When determining the maximum size of a plan, several physical limitations must be considered , including the time required to bind the plan, the size of the environmental descriptor manager (EDM) pool, and fragmentation. There are no restrictions to the number of DBRMs that 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 referenced from a DB2 application. A package corresponds to a program source module. Packages are stored in the DB2 catalog 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, since it is performed prior to the execution of the SQL statement. Packages cannot be directly referenced in a SQL data manipulation (DML) statement. Most applications that access a DB2 database will have a package or group of packages stored (bound) in the system catalog tables. The package is the input to the plan bind using the PKLIST options. A package must be bound into a plan before it is usable. An example of a plan bind is shown below: BIND PLAN (certpln) PKLIST (col1.*) Plan to Package RatioIt is extremely difficult to justify the use of one plan containing all the packages for an installation or even for just all CICS transactions or batch jobs. Plans need to be granular. Large cumbersome plans 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. Advantages of PackagesPackages need to be used based on the application design and objectives. The following are a list of advantages provided by packages:
CollectionsA 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
The CURRENT PACKAGESET special register will allow for package searches on specific collections and will disregard any other DBRMs. Below is an example of using 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
VersioningMaintaining 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, which allow 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. Binding and RebindingThe BIND PACKAGE subcommand allows you to bind DBRMs individually. It provides the ability to test different versions of an application without extensive rebinding. NOTE
Even when DBRMs are bound into packages, all programs must be designated in an application plan. 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. It is stored in the DB2 catalog. In addition to building packages and plans, the bind process
Depending upon 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
Binding or Rebinding a Package or Plan in UsePackages 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. There is no ability to bind or rebind a package or a plan while it is running. However, a different version of a package that is running can be bound. Options for Binding and RebindingThere are a few methods 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, there is a bind option that can be used to ensure that a package or plan can run only from a particular CICS connection or a particular information management systems (IMS) regionthis cannot be enforced in your code. An example is shown below: BIND PLAN(CICSONLY)- MEMBER(CERTDBRM)- ACTION(ADD)- ISOLATION(CS)- OWNER(DB2USER1)- QUALIFIER(DB2USER1)- CACHESIZE(0)- ENABLE(CICS)CICS(CON1) Several other options are discussed at length in later chapters, particularly the ones that affect the program's use of locks, such as the option ISOLATION. Table 6-1 lists options for BIND/REBIND and what it is valid for plans, packages, and trigger packages (REBIND only; trigger packages are discussed in Chapter 15, "Object Relational Programming"). The options chosen are recorded in the SYSPLAN and SYSPACKAGE catalog tables. Table 6-1. Bind/Rebind Options
NOTE
Preliminary StepsBefore performing a bind, consider the following:
InvalidationsIf an object that a package depends on is dropped, the following occurs:
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. Rebinding a PackageThe 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 ListsUsing the PKLIST keyword 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 objects, say REBIND PACKAGE (*) for an ID with SYSADM authority, terminates 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 RebindingAutomatic 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 the options used during the most recent bind process. In most cases, DB2 marks a plan or package that needs to be automatically rebound as invalid. A few common situations in which DB2 marks a plan or package as invalid are
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." NOTE
Removing a Plan or PackageThe only way to remove a plan or package is to use the FREE command. This command removes the object from the catalog tables, and it will no longer be available for use. The example below frees all of the packages in the CERTCL collection: FREE PACKAGE (CERTCL.*) |
Team-Fly |
Top |