COBOL applications that issue SQL statements

You can code SQL statements in certain COBOL program sections.

The allowable sections are shown in the following table.

Table 1. Allowable SQL statements for COBOL program sections
SQL statement Program section
  • BEGIN DECLARE SECTION
  • END DECLARE SECTION
WORKING-STORAGE SECTION1 or LINKAGE SECTION
  • INCLUDE SQLCA
WORKING-STORAGE SECTION1 or LINKAGE SECTION
  • INCLUDE text-file-name
PROCEDURE DIVISION or DATA DIVISION2
  • DECLARE TABLE
  • DECLARE CURSOR
DATA DIVISION or PROCEDURE DIVISION
  • DECLARE VARIABLE
WORKING-STORAGE SECTION1
  • Other
PROCEDURE DIVISION
Notes:
  1. If you use the Db2 coprocessor, you can use the LOCAL-STORAGE SECTION wherever WORKING-STORAGE SECTION is listed in the table.
  2. When including host variable declarations, the INCLUDE statement must be in the WORKING-STORAGE SECTION or the LINKAGE SECTION.

You cannot put SQL statements in the DECLARATIVES section of a COBOL program.

Each SQL statement in a COBOL program must begin with EXEC SQL and end with END-EXEC. If you are using the Db2 precompiler, the EXEC and SQL keywords must appear on one line, but the remainder of the statement can appear on subsequent lines. If you are using the Db2 coprocessor, the EXEC and SQL keywords can be on different lines. Do not include any tokens between the two keywords EXEC and SQL except for COBOL comments, including debugging lines. Do not include SQL comments between the keywords EXEC and SQL.

If the SQL statement appears between two COBOL statements, the period after END-EXEC is optional and might not be appropriate. If the statement appears in an IF…THEN set of COBOL statements, omit the ending period to avoid inadvertently ending the IF statement.

You might code an UPDATE statement in a COBOL program as follows:
EXEC SQL
    UPDATE DSN8C10.DEPT
    SET MGRNO = :MGR-NUM
    WHERE DEPTNO = :INT-DEPT
END-EXEC.
Comments
You can include COBOL comment lines (* in column 7) in SQL statements wherever you can use a blank.

Start of changeAlso, you can include SQL comments (' --') in any embedded SQL statement. A space must precede the two hyphens (' --') that begin the comment. For more information, see SQL comments.End of change

Restrictions: If you are using the Db2 precompiler, be aware of the following restrictions:
  • You cannot include COBOL comment lines between the keywords EXEC and SQL. The precompiler treats COBOL debugging lines and page-eject lines (/ in column 7) as comment lines. The Db2 coprocessor treats the debugging lines based on the COBOL rules, which depend on the WITH DEBUGGING mode setting.
  • You cannot use COBOL inline comments that are identified by a floating comment indicator (*>). COBOL inline comments are interpreted correctly only when the Db2 coprocessor is used.

For an SQL INCLUDE statement, the Db2 precompiler treats any text that follows the period after END-EXEC, and on the same line as END-EXEC, as a comment. The Db2 coprocessor treats this text as part of the COBOL program syntax.

Debugging lines
The Db2 precompiler ignores the 'D' in column 7 on debugging lines and treats it as a blank. The Db2 coprocessor follows the COBOL language rules regarding debugging lines.
Continuation for SQL statements
The rules for continuing a character string constant from one line to the next in an SQL statement embedded in a COBOL program are the same as those for continuing a non-numeric literal in COBOL. However, you can use either a quote or an apostrophe as the first nonblank character in area B of the continuation line. The same rule applies for the continuation of delimited identifiers and does not depend on the string delimiter option.

To conform with SQL standard, delimit a character string constant with an apostrophe, and use a quote as the first nonblank character in area B of the continuation line for a character string constant.

Continued lines of an SQL statement can be in columns 8–72 when using the Db2 precompiler and columns 12–72 when using the Db2 coprocessor.

Delimiters
Delimit an SQL statement in your COBOL program with the beginning keyword EXEC SQL and an END-EXEC as shown in the following example code:
EXEC SQL
  SQL-statement
END-EXEC.
COPY
If you use the Db2 precompiler, do not use a COBOL COPY statement within host variable declarations. If you use the Db2 coprocessor, you can use COBOL COPY.
REPLACE
If you use the Db2 precompiler, the REPLACE statement has no effect on SQL statements. It affects only the COBOL statements that the precompiler generates.

If you use the Db2 coprocessor, the REPLACE statement replaces text strings in SQL statements as well as in generated COBOL statements.

Declaring tables and views
Your COBOL program should include the statement DECLARE TABLE to describe each table and view the program accesses. You can use the Db2 declarations generator (DCLGEN) to generate the DECLARE TABLE statements. You should include the DCLGEN members in the DATA DIVISION.
Dynamic SQL in a COBOL program
In general, COBOL programs can easily handle dynamic SQL statements. COBOL programs can handle SELECT statements if the data types and the number of fields returned are fixed. If you want to use variable-list SELECT statements, use an SQLDA.
Including code
To include SQL statements or COBOL host variable declarations from a member of a partitioned data set, use the following SQL statement in the source code where you want to include the statements:
EXEC SQL INCLUDE member-name END-EXEC.

If you are using the Db2 precompiler, you cannot nest SQL INCLUDE statements. In this case, do not use COBOL verbs to include SQL statements or host variable declarations, and do not use the SQL INCLUDE statement to include CICS® preprocessor related code. In general, if you are using the Db2 precompiler, use the SQL INCLUDE statement only for SQL-related coding. If you are using the COBOL Db2 coprocessor, none of these restrictions apply.

Use the 'EXEC SQL' and 'END-EXEC' keyword pair to include SQL statements only. COBOL statements, such as COPY or REPLACE, are not allowed.

Margins
You must code SQL statements that begin with EXEC SQL in columns 12–72. Otherwise the Db2 precompiler does not recognize the SQL statement.
Names
You can use any valid COBOL name for a host variable. Do not use external entry names or access plan names that begin with 'DSN', and do not use host variable names that begin with 'SQL'. These names are reserved for Db2.
Sequence numbers
The source statements that the Db2 precompiler generates do not include sequence numbers.
Statement labels
You can precede executable SQL statements in the PROCEDURE DIVISION with a paragraph name.
WHENEVER statement
The target for the GOTO clause in an SQL statement WHENEVER must be a section name or unqualified paragraph name in the PROCEDURE DIVISION.
Special COBOL considerations
The following considerations apply to programs written in COBOL:
  • In a COBOL program that uses elements in a multi-level structure as host variable names, the Db2 precompiler generates the lowest two-level names.
  • Using the COBOL compiler options DYNAM and NODYNAM depends on the operating environment.
    TSO and IMS: You can specify the option DYNAM when compiling a COBOL program if you use the following guidelines. IMS and Db2 share a common alias name, DSNHLI, for the language interface module. You must do the following when you concatenate your libraries:
    • If you use IMS with the COBOL option DYNAM, be sure to concatenate the IMS library first.
    • If you run your application program only under , be sure to concatenate the Db2 library first.

    CICS, CAF, and RRSAF: You must specify the NODYNAM option when you compile a COBOL program that either includes CICS statements or is translated by a separate CICS translator or the integrated CICS translator. In these cases, you cannot specify the DYNAM option. If your CICS program has a subroutine that is not translated by a separate CICS translator or the integrated CICS translator but contains SQL statements, you can specify the DYNAM option. However, in this case, you must concatenate the CICS libraries before the Db2 libraries.

    You can compile COBOL stored procedures with either the DYNAM option or the NODYNAM option. If you use DYNAM, ensure that the correct Db2 language interface module is loaded dynamically by performing one of the following actions:

    • Use the ATTACH(RRSAF) precompiler option.
    • Copy the DSNRLI module into a load library that is concatenated in front of the Db2 libraries. Use the member name DSNHLI.
  • To avoid truncating numeric values, use either of the following methods:
    • Use the COMP-5 data type for binary integer host variables.
    • Specify the COBOL compiler option:
      • Start of changeTRUNC(OPT) or TRUNC(STD) if you are certain that the data being moved to each binary variable by the application does not have a larger precision than is defined in the PICTURE clause of the binary variable.End of change
      • TRUNC(BIN) if the precision of data being moved to each binary variable might exceed the value in the PICTURE clause.

    Db2 assigns values to binary integer host variables as if you had specified the COBOL compiler option TRUNC(BIN) or used the COMP-5 data type.

  • If you are using the Db2 precompiler and your COBOL program contains several entry points or is called several times, the USING clause of the entry statement that executes before the first SQL statement executes must contain the SQLCA and all linkage section entries that any SQL statement uses as host variables.
  • If you use the Db2 precompiler, no compiler directives should appear between the PROCEDURE DIVISION and the DECLARATIVES statement.
  • Do not use COBOL figurative constants (such as ZERO and SPACE), symbolic characters, reference modification, and subscripts within SQL statements.
  • Observe the rules for naming SQL identifiers, as described in Identifiers in SQL. However, for COBOL only, the names of SQL identifiers can follow the rules for naming COBOL words, as described in COBOL words with single-byte characters (COBOL) . However, the names must not exceed the allowable length for the Db2 object.
  • Surround hyphens used as subtraction operators with spaces. Db2 usually interprets a hyphen with no spaces around it as part of a host variable name.
  • If you include an SQL statement in a COBOL PERFORM … THRU paragraph and also specify the SQL statement WHENEVER … GO, the COBOL compiler returns the warning message IGYOP3094. That message might indicate a problem. This usage is not recommended.
  • If you are using the Db2 precompiler, all SQL statements and any host variables they reference must be within the first program when using nested programs or batch compilation.
  • If you are using the Db2 precompiler, your COBOL programs must have a DATA DIVISION and a PROCEDURE DIVISION. Both divisions and the WORKING-STORAGE SECTION must be present in programs that contain SQL statements. However, if your COBOL programs requires the LOCAL-STORAGE SECTION, then the Db2 coprocessor should be used instead of the Db2 precompiler.
  • Start of changeThe Db2 precompiler generates this COBOL variable:
    DSN-TMP2 PIC S9(18) COMP-3
    The Db2 coprocessor generates this COBOL variable:
    SQL---SCRVALD DS 10P PIC S9(18) COMP-3
    If you specify COBOL option RULES(NOEVENPACK), the COBOL compiler generates warning IGYDS1348-W, because those variables have an even number of packed decimal digits.End of change

Begin program-specific programming interface information.If your program uses the Db2 precompiler and uses parameters that are defined in LINKAGE SECTION as host variables to Db2 and the address of the input parameter might change on subsequent invocations of your program, your program must reset the variable SQL-INIT-FLAG. This flag is generated by the Db2 precompiler. Resetting this flag indicates that the storage must initialize when the next SQL statement executes. To reset the flag, insert the statement MOVE ZERO TO SQL-INIT-FLAG in the called program's PROCEDURE DIVISION, ahead of any executable SQL statements that use the host variables. If you use the COBOL Db2 coprocessor, the called program does not need to reset SQL-INIT-FLAG.End program-specific programming interface information.

Handling SQL error codes
Cobol applications can request more information about SQL errors from Db2. For more information, see Handling SQL error codes in Cobol applications.