DB2 application programs often include embedded SQL statements. In order to compile these programs, the SQL statements must be converted into a language recognized by the compiler or the assembler. The DB2 pre-compiler or a host-language compiler needs to be used to
The pre-compiler "prepares" the source program for compilation by replacing each EXEC SQL statement with a language-specific CALL statement and placing the SQL text in comment. (DB2 is not accessed during this process.) The pre-compiler can be invoked in DB2I or in batch and
The SQL statements are replaced during the pre-compile 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.
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. 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.
- Replace the SQL statements in the source programs with compatible code.
- Create a database request module (DBRM), which communicates the SQL requests to DB2 during the bind process.
The pre-compiler "prepares" the source program for compilation by replacing each EXEC SQL statement with a language-specific CALL statement and placing the SQL text in comment. (DB2 is not accessed during this process.) The pre-compiler can be invoked in DB2I or in batch and
- Includes the DCLGEN member as specified and the 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 data set (PDS)
The SQL statements are replaced during the pre-compile 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.
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. 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.
No comments:
Post a Comment