Data Sharing Application Development Guidelines

When preparing applications to utilize data sharing in your shop, be sure to abide by the following guidelines.

Favor Binding IMMEDWRITE(NO)

The IMMEDWRITE parameter of the BIND command controls whether immediate writes are to be done for updates that are made to group buffer pool dependent page sets or partitions. This BIND parameter only applies for data sharing environments.

When binding plans and packages with IMMEDWRITE(YES) , the originating transaction will immediately write its updated GBP-dependent buffers instead of waiting until a COMMIT or ROLLBACK . Doing so ensures that the transaction will always obtain the same results regardless of whether or not it runs on the same member as the originating transaction.

But IMMEDWRITE(YES) can negatively impact performance. The more buffer updates to GBP-dependent pages performed by the program, the less efficient it will be.

If consistency is an issue, consider other options, such as always running the dependent transaction on the same DB2 member as the originating transaction or possibly binding with ISOLATION(RR) instead of using IMMEDWRITE(YES) .

Prior to V8, changed pages in a data sharing environment were written during phase 2 of the commit process. As of DB2 V8, default processing will be to write changed pages during phase 1 of commit processing.

NOTE

The IMMEDWRI DSNZPARM can also be used to specify the immediate write characteristics for data sharing operations. If either the BIND parameter or the DSNZPARM is set to YES , the YES will override a NO .

Bind to Minimize Locks with Data Sharing

Favor binding with ISOLATION(CS) and CURRENTDATA(NO) for programs to be run in a data sharing environment. By doing so DB2 will avoid taking locks for read only cursors whenever possible.

If your application can live with "dirty reads," consider binding with ISOLATION(UR) to eliminate even more locks. Beware, though, that reading uncommitted data can cause data integrity problems and should not be used if your application can tolerate such possibilities.

In general, avoid ISOLATION(RR) in a data sharing environment if you can.

Furthermore, encourage thread reuse by binding with the ACQUIRE(USE) and RELEASE(DEALLOCATE) parameters. This, too, can reduce global lock contention .

Ensure Proper COMMIT Scope

Ensure that your batch application programs take frequent COMMIT s to reduce the duration of restart and recovery processes.

Encourage Lock Avoidance

DB2 can use the lock avoidance technique to reduce the number of locks that need to be taken for read-only processes. Recall from Chapter 13, "Program Preparation," that lock avoidance can be enabled for read only cursors by binding plans and packages using CURRENTDATA(NO) .

Lock avoidance reduces the number of locks taken and thereby reduces overhead for data sharing applications.

Be Aware of Sysplex Parallelism

With sysplex parallelism, DB2 provides the ability to utilize the power of multiple DB2 subsystems on multiple CPCs to execute a single query. As with any type of DB2 parallel querying, you will need to BIND your plan or package specifying DEGREE(ANY) to take advantage of sysplex parallelism. Use caution before doing so because you will need to appropriately configure your environment to support parallelism first, before you will gain anything from query parallelism.

Refer to Chapter 21, "The Optimizer," for more information on all forms of query parallelism available with DB2.