BIND Guidelines

 <  Day Day Up  >  

BIND Guidelines

Using the following tips and techniques will ensure effective execution of the BIND statement and the creation of efficiently bound DB2 application programs.

Administer Initial Binds Centrally

A centralized administration group (DBA, bind agent, and so on) should be responsible for all initial binds of applications plans ( BIND ADD ). This provides a point of control for administering plan and package use and freeing old or unused plans and packages when they are no longer required. Centralized control also makes it easier to enforce your shop's DB2 naming standards.

Keep Statistics Current for Binding

Before binding, ensure that the RUNSTATS utility has been executed recently for every table accessed by the plan or package to be bound. This allows the bind process to base access path selections on the most recent statistical information. Without up-to-date statistics, DB2 will create access paths based on inaccurate information ”which can cause poor performance due to sub-optimal access paths.

Avoid Default Parameters

Specify every bind parameter. Defaults are used for certain bind parameters when the BIND command is issued without specifying them. This could be dangerous because the default options are not always the best for performance and concurrency.

Group Like Programs into Collections

You should group like programs by binding them to packages and specifying the same collection identifier. If a customer application is composed of 12 DB2 programs, for example, bind each into a separate package with a collection identifier of CUSTOMER . This makes the administration of packages belonging to the same application easy.

Use Wildcard Package Lists

When multiple packages must exist in the same plan, favor using the wildcard capability of the PKLIST parameter of the BIND PLAN statement. To bind the 12-customer application packages (mentioned in the last guideline) to a single plan, for example, you could specify PKLIST(CUSTOMER.*) . Additionally, all new packages bound in the CUSTOMER collection are automatically added to that plan.

Specify Collections and Packages Carefully in the PKLIST

Avoiding the following scenario will eliminate confusing which package is actually being used during program execution:

  • Binding the same DBRM into different collections (such as C1 and C2 )

  • Binding a plan with a package list specifying both collections ( C1.*,C2.* ), both packages ( C1.PACKAGE , C2.PACKAGE ), or a combination ( C1.*,C2.PACKAGE or C1.PACKAGE,C2.* )

  • Failing to specify SET CURRENT PACKAGESET in the application program

If the current package set is blank, the package is in any collection in the EDM pool, and the consistency tokens match, DB2 will return the package. It does not matter whether the package is from C1 or C2 . For this reason, specifying SET CURRENT PACKAGESET is imperative if you have a package bound into more than one collection in the PKLIST of the same plan. Although many think that DB2 uses packages in the order specified in the package list, this is only true if none of the packages are in the EDM Pool when the plan executes. If a matching package is in the EDM pool and can be used, DB2 will use it and the program might execute an improper package.

Specify Explicit Consistency Tokens

Favor the specification of an explicit consistency token for package versioning over allowing it to default to a timestamp. If a new version with a new timestamp is created every time a package is bound, the DB2 Catalog quickly becomes cluttered with unused versions. Explicitly specifying a consistency token to control versions that must be saved is better. You could, for example, specify a release number such as REL100 , and then increment the number to REL101 , REL102 , REL200 , and so on, to indicate different versions of the software. In this manner, only one version, rather than many versions of each release will exist.

Use the QUALIFIER Parameter

When binding packages, use the QUALIFIER parameter to specify an identifier to be used by the bind process to qualify all tables referenced by SQL statements in the DBRM being bound. The DSN8510.DEPT table, for example, is accessed if the following statement is embedded in a program bound to a package specifying a QUALIFIER of DSN8510 :

 

 EXEC SQL     SELECT  DEPTNO, DEPTNAME     INTO    :DEPTNO, :DEPTNAME     FROM    DEPT END-EXEC. 

Users can specify a qualifier different from their userid if they have the necessary authority to issue the BIND command for the plan or package. The users do not need to be SYSADM or have a secondary authid, as is required with the OWNER parameter.

Optionally, the OWNER parameter can be used to qualify tables at BIND time. When specifying an OWNER , however, the binding agent must be either a SYSADM or set up with a secondary authid equal to the owner being specified.

Strategically Implement Multiple Qualified Tables

If a single plan needs to access tables with different qualifiers, consider one of the following two strategies. The first strategy is to create aliases or synonyms such that every table or view being accessed has the same qualifier. The second method is to separate the tables being accessed into logical processing groups by qualifier. Code a separate program to access each processing group. Then bind each program to a separate package, specifying the qualifier of the tables in that program. Finally, bind all the packages into a single plan.

Use One Program and Multiple Packages for Mirror Tables

When you use mirror tables, one program can access different tables. Suppose that you need an employee table for every month of the year. Each employee table is modeled after DSN8810.EMP but contains only the active employees for the month it supports. The following tables, for example, are differentiated by their qualifier:

 

 JANUARY.EMP FEBRUARY.EMP MARCH.EMP   .   .   . NOVEMBER.EMP DECEMBER.EMP 

Assume that you need 12 reports , each one providing a list of employees for a different month. One program can be coded to access a generic, unqualified EMP table. You then could bind the program to 12 separate packages (or plans), each specifying a different qualifier ( JANUARY through DECEMBER ). For more information on mirror tables, refer to Chapter 5, "Data Definition Guidelines."

CAUTION

This approach will likely increase EDM pool usage. This is so because the package will be allocated once for every collection.


Use the Correct ACTION Parameter

Specify the proper ACTION parameter for your bind. You can specify two types of actions: ADD or REPLACE . ADD indicates that the plan is new. REPLACE indicates that an old plan by the same name will be replaced . Specifying ACTION ( REPLACE ) for a new plan does not cause the bind to fail ”it merely causes confusion.

Establish BIND PLAN Parameter Guidelines

Favor the use of the following parameters when binding application plans:

 

 ISOLATION (CS) VALIDATE (BIND) ACTION (REPLACE) NODEFER (PREPARE) FLAG (I) ACQUIRE (USE) RELEASE (COMMIT) DEGREE (1) CURRENTDATA (NO) EXPLAIN (YES) 

These BIND PLAN parameters usually produce the most efficient and effective DB2 plan. However, one set of BIND parameters will not be applicable for every DB2 application program. Reasons for choosing different options are discussed in other guidelines in this chapter.

Establish BIND PACKAGE Parameter Guidelines

Favor the use of the following parameters when binding packages:

 

 ISOLATION (CS) VALIDATE (BIND) ACTION (REPLACE) SQLERROR (NOPACKAGE) FLAG (I) RELEASE (COMMIT) DEGREE (1) CURRENTDATA (NO) EXPLAIN (YES) 

These BIND PACKAGE parameters usually produce the most efficient and effective DB2 package. Once again, one set of BIND parameters will not be applicable for every DB2 application program. Other guidelines in this chapter cover the occasions when you should choose different options.

Take Care When Specifying Isolation Level

The ISOLATION parameter of the BIND command specifies the isolation level of the package or plan. The isolation level determines the mode of page locking implemented by the program as it runs.

DB2 implements page and row locking at the program execution level, which means that all page or row locks are acquired as needed during the program run. Page or row locks are released when the program issues a COMMIT or ROLLBACK .

You can specify the following four isolation levels:

  • Cursor stability ( CS )

  • Repeatable read ( RR )

  • Read stability ( RS )

  • Uncommitted read ( UR )

They significantly affect how the program processes page locks.

Use Uncommitted Read with Caution

Anyone accustomed to application programming when access to a database is required understands the potential for concurrency problems. To ensure data integrity when one application program attempts to read data that is in the process of being changed by another, the DBMS must forbid access until the modification is complete. Most DBMS products, DB2 included, use a locking mechanism for all data items being changed. Therefore, when one task is updating data on a page, another task cannot access data (read or update) on that same page until the data modification is complete and committed.

Programs that read DB2 data typically access numerous rows during their execution and are thus quite susceptible to concurrency problems. DB2 provides read-through locks, also know as a dirty read or uncommitted read , to help overcome concurrency problems. When you're using an uncommitted read, an application program can read data that has been changed but is not yet committed.

Dirty read capability is implemented at BIND time by specifying ISOLATION(UR) . Application programs bound using the UR isolation level will read data without taking locks. This way, the application program can read data contained in the table as it is being manipulated. Consider the following sequence of events:

  1. To change a specific value, at 9:00 a.m. a transaction containing the following SQL is executed:

     

     UPDATE    DSN8810.EMP    SET FIRSTNME = '"MICHELLE'" WHERE     EMPNO = '10020'; 

    The transaction is a long-running one and continues to execute without issuing a COMMIT .

  2. At 9:01 a.m., a second transaction attempts to SELECT the data that was changed, but not committed.

If the UR isolation level was specified for the second transaction, it would read the changed data even though it had yet to be committed. Obviously, if the program doesn't need to wait to take a lock and merely reads the data in whatever state it happens to be at that moment, the program will execute faster than if it had to wait for locks to be taken and resources to be freed before processing.

The implications of reading uncommitted data, however, must be carefully examined before being implemented. Several types of problems can occur. Using the previous example, if the long-running transaction rolled back the UPDATE to EMPNO ' 10020' , the program using dirty reads may have picked up the wrong name (" 'MICHELLE' ") because it was never committed to the database.

Inaccurate column values are not the only problems that can be caused by using ISOLATION(UR) . A dirty read can cause duplicate rows to be returned where none exist. Alternatively, a dirty read can cause no rows to be returned when one (or more) actually exists. Additionally, an ORDER BY clause does not guarantee that rows will be returned in order if the UR isolation level is used. Obviously, these problems must be taken into consideration before using the UR isolation level. The following rules apply to ISOLATION(UR) :

  • The UR isolation level applies to read-only operations: SELECT , SELECT INTO , and FETCH from a read-only result table.

  • Any application plan or package bound with an isolation level of UR will use uncommitted read functionality for any read-only SQL. Operations contained in the same plan or package that are not read-only will use an isolation level of CS .

  • The isolation level defined at the plan or package level during BIND or REBIND can be overridden as desired for each SQL statement in the program. You can use the WITH clause to specify the isolation level for any individual SQL statement, as in the following example:

     

     SELECT EMPNO, LASTNAME FROM   DSN8810.EMP WITH UR; 

    The WITH clause is used to allow an isolation level of RR , RS , CS , or UR to be used on a statement-by-statement basis. The UR isolation level can be used only with read-only SQL statements. This includes read-only cursors and SELECT INTO statements. The CS , RR , and RS isolation levels can be specified for SELECT , INSERT , UPDATE , and DELETE statements. The WITH clause, however, cannot be used with subselects.

  • DB2 will not choose UR isolation with an access path that uses a Type-1 index. If the plan or package is rebound to change to UR isolation, DB2 will not consider any access paths that use a Type-1 index. If an acceptable Type-2 index cannot be found, DB2 will choose a table scan. This applies only to DB2 V5 and older subsystems because Type 2 indexes are the only type of indexes supported as of DB2 V6.

When is it appropriate to use UR isolation? The general rule of thumb is to avoid UR whenever the results must be 100% accurate. Following are examples of when this would be true:

  • Calculations that must balance are being performed on the selected data

  • Data is being retrieved from one source to insert into or update another

  • Production, mission-critical work is being performed that cannot contain or cause data integrity problems

In general, most current DB2 applications will not be candidates for dirty reads. In a few specific situations, however, the dirty read capability will be of major benefit. Consider the following cases in which the UR isolation level could prove to be useful:

  • Access is required to a reference, code, or look-up table that basically is static in nature. Due to the non-volatile nature of the data, a dirty read would be no different from a normal read the majority of the time. In those cases when the code data is being modified, any application reading the data would incur minimum, if any, problems.

  • Statistical processing must be performed on a large amount of data. Your company, for example, might want to determine the average age of female employees within a certain pay range. The impact of an uncommitted read on an average of multiple rows will be minimal because a single value changed will not greatly impact the result.

  • Dirty reads can prove invaluable in a data warehousing environment that uses DB2 as the DBMS. A data warehouse is a time-sensitive, subject-oriented, store of business data that is used for online analytical processing. Other than periodic data propagation and/or replication, access to the data warehouse is read-only. Because the data is generally not changing, an uncommitted read is perfect in a read-only environment due to the fact that it can cause little damage. More data warehouse projects are being implemented in corporations worldwide and DB2 with dirty read capability is a very wise choice for data warehouse implementation.

  • In those rare cases when a table, or set of tables, is used by a single user only, UR can make a lot of sense. If only one individual can be modifying the data, the application programs can be coded such that all (or most) reads are done using UR isolation level, and the data will still be accurate.

  • Finally, if the data being accessed already is inconsistent, little harm can be done using a dirty read to access the information.

CAUTION

Although the dirty read capability can provide relief of concurrency problems and deliver faster performance in specific situations, it also can cause data integrity problems and inaccurate results. Be sure to understand the implications of the UR isolation level and the problems it can cause before diving headlong into implementing it in your production applications.


Sometimes, just ensuring that you are using lock avoidance can deliver better performance and can be a good alternative to dirty read processing. More information on lock avoidance follows later in this chapter.

Use Caution Before Binding with Repeatable Read ISOLATION

With repeatable read, or RR , all page locks are held until they are released by a COMMIT . Cursor stability, or CS , releases read-only page locks as soon as another page is accessed.

In most cases, you should specify CS to enable the greatest amount of application program concurrency. RR , however, is the default isolation level.

Use the RR page locking strategy only when an application program requires consistency in rows that may be accessed twice in one execution of the program, or when an application program requires data integrity that cannot be achieved with CS . Programs of this nature are rare.

For an example of the first reason to use RR page locking, consider a reporting program that scans a table to produce a detail report, and then scans it again to produce a summarized managerial report. If the program is bound using CS , the results of the first report might not match the results of the second.

Suppose that you are reporting the estimated completion dates for project activities. The first report lists every project and the estimated completion date. The second, managerial report lists only the projects with a completion date greater than one year.

The first report indicates that two activities are scheduled for more than one year. After the first report but before the second, however, an update occurs. A manager realizes that she underestimated the resources required for a project. She invokes a transaction (or uses QMF) to change the estimated completion date of one of her project's activities from 8 months to 14 months. The second report is produced by the same program, but reports 3 activities.

If the program has used an isolation level of RR rather than CS , an update between the production of the two reports would not have been allowed because the program would have maintained the locks it held from the generation of the first report.

For an example of the second reason to use RR page locking, consider a program that is looking for pertinent information about employees in the information center and software support departments who make more than $30,000 in base salary. The program opens a cursor based on the following SELECT statement:

 

 SELECT  EMPNO, FIRSTNME, LASTNAME,         WORKDEPT, SALARY FROM    DSN8810.EMP WHERE   WORKDEPT IN ('C01', 'E21') AND     SALARY > 30000; 

The program then begins to fetch employee rows. Department 'C01' is the information center and department 'E21' is software support. Assume further, as would probably be the case, that the statement uses the DSN8810.XEMP2 index on the WORKDEPT column. An update program that implements employee modifications is running concurrently. The program, for example, handles transfers by moving employees from one department to another, and implements raises by increasing the salary.

Assume that Sally Kwan, one of your employees, has just been transferred from the information center to software support. Assume further that another information center employee, Heather Nicholls, received a 10% raise. Both of these modifications will be implemented by the update program running concurrently with the report program.

If the report program were bound with an isolation level of CS , the second program could move Sally from C01 to E21 after she was reported to be in department C01 but before the entire report was finished. Thus, she could be reported twice: once as an information center employee and again as a software support employee. Although this circumstance is rare, it can happen with programs that use cursor stability. If the program were bound instead with RR , this problem could not happen. The update program probably would not be allowed to run concurrently with a reporting program, however, because it would experience too many locking problems.

Now consider Heather's dilemma. The raise increases her salary 10%, from $28,420 to $31,262. Her salary now fits the parameters specified in the WHERE condition of the SQL statement. Will she be reported? It depends on whether the update occurs before or after the row has been retrieved by the index scan, which is clearly a tenuous situation. Once again, RR avoids this problem.

You might be wondering, "If CS has the potential to cause so many problems, why are you recommending its use? Why not trade the performance and concurrency gain of CS for the integrity of RR ?" The answer is simple: The types of problems outlined are rare. The expense of using RR , however, is so great in terms of concurrency that the tradeoff between the concurrency expense of RR and the efficiency of CS usually is not a sound one.

Consider Read Stability ( RS ) Over Repeatable Read ( RR )

The RS isolation level is similar in functionality to the RR isolation level. It indicates that a retrieved row or page is locked until the end of the unit of work. No other program can modify the data until the unit of work is complete, but other processes can insert values that might be read by your application if it accesses the row a second time.

Use read stability only when your program can handle retrieving a different set of rows each time a cursor or singleton SELECT is issued. If using read stability, be sure your application is not dependent on having the same number of rows returned each time.

Favor Acquiring Table Space Locks When the Table Space Is Used

In addition to a page locking strategy, every plan also has a table space locking strategy. This strategy is implemented by two bind parameters: ACQUIRE and RELEASE .

Remember that a page lock is acquired when the page is requested , and is released after a COMMIT or a ROLLBACK . Table space locking is different. DB2 uses a mixed table space locking strategy ”the programmer specifies when to acquire and release table space locks by means of the ACQUIRE and RELEASE parameters. Table space locking is implemented only at the plan level; it is not implemented at the package level.

The options for the ACQUIRE parameter are USE and ALLOCATE . When you specify USE , table space locks are taken when the table space is accessed. With ALLOCATE , table space locks are taken when the plan is first allocated.

The options for RELEASE are COMMIT and DEALLOCATE . When you specify the COMMIT option, locks are released at commit or rollback time. When you specify DEALLOCATE all locks are held until the plan finishes and is deallocated.

In general, use the following table space locking allocation strategy:

 

 ACQUIRE(USE) RELEASE(COMMIT) 

This provides your program with the highest degree of concurrency.

When you have conditional table access in your program, consider using the following lock and resource allocation strategy:

 

 ACQUIRE(USE) RELEASE(DEALLOCATE) 

With conditional table access, every invocation of the program does not cause that section of code to be executed. By specifying that locks will be acquired only when used, and released only when deallocated, you can increase the efficiency of a program because locks, once acquired, are held during the entire course of the program. This does reduce concurrency, however.

For a batch update program in which you know that you will access every table coded in your program, use the following lock and resource allocation strategy:

 

 ACQUIRE(ALLOCATE) RELEASE(DEALLOCATE) 

All locks are acquired as soon as possible and are not released until they are absolutely not needed. This strategy, too, will reduce concurrency.

For high-volume transactions (one or more transactions per second throughput), use a CICS protected entry thread ( RCT TYPE=ENTRY ) with the following strategy:

 

 ACQUIRE(ALLOCATE) RELEASE(DEALLOCATE) 

A high-volume transaction generally executes much faster if it is not bogged down with the accumulation of table space locks.

In all cases, you should obtain database administration approval before binding with parameters other than ACQUIRE(USE) and RELEASE(COMMIT) .

Specify Validation at BIND Time

A validation strategy refers to the method of checking for the existence and validity of DB2 tables and DB2 access authorization. You can use two types of validation strategies: VALIDATE(BIND) or VALIDATE(RUN) .

VALIDATE(BIND) , the preferred option, validates at bind time. If a table is invalid or proper access authority has not been granted, the bind fails.

VALIDATE(RUN) validates DB2 table and security each time the plan is executed. This capability is useful if a table is changed or authority is granted after the bind is issued. It does, however, impose a potentially severe performance degradation because each SQL statement is validated each time it is executed.

Always specify VALIDATE(BIND) for production plans. Use VALIDATE(RUN) only in a testing environment.

Request All Error Information

Always specify FLAG(I) , which causes the BIND command to return all information, warning, error, and completion messages. This option provides the greatest amount of information pertaining to the success or failure of the bind.

Specify an Appropriate CACHESIZE

The CACHESIZE parameter specifies the size of the authorization cache for a plan. The authorization cache is a portion of memory set aside for a plan to store valid authids that can execute the plan. By storing the authids in memory, the cost of I/O can be saved.

The cache can vary in size from 0 to 4096 bytes in 256 byte increments . For a plan with a small number of users, specify the minimum size, 256. If the plan will have large number of users, calculate the appropriate size as follows:

 

 CACHESIZE = ([number of concurrent users] * 8) + 32 

Take the number returned by the formula and round up to the next 256 byte increment making sure not to exceed 4096.

NOTE

The number 32 is added because the authid cache always uses 32 control bytes.


One final suggestion ”if the plan is executed only infrequently, or has been granted to PUBLIC , do not cache authids. Specify a CACHESIZE of zero.

As of DB2 V5, authorization can be cached for packages as well as plans. However, no CACHESIZE BIND parameter is available for packages. Instead, package caching must be enabled by the system administrator at the subsystem level.

Consider Using CURRENTDATA(NO) for Lock Avoidance

DB2 uses the lock avoidance technique to reduce the number of locks that need to be taken for read only processes. To enable lock avoidance for read only and ambiguous cursors, NO must be specified for the CURRENTDATA option. Unfortunately, YES is the default. By specifying CURRENTDATA(NO) you indicate that currency is not required for cursors that are read only or ambiguous.

Do not use CURRENTDATA(NO) if your program dynamically prepares and executes a DELETE WHERE CURRENT OF statement against an ambiguous cursor after that cursor is opened. DB2 returns a negative SQLCODE to the program if it attempts a DELETE WHERE CURRENT OF statement for any of the following cursors:

  • Cursor uses block fetching

  • Cursor uses query parallelism

  • Cursor that is positioned on a row that is modified by this or another application process

NOTE

For local access, CURRENTDATA(YES) means that the data cannot change while the cursor is positioned on it. CURRENTDATA(NO) is similar to CURRENTDATA(YES) except when a cursor is accessing a base table rather than a result table in a work file. With CURRENTDATA(YES) , DB2 can guarantee that the cursor and the base table are current, whereas CURRENTDATA(NO) does not.

For remote access, CURRENTDATA(YES) turns off block fetching for ambiguous cursors. The data returned with the cursor is current with the contents of the remote table or index for ambiguous cursors.


Consider Using DEGREE(ANY) for Parallelism

When DEGREE(ANY) is specified, DB2 will attempt to execute queries using parallel engines whenever possible. Parallel queries are typically deployed against partitioned table spaces, and can be used to access non-partitioned table spaces when specified in a join with at least one partitioned table space.

At optimization time, DB2 can be directed to consider parallelism by specifying DEGREE(ANY) at BIND time for packages and plan. Following are the three types of parallelism:

  • I/O ” multiple read engines

  • CPU ” multiple processor and multiple read engines

  • Sysplex ” multiple data sharing subsystems

Parallelism can significantly enhance the performance of queries against partitioned table spaces. By executing in parallel, elapsed time usually will decrease, even if CPU time does not. This results in an overall perceived performance gain because the same amount of work will be accomplished in less clock time.

Following are the types of queries that will benefit most from I/O parallelism:

  • Access large amount of data, but return only a few rows

  • Use column functions ( AVG , COUNT , COUNT_BIG , MIN , MAX , STDDEV , SUM , VARIANCE )

  • Access long rows

CPU parallelism extends the capabilities of I/O parallelism. When CPU parallelism is invoked, it is always used in conjunction with I/O parallelism. The reverse of this is not necessarily true. Most of the queries that benefit from I/O parallelism also will benefit from CPU parallelism because as the I/O bottlenecks are reduced, the CPU bottlenecks become more apparent.

Sysplex parallelism extends the parallel capabilities of DB2 even further. When Sysplex parallelism is employed, DB2 can spread a single query across multiple central processor complexes within a data sharing group. For more information on data sharing and Sysplex parallelism, refer to Chapter 19, "Data Sharing."

CAUTION

Before attempting to achieve parallel query operations using DEGREE(ANY) , be sure that your environment is suitable for parallelism. Do you have enough memory and the proper buffer pool configurations to take advantage of parallelism? Is the machine on which you are running your queries capable of parallelism? For example, a machine without multiple CPUs will not be able to run CPU parallelism.


You can use the PARAMDEG DSNZPARM to specify the maximum degree of parallelism for a parallel group. By specifying a maximum value DB2 is limited to the PARAMDEG value as the maximum degree of parallelism. This parameter can be used to throttle DB2 so that it does not create too many parallel tasks and therefore use too much virtual storage.

Specify NODEFER(PREPARE)

Specify NODEFER(PREPARE) rather than DEFER(PREPARE) unless your program contains SQL statements that access DB2 tables at a remote location and are executed more than once during the program's invocation. In this case, specifying DEFER(PREPARE) can reduce the amount of message traffic by preparing each SQL statement only once at the remote location, when it is first accessed. Subsequent execution of the same statement in the same unit of recovery does not require an additional PREPARE .

Use SQLERROR to Control Package Creation

Two options for the SQLERROR parameter exist: NOPACKAGE and CONTINUE . NOPACKAGE is the recommended option when not operating in a distributed environment. By specifying NOPACKAGE , a package will not be created when an SQL error is encountered .

The other option is CONTINUE , which will create a package even if an error is encountered. Because SQL syntax varies from environment to environment, CONTINUE is a viable option when operating in a distributed environment. The package can be created, regardless of the error with the understanding that the SQL will function properly at the remote location.

Specify EXPLAIN(YES) for Production BIND s

At a minimum, all production BIND s should be performed with the EXPLAIN(YES) option. This allows the proper monitoring of the production access path selection made by DB2. Binding without producing EXPLAIN output will hamper your performance tuning abilities , because you will not be able to determine the access paths chosen by DB2 without the EXPLAIN data.

Of course, in a testing environment you will not need to specify EXPLAIN(YES) for every single BIND . This is especially the case at the beginning of the development process. However, you should consider specifying EXPLAIN(YES) even in a test environment once your development efforts are significantly underway. By binding your test programs using EXPLAIN(YES) , you will have access path information at your disposal for your tuning efforts.

CAUTION

You will not necessarily assure efficient production access paths just because you achieve desirable access paths in your test environment. The only way to assure that access paths are the same in both test and production is to be sure that everything about the two environments are the same: statistics, system parameters, memory, and so on.


Use the ENABLE and DISABLE Parameters Effectively

You can use the ENABLE and DISABLE bind options to control the environment in which the plan or package being bound can be executed. ENABLE ensures that the plan or package operates in only the enabled environments. DISABLE permits execution of the plan or package by all subsystems except those explicitly disabled. ENABLE and DISABLE are mutually exclusive parameters (only one can be used per package or plan).

If a plan is bound specifying ENABLE(IMS) , for example, only the IMS subsystem is permitted to execute the plan. If a plan is bound with the DISABLE(CICS) option, the CICS subsystem is not permitted to execute this plan.

Be careful when using ENABLE and DISABLE because they may function differently than one might originally think. ENABLE explicitly enables an environment for execution. The enabled environment, however, is the only environment that can execute the plan or package. So ENABLE limits the environments in which a package or plan can execute. By contrast, specifying DISABLE actually is more open because only one specific area is disabled, thereby implicitly enabling everything else. The bottom line is that ENABLE is more limiting than DISABLE .

Table 13.1 shows valid ENABLE and DISABLE specifications.

Table 13.1. Environments that Can Be Enabled or Disabled

Specification

Package or plan is executed only

BATCH

As a batch job

DLIBATCH

As an IMS batch job

DB2CALL

With the Call Attach Facility

CICS

Online through CICS

IMS

Under the control of IMS

IMSBMP

As an IMS BMP (batch message processor)

IMSMPP

As an online IMS message processing program (that is, a transaction)

RRSAF

With the RRS Attachment Facility

REMOTE

As a remote program


ENABLE and DISABLE are great keywords to consider for plans granted to PUBLIC that rely on an external security mechanism (such as RACF) to control unauthorized access.

Retain Security When BIND ing Existing Plans

Be sure to specify the RETAIN parameter for existing plans. RETAIN indicates that all BIND and EXECUTE authority granted for this plan will be retained. If you fail to specify the RETAIN parameter, all authority for the plan is revoked .

Retain DBRMs Bound in Plans

Develop a consistent scheme for the maintenance and retention of DBRMs bound to application plans and packages. Ensure that DBRMs are copied to the appropriate library (test, education, production, and so on) before the binding of plans in the new environment. This applies to both new and modified programs.

Consider Dynamic Reoptimization

When host variables or parameter markers are used in SQL statements in an application program, DB2 does not know the values that will be supplied at execution time. This lack of information causes DB2 to guess at the best access path using the information available at BIND time.

By specifying the BIND parameter REOPT(VARS) , DB2 will reevaluate the access path at runtime when the host variable and parameter marker values are known. This should result in a better-formulated access path. Reoptimization, however, is not a panacea. Because DB2 must reevaluate access paths at execution time, additional overhead will be consumed. This overhead can negate any performance gains achieved by the new access paths. Enabling reoptimization does not guarantee a different access path; it only allows DB2 to formulate the access path based on the runtime values used.

In general, reoptimization can be an easy-to-implement alternative to dynamic SQL. The overhead of reoptimization will be less than that associated with dynamic SQL because reoptimization does not require statement parsing, authorization checking, dependency checking, or table decomposition.

Do Not Blindly Enable Reoptimization for All Programs

In general, consider specifying REOPT(VARS) in the following situations:

  • Application programs in which multiple SQL statements utilize host variables (or parameter markers)

  • SQL statements in which host variables (or parameter markers) are deployed against columns with very skewed distribution statistics

  • Application programs in which dynamic SQL was considered , but avoided because of its complexity or overhead

Before implementing reoptimization, conduct performance tests to determine its impact on transaction performance.

Consider Isolating Reoptimized Statements

The REOPT and NOREOPT parameters must be specified for an entire program when it is bound into a plan or package. Most programs commonly contain multiple SQL statements, not all of which will benefit from reoptimization.

Consider isolating specific SQL statements into a separate program, and binding it into a package. In this manner, individual SQL statements can be set for reoptimization without impacting the rest of the SQL in a program.

Consider Keeping Prepared Statements Past COMMIT

By specifying KEEPDYNAMIC(YES) , dynamic SQL statements can be held past a COMMIT point. Specify KEEPDYNAMIC(YES) for dynamic SQL programs in DB2 subsystems in which the dynamic SQL prepare cache is enabled. This causes fewer dynamic binds and optimizes the performance of dynamic SQL programs.

Note that when KEEPDYNAMIC(YES) is specified, you also must use NOREOPT(VARS) .

Specify the PATH Parameter

If UDTs, UDFs, or stored procedures are used in your program, be sure to specify the appropriate PATH parameter. The PATH identifies the schema names in the SQL path to be used for function resolution. Refer to Chapter 4 for more information on UDFs and UDTs and Chapter 15, "Using DB2 Stored Procedures," for more information on stored procedures.

You can specify a SQL PATH of up to 254 bytes in length. To determine the length of the SQL path, use the following calculation:

 

 length of each schema name + (2 * total number of names)  (for delimiters)  + (total number of names  1)  (for commas)  

For example, consider the following SQL path definition

 

 SQLPATH('SCHEMA21', 'SCHZ', 'SYSPROC') 

The length of this SQL path would be calculated as follows:

The length of each schema name added together: (8 + 4 + 7) = 19

Total number of schema names times two: (3 * 2) = 6

Total number of schema names minus one: (3 “ 1) = 2

Added together is 19 + 6 + 2 = 27

CAUTION

Be sure to specify the schema names in uppercase in the PATH definition.


Specify the Appropriate DYNAMICRULES Option for Dynamic SQL

The DYNAMICRULES parameter determines the characteristics of dynamic SQL. There are four types of behavior that dynamic SQL can exhibit:

  • BIND behavior

  • DEFINE behavior

  • INVOKE behavior

  • RUN behavior

The following are the six options for the DYNAMICRULES parameter:

DYNAMICRULES(RUN)

Dynamic SQL statements are processed using run behavior. Run behavior means that DB2 uses the authid of the running application and the SQL authid of the CURRENT SQLID special register for authorization checking for dynamic SQL statements. Furthermore, the CURRENT SQLID is used as the qualifier for unqualified table, view, and alias names. When bound with this option, the program can issue dynamic DCL ( GRANT and REVOKE ) or dynamic DDL ( ALTER , CREATE , DROP , and RENAME ). Run behavior is the only behavior that permits dynamic DCL and DDL.

DYNAMICRULES(BIND)

Dynamic SQL statements are processed using bind behavior. Bind behavior means that DB2 uses the authid of the plan or package for dynamic SQL authorization checking. The QUALIFIER value of the BIND is used as the qualifier for unqualified table, view, and alias names. If QUALIFIER is not specified, the authid of the plan or package owner is used to qualify table objects.

DYNAMICRULES(DEFINEBIND)

Dynamic SQL statements are processed using define or bind behavior. When the package is run as a standalone DB2 program, it uses bind behavior as described previously for DYNAMICRULES(BIND) . When the package is run as a stored procedure or UDF, DB2 processes dynamic SQL statements using define behavior. Define behavior means that DB2 uses the authid of the UDF or stored procedure owner for dynamic SQL authorization checking. The owner of the UDF or stored procedure is used as the qualifier for unqualified table, view, and alias names.

DYNAMICRULES(DEFINERUN)

Dynamic SQL statements are processed using define or run behavior. When the package is run as a standalone DB2 program, it uses run behavior as described previously for DYNAMICRULES(RUN) . When the package is run as a stored procedure or UDF, DB2 processes dynamic SQL statements using define behavior, as described under DYNAMICRULES(DEFINEBIND) .

DYNAMICRULES(INVOKEBIND)

Dynamic SQL statements are processed using invoke or bind behavior. When the package is run as a standalone DB2 program, it uses bind behavior as described previously for DYNAMICRULES(BIND) . When the package is run as a stored procedure or UDF, DB2 processes dynamic SQL statements using invoke behavior. Invoke behavior means that DB2 uses the authid of the UDF or stored procedure invoker for dynamic SQL authorization checking. The invoker of the UDF or stored procedure is to qualify any unqualified table, view, and alias names.

DYNAMICRULES(INVOKERUN)

Dynamic SQL statements are processed using invoke or run behavior. When the package is run as a standalone DB2 program, it uses run behavior as described previously for DYNAMICRULES(RUN) . When the package is run as a stored procedure or UDF, DB2 processes dynamic SQL statements using invoke behavior, as described under DYNAMICRULES(INVOKEBIND) .


Use OPTHINT to Change Access Paths

The OPTHINT parameter can be used "tell" DB2 what access paths to use for the plan or package. This information is conveyed to DB2 using rows in a PLAN_TABLE . For more information on optimizer hints, refer to Chapter 21.

Consider the IMMEDWRITE Parameter for Data Sharing

The IMMEDWRITE parameter indicates whether immediate writes will be done for updates made to group buffer pool dependent page sets or partitions. This option applies to data sharing environments only.

An immediate write means that the page is written to the group buffer pool (or to DASD for GBPCACHE NO group buffer pools or GBPCACHE NONE or SYSTEM page sets) as soon as the buffer update completes. To enable immediate write, specify IMMEDWRITE(YES) .

Consider specifying IMMEDWRITE(YES) when one transaction can spawn another transaction that can run on another DB2 member, and the spawned transaction depends on uncommitted updates made by the original transaction. With immediate writes, the original transaction can write the updated data immediately to the group bufferpool-dependent buffers to ensure that the spawned transaction retrieves the correct, updated data.

 <  Day Day Up  >  


DB2 Developers Guide
DB2 Developers Guide (5th Edition)
ISBN: 0672326132
EAN: 2147483647
Year: 2004
Pages: 388

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