CALLDB2: Invoking Subroutines Containing Embedded SQL

In this section:

How to:

CALLDB2, a FOCUS subroutine included in the Adapter for DB2, provides a standard method for invoking user-written subroutines that make embedded SQL calls to DB2.

Prior to CALLDB2, you had to bind subroutines containing embedded SQL with the application plan for the 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 adapter SET PLAN command), and for instructing the adapter to establish a thread to DB2 before invoking the subroutine.

CALLDB2 eliminates the requirement to include adapter Database Request Modules (DBRMs) in the application plan for the subroutine. In addition, CALLDB2 ensures that subroutines do not disrupt the current Adapter for DB2 thread. The 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 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 Adapter for DB2 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.
Top of page
x
Syntax: How to Invoke CALLDB2

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:

& var

Is the Dialogue Manager variable to receive the value returned by the subroutine.

subrtine

Is a subroutine that uses embedded SQL. The subroutine name can be up to eight characters long (unless you are writing the subroutine in a language that allows less) and must be enclosed in single quotation marks. The first character must be a letter (A-Z). Each additional character can be a letter or a number. You must pad the name on the right with blanks if it is less than eight characters long.

planname

Is the DB2 application plan for your subroutine. The plan name can be up to eight characters long and must be enclosed in single quotation marks. You must pad the name on the right with blanks if it is less than eight characters long.

input1 ...

Are the input arguments. You must know what arguments the subroutine requires, their formats, and the order in which to specify them. You can include up to 26 input arguments (normally, user written subroutines allow 28, but CALLDB2 uses two of them for subroutine name and plan name).

format

Is the format of the output value, enclosed in single quotation marks. This parameter is optional. If you do not provide a format, the default format is determined by the format of the last element in the calling list. If your program does not return an output value, the default return value is the last element in the calling list. In cases of error, the returned value is '*ERROR* '.

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.
Top of page
x
Creating CALLDB2-Invoked Subroutines

How to:

The steps for creating a CALLDB2-invoked subroutine are:

  1. Write the subroutine program logic.
  2. Precompile the subroutine.
  3. Compile or assemble the subroutine.
  4. Link-edit the subroutine.
  5. BIND the subroutine.

Introduction to Adapters for Relational Data Sources, includes a discussion of bind concepts.
x
Procedure: How to Write the Subroutine

Your subroutine should assume that the 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 adapter tracks open logical units of work (LUWs), and it cannot detect a COMMIT WORK or ROLLBACK WORK in a subroutine.

Since CALLDB2 is an adapter command, its behavior is affected by the 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 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 adapter to issue a conditional COMMIT or ROLLBACK WORK:

  1. Set AUTOCOMMIT on FIN immediately before entering the subroutine to prevent the adapter from generating an automatic COMMIT WORK at the end of the CALLDB2 command.
  2. Exit the subroutine and return a value to the Dialogue Manager procedure indicating which action, COMMIT or ROLLBACK, the procedure should take on behalf of the subroutine.
  3. After your Dialogue Manager procedure issues the COMMIT or ROLLBACK WORK command, reset AUTOCOMMIT on COMMAND to restore the default adapter environment.

The example in Sample Procedure for Invoking CALLDB2 illustrates this technique.

A CALLDB2 subroutine is also subject to the rules of adapter plan management. Two types of plan management are defined for the adapter, basic and extended. If you use basic plan management, the adapter automatically switches plans as required. The adapter SET PLAN command invokes the alternative, extended plan management. In this case, the application program, not the adapter, manages plans. The discussion in Plan Management in DB2 in Static SQL (DB2), applies to CALLDB2 subroutines.

Example: Preparing a Static Subroutine for use With CALLDB2

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:

  1. Accepts two input values and returns a value named RETCODE.
  2. Checks the SQLCODE after each SQL command. If it encounters a negative SQLCODE, the subroutine branches to PROGRAM-EXIT.
  3. Issues two SQL INSERT commands that place the input values into a single-column DB2 table.
  4. Passes the value of the SQLCODE back to FOCUS. If the procedure encountered an error, it returns the negative SQLCODE from that error back to the Dialogue Manager routine. Otherwise, it passes the last SQLCODE, which should be zero, back to the Dialogue Manager routine. The Dialogue Manager routine will check the value of the returned SQLCODE and issue an SQL COMMIT WORK or ROLLBACK WORK depending on the returned value.

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.
Example: Precompiling the Subroutine

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=DSN910.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).

Example: Compiling or Assembling the Subroutine

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.
Example: Link-Editing the Subroutine

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=DSN910.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 (DSN910.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.
Example: Binding the Subroutine

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=DSN910.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.
Top of page
x
CALLDB2 Run-time Requirements
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
Example: Sample Procedure for Invoking CALLDB2

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:

  1. Sets AUTOCOMMIT on FIN to prevent the adapter from issuing an automatic COMMIT WORK at the end of the subroutine.
  2. Invokes CALLDB2 for subroutine TESTDB2. The return code from the subroutine will be stored in Dialogue Manager variable &RUNOK.
  3. Tests the value of the return code and, depending on the result of the test, stores either the SQL COMMIT WORK or the SQL ROLLBACK WORK command in Dialogue Manager variable &LUW.
  4. Issues the COMMIT or ROLLBACK command.
  5. Sets AUTOCOMMIT on COMMAND to restore data adapter default behavior.
Information Builders