Precompile and Bind

Team-Fly    

 
DB2 Universal Database for OS/390 v7.1 Application Certification Guide
By Susan Lawson
Table of Contents
Chapter 6.  Binding an Application Program

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

  • Replace the SQL statements in the source programs with compilable code.

  • Create a database request module (DBRM), which communicates the SQL requests to DB2 during the bind process.

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.

graphics/06fig01.gif

Precompile

The 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:

  • Includes DCLGEN member.

  • Includes SQLCA.

  • Looks for SQL statements and for host-variable definitions.

  • Verifies the SQL syntax.

  • Matches each column and table name in the SQL to the DECLARE TABLE statements.

  • Prepares the SQL for compilation or assembly in the host language.

  • Produces a DBRM and stores it in a partitioned dataset (PDS).

  • Can be invoked in DB2I or in batch.

NOTE

graphics/note_icon.jpg

DB2 is not accessed during this process.


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.

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

graphics/note_icon.jpg

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


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:

  • Checks SQL syntax.

  • Checks security.

  • Compares column and table names against DB2 catalog.

  • Builds access path strategy for each SQL statement.

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

Plans and Packages

A 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 Ratio

It 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 Packages

Packages need to be used based on the application design and objectives. The following are a list of advantages provided by packages:

  • Ease of maintenance: With packages, there is no need to bind the entire plan 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 for the ability to add packages to an existing application plan without having to bind the entire plan again.

  • Versioning: This allows for the ability to maintain several versions of a plan without using packages requiring a separate plan for each version, and therefore separate plan names and RUN commands. This is discussed later in this section.

  • 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 they 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. In order to redirect SQL statements, for example, from a test table to a production table, all that is required is a rebind.

  • CICS flexibility: With packages, there is no need to have dynamic plan selection and the associated exit routine. A package listed within a plan is not accessed until it is executed. However, it is possible to use dynamic plan selection and packages together. This 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

graphics/note_icon.jpg

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


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

graphics/note_icon.jpg

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


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, 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 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

graphics/note_icon.jpg

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. 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

  • 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 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

graphics/note_icon.jpg

Regardless of what the plan contains, a plan must be bound before the application can run.


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.

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 Rebinding

There are a few methods used for BINDing and REBINDing packages and plans.

  • The BIND (ADD) option can be used to allow for the creation of a new plan or package.

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

  • REBINDing is used when a program has not changed but maybe 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, 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

Option

Valid Values

Plan

Package

Trigger

ACQUIRE

USE, ALLOCATE

X

X

 

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

ACTION

REPLACE, ADD

X, BO

X, BO

 
 

REPLACE(RPLVER)

 

X, BO

 
 

REPLACE(RETAIN)

X, BO

   

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

CACHESIZE

Value of PLAN AUTH CACHE; decimal value

X

   

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

COPY

Collection-id, package-id, COPYVER

 

X, BO

 

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

CURRENTDATA

YES, NO

X

X

X

Determines whether to require data currency for RO and ambiguous cursors when isolation level is CS

CURRENTSERVER

Location-name

X

   

Determines the location to connect to before running the plan

DBPROTOCOL

DRDA, PRIVATE

X

X

 

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

DEFER

DEFER(PREPARE), NODEFER(PREPARE)

X

X

 

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

DEGREE

1, ANY

X

X

 

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

DISCONNECT

EXPLICIT, AUTOMATIC, CONDITIONAL

X

   

Determines which remote connections to destroy during commit operations

DYNAMICRULES

RUN, BIND

X

X

 
 

DEFINEBIND, DEFINERUN, INVOKEBIND, INVOKERUN

 

X

 

Determines what values apply at runtime for dynamic SQL attributes

ENABLE DISABLE

BATCH, CICS, DB2CALL, DLIBATCH, IMS, IMSBMP, IMSMPP, RRSAF

X

X

 
 

REMOTE

 

X

 

Determines which connections can use the plan or package

ENCODING

ASCII, EBCIDIC, UNICODE, ccsid

X

X

 

Specifies the application encoding for all static statements in the plan or package

EXPLAIN

NO, YES

X

X

X

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

FLAG

I, W, E, C

X

X

X

Determines what messages to display

IMMEDIATE

NO, PH1, YES

X

X

 

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

ISOLATION

RR, RS, CS, UR, NC

X

X

X

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

KEEPDYNAMIC

NO, YES

X

X

 

Determines whether DB2 keeps dynamic SQL statements after commit points

LIBRARY

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

X, BO

X, BO

 

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

MEMBER

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

X, BO

X, BO

 

Determines what DBRMs to include in the plan or package

OPTHINT

Hint-id

X

X

 

Controls whether query optimization hints are used for static SQL

OPTIONS

COMPOSITE, COMMAND

 

X, BC

 

Specifies which bind options to use for the new package

OWNER

Authorization-id

X

X

 

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

PACKAGE

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

 

X

 
 

( [*] )

 

X, RO

 

Determines what package or packages to bind or rebind

PATH

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

X

X

 

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

PKLIST or NOPKLIST

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

X

   

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

PLAN

Plan-name

X

   
 

( [*] )

X, RO

   

Determines what plan or plans to bind or rebind

QUALIFIER

Qualifier-name

X

X

 

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

RELEASE

COMMIT, DEALLOCATE

X

X

X

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

REOPT

NOREOPT(VARS), REOPT(VARS)

X

X

 

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

SQLERROR

NOPACKAGE, CONTINUE

 

X

 

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

SQLRULES

DB2, STD

X

   

Determines if a Type 2 connection can be made according to DB2 rules for an existing connection

VALIDATE

RUN, BIND

X

X

 

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

[*] BO BIND only, BC BIND COPY, RO REBIND only

NOTE

graphics/note_icon.jpg

If the values in BIND/REBIND PACKAGE are different from those in BIND/REBIND PLAN, then the values for the BIND/REBIND PACKAGE will prevail.


Preliminary Steps

Before performing a bind, consider the following:

  • Determine how the DBRMs should be bound (i.e., 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, "Locking and Concurrency.)

Invalidations

If an object that a package depends on is dropped, the following occurs:

  • 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.

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 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 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 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

  • 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.

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

graphics/note_icon.jpg

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


Removing a Plan or Package

The 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


DB2 Universal Database for OS. 390 v7. 1 Application Certification Guide
DB2(R) Universal Database for OS/390 V7.1 Application Certification Guide (IBM DB2 Certification Guide Series)
ISBN: 0131007718
EAN: 2147483647
Year: 2002
Pages: 163
Authors: Susan Lawson

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