SQL, threadsafe and other programming considerations for CICS DB2 applications

This section describes the following SQL and general programming considerations, and programming techniques:

Enabling CICS DB2 applications to exploit the open transaction environment (OTE) through threadsafe programming

The CICS® DB2® attachment facility includes a CICS DB2 task-related user exit, DFHD2EX1, that is invoked when an application program makes an SQL request. It manages the process of acquiring a thread connection into DB2, and of returning control to the application program when the DB2 processing is complete.

When CICS is connected to DB2 Version 5 or earlier, the CICS DB2 task-related user exit operates as a quasi-reentrant task-related user exit program. It runs on the CICS main TCB (the QR TCB) and uses its own subtask thread TCBs to run threads, switching to and from the subtask thread TCBs for each DB2 request. However, when CICS is connected to DB2 Version 6 or later, the CICS DB2 attachment facility exploits the open transaction environment (OTE), to enable the CICS DB2 task-related user exit to invoke and return from DB2 without switching TCBs. In the open transaction environment, the CICS DB2 task-related user exit operates as a threadsafe and open API task-related user exit program--it is automatically enabled using the OPENAPI option on the ENABLE PROGRAM command during connection processing. This enables it to receive control on an open L8 mode TCB. Requests to DB2 are also issued on the L8 TCB, so it acts as the thread TCB, and no switch to a subtask TCB is needed. For full details of the CICS DB2 configuration needed to support the open transaction environment, see Migrating to a different release of DB2.

In the open transaction environment, if the user application program that invoked the task-related user exit conforms to threadsafe coding conventions and is defined to CICS as threadsafe, it can also run on the L8 TCB. Before its first SQL request, the application program runs on the CICS main TCB, the QR TCB. When it makes an SQL request and invokes the task-related user exit, control passes to the L8 TCB, and DB2 processing is carried out. On return from DB2, if the application program is threadsafe, it now continues to run on the L8 TCB.

Where the correct conditions are met, the use of open TCBs for CICS DB2 applications decreases usage of the QR TCB, and avoids TCB switching. An ideal CICS DB2 application program for the open transaction environment is a threadsafe program, containing only threadsafe EXEC CICS commands, and using only threadsafe user exit programs. An application like this will move to an L8 TCB when it makes its first SQL request, and then continue to run on the L8 TCB through any amount of DB2 requests and application code, requiring no TCB switching. This situation produces a significant performance improvement where an application program issues multiple SQL calls. The gains are also significant when using an enterprise bean, because when enterprise beans make DB2 requests, they require additional TCB switches to and from the enterprise bean's own TCB (see Using JDBC and SQLJ in enterprise beans: special considerations). If the application program does not issue many SQL calls, the performance benefits might not be as significant.

If the execution of the program involves any actions that are not threadsafe, CICS switches back to the QR TCB at that point. Such actions are non-threadsafe CICS requests issued by the program, the use of non-threadsafe dynamic plan exits, the use of non-threadsafe task-related user exits, and the involvement of non-threadsafe global user exits. Switching back and forth between the open TCB and the QR TCB is detrimental to the application's performance.

In order to gain the performance benefits of the open transaction environment for CICS DB2 applications, you must meet the following conditions:

  1. CICS must be connected to DB2 Version 6 or later. Migrating to a different release of DB2 has full details of the CICS DB2 configuration needed to support the open transaction environment, including APARs that must be applied for DB2 and for CICS.
  2. The system initialization parameter FORCEQR must not be set to YES. FORCEQR forces programs defined as threadsafe to run on the QR TCB, and it might be set to YES as a temporary measure while problems connected with threadsafe-defined programs are investigated and resolved.
  3. The CICS DB2 application must have threadsafe application logic (that is, the native language code in between the EXEC CICS commands must be threadsafe), use only threadsafe EXEC CICS commands, and be defined to CICS as threadsafe. Only code that has been identified as threadsafe is permitted to execute on open TCBs. If your CICS DB2 application is not defined as threadsafe, or if it uses EXEC CICS commands which are not threadsafe, TCB switching will take place and some or all of the performance benefits of OTE exploitation will be lost.
  4. Any dynamic plan exits used by the CICS DB2 attachment facility must be coded to threadsafe standards and defined to CICS as threadsafe. The default dynamic plan exit DSNCUEXT, which is invoked as a CICS user-replaceable program, is not defined to CICS as threadsafe, but the alternative CICS-supplied sample dynamic plan exit DFHD2PXT is so defined. See Dynamic plan exits for more information.
  5. Any global user exits on the execution path used by the application must be coded to threadsafe standards and defined to CICS as threadsafe (for CICS DB2 applications, note in particular the global user exits XRMIIN and XRMIOUT).
  6. Any other task-related user exits used by the application must be defined to CICS as threadsafe, or as OPENAPI.

See the CICS Application Programming Guide for information on how to make application programs and user exit programs threadsafe. By defining a program to CICS as threadsafe, you are only specifying that the application logic is threadsafe, not that all the EXEC CICS commands included in the program are threadsafe. CICS can ensure that EXEC CICS commands are processed safely by switching to the QR TCB for those commands not yet converted that still rely on quasi-reentrancy. In order to permit your program to run on an open TCB, CICS needs you to guarantee that your application logic is threadsafe.

The EXEC CICS commands that are threadsafe, and so do not involve TCB switching, are indicated in the command syntax diagrams in the CICS Application Programming Reference and the CICS System Programming Reference with the statement "This command is threadsafe", and are listed in "Threadsafe command list" in the CICS Application Programming Reference and Appendix D of the CICS System Programming Reference.

If a user application program in the open transaction environment is not defined as threadsafe, the CICS DB2 task-related user exit still runs on an L8 TCB, but the application program runs on the QR TCB throughout the task. Every time the program makes an SQL request, CICS switches from the QR TCB to the L8 TCB and back again, so the performance benefits of the open transaction environment are negated.

The table below shows what happens when application programs with different concurrency attributes invoke the CICS DB2 task-related user exit when CICS is connected to different versions of DB2.

Table 6. Combinations of application programs and the CICS DB2 task-related user exit
Program's concurrency attribute CICS DB2 task-related user exit's operation Effect
QUASIRENT or THREADSAFE Quasi-reentrant (when connected to DB2 Version 5 or earlier) Application program and task-related user exit run under the CICS QR TCB. The task-related user exit manages its own TCBs, switching to and from them for each DB2 request.
QUASIRENT Threadsafe and open API (when connected to DB2 Version 6 or later) Application program runs under the CICS QR TCB. Task-related user exit runs under an L8 TCB, and DB2 requests are executed under the L8 TCB. CICS switches to and from the CICS QR TCB and the L8 TCB for each DB2 request.
THREADSAFE Threadsafe and open API (when connected to DB2 Version 6 or later) OTE exploitation. Task-related user exit runs under an L8 TCB, and DB2 requests are executed under the L8 TCB. The application program also runs on the L8 TCB when control is returned to it. No TCB switches are needed until the task terminates, or if it issues a non-threadsafe CICS request which forces a switch back to the QR TCB.

In summary, to gain the performance benefits of the open transaction environment:

  1. CICS must be connected to DB2 Version 6 or later. If CICS is connected to DB2 Version 5 or earlier, the CICS DB2 task-related user exit operates as a quasi-reentrant task-related user exit, switching TCBs for every DB2 request.
  2. FORCEQR must not be set to YES.
  3. The CICS DB2 application must have threadsafe application logic (that is, the native language code in between the EXEC CICS commands must be threadsafe), use only threadsafe EXEC CICS commands, and be defined to CICS as threadsafe. If the application program is not defined as threadsafe, and so must operate on the CICS QR TCB, TCB switching occurs for every DB2 request, even if the task-related user exit is running on an open TCB. If the application program is defined as threadsafe but uses non-threadsafe EXEC CICS commands, TCB switching occurs for every non-threadsafe EXEC CICS command.
  4. The CICS DB2 application must use only threadsafe or open API dynamic plan exits, task-related user exits and global user exits. If any non-threadsafe exits are used, this forces a switch back to the QR TCB.

If all these conditions are met, you can gain the performance benefits of the open transaction environment.

SQL language

The complete SQL language is available to the CICS programmer with only minor restrictions. For a detailed description on using the SQL language in a CICS program, see the DB2 Universal Database™ for OS/390® and z/OS® Application Programming and SQL Guide.

In a CICS program, it is possible to use:

CICS also supports both dynamic and static SQL statements.

However, for performance and concurrency reasons, it is recommended that in general you do not issue DDL and GRANT and REVOKE statements in CICS. You should also limit dynamic SQL use.

The reason for these recommendations is that the DB2 catalog pages can be locked, with a lower concurrency level as a consequence. Also the resource consumption for these types of SQL statements is typically higher than resource consumption for static DML SQL statements.

Using qualified and unqualified SQL

Programmers writing CICS DB2 programs can use qualified and unqualified SQL. In qualified SQL, the creator is specified in front of the table or view name. In unqualified SQL, the creator is not specified.

When programmers develop CICS DB2 standards, it is important to determine the use of qualified and unqualified SQL. This decision influences many other aspects of the DB2 environment. The main relationships to other DB2 areas and some consequences for the two types of SQL statements are shown in Table 7.

Table 7. Qualified and unqualified SQL
Relationship to other DB2 areas Qualified SQL Unqualified SQL
Use of synonyms Not possible Possible
Binder ID Any Same as creator
Number of creators for tables and table spaces Any One
Use of VALIDATE(RUN) Is qualified Uses binder to qualify
Use of dynamic SQL Is qualified Uses executor to qualify
Require a separate test DB2 subsystem Yes No
Require same creator in test DB2 and production DB2 Yes No
Possibility of using multiple versions of the test tables in the same test DB2 subsystem No Yes

Some of the limitations shown in Table 7 can be bypassed if you develop your own preprocessor to modify the source code before invoking the DB2 precompiler. This allows you, for example, to change the creator in the SQL statements.

It is recommended that you use qualified SQL for dynamic SQL statements, because it is easier to administer.

If you use unqualified SQL, you must decide how to supply the CREATOR to fully identify the tables and views. There are two possibilities:

For these reasons, the use of unqualified SQL in dynamic SQL statements is not recommended.

Views

It is generally recommended that you use views where appropriate. Some views, however, cannot be updated.

In a real-time, online system, you often need to update rows you have retrieved using views. If the view update restriction forces you to update the base table directly (or by using another view), you should consider only views that can be updated. In most cases this makes the program easier to read and modify.

Updating index columns

When updating columns that are used in one or more indexes, consider the following:

Dependency of unique indexes

A programmer can take advantage of the fact that DB2 returns only one row from a table with a unique index, if the full key is supplied in the SELECT statement. A cursor is not needed in this case. However, if at a later time the index is changed and the uniqueness is dropped, then the program does not execute correctly when two or more rows are returned. The program then receives an SQL error code.

Commit processing

CICS ignores any EXEC SQL COMMIT statement in your application programs. The DB2 commit must be synchronized with CICS, which means that your program must issue an EXEC CICS SYNCPOINT command. CICS then performs the commit processing with DB2. An implicit SYNCPOINT is always invoked by the EXEC CICS RETURN at EOT.

You should be aware of the actions taken at SYNCPOINT:

Serializing transactions

You may need to serialize the execution of one or more transactions. This typically occurs when the application logic was not designed to deal with concurrency and in cases where the risk of deadlocks is too high.

You should allow serialization only for low-volume transactions because of potential queueing time.

The following methods each have different serialization start and end times:

Page contention

When designing applications and databases, consider the impact of having many transactions accessing the same part of a table space. The term "hot spot" is often used to describe a small part of the table space, where the access density is significantly higher than the access density for the rest of the table space.

If the pages are used for SELECT processing only, there is no concurrency problem. The pages are likely to stay in the buffer pool, so little I/O activity takes place. However, if the pages are updated frequently, you may find that you have concurrency problems, because the pages are locked from first update until syncpoint. Other transactions using the same pages have to wait. Deadlocks and timeouts often occur in connection with hot spots.

Two examples of hot spots are sequential number allocation and insert in sequence.

Sequential number allocation

If you use one or more counters to supply your application with new sequential numbers, consider the following:

Insert in sequence

In situations where many transactions are inserting rows in the same table space, you should consider the sequence of the inserted rows. If you base a clustering index on a field with a time stamp, or a sequential number, DB2 tries to insert all rows adjacent to each other. The pages where the rows are inserted can then be considered a hot spot.

Note that in the clustering index, all inserts are also in the same page, within a given period.

If there is more than one index and the nonclustering index is used for data retrieval, the risk of deadlock between index and data is increased. In general terms, the INSERT obtains the X-locks (exclusive locks) in the following order:

  1. Clustering index leaf page
  2. Data page
  3. Nonclustering index leaf page

When the SELECT statement uses the nonclustered index, the S-locks (shared locks) are obtained in this order:

  1. Nonclustering index leaf page
  2. Data page

This is the opposite order to the order of the INSERT locks. Often the SELECT rate is higher for the new rows. This means that the data pages are common for the INSERT and the SELECT statements. Where the index page is also the same, a deadlock can occur.

A solution to the deadlock risk is to spread the rows by choosing another index as clustering.

The general methods of how to handle deadlock situations are described in Handling deadlocks in the CICS DB2 environment.

CICS and CURSOR WITH HOLD option

The WITH HOLD option on a CURSOR declaration in a CICS program causes the following effects during a SYNCPOINT:

All locks are released, except for those required to maintain the cursor's position. Any exclusive page locks are downgraded to shared locks.

In conversational CICS applications, you can use DECLARE CURSOR...WITH HOLD to request that the cursor is not closed at syncpoint time. However, all cursors are always closed at end of task (EOT) and on SYNCPOINT ROLLBACK. Across EOTs, a cursor declared WITH HOLD must be reopened and repositioned just as if the WITH HOLD option were not specified. The scope of the held cursor is a single task.

In summary:

If you try to hold a cursor across EOT, the cursor is closed and you get an SQLCODE -501 when you execute the next FETCH. The precompiler cannot detect this and you do not get a warning message notifying you of this situation.

In general, threads can become candidates for reuse at each syncpoint. When you use DECLARE CURSOR...WITH HOLD in the CICS applications, consider the following recommendations:

EXEC CICS RETURN IMMEDIATE command

When the TRANSID option is specified in conjunction with the IMMEDIATE option, CICS avoids sending an end bracket (EB) to the terminal during the termination of the transaction that issued the RETURN command, and immediately initiates the transaction designated by the TRANSID option. The keyboard remains locked during this transaction, since no EB was sent to the terminal.

The new transaction behaves as if it were started by input from the terminal. You can pass data to the transaction designated by the TRANSID option, using a COMMAREA. If you choose to, the transaction issuing the RETURN command can also pass a terminal input message using the INPUTMSG and INPUTMSGLEN options. This facility allows you to immediately initiate a transaction that expects to be initiated as a result of terminal input.

This facility provides the same general capability as that achieved by issuing an EXEC CICS START TRANSID(....) with TERMID(....) set to the EIBTRMID value, but with much less overhead and without the momentary keyboard unlocking. The EXEC CICS RETURN TRANSID() IMMEDIATE command permits a pseudoconversational transaction to switch transaction codes. This could be advisable, for example, to keep DB2 plan sizes smaller or to have better accounting statistics for charge-back purposes.

Avoiding AEY9 abends

You can use the following CICS command to detect whether the CICS DB2 attachment facility is enabled:

 
   EXEC CICS EXTRACT EXIT PROGRAM('DFHD2EX1')
            ENTRY('DSNCSQL')
            GASET(name1)
            GALENGTH(name2)
 

If you specify a program name of DSNCEXT1 or DSN2EXT1 CICS dynamically changes it to the required name DFHD2EX1. If you get the INVEXITREQ condition, the CICS DB2 attachment facility is not enabled.

When the CICS DB2 attachment facility is enabled it is not necessarily connected to DB2. It can be waiting for DB2 to initialize. When this occurs, and an application issues an EXEC SQL command when CONNECTERROR=ABEND is specified in the DB2CONN, an AEY9 abend would result. CONNECTERROR=SQLCODE would result in a -923 SQL code being returned to the application.

You can use the INQUIRE EXITPROGRAM command with the CONNECTST keyword in place of the EXTRACT EXIT command to determine whether the CICS is connected to DB2.

The CONNECTST keyword of the INQUIRE EXITPROGRAM command returns values:

If the command fails with PGMIDERR, this is the same as NOTCONNECTED.

Figure 29 shows an example of assembler code using the INQUIRE EXITPROGRAM command.

Figure 29. Example of the INQUIRE EXITPROGRAM command
CSTAT    DS    F
ENTNAME  DS    CL8
EXITPROG DS    CL8
        ...
         MVC   ENTNAME,=CL8'DSNCSQL'
         MVC   EXITPROG,=CL8'DFHD2EX1'
         EXEC CICS INQUIRE EXITPROGRAM(EXITPROG)               X
               ENTRYNAME(ENTNAME) CONNECTST(CSTAT) NOHANDLE
         CLC   EIBRESP,DFHRESP(NORMAL)
         BNE   NOTREADY
         CLC   CSTAT,DFHVALUE(CONNECTED)
         BNE   NOTREADY
 

If you specify a program name of DSN2EXT1, CICS dynamically changes it to the required name, DFHD2EX1.

Further consideration on the use of the EXTRACT EXIT or INQUIRE EXITPROGRAM commands by applications has to be made when running in an environment where dynamic workload balancing using the MVS™ workload manager is taking place.

If an application avoids making DB2 calls because it knows the CICS DB2 connection is not active, but issues an error message instead and returns normally, it could delude the workload manager into routing more work to the CICS region. This is called the "storm drain effect". Because the application did not abend, the workload manager believes that good response times are being achieved by this CICS region for DB2 work, and routes more work down the "storm drain".

Start of changeThis effect can be avoided by ensuring the application abends. Alternatively, it can be avoided by running with STANDBYMODE=RECONNECT and CONNECTERROR=SQLCODE in the DB2CONN. In this situation, applications should not use the Extract or Inquire Exitprogram commands to test whether DB2 work is possible. Instead, a test should be made for -923 SQLcode to be returned if CICS is not connected to DB2. At the time of returning the -923 SQLcode, the CICS DB2 attachment facility informs the MVS workload manager that the request has failed. A dynamic routing program can subsequently use this information to avoid the "storm drain effect". The CICSPlex SM dynamic routing program implements this function to avoid routing more DB2 work to the affected CICS region. No notification occurs when the condition causing the -923 SQLcode has passed. Because of this, after a given time, CICSPlex SM reactivates the CICS region in terms of its eligibility to receive work, and sends a test item known as a "sacrificial lamb" to the CICS region to test whether the problem still exists.End of change

You are, therefore, advised to do the following:

It is advisable to avoid shutting down the attachment. The CICS DB2 SPI commands allow dynamic modification of the environment without shutting down the attachment.

Related concepts
Application design and development considerations for CICS DB2
Designing the relationship between CICS applications and DB2 plans and packages
Developing a locking strategy in the CICS DB2 environment
Using JDBC and SQLJ to access DB2 data from Java programs and enterprise beans written for CICS
Migration planning for threadsafe programming and the open transaction environment
(OTE)
[[ Contents Previous Page | Next Page Index ]]