This section describes the following SQL and general programming considerations, and programming techniques:
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:
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.
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:
If all these conditions are met, you can gain the performance benefits of the open transaction environment.
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.
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.
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.
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.
When updating columns that are used in one or more indexes, consider the following:
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.
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:
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:
The disadvantage compared to the other techniques is that the serialization is done in the application code and requires the programs to be changed.
The LOCK TABLE statement can be used to serialize CICS transactions and other programs, if EXCLUSIVE mode is specified. Note that it is the whole table space that is locked, not the table referenced in the statement.
The serialization starts when the LOCK statement is executed. The end time for the serialization is when the table space lock is released. This can be at syncpoint or at thread deallocation time.
Use this technique with care, because of the risk of locking the table space until thread deallocation time. However, this technique is the only one that works across the complete DB2 system. The other techniques are limited to controlling serialization of only CICS transactions.
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.
If you use one or more counters to supply your application with new sequential numbers, consider the following:
When for example row 2 in page 2 is accessed, a table space scan can occur. The scan stops to wait at page number 1, if this page is locked by another transaction. You should therefore avoid a table space scan.
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:
When the SELECT statement uses the nonclustered index, the S-locks (shared locks) are obtained in this order:
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.
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:
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.
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.
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".
This 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.
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.