Query Db2 and data sets

This guide runs a batch program that updates a Db2 table, keeps the state it left, and reads it back. Needs harwell:runs:write, which includes the harwell:runs:read tools used here.

1. Seed the tables

DB/seed.sql holds the DDL and rows a fresh volume starts with.

billing/
  DB/seed.sql
  Progs/RAISE.cbl
  Procs/RAISEJOB.jcl
  run.json

DB/seed.sql:

CREATE TABLE ACCOUNTS (
  ACCT CHAR(11) NOT NULL,
  BAL  INTEGER  NOT NULL
);
INSERT INTO ACCOUNTS VALUES ('00000000011', 500000);
INSERT INTO ACCOUNTS VALUES ('00000000012', 250000);

2. Run SQL from a program

Progs/RAISE.cbl:

       IDENTIFICATION DIVISION.
       PROGRAM-ID. RAISE.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
           EXEC SQL INCLUDE SQLCA END-EXEC.
       01  WS-ACCT  PIC X(11) VALUE '00000000011'.
       01  WS-BAL   PIC S9(9) VALUE 0.
       01  WS-SHOW  PIC 9(9) VALUE 0.
       PROCEDURE DIVISION.
           EXEC SQL
               UPDATE ACCOUNTS SET BAL = BAL + 1000
                WHERE ACCT = :WS-ACCT
           END-EXEC
           EXEC SQL
               SELECT BAL INTO :WS-BAL FROM ACCOUNTS
                WHERE ACCT = :WS-ACCT
           END-EXEC
           MOVE WS-BAL TO WS-SHOW
           DISPLAY 'BAL=' WS-SHOW
           GOBACK.

Procs/RAISEJOB.jcl:

//RAISEJOB JOB
//STEP01   EXEC PGM=RAISE
//SYSOUT   DD SYSOUT=*

run.json:

{"submit": ["RAISEJOB"]}

3. Keep the volume

Each call gets a fresh volume, discarded when the call ends, unless you keep it. Set return_work:

run_unit {"unit_zip_b64": "<billing, zipped and base64-encoded>", "return_work": true}

The step displays BAL=000501000. The result names the kept volume:

{"status": "ok", "work_id": "r-20261103T120000Z-...", ...}

4. Query the tables

sql {"work_id": "r-20261103T120000Z-...", "statement": "SELECT ACCT, BAL FROM ACCOUNTS ORDER BY ACCT"}

{"columns": ["ACCT", "BAL"], "rows": [["00000000011", 501000], ["00000000012", 250000]], "more": false, "sqlcode": 0, ...}

sql runs one SELECT, WITH or VALUES statement in Db2’s dialect, read-only. A statement that fails returns Db2’s SQLCODE and SQLSTATE: -204 for an unknown table, -206 for an unknown column, -104 for a syntax error.

5. List the data sets

list_datasets {"work_id": "r-20261103T120000Z-...", "pattern": "PAY.**"}

Each data set comes with its organization, RECFM, LRECL, bytes and record count, and the Db2 tables beside them. In a pattern, * matches within one qualifier, ** across qualifiers and % one character.

6. Read records

read_dataset {"work_id": "r-...", "dsn": "PAY.TOTAL"}

Each record comes as text and hex. Give a copybook to decode fields, including COMP-3, COMP and zoned decimal:

read_dataset {"work_id": "r-...", "dsn": "PAY.INPUT",
              "copybook": "       01  IN-REC.\n           05 IN-NAME   PIC X(10).\n           05 IN-AMOUNT PIC 9(5).",
              "record": "IN-REC"}

copybook is the copybook’s source text. Pass copybooks ({MEMBER: source}) for each COPY it contains. A KSDS or RRDS a program wrote is refused: REPRO it to a sequential data set in a job, then read that.

7. Carry on from the volume

Pass the work_id to the next call. It starts from the state the last run left:

run_unit {"unit_zip_b64": "<billing>", "work_id": "r-20261103T120000Z-...", "return_work": true}

The balance is now 502000. To carry state without passing ids, use a system.

SQLCODEs in a result

Every SQL statement that ends with a negative SQLCODE is a warning in the result’s diagnostics, at the program’s line, whether or not the program checks SQLCODE:

SSYS001.cbl:132: SQLCODE -305 (SQLSTATE 22002) on SELECT ... FROM SYSTEM_PARAMETER: THE NULL VALUE CANNOT BE ASSIGNED TO OUTPUT HOST VARIABLE position-number BECAUSE NO INDICATOR VARIABLE IS SPECIFIED (2 times)

One line per statement and code, with the count when it failed more than once. The text is IBM’s for the codes Db2 defines. The step’s return code stays the program’s own. See Errors.

SQL in CALLed programs

A subprogram with EXEC SQL, CALLed from a batch or online program, runs its SQL on the caller’s unit of work, as on z/OS.

Get started

Guides

Concepts

Reference

Query Db2 and data sets

1. Seed the tables

2. Run SQL from a program

3. Keep the volume

4. Query the tables

5. List the data sets

6. Read records

7. Carry on from the volume

SQLCODEs in a result

SQL in CALLed programs

​1. Seed the tables

​2. Run SQL from a program

​3. Keep the volume

​4. Query the tables

​5. List the data sets

​6. Read records

​7. Carry on from the volume

​SQLCODEs in a result

​SQL in CALLed programs

1. Seed the tables

2. Run SQL from a program

3. Keep the volume

4. Query the tables

5. List the data sets

6. Read records

7. Carry on from the volume

SQLCODEs in a result

SQL in CALLed programs