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.
*-----------------* 

In ILE COBOL (SQLCBLLE), the EXECUTE IMMEDIATE statement is used to prepare and run a dynamic SQL statement in a single step. It is ideal for the SQL statements with below listed conditions:

• Do not return rows (Like SELECT statements)
• Do not contain host variable or placeholders (?) (Like ‘WHERE WHSE_ID = :WH-WHSE’)

It is mainly used for:

• Data definition commands (CREATE, DROP, ALTER)
• Simple data modification commands (UPDATE, DELETE).

PROCESS APOST.
IDENTIFICATION DIVISION.
*------------------------*
PROGRAM-ID.  EXECIMMED.
AUTHOR.      PROGRAMMER.

DATA DIVISION.
*--------------*
WORKING-STORAGE SECTION.
*---------------------------------------------------------------*
* SQL INCLUDE FOR STATUS CHECKING                               *    
*---------------------------------------------------------------*
      EXEC SQL INCLUDE SQLCA END-EXEC.
*---------------------------------------------------------------*
* SQL HOST VARIABLES                                            *
*---------------------------------------------------------------*
    EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01  WS-SQL-TXT   PIC X(500).
    EXEC SQL END DECLARE SECTION END-EXEC.

PROCEDURE DIVISION.
*-------------------*
MAIN-LINE.
    PERFORM A100-EXECUTE-DYNAMIC-SQL.
    STOP RUN.

*----------------------------------------------------------------*
* A100-EXECUTE-DYNAMIC-SQL: Build and Run the SQL statement      *
*----------------------------------------------------------------*
A100-EXECUTE-DYNAMIC-SQL.
    *> == Build the SQL String ==
    STRING 'DELETE FROM ORDHDRPF OH '           DELIMITED BY SIZE
           'WHERE OH.ORD_STS = ''CANCELLED'' '  DELIMITED BY SIZE
           'AND OH.ORD_DAT <= 20241231'         DELIMITED BY SIZE 
    INTO   WS-SQL-TXT.
    *> == Execute Immediate ==
    EXEC SQL
       EXECUTE IMMEDIATE :WS-SQL-TXT
    END-EXEC.

    *> == Handle error using SQLCODE == 
    EVALUATE TRUE
    WHEN SQLCODE = 0
       DISPLAY 'DYNAMIC SQL EXECUTED SUCCESSFULLY.'
       DISPLAY 'ROWS AFFECTED: ' SQLERRD(3)
    WHEN SQLCODE = 100
       DISPLAY 'SQL EXECUTED BUT NO ROWS WERE AFFECTED.'
    WHEN OTHER
       DISPLAY 'SQL EXECUTION FAILED. SQLCODE: ' SQLCODE
    END-EVALUATE.
*----------------------------------------------------------------*

PREPARE & EXECUTE

In ILE COBOL (SQLCBLLE), the combination of PREPARE & EXECUTE statements is used to prepare and run a dynamic SQL statement in two steps.

PREPARE: Converts a character string containing an SQL statement into an executable form. It checks the syntax and validates table and column names.

EXECUTE: Runs the prepared statement. Developers can run the same prepared statement multiple times, optionally passing different values using host variables.

• Named parameter markers (Host Variables)
• Unnamed parameter markers (?)

Named parameter markers (Host Variables):

PROCESS APOST.
IDENTIFICATION DIVISION.
*------------------------*
PROGRAM-ID.  PREPEXEC1.
AUTHOR.      PROGRAMMER.

DATA DIVISION.
*--------------*
WORKING-STORAGE SECTION.
*----------------------------------------------------------------*
* SQL INCLUDE FOR STATUS CHECKING                                *
*----------------------------------------------------------------*
     EXEC SQL INCLUDE SQLCA END-EXEC.
*----------------------------------------------------------------*
* SQL HOST VARIABLES                                             *
*----------------------------------------------------------------*
    EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01  WS-SQL-TXT   PIC X(500).
01  OH-ORD-DAT   PIC 9(08).
    EXEC SQL END DECLARE SECTION END-EXEC.

 PROCEDURE DIVISION.
*-------------------*
MAIN-LINE.
    PERFORM A100-EXECUTE-DYNAMIC-SQL.
    STOP RUN.

*----------------------------------------------------------------*
* A100-EXECUTE-DYNAMIC-SQL: Build and Run the SQL statement      *
*----------------------------------------------------------------*
A100-EXECUTE-DYNAMIC-SQL.
    MOVE 20241231                TO OH-ORD-DAT.    
    *> === Build the SQL String === 
    STRING 'DELETE FROM ORDHDRPF OH '           DELIMITED BY SIZE
            'WHERE OH.ORD_STS = ''CANCELLED'' '  DELIMITED BY SIZE
            'AND OH.ORD_DAT <= '                 DELIMITED BY SIZE 
            FUNCTION TRIM(OH-ORD-DAT)            DELIMITED BY SPACE 
    INTO   WS-SQL-TXT.
    
    *> === Prepare SQL Statement ===
    EXEC SQL
        PREPARE STMT1 FROM :WS-SQL-TXT
    END-EXEC.

    *> === Handle error for PREPARE ===
    IF  SQLCODE NOT = 0 
         DISPLAY "ERROR IN PREPARING STATEMENT: " SQLCODE 
         PERFORM Z999-EXIT 
    END-IF.

    *> === Execute prepared Statement ===
    EXEC SQL
        EXECUTE STMT1
    END-EXEC.

    *> === Handle error for EXECUTE ===
    EVALUATE TRUE
    WHEN SQLCODE = 0
        DISPLAY 'DYNAMIC SQL EXECUTED SUCCESSFULLY.'
        DISPLAY 'ROWS AFFECTED: ' SQLERRD(3)
    WHEN SQLCODE = 100
        DISPLAY 'SQL EXECUTED BUT NO ROWS WERE AFFECTED.'
    WHEN OTHER
        DISPLAY 'SQL EXECUTION FAILED. SQLCODE: ' SQLCODE
    END-EVALUATE.
*----------------------------------------------------------------*

Unnamed parameter markers(?):

 PROCESS APOST.
 IDENTIFICATION DIVISION.
*------------------------*
 PROGRAM-ID.  PREPEXEC2.
 AUTHOR.      PROGRAMMER.

 DATA DIVISION.
*--------------*
 WORKING-STORAGE SECTION.
*----------------------------------------------------------------*
* SQL INCLUDE FOR STATUS CHECKING                                *
*----------------------------------------------------------------*
     EXEC SQL INCLUDE SQLCA END-EXEC.
*----------------------------------------------------------------*
* SQL HOST VARIABLES                                             *
*----------------------------------------------------------------*
     EXEC SQL BEGIN DECLARE SECTION END-EXEC.
 01  WS-SQL-TXT   PIC X(500).
 01  OH-ORD-DAT   PIC 9(08).
     EXEC SQL END DECLARE SECTION END-EXEC.

 PROCEDURE DIVISION.
*-------------------*
 MAIN-LINE.
    PERFORM A100-EXECUTE-DYNAMIC-SQL.
    PERFORM Z999-EXIT.

*----------------------------------------------------------------*
* A100-EXECUTE-DYNAMIC-SQL: Build and Run the SQL statement      *
*----------------------------------------------------------------*
 A100-EXECUTE-DYNAMIC-SQL.
     MOVE 20241231                TO OH-ORD-DAT.    
     *> === Build the SQL String === 
     STRING 'DELETE FROM ORDHDRPF OH '           DELIMITED BY SIZE
            'WHERE OH.ORD_STS = ''CANCELLED'' '  DELIMITED BY SIZE
            'AND OH.ORD_DAT <= ?'                DELIMITED BY SIZE 
     INTO   WS-SQL-TXT.
    
     *> === Prepare SQL Statement ===
     EXEC SQL
        PREPARE STMT1 FROM :WS-SQL-TXT
     END-EXEC.

     *> == Handle error for PREPARE ==
     IF  SQLCODE NOT = 0 
         DISPLAY "ERROR IN PREPARING STATEMENT: " SQLCODE 
         PERFORM Z999-EXIT 
     END-IF.

     *> == Execute prepared Statement ==
     EXEC SQL
        EXECUTE STMT1 USING :OH-ORD-DAT 
     END-EXEC.

     *> == Handle error for EXECUTE ==
     EVALUATE TRUE
     WHEN SQLCODE = 0
        DISPLAY 'DYNAMIC SQL EXECUTED SUCCESSFULLY.'
        DISPLAY 'ROWS AFFECTED: ' SQLERRD(3)
     WHEN SQLCODE = 100
        DISPLAY 'SQL EXECUTED BUT NO ROWS WERE AFFECTED.'
     WHEN OTHER
        DISPLAY 'SQL EXECUTION FAILED. SQLCODE: ' SQLCODE
     END-EVALUATE.
*----------------------------------------------------------------*

Comparisons:

In DB2, a subquery (also known as an inner or nested query) is a SQL statement embedded within another SQL query. It is used to retrieve data based on the result of the inner query. Typically, the inner (subquery) is executed first, and its result is then used by the outer query.

Basic rules of subqueries:

• A subquery must be enclosed in parentheses ().
• A subquery must be full SELECT statement. It cannot be INSERT, UPDATE, DELETE query.
• A subquery must return a result suitable for the condition where it is used.

Subqueries can be grouped in three simple ways:

• On relationship with outer query: Whether the subquery depends on the outer query or not (correlated or non-correlated).
• On type of result they return: Whether they return a single value, multiple values, or multiple rows (table).
• On temporary tables (Derived tables or CTE)

Types of subqueries based on relationships:

• A non-correlated subquery is one that does not depend on the outer query. It can be executed independently because it doesn’t reference any columns from the outer query.
• A correlated subquery is a subquery that depends on the outer query. It uses values from the outer query, so it is executed once for each row of the outer query.

Types of subqueries based on return:

Basic Subqueries (Non-Correlated)

• Scalar Subquery (Single Value): A scalar subquery is a subquery that returns exactly one value (one column and exactly one row).

  -- Find employees who earn more than the company’s average salary
  SELECT  emp_id, first_name, salary
  FROM    employees
  WHERE   salary > (SELECT  AVG(salary) 
                    FROM    employees)
  
  -- Fetching a new column returning single value
  SELECT  e.emp_id,
          e.first_name,  
          e.dept_id,
          e.salary,
          (SELECT MAX(sal) FROM EMP) as MAX_SALARY 
  FROM    employess e
  
  --  Cross-Table Scalar Subquery (employees and department)
  SELECT  e.first_name,
          e.salary, 
          (SELECT  d.department_name 
           FROM    department D 
          WHERE    d.dept_no = e.dept_no) as DEPARTMENT_NAME 
  FROM empolyees e
  
  -- Fetching Departments having headcounts more than reference depart.
  SELECT   D.DEPTNO, D.DNAME, COUNT(*) AS EMPLOYEE_COUNT
  FROM     DEPT D
  GROUP BY D.DEPTNO, D.DNAME
  HAVING COUNT(*) > (
      SELECT COUNT(*) 
      FROM DEPT  
      WHERE DEPTNO = 10)
  

• Row Subquery (Single Row): A row subquery (also called a row constructor or tuple comparison) is a subquery that returns a single row with multiple columns. If subquery returns more than one row, then outer query is not executed.

  -- Fetch maximum and minimum salary range of JOBID of specific DEPTNO
  SELECT MAXSAL, MINSAL
  FROM DEPT
  WHERE (DEPTNO, JOBID) = (
      SELECT DEPTNO, JOBID
      FROM EMP
      WHERE EMPNO = '000010')
  

• Multi-Row Subquery (Multiple rows): A multi-row subquery returns more than one row and is used with operators like IN, ANY, and ALL to compare values from the outer query.

  IN: Checks if a value exists in each list

  ANY: True if the condition is satisfied by any one value

  ALL: True only if the condition is satisfied by all values

  EXISTS: True if the subquery returns any rows (ignores actual data)

  -- Using IN Clause: Departments located in New York
  SELECT  emp_id, last_name 
  FROM    employees
  WHERE   dept_id IN (SELECT  dept_id 
                      FROM    departments 
                      WHERE   location = 'NY')
  
  -- Using ALL Clause: Earns more than EVERYONE in Dept 10
  SELECT  emp_id, salary
  FROM    employees
  WHERE   salary > ALL (SELECT  salary 
                        FROM    employees 
                        WHERE   dept_id = 10)
  
  -- Using EXIST Clause: Getting Customer name in order table
  SELECT   customer_name 
  FROM     Customers c 
  WHERE    EXISTS (SELECT   * 
                   FROM     Orders o 
                   WHERE    o.customer_id =  ‘PO4444’)
  
  SELECT   customer_name 
  FROM     Customers c 
  WHERE    EXISTS (SELECT   1 
                   FROM     Orders o 
                   WHERE    o.customer_id =  ‘PO4444’
                   AND      o.Status = ‘PENDING’)
  

Correlated Subqueries

These reference a column from the outer table (outer Column). DB2 evaluates the subquery once for every single row processed by the outer query.

• Correlated EXISTS

  -- Find departments with assigned employees.
  SELECT  d.dept_id, d.dept_name
  FROM    departments d
  WHERE   EXISTS(SELECT  1 
                 FROM    employees e 
                 WHERE   e.dept_id = d.dept_id)
  
  -- Find all customers who have placed at least one order
  SELECT  customer_id, customer_name 
  FROM    Customers c 
  WHERE   EXISTS  (SELECT  1 
                   FROM    Orders o 
                   WHERE   o.customer_id = c.customer_id);
  
  -- Find all customers who have never placed an order:
  SELECT   customer_id, customer_name 
  FROM     Customers c 
  WHERE    NOT EXISTS (SELECT  1 
                       FROM    Orders o 
                       WHERE   o.customer_id = c.customer_id)
          

• Correlated subqueries for data modifications

  -- Updating with correlated subqueries
  UPDATE accounts a
  SET status = 'SUSPENDED'
  WHERE EXISTS (
      SELECT 1 
      FROM payments p 
      WHERE p.account_id = a.account_id 
        AND p.due_date < CURRENT DATE 
        AND p.status = 'UNPAID')
  
  -- Deleting with correlated subqueries
  DELETE FROM Customers c 
  WHERE EXISTS (SELECT  1 
                FROM    Blacklist b 
                WHERE   b.email = c.email)
  

Derived Tables (Subqueries used as table for join)

A derived table is a temporary result set created by placing a subquery directly within the FROM or JOIN clause of an outer query. It is treated exactly like a physical database table during query execution, existing only for the duration of that single

-- Derived Table in the ‘FROM’ Clause
-- Get list of daily sales where total revenue > 5000
SELECT  daily_sales.sale_date, daily_sales.total_revenue 
FROM   (SELECT   sale_date, SUM(amount) AS total_revenue 
        FROM     transactions 
        GROUP BY sale_date) AS daily_sales 
WHERE   daily_sales.total_revenue > 5000;

-- Derived Table in the ‘JOIN’ Clause
-- Get list of customers having orders more than 10
SELECT  c.customer_name, o_stats.total_orders 
FROM    customers AS c 
INNER   JOIN (SELECT customer_id, COUNT(*) AS total_orders 
        FROM  orders 
        GROUP BY customer_id) AS o_stats 
ON c.id = o_stats.customer_id 
WHERE o_stats.total_orders > 10;

CTE Subqueries

A CTE (Common Table Expression) is a temporary, named result set that programmers define at the very beginning of a SQL query. It acts exactly like a virtual table or view, but it exists only for the duration of that single execution.

WITH regional_sales AS 
( -- CTE - subquery 
SELECT  region, SUM(amount) AS total_revenue 
FROM    orders 
GROUP BY region ) 
-- This is the main outer query referencing the CTE 
SELECT  region, total_revenue 
FROM    regional_sales 
WHERE   total_revenue > 100000;

Use Subqueries in SQLCBLLE programs

All types of subqueries can be used in SQLCBLLE programs in static or dynamic cursor statement.

 IDENTIFICATION DIVISION.
*------------------------*
 PROGRAM-ID. SQLSUBQ.

 DATA DIVISION.
*--------------*
 WORKING-STORAGE SECTION.
* --------------------------------------------------------------* 
* SQL Communications Area & Host Variables                      *
* --------------------------------------------------------------*
     EXEC SQL INCLUDE SQLCA END-EXEC.
     EXEC SQL BEGIN DECLARE SECTION END-EXEC.
 01  WS-HOST-VARS.
     05  WS-EMP-ID       PIC S9(9) COMP-4.
     05  WS-EMP-NAME     PIC X(30).
     05  WS-EMP-SALARY   PIC S9(7)V99 COMP-3.
     05  WS-LOCATION     PIC X(20) VALUE 'NEW YORK'.
     EXEC SQL END DECLARE SECTION END-EXEC.

 PROCEDURE DIVISION.
* ------------------*
 MAIN-PROCESS.
     *> DECLARE Cursor with a Nested Subquery
     EXEC SQL
        DECLARE  EMP_CURS CURSOR FOR
        SELECT   EMP_ID, EMP_NAME, SALARY
        FROM     EMPLOYEE
        WHERE    DEPT_ID IN (
                SELECT   DEPT_ID 
                FROM     DEPARTMENTS 
                WHERE    LOC_NAME = :WS-LOCATION)
     END-EXEC.

     *> OPEN Cursor
     EXEC SQL
        OPEN EMP_CURS
     END-EXEC.

     IF SQLCODE NOT = 0
        DISPLAY "ERROR OPENING CURSOR: " SQLCODE
        PERFORM CLEAN-UP
     END-IF.

     *> FETCH Loop
     PERFORM FETCH-ROW UNTIL SQLCODE = 100.
     PERFORM CLEAN-UP.

 FETCH-ROW.
* ---------*
     EXEC SQL
        FETCH EMP_CURS 
        INTO :WS-EMP-ID, :WS-EMP-NAME, :WS-EMP-SALARY
     END-EXEC.

     IF SQLCODE = 0
        DISPLAY "ID: " WS-EMP-ID 
                " Name: " WS-EMP-NAME 
                " Salary: " WS-EMP-SALARY
     ELSE
         IF SQLCODE NOT = 100
            DISPLAY "FETCH ERROR occurred: " SQLCODE
         END-IF
     END-IF.

 CLEAN-UP.
* --------*
    *> CLOSE Cursor
     EXEC SQL
        CLOSE EMP_CURS
     END-EXEC.
     GOBACK.
* --------------------------------------------------------------*

Introduction to CTEs

A Common Table Expression (CTE) is a temporary result set that exists only within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.

CTEs improve query

• Readability
• Simplify complex nested subqueries
• Reusability
• Allow for recursive processing within DB2 databases

Unlike temporary tables, CTEs require no manual deletion or storage management because DB2 manages their lifecycle automatically.

Basic Syntax

A CTE is defined using the WITH keyword, followed by the expression name, an optional column list, and the defining query.

WITH cte_name (column1, column2, ...) AS (
    -- The subquery that defines the CTE
    SELECT source_col1, source_col2, ...
    FROM source_table
    WHERE condition
)
-- The primary outer query that consumes the CTE
SELECT column1, column2
FROM cte_name
WHERE column1 > Constant

Types of CTEs

There are 5 types of CTEs:

• Single CTE

• Multiple CTEs

• Nested CTEs

  • Type1 – Referencing previously defined CTE

  • Type2 – Truly nested CTE – using two ‘WITH’ clause

• Recursive CTEs

  • Type1 – Termination condition is embedded naturally within the join

  • Type2 – Termination condition is embedded via an explicit boundary condition

• Composite CTEs

Single CTE

A single CTE defines exactly one temporary result set that programmers can reference once or multiple times within the primary outer query block. This isolates specific business logic, such as pre-aggregating values, before executing a final join operation.

Syntax for Single CTE

-- The subquery that defines the CTE
WITH cte_name (column1, column2, ...) AS (
    SELECT source_col1, source_col2, ...
    FROM source_table
    WHERE condition
)
-- The primary outer query that consumes the CTE
SELECT column1, column2
FROM cte_name WHERE column1 > Constant

Example for Single CTE

-- The subquery that defines the CTE
WITH Dept_Avg AS (
    SELECT DEPTID, AVG(SALARY) AS AVERAGE_SAL
    FROM EMPLOYEE
    GROUP BY DEPTID
)
-- The primary outer query that consumes the CTE joining another table
SELECT E.EMPNAME, E.SALARY, D.AVERAGE_SAL
FROM EMPLOYEE E
JOIN Dept_Avg D ON E.DEPTID = D.DEPTID
WHERE E.SALARY > D.AVERAGE_SAL

Multiple CTEs

Programmers can specify multiple distinct Common Table Expressions within a single query structure by separating each individual declaration with a comma under a single WITH keyword. Subsequent expressions can seamlessly reference previously defined expressions within the same block, providing a structured pipeline for complex data transformations.

Syntax for Multiple CTEs

-- First CTE
WITH cte_name1 AS (
    SELECT column1, column2 
    FROM table1
    WHERE condition1
),
-- Second CTE
cte_name2 AS (
    SELECT column3, column4 
    FROM table2
    WHERE condition2
)
-- Main Query (No trailing comma before this statement)
SELECT column5 
FROM cte_name1
INNER JOIN cte_name2 ON condition3

Example for Multiple CTEs

-- First CTE
WITH High_Earners AS (
    SELECT EMPID, EMPNAME, DEPTID, SALARY
    FROM   EMPLOYEE
    WHERE  SALARY > 400000
),
-- Second CTE
Dept_Info AS (
    SELECT DEPTID, DEPTNAME
    FROM   DEPARTMENT
    WHERE  LOCATION = 'NEW YORK'
)
-- Joining CTEs for result
SELECT H.EMPNAME, H.SALARY, D.DEPTNAME
FROM   High_Earners H
JOIN   Dept_Info D 
       ON H.DEPTID = D.DEPTID

Nested CTEs

A nested Common Table Expression (CTE)—often referred to as sequential or cascading CTEs—is a SQL technique where one CTE references a previously defined CTE within the same WITH clause. This creates a logical pipeline or dependency chain, allowing you to break down complex multi-step data transformations into isolated, readable layers without resorting to heavily nested subqueries.

There are two types of nested CTEs:

• Type1 – Referencing previously defined CTE

• Type2 – Truly nested CTE – using two ‘WITH’ clause

Syntax for Type1 – Nested CTEs

-- First CTE: Extract or filter initial data
WITH First_CTE AS (
    SELECT column1, column2 
    FROM source_table
),
-- Second CTE references First_CTE to aggregate or transform further
Second_CTE AS (
    SELECT column1, COUNT(column2) AS total_count
    FROM First_CTE -- Referencing takes place here
    GROUP BY column1
)
-- Main Query: Consume the final output
SELECT * 
FROM Second_CTE
WHERE total_count > constant

Example for Type1 – Nested CTEs

-- Calculate total sales for every department
WITH Department_Sales AS (
    SELECT department, SUM(price) AS total_sales
    FROM orders
    GROUP BY department
),
-- Nest the data to filter only high-performing departments
High_Performers AS (
    SELECT department, total_sales
    FROM Department_Sales -- Referencing the first CTE
    WHERE total_sales > 50000
)
-- Pull final performance metrics
SELECT AVG(total_sales) AS overall_high_performer_average
FROM High_Performers

Syntax for Type2 – Nested CTEs

-- Outer CTE
WITH outer_cte AS (
-- Inner CTE 
    WITH inner_cte AS (
        SELECT column1 FROM original_table
    )
    SELECT column1 FROM inner_cte -- Inner CTE is only scoped to this block
)
-- Main query
SELECT * 
FROM outer_cte

Example for Type2 – Nested CTEs

WITH high_value_months AS (
    -- Enclosed nesting limits the scope of monthly_aggregates
    WITH monthly_aggregates AS (
        SELECT order_month, SUM(order_total) AS monthly_total
        FROM store_orders
        GROUP BY order_month
    )
    SELECT order_month, monthly_total
    FROM monthly_aggregates
    WHERE monthly_total > 50000
)
SELECT * 
FROM high_value_months

Recursive CTEs

A recursive CTE provides the specialized mechanism required to navigate hierarchical data structures by referencing itself iteratively. It is divided structurally into three distinct, interconnected components:

• Anchor member – The anchor member functions as the baseline query that establishes the starting result set of the hierarchy.

• Recursive member – The recursive member follows, referencing the CTE name directly and joining it back to the base table to fetch subsequent levels of data.

• Termination condition – The termination condition is embedded naturally within the join or via an explicit boundary condition, preventing infinite loops and concluding execution when no further rows are produced.

There are two types of recursive CTEs:

• Type1 – Termination condition is embedded naturally within the join

• Type2 – Termination condition is embedded via an explicit boundary condition

Syntax for Type 1 & 2 – Recursive CTEs

WITH RECURSIVE cte_name (column1, column2, ...) AS (
    -- 1. Anchor Member (Base query)
    SELECT initial_value1, initial_value2, ...
    FROM table_name
    WHERE condition

    UNION ALL

    -- 2. Recursive Member (References cte_name)
    SELECT expression1, expression2, ...
    FROM table_name
    INNER JOIN cte_name 
          ON table_name.column = cte_name.column

    -- 3. Termination condition 
    WHERE termination_condition
)
-- 4. Outer Query (Consumes the CTE results)
SELECT * FROM cte_name

Example for Type 1 – Recursive CTEs

-- Prepare hierarchical structure of an organization.
WITH Org_Structure (EMPID, EMPNAME, MANAGERID, ORG_LEVEL) AS (
    -- Anchor Member
    SELECT EMPID, EMPNAME, MANAGERID, 1
    FROM   EMPLOYEE
    WHERE  MANAGERID IS NULL
    UNION  ALL
    -- Recursive Member
    SELECT E.EMPID, E.EMPNAME, E.MANAGERID, O.ORG_LEVEL + 1
    FROM   EMPLOYEE E
    JOIN   Org_Structure O
    -- Termination Condition is embedded naturally within the join   
    ON   E.MANAGERID = O.EMPID
)
SELECT EMPID, EMPNAME, MANAGERID, ORG_LEVEL
FROM   Org_Structure

Example for Type 2 – Recursive CTEs

-- Preparing list of territory 
WITH      recursive territory (territory) AS ( 
SELECT    1                                     
FROM      SYSIBM.sysdummy1                     
UNION     ALL                                  
SELECT    territory + 1                        
FROM      territory                            
WHERE     territory < 12)  -- Explicit termination condition                    
SELECT    * 
FROM      territory                        

Composite CTEs

A composite CTE is a combination of different types of CTEs, including single, multiple, recursive, and nested CTEs, used together in various ways.

Examples of composite CTE combinations include:

• Single and Recursive CTEs

• Multiple and Recursive CTEs

• Single and Nested CTEs

• Multiple and Nested CTEs

• Single, Nested, and Recursive CTEs.

• Multiple, Nested, and Recursive CTEs.

Example for Composite CTEs

-- 1st recursive CTE
WITH recursive lossyear (loss_year, paid_losses) AS
(
       SELECT 2025, 0.00
       FROM   SYSIBM.sysdummy1
       UNION  ALL
       SELECT loss_year - 1, 0.00
       FROM   lossyear
       WHERE  loss_year > 2016),
-- 2nd recursive CTE
paidyear (paid_year, paid_losses) AS
(
       SELECT 2025, 0.00
       FROM   SYSIBM.sysdummy1
       UNION ALL
       SELECT paid_year - 1, 0.00
       FROM   paidyear
       WHERE  paid_year > 2016
),
-- 3rd CTE
classcode AS 
  (SELECT * FROM (VALUES
           ('91000'),('91001')) AS class_code(classcode)
)
-- Main outer query
SELECT classcode, rptyear, lossyear, sum(lossamt)
From (
-- 4th recursive CTE – combination of nested CTEs
-- Anchor member  
SELECT classcode, rptyear, lossyear, lossyear.paid_losses
FROM   CROSS JOIN class_code –- Referencing previous CTE 
       CROSS JOIN lossyear –- Referencing previous recursive CTE
       CROSS JOIN paidyear –- Referencing previous recursive CTE
       UNION ALL
       -- Recursive member 
       (SELECT Int(pol_active_date/10000) + 1900  AS RPTYEAR,
               Int(pol_loss_date/10000) + 1900    AS LOSSYEAR,
               pol_group_line                     AS GRPLNE,
               pol_user_line                      AS USRLNE,
               pol_class_code                     AS CLASSCODE,
               pol_trans_code                     AS TRANSCODE,
               Substr(pol_nbr, 25, 2)             AS POLTYPE,
               pol_loss_amount                    AS LOSSAMT
        FROM   POLHSTPF)  
WHERE  grplne = '5000' -- Explicit Termination all ‘WHERE’ conditions 
       AND usrlne = '400' -- 
       AND poltype = '99'
       AND classcode IN ('91000', '91001')
       AND transcode IN ('101', '102', '103')
       AND lossyear > 2015
       AND rptyear <= 2025) as combined_data
GROUP  BY classcode, rptyear, lossyear
ORDER  BY classcode, rptyear desc, lossyear desc

Non-Select Operations with CTEs

CTEs are not restricted exclusively to standard SELECT data retrieval queries, developers can also attach them directly to data modification language operations such as INSERT, UPDATE, or DELETE statements. This pattern allows developers to isolate and filter complex target criteria within the temporary block before modifying the underlying physical tables. It is also applicable to all types of CTEs – Single, multiple & recursive CTEs.

Example for use of Non-Select Operations with CTEs

WITH Expired_Contracts AS (
     SELECT CONTRACT_ID
     FROM   VENDOR_CONTRACTS
     WHERE  END_DATE < CURRENT DATE
     AND    STATUS = 'INACTIVE'
)
-- Delete operation
DELETE FROM CONTRACT_MILESTONES
WHERE  CONTRACT_ID IN (SELECT CONTRACT_ID 
                       FROM   Expired_Contracts)

CTEs in SQLCBLLE programs

In SQLCBLLE, multi-row result sets generated via CTE queries must be bound inside embedded SQL Cursors using EXEC SQL and END-EXEC delimiters. Host variables map program data fields to the cursor structure.

Example: Single CTE in SQL Statement

 IDENTIFICATION DIVISION.
*------------------------*
 PROGRAM-ID. CBLCTES.
 DATA DIVISION.
*--------------*
 WORKING-STORAGE SECTION.
     EXEC SQL INCLUDE SQLCA END-EXEC.
     EXEC SQL BEGIN DECLARE END-EXEC.
 01  WS-VARS.
     05  H-EMP-ID    PIC 9(9).
     05  H-SALARY    PIC 9(7)V99 COMP-3.
     EXEC SQL END DECLARE END-EXEC.

 PROCEDURE DIVISION.
*-------------------*
 000-MAIN-LINE.
     *> === declare single CTE cursor ===
     EXEC SQL
        DECLARE C01 CURSOR FOR
        WITH    DEPT_AVG AS (
        SELECT  DEPT_NO, AVG(SALARY) AS ASAL 
        FROM    EMPLOYEE GROUP BY DEPT_NO
        )
        SELECT  E.EMP_ID, E.SALARY FROM EMPLOYEE E
        INNER   JOIN DEPT_AVG D 
                ON E.DEPT_NO = D.DEPT_NO
        WHERE E.SALARY > D.ASAL
     END-EXEC.

     *> === open single CTE cursor ===
     EXEC SQL OPEN C01 END-EXEC.

     *> === apply fetch loop for single CTE cursor ===
     PERFORM UNTIL SQLCODE NOT = 0
        EXEC SQL 
           FETCH C01 
           INTO :H-EMP-ID, :H-SALARY 
        END-EXEC
        DISPLAY “Empolyee ID: ” H-EMP-ID
        DISPLAY “Salary: “ H-SALARY 
     END-PERFORM. 

     *> === close single CTE cursor ===
     EXEC SQL CLOSE C01 END-EXEC.
     GOBACK.

Example: Multiple CTEs in SQL Statement

 IDENTIFICATION DIVISION.
*------------------------*
 PROGRAM-ID. CBLCTEM.
 DATA DIVISION.
*--------------*
 WORKING-STORAGE SECTION.
     EXEC SQL INCLUDE SQLCA END-EXEC.
     EXEC SQL BEGIN DECLARE END-EXEC.
 01  WS-VARS.
     05  H-REG-ID    PIC 9(4).
     05  H-REVENUE   PIC S9(9)V99 COMP-3.
     EXEC SQL END DECLARE END-EXEC.

 PROCEDURE DIVISION.
*-------------------*
 000-MAIN-LINE.
     *> === declare multiple CTEs cursor ===
     EXEC SQL
        DECLARE C02 CURSOR FOR
        WITH    R_TOTALS AS (
        SELECT  REGION_ID, SUM(REVENUE) AS R_REV 
        FROM    SALES_LOG GROUP BY REGION_ID
        ),
        G_BENCH AS (
        SELECT  AVG(R_REV) AS G_REV 
        FROM    R_TOTALS
        )
        SELECT  R.REGION_ID, R.R_REV 
        FROM    R_TOTALS R
        CROSS   JOIN G_BENCH G 
        WHERE   R.R_REV > G.G_REV
     END-EXEC.
     
     *> === open multiple CTEs cursor ===
     EXEC SQL OPEN C02 END-EXEC.

     *> === apply fetch loop for multiple CTEs cursor ===
     PERFORM UNTIL SQLCODE NOT = 0
        EXEC SQL 
           FETCH C02 
           INTO :H-REG-ID, :H-REVENUE 
        END-EXEC
        DISPLAY “Region ID: ” H-REG-ID
        DISPLAY “Revenue: ” H-REVENUE 
     END-PERFORM.

     *> === close multiple CTEs cursor ===
     EXEC SQL CLOSE C02 END-EXEC.
     GOBACK.

Example: Nested CTEs in SQL Statement

 IDENTIFICATION DIVISION.
*------------------------*
 PROGRAM-ID. CBLCTEM.
 DATA DIVISION.
*--------------*
 WORKING-STORAGE SECTION.
     EXEC SQL INCLUDE SQLCA END-EXEC.
     EXEC SQL BEGIN DECLARE END-EXEC.
 01  WS-VARS.
     05  H-REG-ID    PIC 9(4).
     05  H-REVENUE   PIC S9(9)V99 COMP-3.
     EXEC SQL END DECLARE END-EXEC.

 PROCEDURE DIVISION.
*-------------------*
 000-MAIN-LINE.
     *> === declare nested CTEs cursor ===
     EXEC SQL
        DECLARE C03 CURSOR FOR
        WITH    R_TOTALS AS (
        SELECT  REGION_ID, SUM(REVENUE) AS R_REV 
        FROM    SALES_LOG GROUP BY REGION_ID
        ),
        G_BENCH AS (
        SELECT  AVG(R_REV) AS G_REV 
        FROM    R_TOTALS –- Referencing previous CTE 
        )
        SELECT  R.REGION_ID, R.R_REV 
        FROM    R_TOTALS R  
        CROSS   JOIN G_BENCH G 
        WHERE   R.R_REV > G.G_REV
     END-EXEC.
     
     *> === open nested CTEs cursor ===
     EXEC SQL OPEN C03 END-EXEC.

     *> === apply fetch loop for nested CTEs cursor ===
     PERFORM UNTIL SQLCODE NOT = 0
        EXEC SQL 
           FETCH C03 
           INTO :H-REG-ID, :H-REVENUE 
        END-EXEC
        DISPLAY “Region ID: ” H-REG-ID
        DISPLAY “Revenue: ” H-REVENUE 
     END-PERFORM.

     *> === close nested CTEs cursor ===
     EXEC SQL CLOSE C03 END-EXEC.
     GOBACK.

Example: Recursive CTEs in SQL Statement

 IDENTIFICATION DIVISION.
*------------------------*
 PROGRAM-ID. CBLCTER.
 DATA DIVISION.
*--------------*
 WORKING-STORAGE SECTION.
     EXEC SQL INCLUDE SQLCA END-EXEC.
     EXEC SQL BEGIN DECLARE END-EXEC.
 01  WS-VARS.
     05  H-PART-ID   PIC 9(9).
     05  H-PARENT-ID PIC 9(9).
     05  H-LEVEL     PIC 9(4).
     EXEC SQL END DECLARE END-EXEC.

 PROCEDURE DIVISION.
*-------------------*
 000-MAIN-LINE.
     *> === declare recursive CTEs cursor ===
     EXEC SQL
        DECLARE C04 CURSOR FOR
        WITH BOM_EX (PART_ID, PARENT_ID, LVL) AS (
        SELECT  PART_ID, PARENT_ID, 1 
        FROM    BOM
        WHERE   PARENT_ID IS NULL
        UNION   ALL
        SELECT  C.PART_ID, C.PARENT_ID, P.LVL + 1 
        FROM    BOM C
        JOIN    BOM_EX P 
                ON C.PARENT_ID = P.PART_ID
        WHERE   P.LVL < 50)
        SELECT  PART_ID, PARENT_ID, LVL 
        FROM    BOM_EX
     END-EXEC.

     *> === open recursive CTE cursor ===
     EXEC SQL OPEN C04 END-EXEC.

     *> === apply fetch loop for recursive CTEs cursor ===
     PERFORM UNTIL SQLCODE NOT = 0
        EXEC SQL 
           FETCH C04 
           INTO :H-PART-ID, :H-PARENT-ID, :H-LEVEL 
        END-EXEC
        DISPLAY “Part ID: ” H-PART-ID
        DISPLAY “Parent ID: ” H-PARENT-ID
        DISPLAY “Parent ID: ” H-LEVEL
     END-PERFORM.

     *> === close recursive CTEs cursor ===
     EXEC SQL CLOSE C04 END-EXEC.
     GOBACK.

A cursor is a database mechanism allowing programmers to retrieve, navigate, and manipulate data row-by-row from a multi-row result set. It serves as a pointer to specific records within an SQL query result. Cursors can be categorized into:

• Data modification-based cursors.

• Data modification statement-based cursors.

• Position modification statement-based cursors.

• Modified data reflection-based cursors.

• Processing stability-based cursors.

• Mixed feature cursors.

• Data modification-based cursors:

  Cursors are categorized as read-only, or updateable based on data modification constraints. Read-only cursors are used to fetch fields of cursor and never meant to update or delete any record due to which records are not locked. Updateable cursors lock fetched rows to allow modification via positioned statements. Based on data modification-based cursors are:

  • Read Only Cursors
  • Updatable Cursors

  *> Read-Only Cursor Example – It contains ‘FOR FETCH ONLY’ clause 
  EXEC SQL 
     DECLARE CUR_READ CURSOR FOR 
     SELECT  DEPTNO 
     FROM    DEPARTMENT 
     FOR     FETCH ONLY
  END-EXEC.
  
  *> Updatable Cursor Example – It contains ‘FOR UPDATE OF’ clause
  EXEC SQL 
     DECLARE CUR_UPD CURSOR FOR 
     SELECT  SALARY 
     FROM    EMPLOYEE
     WHERE   DESIGNATION = “FRESHER”  
     FOR     UPDATE OF SALARY
  END-EXEC.
  

• Data modification statement-based cursors:

  Programmers can use updatable cursors to modify or delete rows in a database table. This is known as a positioned update or positioned delete, because the operation is performed on the row currently selected by the cursor. Based on the type of data modification, there are two types of cursors:

  • Cursors for UPDATE – Used to update the current row selected by the cursor.
  • Cursors for DELETE – Used to delete the current row selected by the cursor.

  *> For modification - It contains ‘WHERE CURRENT OF CURSOR_Name’ 
  *> clause
  EXEC SQL 
     UPDATE EMPLOYEE 
     SET SALARY = 50000 
     WHERE CURRENT OF CUR_UPD
  END-EXEC.
  
  *> For modification - It contains ‘WHERE CURRENT OF CURSOR_Name’ 
  *> clause
  EXEC SQL 
     DELETE FROM EMPLOYEE  
     WHERE CURRENT OF CUR_UPD
  END-EXEC.
  

• Position based cursors

  Cursors can be grouped based on how rows are retrieved and whether the same row can be fetched more than once. Based on position-based cursors:

  • Serial/non-scrollable Cursors.
  • Scrollable Cursors.

  Serial Cursors

  A serial cursor is a cursor created without the SCROLL keyword. Rows are fetched one at a time in order. Each row can be fetched only once while the cursor is open. When the cursor is opened, it starts before the first row. Each FETCH moves the cursor to the next row and makes that row the current row. If an INTO clause is used, the row data is copied into host variables. This continues until there are no more rows (SQLCODE = 100). Once the end of the result set is reached, programmers cannot go back to previous rows. Programmers must close and reopen the cursor to read the rows again. A serial cursor only moves forward.

  Scrollable Cursors

  A scrollable cursor allows programmers to move both forward and backward through the result set. Rows can be fetched multiple times. When opened, the cursor starts before the first row. Each FETCH can move the cursor to a specific position based on the option used. The selected row becomes the current row. If an INTO clause is used, the row data is copied into host variables. Reaching the beginning or end of the result set does not require closing the cursor. Programmers can continue moving around the result set using different fetch options.

  Fetch Options:

  Option	Description
  NEXT	Move to the next row (default).
  PRIOR	Move to the previous row.
  FIRST	Move to the first row.
  LAST	Move to the last row.
  BEFORE	Move before the first row.
  AFTER	Move after the last row.
  CURRENT	Stay on the current row.
  RELATIVE n	Move relative to the current row.

  Examples of RELATIVE n

  • RELATIVE -1 → Move to the previous row.
  • RELATIVE +3 → Move three rows forward from the current row.
  • RELATIVE 0 → Stay on the current row.

  -- ========== ====== ===
  -- === Serial Cursor ===
  -- ========== ====== ===
  EXEC SQL
     DECLARE  SERIAL_CUR CURSOR FOR
      SELECT  EMPNO, EMPNAME
        FROM  EMPLOYEE
  END-EXEC.
  -- === Open Serial Cursor ===
  EXEC SQL 
     OPEN  SERIAL_CUR
  END-EXEC.
  -- === Fetch (FETCH/FETCH NEXT)Serial Cursor ===
  EXEC SQL
     FETCH  FROM SERIAL_CUR
     INTO   :WS-EMPNO, :WS-EMPNAME
  END-EXEC.
  EXEC SQL
     FETCH  NEXT FROM SERIAL_CUR
     INTO   :WS-EMPNO, :WS-EMPNAME
  END-EXEC.
  -- === Close Serial Cursor ===
  EXEC SQL 
     CLOSE  SERIAL_CUR 
  END-EXEC.
  -- ============== ====== ===
  -- === Scrollable Cursor ===
  -- ============== ====== ===
  EXEC SQL
     DECLARE  SCROLL_CUR SCROLL CURSOR FOR
      SELECT  EMPNO, EMPNAME, SALARY
        FROM  EMPLOYEE
  END-EXEC.
  -- Open Scrollable Cursor
  EXEC SQL 
     OPEN  SCROLL_CUR
  END-EXEC.
  -- Fetch(FIRST/LAST/NEXT/PRIOR/RELATIVE/CURRENT) Scrollable Cursor
  EXEC SQL
     FETCH  FIRST FROM SCROLL_CUR
     INTO   :WS-EMPNO, :WS-EMPNAME, :WS-SALARY
  END-EXEC.
  EXEC SQL
     FETCH  LAST FROM SCROLL_CUR 
     INTO   :WS-EMPNO, :WS-EMPNAME, :WS-SALARY
  END-EXEC.
  EXEC SQL
     FETCH  NEXT FROM SCROLL_CUR 
     INTO   :WS-EMPNO :WS-EMPNAME, :WS-SALARY
  END-EXEC.
  EXEC SQL
     FETCH  PRIOR FROM SCROLL_CUR 
     INTO   :WS-EMPNO :WS-EMPNAME, :WS-SALARY
  END-EXEC.
  EXEC SQL
     FETCH  RELATIVE +2 FROM SCROLL_CUR 
     INTO   :WS-EMPNO, :WS-EMPNAME, :WS-SALARY
  END-EXEC.
  EXEC SQL
     FETCH  CURRENT FROM SCROLL_CUR 
     INTO   :WS-EMPNO, :WS-EMPNAME, :WS-SALARY
  END-EXEC.
  -- Close Serial Cursor
  EXEC SQL 
     CLOSE SCROLL_CUR 
  END-EXEC.
  -- ============== ====== ===
  

• Modified data reflection-based cursors:

  This property defines whether a scrollable cursor reflects changes made to the underlying data by other transactions after the cursor is opened. Based on modified data reflection cursors:

  • Insensitive Scrollable Cursors
  • Sensitive Scrollable Cursors

  Insensitive Scrollable Cursors

  An insensitive scrollable cursor works with a snapshot of the result set taken when the cursor is opened. Changes made by other transactions like INSERT, UPDATE & DELETE are not visible. Row values, membership, and ordering remain unchanged throughout the life of the cursor. The result set is typically stored in temporary storage.

  Sensitive Scrollable Cursors

  A sensitive scrollable cursor can detect changes made to the underlying table while the cursor is open. Inserts, updates and deletes made by other transactions may be visible. Depending on the cursor type, changes in row values, row membership, and row order can be reflected. Useful when an application needs to work with the most current data.

  -- =============== ========== ====== ===
  -- === Insensitive Scrollable Cursor ===
  -- =============== ========== ====== ===
  EXEC SQL
     DECLARE  C01 INSENSITIVE SCROLL CURSOR FOR
      SELECT  EMPNO, NAME, SALARY
        FROM  EMPLOYEE
  END-EXEC.
  -- === Open Insensitive Scrollable Cursor ===
  EXEC SQL 
     OPEN  C01
  END-EXEC.
  -- === Fetch Insensitive Scrollable Cursor ===
  EXEC SQL
     FETCH  FRIST FROM C01
     INTO   :WS-EMPNO, :WS-EMPNAME, :WS-SALARY
  END-EXEC.
  EXEC SQL
     FETCH  LAST FROM C01 
     INTO   :WS-EMPNO :WS-EMPNAME
  END-EXEC.
  EXEC SQL
     FETCH  NEXT FROM C01 
     INTO   :WS_EMPNO, :WS_EMPNAME, :WS-SALARY
  END-EXEC.
  EXEC SQL
     FETCH  PRIOR FROM C01 
     INTO   :WS_EMPNO, :WS_EMPNAME, :WS-SALARY
  END-EXEC.
  -- === Close Insensitive Scrollable Cursor ===
  EXEC SQL 
     CLOSE  C01
  END-EXEC.
  -- ============= ========== ====== ===
  -- === Sensitive Scrollable Cursor ===
  -- ============= ========== ====== ===
  EXEC SQL
     DECLARE  C02 SENSITIVE SCROLL CURSOR FOR
      SELECT  EMPNO, EMPNAME, SALARY
        FROM  EMPLOYEE
  END-EXEC.
  -- Open Sensitive Scrollable Cursor
  EXEC SQL 
     OPEN  C02
  END-EXEC.
  -- Fetch Scrollable Cursor
  EXEC SQL
     FETCH  FRIST FROM C02
     INTO   :WS-EMPNO, :WS-EMPNAME, :WS-SALARY
  END-EXEC.
  EXEC SQL
     FETCH  LAST FROM C02 
     INTO   :WS-EMPNO :WS-EMPNAME
  END-EXEC.
  EXEC SQL
     FETCH  NEXT FROM C02 
     INTO   :WS_EMPNO, :WS_EMPNAME, :WS-SALARY
  END-EXEC.
  EXEC SQL
     FETCH  PRIOR FROM C02 
     INTO   :WS_EMPNO, :WS_EMPNAME, :WS-SALARY
  END-EXEC.
  -- Close Serial Cursor
  EXEC SQL 
     CLOSE C02
  END-EXEC.
  -- ======== ==========
  

• Processing stability-based cursors:

  Standard database behavior automatically closes all active cursors during a transaction commit. By defining a cursor WITH HOLD, the database preserves the tracking context and pointer location across transaction validation checkpoints, preventing expensive repositioning cycles in heavy batch loops. Based on processing stability-based cursors:

  • Cursor Without Hold – A WITHOUT HOLD cursor automatically closes whenever a COMMIT or ROLLBACK statement executes. It is ideal for small, atomic transactions where programmers want to lock and release resources quickly.

    Note: WITHOUT HOLD clause is optional, even if it is not used, it is considered as without hold.

     IDENTIFICATION DIVISION.
    *------------------------*
     PROGRAM-ID.   CURSNOHLD.
    *=== CURSORS WITHOUT HOLD ===
     DATA DIVISION.
     WORKING-STORAGE SECTION.
    *------------------------*
         EXEC SQL INCLUDE SQLCA END-EXEC.
         EXEC SQL BEGIN DECLARE END-EXEC.
     01  WS-EMP-REC.
         05  WS-EMP-ID    PIC X(6).
         05  WS-EMP-NAME  PIC X(20).
         EXEC SQL END DECLARE END-EXEC.
    
     PROCEDURE DIVISION.
    *-------------------*
     MAIN-PARA.
         *> DECLARE THE CURSOR (Default is without hold)
         EXEC SQL
            DECLARE C1_NOHOLD CURSOR FOR
             SELECT EMPNO, LASTNAME 
               FROM EMPLOYEE
              WHERE DEPTNO = 'A00'
         END-EXEC.
      
         *> OPEN THE CURSOR
         EXEC SQL 
        OPEN C1_NOHOLD 
         END-EXEC.
         IF SQLCODE NOT = 0
            DISPLAY 'ERROR IN OPENING CURSOR: ' SQLCODE
            STOP RUN
         END-IF.
    
         *> FETCH AND PROCESS LOOP
         PERFORM UNTIL SQLCODE = 100
        EXEC SQL
               FETCH C1_NOHOLD 
       INTO :WS-EMP-ID, :WS-EMP-NAME
            END-EXEC.
            IF SQLCODE = 0
               DISPLAY 'EMP ID: ' WS-EMP-ID 
              ' NAME: '  WS-EMP-NAME
      EXEC SQL
         UPDATE EMPOLYEE A 
         SET A.SALARY = A.SALARY + A.SALARY * 0.5
         WHERE EMPNO = :WS-EMP-ID
      END-EXEC
      *> === DO NOT APPLY COMMIT OR ROLLBACK ===*
      *> === EXEC SQL COMMIT END-EXEC.       ===* 
      *> === SQLCODE = -501                  ===*
            ELSE 
      IF SQLCODE NOT = 100
                  DISPLAY 'FETCH ERROR: ' SQLCODE
               END-IF
            END-IF
     END-PERFORM. 
         *> CLOSE THE CURSOR
         EXEC SQL 
        CLOSE C1_NOHOLD 
         END-EXEC.
         GOBACK.
    

  • Cursor With Hold – A WITH HOLD cursor remains open and maintains its position even after a COMMIT is executed. This is standard practice in heavy batch processing jobs to prevent the database lock list from filling up, allowing programmers to commit chunks of data (e.g., every 1,000 rows) without restarting the cursor fetch from scratch.

     IDENTIFICATION DIVISION.
     PROGRAM-ID. CURSHOLD.
    
     DATA DIVISION.
     WORKING-STORAGE SECTION.
    *    == host variables == 
         EXEC SQL INCLUDE SQLCA END-EXEC.
      EXEC SQL BEGIN DECLARE END-EXEC.
     01  WS-EMP-REC.
         05 WS-EMP-ID    PIC X(6).
         05 WS-EMP-NAME  PIC X(20).
         EXEC SQL END DECLARE END-EXEC.
    *    == program variables ==
     01  WS-COUNTERS.
         05 WS-COMMIT-CNT PIC 9(4) COMP VALUE ZERO.
    
     PROCEDURE DIVISION.
     MAIN-PARA.
    *    DECLARE THE CURSOR WITH HOLD
         EXEC SQL
            DECLARE C2_HOLD CURSOR WITH HOLD FOR
             SELECT EMPNO, LASTNAME 
               FROM EMPLOYEE
              WHERE DEPTNO = 'B01'
         END-EXEC.
    
    *    OPEN THE CURSOR
         EXEC SQL 
         OPEN C2_HOLD 
      END-EXEC.
         IF SQLCODE NOT = 0
            DISPLAY 'ERROR OPENING CURSOR: ' SQLCODE
            STOP RUN
         END-EXEC.
    
    *    FETCH AND PROCESS LOOP
         PERFORM UNTIL SQLCODE = 100
          EXEC SQL
                FETCH C2_HOLD 
       INTO :WS-EMP-ID, :WS-EMP-NAME
             END-EXEC
             IF SQLCODE = 0
                DISPLAY 'PROCESSING HELD ROW: ' WS-EMP-ID
                ADD 1 TO WS-COMMIT-CNT
                *> Example: Commit data every 500 records to free up locks
                IF WS-COMMIT-CNT >= 500
                   EXEC SQL COMMIT END-EXEC
                   DISPLAY 'COMMIT EXECUTED. CURSOR POSITION RETAINED.'
                   MOVE ZERO TO WS-COMMIT-CNT
                END-IF
             ELSE 
          IF SQLCODE NOT = 100
                    DISPLAY 'FETCH ERROR: ' SQLCODE
                END-IF
       END-IF
      END-PERFORM.
    
    *    CLOSE THE CURSOR EXPLICITLY (Crucial for Held Cursors)
         EXEC SQL 
         CLOSE C2_HOLD 
      END-EXEC.
    
    *    Final Commit for the last remaining block of rows
         EXEC SQL COMMIT END-EXEC.
         GOBACK.
    

    Comparison:

    Feature	WITHOUT HOLD (Default)	WITH HOLD
    Commit Behavior	Closes instantly.	Stays open; maintains position.
    Rollback Behavior	Closes instantly.	Closes instantly.
    Lock Release	Releases all row locks on commit.	Releases locks except for the current row position lock.
    Best Used For	Fast online transactions, light lookups.	Massive batch updates, chunked

• Mixed features cursors:

  Developers combine more than one features–Sensitivity for modifications, scrollability for random access in generated result set. A cursor can be declared scrollable, updateable, and held simultaneously. These multi-property implementations demand close observation of isolation tiers and locking profiles to avoid lock escalation bottlenecks.

  Based on mixed features cursors:

  • Sensitive Static Scrollable Cursor – In IBM i (Db2 for i), a Sensitive Static Scrollable Cursor allows a programmer to navigate forward and backward through a result set. While it immediately reflects updates and deletions made to existing rows (even by other jobs), it will not show newly inserted rows, and the row count remains fixed after opening.

    === Basic Syntax for SENSITIVE STATIC SCROLLABLE CURSOR ===
    EXEC SQL 
       DECLARE  Csr_Name SENSITIVE STATIC SCROLL CURSOR FOR 
       SELECT   Column1, Column2 
       FROM     MyTable 
       ORDER BY Column1 
       FOR      UPDATE OF Column2
    END-EXEC.
    
    -- === SENSITIVE STATIC SCROLLABLE CURSOR ===
    EXEC SQL 
        DECLARE  C03 SENSITIVE STATIC SCROLL CURSOR FOR 
        SELECT   * 
        FROM     INVENTORY 
        FOR      UPDATE
    END-EXEC.
    

  • ensitive Dynamic Scrollable Cursor – A Sensitive Dynamic Scrollable Cursor in IBM i (Db2 for i) is a cursor that lets programmers scroll both forward and backward through a result set while immediately reflecting database updates, deletes, and new row inserts made by other jobs.

    === Basic Syntax for SENSITIVE DYNAMIC SCROLL CURSOR ===
    EXEC SQL 
       DECLARE  Csr_Name SENSITIVE DYNAMIC SCROLL CURSOR FOR 
       SELECT   Column1, Column2 
       FROM     MyTable 
       ORDER BY Column1 
       FOR      UPDATE OF Column2
    END-EXEC.
    
    -- === SENSITIVE DYNAMIC SCROLLABLE CURSOR ===
    EXEC SQL 
        DECLARE  C04 SENSITIVE DYNAMIC SCROLL CURSOR FOR 
        SELECT   * 
        FROM     INVENTORY 
        FOR      UPDATE
    END-EXEC.
    

A Sensitive Static cursor uses a temporary result table where membership is locked at open of cursor, while a Sensitive Dynamic cursor rebuilds its membership on every fetch to directly query the live data.

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.
*----------------------------------------------------------------*