ILE COBOL – SQLCBLLE

SYSIBM.SYSDUMMY1 is a built-in dummy table in IBM Db2. It contains exactly one column (IBMREQD) and one row, with the value ‘Y’. It is used as a placeholder in SELECT statements when a table reference is syntactically required but no actual table data is needed.

Commonly used in below cases:

• Evaluating Functions

  1. SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1;

• Checking Connectivity

  1. SELECT 1 FROM SYSIBM.SYSDUMMY1;

• Testing Global Variables

  1. SELECT USER FROM SYSIBM.SYSDUMMY1;

STRSQL Session:

// For Date & Time 
SELECT CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP, CURRENT DATE + 1 
   DAY, CURRENT – 1 DAY FROM SYSIBM.SYSDUMMY1

// For Security & Identity
SELECT CURRENT USER, USER, SYSTEM_USER FROM SYSIBM.SYSDUMMY1

// For Environment Control
SELECT CURRENT SCHEMA, CURRENT PATH FROM SYSIBM.SYSDUMMY1

// For Client Information 
SELECT CURRENT CLIENT_APPLNAME, CURRENT CLIENT_USERID, 
CURRENT CLIENT_WRKSTNNAME FROM SYSIBM.SYSDUMMY1

IBMi Access Client Solutions – Run SQL Scripts:

// For Date & Time 
SELECT CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP, CURRENT DATE + 1 
   DAY, CURRENT – 1 DAY FROM SYSIBM.SYSDUMMY1;

// For Security & Identity
SELECT CURRENT USER, USER, SYSTEM_USER FROM SYSIBM.SYSDUMMY1;

// For Environment Control
SELECT CURRENT SCHEMA, CURRENT PATH FROM SYSIBM.SYSDUMMY1;

// For Client Information 
SELECT CURRENT CLIENT_APPLNAME, CURRENT CLIENT_USERID, 
CURRENT CLIENT_WRKSTNNAME FROM SYSIBM.SYSDUMMY1;

 IDENTIFICATION DIVISION.
*------------------------*
 PROGRAM-ID. DB2REGPGM.

 DATA DIVISION.
*---------------*
 WORKING-STORAGE SECTION.
 EXEC SQL INCLUDE SQLCA END-EXEC.
 EXEC SQL BEGIN DECLARE SECTION END-EXEC.
 01  WS-DATE-TIME-VARS.
     05  WS-CUR-DATE       PIC X(10).
     05  WS-CUR-TIME       PIC X(8).
     05  WS-CUR-TIMESTAMP  PIC X(26).
     05  WS-TOMORROW       PIC X(10).
     05  WS-YESTERDAY      PIC X(10).
 01  WS-SECURITY-VARS.
     05  WS-CUR-USER       PIC X(128).
     05  WS-USER           PIC X(128).
     05  WS-SYSTEM-USER    PIC X(128).
 01  WS-ENV-VARS.
     05  WS-CUR-SCHEMA     PIC X(128).
     05  WS-CUR-PATH       PIC X(2048).
 01  WS-CLIENT-VARS.
     05  WS-CLIENT-APP     PIC X(255).
     05  WS-CLIENT-USER    PIC X(255).
     05  WS-CLIENT-WS      PIC X(255).
 EXEC SQL END DECLARE SECTION END-EXEC.
 
 PROCEDURE DIVISION.
*-------------------*
 MAIN-LOGIC.
     PERFORM FETCH-DATE-TIME
     PERFORM FETCH-SECURITY
     PERFORM FETCH-ENVIRONMENT
     PERFORM FETCH-CLIENT-INFO
     GOBACK.

 FETCH-DATE-TIME.
*----------------*
     EXEC SQL
        SELECT CURRENT DATE, 
               CURRENT TIME, 
               CURRENT TIMESTAMP, 
               CURRENT DATE + 1 DAY, 
               CURRENT DATE - 1 DAY
        INTO   :WS-CUR-DATE, 
               :WS-CUR-TIME, 
               :WS-CUR-TIMESTAMP, 
               :WS-TOMORROW, 
               :WS-YESTERDAY
        FROM   SYSIBM.SYSDUMMY1
     END-EXEC.

 FETCH-SECURITY.
*---------------*
     EXEC SQL
        SELECT CURRENT USER, 
               USER, 
               SYSTEM_USER
        INTO   :WS-CUR-USER, 
               :WS-USER, 
               :WS-SYSTEM-USER
        FROM   SYSIBM.SYSDUMMY1
     END-EXEC.

 FETCH-ENVIRONMENT.
*------------------*
     EXEC SQL
        SELECT CURRENT SCHEMA, 
               CURRENT PATH
        INTO   :WS-CUR-SCHEMA, 
               :WS-CUR-PATH
        FROM   SYSIBM.SYSDUMMY1
     END-EXEC.

 FETCH-CLIENT-INFO.
*------------------*
     EXEC SQL
        SELECT CURRENT CLIENT_APPLNAME, 
               CURRENT CLIENT_USERID, 
               CURRENT CLIENT_WRKSTNNAME
        INTO   :WS-CLIENT-APP, 
               :WS-CLIENT-USER, 
               :WS-CLIENT-WS
        FROM   SYSIBM.SYSDUMMY1
     END-EXEC.

 FETCH-EXIT. EXIT.
*-----------------* 

SQLCBL (Native COBOL with embedded SQL) /SQLCBLLE (ILE COBOL with Embedded SQL) programming means writing SQL statements inside a COBOL program so that the program can read, insert, update, or delete data from a database such as DB2. Using an embedded programming language like SQLCBLLE offers a paradigm shift for programmers by moving from “how to get data” (procedural) to “what data is needed” (declarative).

Key differences:

Core Concepts:

Delimiters: All SQL statements must be written between EXEC SQL and END-EXEC. Programmers must not use a period or full stop inside it, because everything between these two keywords is treated as one single statement.

EXEC SQL 
    ….. .
END-EXEC.

Host Variables: These are standard COBOL variables used within SQL statements to pass or receive data. They must be preceded by a colon (e.g.=: WS-ID) when used inside an SQL block.

EXEC SQL 
    SET :WS-TARGET-VAR = :WS-SOURCE-VAR
END-EXEC.

EXEC SQL
    VALUES (:WS-SOURCE-VAR) INTO :WS-TARGET-VAR
END-EXEC.

Declare Section: The Declare Section is a designated area in the WORKING-STORAGE SECTION that registers COBOL variables with the SQL pre-compiler. Without this section, the SQL pre-compiler will not recognize your COBOL variables when you try to use them inside an EXEC SQL statement.

EXEC SQL BEGIN DECLARE SECTION END-EXEC.
   01 WS-EMP-ID        PIC 9(5).
   01 WS-EMP-NAME      PIC X(30).
   01 WS-SALARY        PIC S9(7)V99 COMP-3.
EXEC SQL END DECLARE SECTION END-EXEC.

SQLCA (SQL Communication Area): A mandatory structure used by the database to communicate the results of an SQL execution back to the program. It includes the SQLCODE variable, where 0 typically indicates success.
The SQLCA (SQL Communication Area) is a mandatory data structure used by the IBM i database engine to pass feedback details back to your CBLLE program after every single EXEC SQL statement executes.

WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
   01 WS-EMP-ID        PIC 9(6).
   01 WS-EMP-NAME      PIC X(30).
   01 WS-SALARY        PIC S9(7)V99 COMP-3.
EXEC SQL END DECLARE SECTION END-EXEC.

PROCEDURE DIIVSION.
   MOVE 990161         TO WS-EMP-ID
   MOVE 003687.00      TO WS-SALARY 
   EXEC SQL 
 SELECT EMP_NAME, EMP_SALARY 
 INTO      :WS-EMP-NAME, :WS-SALARY 
 FROM      EMPLOYEE 
 WHERE      EMP_ID = :WS-EMP-ID 
   END-EXEC.
   IF SQLCODE < 0
      DISPLAY “SQLCODE:” SQLCODE
   ENDIF.  

Omitting “EXEC SQL INCLUDE SQLCA END-EXEC.” causes compilation error all programs utilizing SQLCODE and SQLSTATE. The compiler will reject the code because it cannot locate the definitions for built-in tracking variables like:

• Error Handling: SQLCODE, SQLSTATE, WHENEVER SQLERROR, WHENEVER NOT FOUND.
• Warning Flags: WHENEVER SQLWARNING, SQLWARN0, SQLWARN1, SQLWARN2, SQLWARN3.
• Execution Metadata & Structure Metrices: SQLERRD, SQLCAID, SQLCABC.
• Diagnostic Messages: SQLERRP, SQLERRML, SQLERRMC.

 IDENTIFICATION DIVISION.
*------------------------*
 PROGRAM-ID. UPDATEEMP.   
    
 DATA DIVISION.
*--------------*
 WORKING-STORAGE SECTION.
*------------------------*
* Missing: EXEC SQL INCLUDE SQLCA END-EXEC.

 PROCEDURE DIVISION.
*-------------------*
 MAIN-PARA.
* === THIS STATEMENT FAILS COMPILATION ===
* Error: Pre-compiler cannot expand WHENEVER directive because SQLCA * is missing
 EXEC SQL 
    WHENEVER SQLERROR GO TO ERROR-HANDLING 
 END-EXEC.
 EXEC SQL
    UPDATE EMPLOYEE
    SET SALARY = SALARY * 1.05
    WHERE DEPT_ID = 10
 END-EXEC.          

 DISPLAY "Updates successful."
 GOBACK.

 ERROR-HANDLING.
    DISPLAY "Transaction rolled back due to an error."
    GOBACK.

Example:

 IDENTIFICATION DIVISION.
*------------------------*
 PROGRAM-ID.  UPDTSAL.
 AUTHOR.  PROGRAMMER.
 
 ENVIRONMENT DIVISION.
*---------------------*
 DATA DIVISION.
 WORKING-STORAGE SECTION.
 *> Declare SQL Communication Area for error checking
 EXEC SQL INCLUDE SQLCA END-EXEC.
 EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
 *> Define the host variables to pass parameters into SQL
 01  WS-DEPT-ID        PIC X(3).
 01  WS-HIKE-PCT       PIC S9V99 COMP-3.
 01  WS-ROWS-UPDATED   PIC 9(5).
 EXEC SQL END DECLARE SECTION END-EXEC.
 PROCEDURE DIVISION.
*-------------------*
 000-MAIN-LOGIC.
    *> Initialize hike percentage using SQL SET
    EXEC SQL 
       SET   :WS-DEPT-ID = 'D01',
    :WS-HIKE-PCT = 0.10    
    END-EXEC.
    *> Execute the SQL Update statement
    EXEC SQL
       UPDATE  EMPFILE
       SET  SALARY = SALARY + (SALARY * :WS-HIKE-PCT)
       WHERE  DEPTNO = :WS-DEPT-ID
    END-EXEC.
    *> Check execution status
    IF SQLCODE = 0
       DISPLAY 'Salary update successful.'
       *> Retrieve number of updated rows (optional)
   EXEC SQL
  GET DIAGNOSTICS :WS-ROWS-UPDATED = ROW_COUNT
   END-EXEC
   DISPLAY 'Employees updated: ' WS-ROWS-UPDATED
    ELSE
   DISPLAY 'Update failed. SQLCODE: ' SQLCODE
    END-IF.
 GOBACK.

In IBMi Db2, the EXEC SQL SET statement is primarily used within application programs (e.g. COBOL) to assign values to variables.

Common Usage Scenarios:

1. Assigning a constant or expression: Programmers can set a variable to a specific value or the result of a calculation.

*> Single host variable 
EXEC SQL 
   SET :hv_name = 'ASHWIN'
END-EXEC.
EXEC SQL 
   SET :hv_salary = :hv_salary * 1.10
END-EXEC.
*> Multiple host variable 
EXEC SQL 
   SET :hv_name = 'ASHWIN', 
       :hv_salary = :hv_salary * 1.10
END-EXEC.

2. Assigning Results from a Table: The VALUES INTO or SELECT INTO statements are often preferred for retrieving database values into host variables.

EXEC SQL 
   VALUES (CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP)
     INTO :HV-CURR-DATE, :HV-CURR-TIME, :HV-CURR-TS
END-EXEC.

Special Registers and SET and VALUES

In IBM i, a special register is a reserved storage area maintained automatically by the system. It holds contextual information about the current execution environment, which application programs can query or change dynamically. These registers are also called as DB2 for i(SQL) Special Registers.

• Date & Time: These are read-only special registers that cannot be modified.

  1. CURRENT DATE (returns YYYY-MM-DD)
  2. CURRENT TIME (returns HH.MM.SS)
  3. CURRENT TIMESTAMP (returns YYYY-MM-DD-HH.MM.SS.UUUUUU)

   IDENTIFICATION DIVISION.
  *------------------------*
   PROGRAM-ID. GETSYSTEM.
   DATA DIVISION.
  *--------------*
   WORKING-STORAGE SECTION.
   EXEC SQL INCLUDE SQLCA END-EXEC.
   EXEC SQL BEGIN DECLARE SECTION END-EXEC.
   01  HV-CURR-DATE     PIC X(10).
   01  HV-CURR-TIME     PIC X(8).
   01  HV-CURR-TS       PIC X(26).
   01  HV-TOMORROW      PIC X(10).
   01  HV-YESTERDAY     PIC X(10).
   EXEC SQL END DECLARE SECTION END-EXEC.
  
   PROCEDURE DIVISION.
  *-------------------*
   MAIN-PROCESS.
  * Get Current date, time and timestamp using VALUES  
   EXEC SQL
      VALUES (CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP)
        INTO :HV-CURR-DATE, :HV-CURR-TIME, :HV-CURR-TS
   END-EXEC.
  
  * Get Current date, time and timestamp using SET
   EXEC SQL 
      SET :HV-CURR-DATE = CURRENT DATE, 
          :HV-CURR-TIME = CURRENT TIME, 
          :HV-CURR-TS   = CURRENT TIMESTAMP 
   END-EXEC.
  
  * Get tomorrow's date or yesterday's date 
   EXEC SQL 
      SET :HV-TOMORROW = CURRENT DATE + 1 DAY, 
          :HV-YESTERDAY = CURRENT DATE - 1 DAY 
   END-EXEC.
   GOBACK.
  

• Security & Identity: These are read-only special registers that cannot be modified.

  1. CURRENT USER
  2. USER
  3. SYSTEM_USER

   IDENTIFICATION DIVISION.
  *------------------------*
   PROGRAM-ID. GETUSERS.
   DATA DIVISION.
  *--------------*
   WORKING-STORAGE SECTION.
   EXEC SQL INCLUDE SQLCA END-EXEC.
   EXEC SQL BEGIN DECLARE SECTION END-EXEC.
   01 HV-CURR-USER PIC X(10). 
   01 HV-USER      PIC X(10). 
   01 HV-SYS-USER  PIC X(10).
   EXEC SQL END DECLARE SECTION END-EXEC.
  
   PROCEDURE DIVISION.
  *-------------------*
   MAIN-PROCESS.
  * Get Current user, user, system user using VALUES  
   EXEC SQL
      VALUES (CURRENT USER, USER, SYSTEM_USER)
        INTO :HV-CURR-USER, :HV-USER, :HV-SYS-USER
   END-EXEC.
  
  * Get Current user, user, system user using SET  
   EXEC SQL
      SET CURRENT USER = :HV-CURR-USER, 
          USER         = :HV-USER, 
          SYSTEM_USER  = :HV-SYS-USER)
   END-EXEC.
   GOBACK.
  

• Environment Control: These are updatable special registers.

  1. CURRENT SCHEMA (the default library)
  2. CURRENT PATH (the search routine path).

   IDENTIFICATION DIVISION. 
  *------------------------* 
   PROGRAM-ID. GETENVCTL. 
  
   DATA DIVISION. 
  *--------------* 
   WORKING-STORAGE SECTION. 
   EXEC SQL INCLUDE SQLCA END-EXEC. 
   EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
   01 HV-CURR-SCHEMA   PIC X(10). 
   01 HV-SCHEMA        PIC X(10). 
   01 HV-CURR-PATH     PIC X(100). 
  *
   01 HV-NEW-SCHEMA    PIC X(10) VALUE "PRODLIB ". 
   01 HV-NEW-PATH      PIC X(50) VALUE "'LIB1', 'LIB2', 'SYSIBM'". 
   EXEC SQL END DECLARE SECTION END-EXEC. 
   PROCEDURE DIVISION. 
  *-------------------* 
   MAIN-PROCESS. 
  *   Retrieve the initial values using VALUES INTO 
      EXEC SQL 
         VALUES (CURRENT SCHEMA, CURRENT PATH) 
         INTO   :HV-CURR-SCHEMA, :HV-CURR-PATH 
      END-EXEC. 
      DISPLAY "INITIAL SCHEMA: " HV-CURR-SCHEMA 
      DISPLAY "INITIAL PATH: " HV-CURR-PATH 
  *    Modify the registers using SET 
       EXEC SQL 
          SET CURRENT SCHEMA = :HV-NEW-SCHEMA 
       END-EXEC. 
       EXEC SQL 
          SET CURRENT PATH = :HV-NEW-PATH 
       END-EXEC. 
  *    Verify the changes were applied 
       EXEC SQL 
          VALUES (CURRENT SCHEMA, CURRENT PATH) 
          INTO :HV-SCHEMA, :HV-CURR-PATH 
       END-EXEC. 
       DISPLAY "NEW SCHEMA: " HV-SCHEMA 
       DISPLAY "NEW PATH: " HV-CURR-PATH 
       GOBACK.
  

• Client Information: These are read-only special registers that cannot be modified.

  1. CURRENT CLIENT_APPLNAME (Contains the name of the specific application program executing the SQL)
  2. CURRENT CLIENT_USERID (Contains the user ID specified by the client connection.)
  3. CURRENT CLIENT_WRKSTNNAME.

   IDENTIFICATION DIVISION. 
  *------------------------* 
   PROGRAM-ID. GETCLNTINF. 
  
   DATA DIVISION. 
  *--------------* 
   WORKING-STORAGE SECTION. 
   EXEC SQL INCLUDE SQLCA END-EXEC. 
   EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
   01 HV-CURR-APPL     PIC X(255). 
   01 HV-CURR-USER     PIC X(255). 
   01 HV-CURR-WRKSTN   PIC X(255). 
   EXEC SQL END DECLARE SECTION END-EXEC. 
   
   PROCEDURE DIVISION. 
  *-------------------* 
   MAIN-PROCESS. 
  *   Retrieve the client information’s using VALUES INTO 
      EXEC SQL 
         VALUES (CURRENT CLIENT_APPLNAME, 
                 CURRENT CLIENT_USERID, 
                 CURRENT CLIENT_WRKSTNNAME) 
         INTO   :HV-CURR-APPL, 
                :HV-CURR-USER, 
                :HV-CURR-WRKSTN 
      END-EXEC. 
      
      DISPLAY "INITIAL APPLNAME: " HV-CURR-APPL 
      DISPLAY "INITIAL USERID:   " HV-CURR-USER 
      DISPLAY "INITIAL WRKSTN:   " HV-CURR-WRKSTN 
      
  *   Get the client information’s using SET 
      EXEC SQL 
         SET :HV-CURR-APPL   = CURRENT CLIENT_APPLNAME,
             :HV-CURR-USER   = CURRENT CLIENT_USERID,    
    :HV-CURR-WRKSTN = CURRENT CLIENT_WRKSTNNAME
      END-EXEC. 
    
      DISPLAY "NEW APPLNAME:     " HV-CURR-APPL 
      DISPLAY "NEW USERID:       " HV-CURR-USER 
      DISPLAY "NEW WRKSTN:       " HV-CURR-WRKSTN 
      GOBACK.
  

A cursor is a pointer used by an embedded SQL program to process one row at a time from a table. Normally, SQL works on many rows at once, but COBOL works one record at a time. A cursor connects SQL and COBOL. Programmer’s use cursors when:

• The query returns multiple rows and each row needs to be processed individually.
• Retrieve required data applying “WHERE” clause and ordered row via “ORDER BY” clause with different key fields for batch processing or detailed handling.
• SQL aggregate functions calculate a single summary value from a set of input values, typically used with GROUP BY to categorize data. Key functions include SUM () (total), AVG () (average), COUNT () (row count), MIN () (minimum), and MAX () (maximum).

Cursor Life Cycle

A cursor has a fixed life cycle with four main steps:

1. DECLARE – Define the cursor and the SELECT statement
2. OPEN – Execute the SELECT and prepare result set
3. FETCH – Read one row at a time
4. CLOSE – Release cursor resources

Declare Cursor

The DECLARE CURSOR statement is used to define the cursor and associates it with a SELECT query statement.

Syntax:

EXEC SQL
   DECLARE cursor_name CURSOR FOR SELECT col1, col2 FROM table_name
END-EXEC.

EXEC SQL
   DECLARE cursor_name CURSOR FOR SELECT * FROM table_name
END-EXEC.

Open Cursor

The OPEN cursor statement executes the SELECT query statement and creates the result set available and also makes it available for fetching.

Syntax:

EXEC SQL
   OPEN cursor_name
END-EXEC.

Fetch Cursor

The FETCH cursor statement retrieves the next row from the cursor into program variables.

Syntax:

EXEC SQL
   FETCH cursor_name INTO :host_variable1, :host_variable2
END-EXEC.

EXEC SQL
   FETCH cursor_name INTO :host_variable
END-EXEC.

Close Cursor

The CLOSE cursor statement releases the result set and system resources used by the cursor.

EXEC SQL
   CLOSE cursor_name
END-EXEC.

Example:

 IDENTIFICATION DIVISION.
*------------------------*
 PROGRAM-ID.  GETPENDORD.
 AUTHOR.      PROGRAMMER.

 DATA DIVISION.
*--------------*
 WORKING-STORAGE SECTION.
*----------------------------------------------------------------*
* SQL INCLUDE FOR SQL COMMUNICATIONS AREA (SQLCA)                *
*----------------------------------------------------------------*
 EXEC SQL INCLUDE SQLCA END-EXEC.
*----------------------------------------------------------------*
* SQL HOST VARIABLES DECLARATION                                 *
*----------------------------------------------------------------*
 EXEC SQL BEGIN DECLARE SECTION END-EXEC.
 01  OH-ORDR-ID      PIC 9(10).
 01  WH-WHSE-ID      PIC 9(04). 
 01  OD-ITEM-ID      PIC X(15).
 01  OD-QTY-ORD      PIC 9(05).
 01  WH-QTY-AVL      PIC 9(05).
 01  WS-REQ-QTY      PIC S9(05).
 EXEC SQL END DECLARE SECTION END-EXEC.
*----------------------------------------------------------------*
* PROGRAM VARIABLES                                              *
*----------------------------------------------------------------*
01  SWITCHES.
    05  EOF-SWITCH   PIC X(1) VALUE 'N'.
        88  END-OF-FILE       VALUE 'Y'.

 PROCEDURE DIVISION.
*-------------------*
 MAIN-LINE.
    PERFORM A100-OPEN-CURSOR
       THRU A100-EXIT. 
    PERFORM A200-FETCH-RECORD
       THRU A200-EXIT 
      UNTIL END-OF-FILE. 
    PERFORM A300-CLOSE-CURSOR
       THRU A300-EXIT.
    STOP RUN. 
*----------------------------------------------------------------*
* A100-OPEN-CURSOR: DECLARE AND OPEN THE SQL CURSOR              *
*----------------------------------------------------------------*
 A100-OPEN-CURSOR.
    *> CURSOR JOINS PENDING ORDERS WITH INVENTORY TO FIND SHORTAGES
    EXEC SQL
       DECLARE PEND_ORDR_CUR CURSOR FOR
       SELECT  OH.ORDR_ID, WH.WHSE_ID, OD.ITEM_ID, OD.QTY_ORD,     
               IFNULL(WH.QTY_AVL,0), 
               (OD.QTY_ORD - IFNULL(WH.QTY_AVL,0)) as "REQ_QTY"
       FROM    ORDHDRPF OH
       INNER   JOIN ORDDTLPF OD
       ON      OH.ORDR_ID = OD.ORDR_ID  
       AND     OH.WHSE_ID = OD.WHSE_ID
       LEFT    OUTER JOIN WHSMSTPF WH
       ON      OD.WHSE_ID = WH.WHSE_ID
       AND     OD.ITEM_ID = WH.ITEM_ID
       WHERE   OH.ORD_STS = 'PENDING'
       AND    (OD.QTY_ORD - IFNULL(WH.QTY_AVL,0)) > 0 
    END-EXEC.

    *> OPEN CURSOR
    EXEC SQL
       OPEN PEND_ORDR_CUR
    END-EXEC.

    *> HANDLE ERROR
    IF SQLCODE NOT = 0
     *> THROW ERROR
       DISPLAY 'ERROR OPENING CURSOR. SQLCODE: ' SQLCODE
       MOVE 'Y'        TO EOF-SWITCH
    END-IF.
 A100-EXIT. EXIT. 
*----------------------------------------------------------------*
* A200-FETCH-RECORD: LOOP THROUGH RECORDS AND PROCESS REPORT     *
*----------------------------------------------------------------*
 A200-FETCH-RECORD.
    *> FETCH NEXT ROW FROM CURSOR
    EXEC SQL
       FETCH PEND_ORDR_CUR 
       INTO :OH-ORDR-ID, :WH-WHSE-ID, :OD-ITEM-ID,  
            :OD-QTY-ORD, :WH-QTY-AVL, :WS-REQ-QTY
    END-EXEC.

    *> HANDLE ERROR
    EVALUATE TRUE
    WHEN SQLCODE = 0
       PERFORM B100-PROCESS-UNAVAILABLE-ITEM
          THRU B100-EXIT 
    WHEN SQLCODE = 100
       MOVE 'Y'         TO EOF-SWITCH
    WHEN OTHER
       DISPLAY 'SQL ERROR OCCURRED. SQLCODE: ' SQLCODE
       MOVE 'Y'         TO EOF-SWITCH
    END-EVALUATE.
 A200-EXIT. EXIT.
*----------------------------------------------------------------*
* B100-PROCESS-UNAVAILABLE-ITEM: PROCESS UNAVILABLE ITEMS        *
*----------------------------------------------------------------*
 B100-PROCESS-UNAVAILABLE-ITEM.
    DISPLAY ' ORDER   =' OH-ORDR-ID 
            ' WHSE    =' WH-WHSE-ID
            ' ITEM    =' OD-ITEM-ID 
            ' QTY_ORD =' WH-QTY-ORD 
            ' QTY_AVL =' WH-QTY-AVL
            ' REQ_QTY =' WS-REQ-QTY
 B100-EXIT. EXIT.
*----------------------------------------------------------------*
* A300-CLOSE-CURSOR: CLEAN UP SQL RESOURCES                      *
*----------------------------------------------------------------*
 A300-CLOSE-CURSOR.
    EXEC SQL
       CLOSE PEND_ORDR_CUR
    END-EXEC.
 A300-EXIT. EXIT.
*----------------------------------------------------------------*