COBOL | DB2(R) Universal Database for OS/390 V7.1 Application Certification Guide (IBM DB2 Certification Guide Series)


Team-Fly

	DB2 Universal Database for OS/390 v7.1 Application Certification Guide By Susan Lawson
	Table of Contents

	Chapter 12. DB2 Traditional Programming

COBOL

COBOL is a traditional and very common mainframe programming language that is utilized for a variety of business applications. COBOL programs can be online or batch programs. COBOL programs can run in any OS/390 address space, can execute under a variety of transaction managers such as CICS, and can be used for DB2 stored procedures and user -defined functions. COBOL is one of the most common programming languages, and there are many billions of lines of COBOL code in use today.

COBOL API

DB2 provides a COBOL API that allows you to embed static or dynamic SQL into a COBOL program. You can code SQL statements in the COBOL program sections, which are listed in Table 12-1.

Table 12-1. COBOL Program Sections for SQL Statements

SQL Statement	Program Section
BEGIN DECLARE SECTION	Working-storage section or linkage section
END DECLARE SECTION	Working-storage section or linkage section
INCLUDE SQLCA	Working-storage section or linkage section
INCLUDE text-file- name	Data division or procedure division
DECLARE TABLE	Data division or procedure division
DECLARE CURSOR	Data division or procedure division
Other	Procedure division

NOTE

When including host variable declarations in an included text file, the INCLUDE statement must be in the working-storage section or linkage section.

Embedding SQL in a COBOL Program

Each SQL statement in a COBOL program must begin with EXEC SQL and end with END-EXEC. If the SQL statement is embedded between two COBOL statements, the period at the end of the END-EXEC is optional and might not be appropriate. If the SQL statement is embedded in a COBOL IF...ELSE expression, the period should be left off the end of the SQL statement in order to avoid inadvertently terminating the IF...ELSE expression. The EXEC and SQL keywords must appear on the same line of the program, after which the rest of a statement can appear on any number of lines. Here is an example of an embedded SQL statement in a COBOL program:

 EXEC SQL  INSERT INTO YLA.TABLE1 VALUES(:VAR1, :VAR2, DEFAULT) END-EXEC.

A COBOL program that contains SQL statements must include either a SQLCODE or SQLSTATE host variable, or both. The program can either define these variables, or you can use an INCLUDE SQLCA statement to include the SQL communications area (SQLCA) within the program. The SQLCA structure contains both the SQLSTATE and SQLCODE variables .

The 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 DECLARE TABLE statements. You should include the DCLGEN members in the data division. Refer to Chapter 5, "Using SQL in an Application Program," to create the DCLGEN. The INCLUDE statement can be used to include table declarations, host variable declarations, and SQL statements from members of a partitioned dataset at precompile time.

Dynamic SQL statements can be coded into COBOL programs. COBOL programs can handle dynamic SQL statements if the data types and the number of fields returned are fixed. If you need to use variable-list SELECT statements in your COBOL program, then you must use a SQL descriptor area (SQLDA) within your program.

Host Variable Declarations

You must explicitly declare all host variables used in SQL statements in the working-storage section or linkage section of your program's data division. You must explicitly declare each host variable before it is first used in any SQL statement. You can precede the COBOL statements that define the host variables with the statement BEGIN DECLARE SECTION and follow the statements with the statement END DECLARE SECTION. A colon (:) must precede all host variable references in a SQL statement. There are numerous mappings between SQL data types and the equivalent COBOL variable declarations. Table 12-2 lists only the most typical data type mappings:

Table 12-2. SQL and COBOL Data Type Mappings

SQL Data Type	COBOL Data Type
SMALLINT	S9(4) COMP-4
INTEGER	S9(9) COMP-4
DECIMAL(p,s) or NUMERIC(p,s)	S9(p-s)V9(s) COMP-3
CHAR(n)	X(n)
VARCHAR(n)	01 VAR-NAME. 49 VAR-LEN PIC S9(4) USAGE BINARY. 49 VAR-TXT PIC X(n)
DATE	X(10)
TIMESTAMP	X(26)

In addition to these and many other basic data type mappings, there are several special-purpose COBOL data types, including result set locators used for processing result sets returned from stored procedures, table locators used for accessing transition tables created by a trigger and passed to a user-defined function or a stored procedure, LOB data types, LOB locators used to avoid the materialization of LOBs in an application program, and the ROWID data type.

Indicator Variables

Indicator variables are used in a COBOL program to indicate a null value for an optional DB2 table column. Your COBOL program will have to interpret or set the indicator variable in order to deal with optional columns . The indicator variable is typically set to 0 to indicate the presence of a value and 1 to indicate the null value. You declare indicator variables just as you would a SMALLINT variable in your program. You reference the indicator in your SQL statements immediately following the variable for the optional column to which they relate. For example,

 EXEC SQL     FETCH CURSOR1 INTO :VAR1 :VAR1-IND END EXEC.

The above statement fetches a column from a DB2 cursor into the COBOL variable VAR1, and since the column in this case is optional, the indicator variable is placed in the COBOL variable VAR1-IND. The indicator variable can then be tested in program logic or referenced in further SQL statements to determine whether or not the value of VAR1 is null.

Error Handling

The SQLCODE or SQLSTATE variables should be tested after each SQL statement called. The DSNTIAR program can be used to help interpret SQL errors when detected . The DSNTIAR program is called from your COBOL program with the SQLCA as an input parameter. DSNTIAR then places an English message in an output parameter. This message can then be used as part of a display of the error in the program.

COBOL Program Example

The following simple COBOL program processes a single DB2 cursor, and displays the result:

 IDENTIFICATION DIVISION.  PROGRAM-ID. YLAPROG1. AUTHOR. REMARKS. *********************************************************** *                                                         * * THIS PROGRAM USES COBOL TO READ THE                     * * DB2 SYSDUMMY1 CATALOG TABLE, AND DISPLAY THE RESULT.    * *                                                         * *********************************************************** ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. *********************************************************** * SQLCA                                                   * *********************************************************** EXEC SQL    INCLUDE SQLCA END-EXEC. *********************************************************** * DECLARATION OF SYSDUMMY1 TABLE                          * *********************************************************** EXEC SQL DECLARE SYSIBM.SYSDUMMY1 TABLE    (IBMREQD     CHAR(1) NOT NULL) END-EXEC. *********************************************************** * HOST VARIABLE DECLARATIONS                              * *********************************************************** 01 SYSDUMMY1-TBL.    05 IBMREQD-CDE      PIC X(1). *********************************************************** * DB2 CURSOR DECLARATION                                  * *********************************************************** EXEC SQL DECLARE CURS1 CURSOR FOR    SELECT IBMREQD    FROM   SYSIBM.SYSDUMMY1 END-EXEC. *********************************************************** * BEGIN THE PROGRAM                                       * *********************************************************** PROCEDURE DIVISION. PERFORM A100-OPEN. PERFORM A200-FETCH    UNTIL SQLCODE IS NOT EQUAL TO ZERO. PERFORM A300-CLOSE. PROGRAM-END.    GOBACK. A100-OPEN.    EXEC SQL       OPEN CURS1    END-EXEC.    IF SQLCODE IS NOT EQUAL TO ZERO       DISPLAY 'SQLCODE FROM OPEN IS ' SQLCODE       GOTO PROGRAM-END       END-IF. A200-FETCH.    EXEC SQL       FETCH CURS1 INTO :IBMREQD-CDE    END-EXEC.    IF SQLCODE IS EQUAL TO ZERO       DISPLAY 'IBMREQD CODE IS ' IBMREQD-CDE    ELSE       DISPLAY 'SQLCODE FROM FETCH IS ' SQLCODE       GOTO PROGRAM-END       END-IF. A300-CLOSE.    EXEC SQL       CLOSE CURS1    END-EXEC.


Team-Fly

Top