Coordinating your DB2CONN, DB2ENTRY, and BIND options

You can create many different combinations of DB2CONN, DB2ENTRY, and BIND options. As we have read, one of the most important things you need to do to optimize performance is to define whether a given set of transactions should use one or more protected threads. Selecting thread types for optimum performance has more detailed advice about this. You should then define the BIND parameters ACQUIRE and RELEASE to minimize the total amount of work related to the main activities involved in processing SQL transactions. Selecting BIND options for optimum performance has more detailed advice about this.

In general it is recommended that you initially set your DB2CONN, DB2ENTRY and BIND options to the values shown in Table 1. You may find that you get better performance from other combinations for your own transactions. For each transaction type a recommended thread type and BIND option are shown. There are also recommendations for whether transactions should overflow to the pool.

Table 1. Recommended combinations of DB2CONN, DB2ENTRY and BIND options
Transaction Description Thread Type Overflow ACQUIRE RELEASE
High volume (all types) Protected Entry Note 1 ALLOCATE DEALLOCATE
Terminal-oriented with many commits (plus non-terminal if NONTERMREL=YES) Protected Entry Note 2 Note 3 DEALLOCATE
Low volume, requires fast response time Unprotected Entry Yes USE COMMIT
Low volume, limited concurrency Unprotected Entry Never USE COMMIT
Low volume, does not require fast response time Pool Not Applicable USE COMMIT
Non-terminal-oriented with many commits (NONTERMREL=NO) Note 4 Note 4 Note 3 DEALLOCATE
Notes:
  1. Yes, but define enough entry threads so this happens infrequently.
  2. Yes, but if it overflows to the pool no protected thread is used.
  3. ALLOCATE if most of the SQL in the plan is used. Otherwise use ACQUIRE(USE).
  4. Threads are held until EOT. Use pool threads for a short transaction. Consider entry threads for longer running transactions.

In Table 1 limited concurrency means only a limited number (n) of transactions are allowed to execute at the same time. A special case exists when n=1. In that case the transactions are serialized. You can still use a protected thread if the transaction rate is high enough to make it worthwhile. The transactions cannot be controlled, if overflow to the pool is allowed. You should normally use the CICS® mechanism for limiting the number of transactions running in a specific class, rather than forcing transactions to queue for a limited number of threads.

As Table 1 shows, only a few combinations of DB2CONN, DB2ENTRY, and BIND options are generally recommended. However, in specific situations other combinations can be used.

Table 2 shows a summary of the activities involved in processing SQL requests for the three recommended sets of DB2CONN, DB2ENTRY, and BIND specifications. An "X" indicates a required activity. The table also demonstrates the performance advantage of using protected threads without changing the authorization ID.

Table 2. Activities involved in processing SQL requests for different DB2CONN, DB2ENTRY, and BIND specifications
Activity Protected Threads Unprotected Threads
ACQUIRE(ALLOCATE) RELEASE(DEALLOCATE) (USE) (COMMIT) (USE) (DEALLOCATE)
Activity for each thread Activity for each transaction Activity for each transaction Activity for each transaction
Create thread: X X X
SIGNON X (1) X X
Authorization Check X (1) X X
Load SKCT Header X X X
Load CT Header X X X
Acquire all TS locks X
Load all DBDs X
For each SQL statement:
Load SKCT SQL section (2) (2) (2)
Create CT copy (3) X X
Acquire all TS locks X X
Load all DBDs X X
Commit:
Release page locks X X X
Release TS locks X
Free CT pages X
Terminate Thread: X X X
Release TS locks X X X
Free CT pages X X
Free work storage X X X
Notes:
X. Required activity
1. Only if new authorization ID
2. Only if SQL section is not already in EDM pool
3. Only if SQL section is not already in Cursor Table

Related concepts
Defining the CICS DB2 connection
Using the DB2 group attach facility
The MAXOPENTCBS system initialization parameter and TCBLIMIT
What happens during SQL processing
How threads are created, used, and terminated
Selecting thread types for optimum performance
Selecting BIND options for optimum performance
[[ Contents Previous Page | Next Page Index ]]