How to Write DB2 Dynamic SQL Logic in COBOL

Here’s a sample COBOL program that demonstrates how to use Dynamic SQL. Dynamic SQL allows you to write and execute queries at runtime.

COBOL Sample Program

Add definitions for error codes as usual in the working storage section.

01  MSG-SQLERR.
               02  FILLER PIC X(31)
                   VALUE ' XXXXXXXX SQL ERROR, SQLCODE = '.
               02  MSG-MINUS       PIC X(1).
               02  MSG-PRINT-CODE  PIC 9(8).
               02  FILLER PIC X(81) VALUE '  

Add SQL descriptor area in the working storage section.

*****************************************************
      * SQL DESCRIPTOR AREA                               *
      *****************************************************
       01  SQLDA.
               02  SQLDAID     PIC X(8)   VALUE 'SQLDA   '.
               02  SQLDABC     PIC S9(8) COMPUTATIONAL  VALUE 33016.
               02  SQLN        PIC S9(4) COMPUTATIONAL  VALUE 750.
               02  SQLD        PIC S9(4) COMPUTATIONAL  VALUE 0.
               02  SQLVAR      OCCURS 1 TO 750 TIMES
                                        DEPENDING ON SQLN.
                   03  SQLTYPE     PIC S9(4) COMPUTATIONAL.
                   03  SQLLEN      PIC S9(4) COMPUTATIONAL.
                   03  SQLDATA     POINTER.
                   03  SQLIND      POINTER.
                   03  SQLNAME.
                       49  SQLNAMEL    PIC S9(4) COMPUTATIONAL.
                       49  SQLNAMEC    PIC X(30).
      *
      *  DATA TYPES FOUND IN SQLTYPE, AFTER REMOVING THE NULL BIT
      *
       77  VARCTYPE            PIC S9(4)  COMP VALUE +448.
       77  CHARTYPE            PIC S9(4)  COMP VALUE +452.
       77  VARLTYPE            PIC S9(4)  COMP VALUE +456.
       77  VARGTYPE            PIC S9(4)  COMP VALUE +464.
       77  GTYPE               PIC S9(4)  COMP VALUE +468.
       77  LVARGTYP            PIC S9(4)  COMP VALUE +472.
       77  FLOATYPE            PIC S9(4)  COMP VALUE +480.
       77  DECTYPE             PIC S9(4)  COMP VALUE +484.
       77  INTTYPE             PIC S9(4)  COMP VALUE +496.
       77  HWTYPE              PIC S9(4)  COMP VALUE +500.
       77  DATETYP             PIC S9(4)  COMP VALUE +384.
       77  TIMETYP             PIC S9(4)  COMP VALUE +388.
       77  TIMESTMP            PIC S9(4)  COMP VALUE +392.
      *

Declare the cursor and include the SQL communication area as usual in the working storage section.

*****************************************************
      *   DECLARE CURSOR AND STATEMENT FOR DYNAMIC SQL
      *****************************************************
      *
                EXEC SQL DECLARE DT CURSOR FOR SEL  END-EXEC.
                EXEC SQL DECLARE SEL STATEMENT      END-EXEC.
      *
      *****************************************************
      * SQL INCLUDE FOR SQLCA                             *
      *****************************************************
                EXEC SQL INCLUDE SQLCA  END-EXEC.

Write SQL error handling logic in procedure division.

*****************************************************
      * SQL RETURN CODE HANDLING                          *
      *****************************************************
           EXEC SQL WHENEVER SQLERROR   GOTO DBERROR END-EXEC.
           EXEC SQL WHENEVER SQLWARNING GOTO DBERROR END-EXEC.
           EXEC SQL WHENEVER NOT FOUND  CONTINUE     END-EXEC.
      *

Write the logic of PREPARE statement in the procedure division, which builds the SQL query.

  PROCESS-INPUT.
      *
           MOVE TNAME TO STMTTAB.
           MOVE STMTBLD TO STMTCHAR.
           MOVE +750 TO SQLN.
           EXEC SQL PREPARE SEL INTO :SQLDA FROM :STMTBUF  END-EXEC.

You can now use the cursors as usual for Open, Fetch, and Close statements.

****************************************************************
      *                                                              *
      *    SET LENGTH OF OUTPUT RECORD.                              *
      *    EXEC  SQL OPEN DT END-EXEC.                               *
      *    DO WHILE SQLCODE IS 0.                                    *
      *       EXEC SQL FETCH DT USING DESCRIPTOR :SQLDA END-EXEC.    *
      *       ADD IN MARKERS TO DENOTE NULLS.                        *
      *       WRITE THE DATA TO SYSREC01.                            *
      *       INCREMENT DATA RECORD COUNTER.                         *
      *    END.                                                      *
      *                                                              *
      ****************************************************************
      *                                         **OPEN CURSOR
           EXEC SQL OPEN DT  END-EXEC.
           PERFORM BLANK-REC.
           EXEC SQL FETCH DT USING DESCRIPTOR :SQLDA END-EXEC.
      *                                          **NO ROWS FOUND
      *                                          **PRINT ERROR MESSAGE
                IF SQLCODE = NOT-FOUND
                   WRITE MSGREC FROM MSG-NOROW
                      AFTER ADVANCING 2 LINES
                   MOVE 'Y' TO ERR-FOUND
                ELSE
      *                                          **WRITE ROW AND
      *                                          **CONTINUE UNTIL
      *                                          **NO MORE ROWS
                   PERFORM WRITE-AND-FETCH
                      UNTIL SQLCODE IS NOT EQUAL TO ZERO.
      *
           EXEC SQL WHENEVER NOT FOUND  GOTO CLOSEDT    END-EXEC.
      *
       CLOSEDT.
           EXEC SQL CLOSE DT  END-EXEC.
      *

References

• COBOL Program Explained the Usage of Dynamic SQL