> ## Documentation Index
> Fetch the complete documentation index at: https://docs.metaphi.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Query Db2 and data sets

> Seed Db2 tables, run SQL from COBOL, keep the run's volume, and read its tables 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.

```text theme={"dark"}
billing/
  DB/seed.sql
  Progs/RAISE.cbl
  Procs/RAISEJOB.jcl
  run.json
```

`DB/seed.sql`:

```sql theme={"dark"}
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`:

```cobol theme={"dark"}
       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`:

```text theme={"dark"}
//RAISEJOB JOB
//STEP01   EXEC PGM=RAISE
//SYSOUT   DD SYSOUT=*
```

`run.json`:

```json theme={"dark"}
{"submit": ["RAISEJOB"]}
```

## 3. Keep the volume

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

```json theme={"dark"}
run_unit {"unit_zip_b64": "<billing, zipped and base64-encoded>", "return_work": true}
```

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

```json theme={"dark"}
{"status": "ok", "work_id": "r-20261103T120000Z-...", ...}
```

## 4. Query the tables

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

```json theme={"dark"}
{"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

```json theme={"dark"}
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

```json theme={"dark"}
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:

```json theme={"dark"}
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:

```json theme={"dark"}
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](https://docs.metaphi.ai/harwell/guides/systems.md).

## 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`:

```text theme={"dark"}
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](https://docs.metaphi.ai/harwell/errors.md#sqlcodes).

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


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.