CALLDB2, a FOCUS subroutine included in the DB2 Data Adapter, provides a standard method for invoking user-written subroutines that make embedded SQL calls to DB2. |
In this section: Creating CALLDB2-Invoked Subroutines How to: |
Prior to CALLDB2, you had to bind subroutines containing embedded SQL with the application plan for the data adapter, so they generated increased administrative overhead. The application programmer was responsible for run-time plan management (making sure to invoke the correct application plan with the data adapter SET PLAN command), and for instructing the data adapter to establish a thread to DB2 before invoking the subroutine.
CALLDB2 eliminates the requirement to include data adapter Database Request Modules (DBRMs) in the application plan for the subroutine. In addition, CALLDB2 ensures that subroutines do not disrupt the current DB2 Data Adapter thread. The data adapter automatically closes any existing thread to DB2 and opens a new thread to the application plan for the subroutine. After the subroutine completes, the data adapter restores the original environment. With CALLDB2, you can invoke subroutines when no prior thread to DB2 exists.
Note: The CALLDB2 feature requires that the DB2 Data Adapter be installed to use the Call Attachment Facility (CAF). You can verify this by issuing the SQL DB2 ? command; Call Attach should be ON.
For additional documentation, consult your FOCUS documentation. Read it before writing subroutines that use CALLDB2.
Note: Any additions, differences, or limitations described in this topic override that document.
CALLDB2 is an Information Builders-supplied subroutine you invoke from a Dialogue Manager program. The syntax is
-SET &var = CALLDB2('subrtine','planname',input1,input2,...,['format']);
where:
Although Dialogue Manager variables contain only alphanumeric data, they can serve as numeric arguments; the -SET command converts their alphanumeric values to double-precision format before passing them to the subroutine. However, if a subroutine returns a numeric value and you set a Dialogue Manager variable to this value, FOCUS truncates the output to an integer and converts it to a character string before storing it in the variable.
Note: You can invoke CALLDB2 only from Dialogue Manager -SET, -IF, or -TSO RUN control statements. You cannot invoke it from DEFINE, COMPUTE, MODIFY VALIDATE or IF commands, or from Financial Modeling Language (also known as Extended Matrix Reporting) RECAP commands.
The steps for creating a CALLDB2-invoked subroutine are:
|
How to: Example: Preparing a Static Subroutine for use With CALLDB2 |
Introduction to Relational Data Adapters, includes a discussion of bind concepts.
Your subroutine should assume that the data adapter will do all thread handling. This includes connection to DB2, disconnection from DB2, and the opening and closing of threads. In addition, do not code COMMIT WORK or ROLLBACK WORK statements within the subroutine itself. The data adapter tracks open logical units of work (LUWs), and it cannot detect a COMMIT WORK or ROLLBACK WORK in a subroutine.
Since CALLDB2 is a data adapter command, its behavior is affected by the data adapter SET AUTOaction ON event command (see Controlling Connection Scope, for more information about this command). If AUTOCOMMIT is set ON COMMAND, the default setting, the data adapter assumes there is an open LUW and automatically issues a COMMIT WORK at the end of a CALLDB2 subroutine.
If your subroutine requires the data adapter to issue a conditional COMMIT or ROLLBACK WORK:
The example in Sample Procedure for Invoking CALLDB2 illustrates this technique.
A CALLDB2 subroutine is also subject to the rules of data adapter plan management. Two types of plan management are defined for the data adapter: basic and extended. If you use basic plan management, the data adapter automatically switches plans as required. The data adapter SET PLAN command invokes the alternative, extended plan management. In this case, the application program, not the data adapter, manages plans. The discussion in Plan Management in DB2 in Static SQL (DB2 and DB2 for VM), applies to CALLDB2 subroutines.
The following very simple COBOL program illustrates the steps involved in preparing a static subroutine for use with CALLDB2:
ID DIVISION.
PROGRAM-ID. TESTDB2.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
LINKAGE SECTION.
01 VALUE1 PIC X(8).
01 VALUE2 PIC X(8).
01 RETCODE PIC S9(9) COMP SYNC.
1. PROCEDURE DIVISION USING VALUE1, VALUE2, RETCODE.
2. EXEC SQL WHENEVER SQLERROR GO TO PROGRAM-EXIT
END-EXEC.
3. EXEC SQL INSERT INTO USER1.TESTDB2
(COL1)
VALUES(:VALUE1)
END-EXEC.
EXEC SQL INSERT INTO USER1.TESTDB2
(COL1)
VALUES(:VALUE2)
END-EXEC.
4. PROGRAM-EXIT.
MOVE SQLCODE TO RETCODE.
GOBACK.
//**
The subroutine:
The following steps prepare the sample subroutine, TESTDB2, for execution. JCL for each step is provided for illustrative purposes. In general, these steps apply to any user-written subroutine containing embedded SQL. Your JCL will vary depending on such factors as choice of language and site-specific requirements or conventions. Consult the appropriate IBM manual for help with each step.
This example includes the sample program as in-stream input to the precompilation step:
//Job card goes here...
//*****************************************************************
//*** PRECOMPILE COBOL II CODE CONTAINING EMBEDDED (STATIC) SQL ***
//*****************************************************************
//PC EXEC PGM=DSNHPC,
// PARM='HOST(COB2),OPTIONS,SOURCE,XREF'
//STEPLIB DD DISP=SHR,DSN=DSN710.SDSNLOAD
//DBRMLIB DD DISP=SHR,DSN=prefix.DBRMLIB.DATA(TESTDB2)
//SYSCIN DD DSN=prefix.PRECOMP.OUTPUT,DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,SPACE=(TRK,(20,10),RLSE),
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=4080)
//SYSPRINT DD SYSOUT=*
//SYSTERM DD SYSOUT=*
//SYSUT1 DD UNIT=SYSDA,SPACE=(800,(500,500),,,ROUND)
//SYSUT2 DD UNIT=SYSDA,SPACE=(800,(500,500),,,ROUND)
//SYSUT3 DD UNIT=SYSDA,SPACE=(800,(500,500),,,ROUND)
//SYSUT4 DD UNIT=SYSDA,SPACE=(800,(500,500),,,ROUND)
//SYSUT5 DD UNIT=SYSDA,SPACE=(800,(500,500),,,ROUND)
//SYSIN DD *
ID DIVISION.
PROGRAM-ID. TESTDB2.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
LINKAGE SECTION.
01 VALUE1 PIC X(8).
01 VALUE2 PIC X(8).
01 RETCODE PIC S9(9) COMP SYNC.
PROCEDURE DIVISION USING VALUE1, VALUE2, RETCODE.
EXEC SQL WHENEVER SQLERROR GO TO PROGRAM-EXIT
END-EXEC.
EXEC SQL INSERT INTO USER1.TESTDB2
(COL1)
VALUES(:VALUE1)
END-EXEC.
EXEC SQL INSERT INTO USER1.TESTDB2
(COL1)
VALUES(:VALUE2)
END-EXEC.
PROGRAM-EXIT.
MOVE SQLCODE TO RETCODE.
GOBACK.
//**
The precompilation process produces a modified source program (the data set allocated to DDNAME SYSCIN) and a database request module or DBRM (member TESTDB2 in the data set allocated to DDNAME DBRMLIB).
After precompilation, compile or assemble the modified source program:
//Job card goes here...
//***************************************
//*** COBOL II COMPILE OF SOURCE CODE ***
//***************************************
//COB2 EXEC PGM=IGYCRCTL,PARM='OBJECT',REGION=1024K
//STEPLIB DD DSN=VSCOBOL.COBCOMP,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSLIN DD DSN=prefix.OBJECT.MODULE,DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,SPACE=(TRK,(20,10),RLSE)
//SYSUT1 DD UNIT=SYSDA,SPACE=(CYL,(1,1))
//SYSUT2 DD UNIT=SYSDA,SPACE=(CYL,(1,1))
//SYSUT3 DD UNIT=SYSDA,SPACE=(CYL,(1,1))
//SYSUT4 DD UNIT=SYSDA,SPACE=(CYL,(1,1))
//SYSUT5 DD UNIT=SYSDA,SPACE=(CYL,(1,1))
//SYSUT6 DD UNIT=SYSDA,SPACE=(CYL,(1,1))
//SYSUT7 DD UNIT=SYSDA,SPACE=(CYL,(1,1))
//SYSIN DD DSN=prefix.PRECOMP.OUTPUT,DISP=(OLD,DELETE)
//**
The output of the compilation in this example is the data set allocated to DDNAME SYSLIN.
Link-edit the output of the compilation or assembly to produce a run-time module:
//Job card goes here...
//**********************************
//*** LINK EDIT OBJECT MODULE(S) ***
//**********************************
//LKED EXEC PGM=IEWL,PARM='LIST,XREF,LET,MAP',REGION=512K
//SYSLIN DD DSN=prefix.OBJECT.MODULE,DISP=(OLD,DELETE)
// DD *
INCLUDE DB2(DSNALI)
INCLUDE DB2(DSNHADDR)
MODE AMODE(31),RMODE(ANY)
NAME TESTDB2(R)
/*
//SYSLMOD DD DSN=prefix.TESTDB2.LOAD,DISP=SHR
//SYSLIB DD DSN=VSCOBOL.COBLIB,DISP=SHR
//DB2 DD DSN=DSN710.SDSNLOAD,DISP=SHR
//SYSUT1 DD UNIT=SYSDA,SPACE=(CYL,(1,1))
//SYSPRINT DD SYSOUT=*
//
/*
The run-time module in this example is the data set allocated to DDNAME SYSLMOD. You must include DSNALI, the language interface, during link-edit. DSNALI is in the DB2 load library (DSN710.SDSNLOAD in the example). The link-edit of the sample program also includes DSNHADDR, used to move values to and from the SQL Descriptor Area (SQLDA). DSNHADDR is required only for COBOL programs.
Now, bind the DBRM created during precompilation into an application plan, and store it in the DB2 database:
//Job card goes here...
//**********************************
//*** BIND THE PLAN ***
//**********************************
//BIND EXEC PGM=IKJEFT01,DYNAMNBR=10
//STEPLIB DD DSN=DSN710.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//DBRMLIB DD DSN=prefix.DBRMLIB.DATA,DISP=SHR
//SYSTSIN DD *
DSN SYSTEM(DB2P)
BIND PLAN(TESTDB2) MEMBER(TESTDB2) -
ACTION(REPLACE) ISOLATION(RR)
END
//*
The BIND subcommand creates an application plan called TESTDB2 that contains SQL statements from the DBRM named TESTDB2.
Refer to the DB2 Command and Utility Reference for an explanation of possible BIND parameters.
Note: If you make any changes to your subroutine, you have to repeat the precompilation, compilation or assembly, link-edit, and BIND steps (see Precompiling the Subroutine, through Binding the Subroutine).
Add the run-time library for your subroutine ('prefix.TESTDB2.LOAD' in the example) to either the CLIST allocation for USERLIB or the JCL allocation for STEPLIB (making sure the data set with the largest blocksize appears first). For example |
Example: |
ALLOC F(USERLIB) DA('prefix.TESTDB2.LOAD' -
'prefix.FOCSQL.LOAD' -
'prefix.FOCLIB.LOAD' -
'prefix.FUSELIB.LOAD') SHR REU
or:
//STEPLIB DD DSN=prefix.TESTDB2.LOAD,DISP=SHR
// DD DSN=prefix.FOCSQL.LOAD,DISP=SHR
// DD DSN=prefix.FOCLIB.LOAD,DISP=SHR
// DD DSN=prefix.FUSELIB.LOAD,DISP=SHR
This sample Dialogue Manager procedure invokes the TESTDB2 subroutine:
1. SQL DB2 SET AUTOCOMMIT ON FIN
-RUN
-*
2. -SET &RUNOK = CALLDB2('TESTDB2 ','TESTDB2 ','BLUE','RED','I4') ;
-RUN
3. -SET &LUW = IF &RUNOK NE 0 THEN 'SQL DB2 ROLLBACK WORK' ELSE
- 'SQL DB2 COMMIT WORK' ;
4. &LUW
-RUN
5. SQL DB2 SET AUTOCOMMIT ON COMMAND
-RUN
The procedure:
| Information Builders |