Exits that include SQL

There are a number of additional factors to consider when you code an exit that includes SQL statements.

Program preparation

As with any program that contains SQL statements, the DB2® precompiler must be used. In the COBOL sample exits included in this manual, the precompiler is invoked by the language compiler. You can use any of your own procedures to compile the exits.

The DB2 package should be bound into the same DB2 collection as your DDM driver module.

Linkage-edit

When your exit includes SQL calls, you must include the CICS® VT module VIDHLIPI in your linkage edit step. Sample compile/link/bind JCL for a COBOL exit is shown in JCL to compile COBOL exit.

VIDDMPD DD statement

Your exit must include code to handle unexpected SQL return codes. Based on parameter values you specify, you can cause a call to abend or pass an unsuccessful return code back to your application program. These options are discussed in Processing errors in an FBE.

Performance

FBEs that contain SQL may add a measurable processing overhead, depending on the number of SQL calls processed. It is essential that the SQL is as efficient as possible. For example, if your exit has to retrieve a single row from a table, it is more efficient to use a singleton select than a cursor declare/open/fetch/close. You must ensure that the SQL access path is efficient and always uses an index. Your SQL should not perform a tablespace scan or any sorting. Additional DB2 indexes are often required to support FBEs that issue SQL calls.

Concept Concept

Feedback


Timestamp icon Last updated: Monday, 10 February 2014


http://pic.dhe.ibm.com/infocenter/cicsts/v5r1/topic/com.ibm.cics.vt.doc//topics/cvtdecsql.html