DB2 host variable arrays, LOB, XML, decimal and datetime hosts

Host variable arrays

A host-variable array is a COBOL OCCURS table used as a host in SQL. Multi-row FETCH (FETCH ... FOR :n ROWS) and multi-row INSERT write or read several rows in one API call. That cuts application-Db2 trips compared with a FETCH loop of one row.

Declare the array in WORKING-STORAGE or LINKAGE. OCCURS is legal here. Pair each array with an indicator array of the same dimension when the column is nullable. After a rowset FETCH, SQLERRD(3) in the SQLCA tells how many rows were returned. SQLCODE +100 can mean “this was the last rowset”—some slots may still hold rows.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
       01  HV-ROWSET-SIZE        PIC S9(9) COMP-5 VALUE 20.
       01  HV-EMPNO-ARR.
           05 HV-EMPNO           PIC X(6) OCCURS 20 TIMES.
       01  HV-SALARY-ARR.
           05 HV-SALARY          PIC S9(7)V99 COMP-3 OCCURS 20 TIMES.
       01  HV-SALARY-IND-ARR.
           05 HV-SALARY-IND      PIC S9(4) COMP-5 OCCURS 20 TIMES.
 
           EXEC SQL
                DECLARE C1 CURSOR WITH ROWSET POSITIONING FOR
                 SELECT EMPNO, SALARY
                   FROM HR.EMPLOYEE
                  WHERE WORKDEPT = :HV-DEPT
           END-EXEC.
           EXEC SQL OPEN C1 END-EXEC.
           EXEC SQL
                FETCH NEXT ROWSET FROM C1
                 FOR :HV-ROWSET-SIZE ROWS
                 INTO :HV-EMPNO,
                      :HV-SALARY:HV-SALARY-IND
           END-EXEC.

Use the same array names in SQL as the OCCURS elementary or group names your precompiler expects (shop examples sometimes use the group, sometimes the elementary item). Process elements 1 through SQLERRD(3). Do not assume all 20 slots are valid. For INSERT, set every element you intend to send, set indicators, then:

1
2
3
4
5
           EXEC SQL
                INSERT INTO HR.STAGE_EMP (EMPNO, SALARY)
                VALUES (:HV-EMPNO, :HV-SALARY:HV-SALARY-IND)
                FOR :HV-ROWSET-SIZE ROWS
           END-EXEC.

Atomic versus not-atomic multi-row INSERT changes whether one bad row fails the whole set. Check SQLCODE, GET DIAGNOSTICS, and your shop standard before using NOT ATOMIC in production.

Rowset size and GET DIAGNOSTICS

Pick a rowset size that fits below Language Environment stack and region limits. Twenty to one hundred rows is a common starting range for modest row widths. Wide rows with VARCHAR(4046) times 100 quickly become large WORKING-STORAGE. Measure. Too small a rowset wastes trips; too large increases CPU per FETCH and storage.

After a multi-row INSERT, use GET DIAGNOSTICS when you need per-row results (especially NOT ATOMIC). SQLCODE on the statement is a summary; individual row errors live in diagnostic areas. Beginners should start with ATOMIC inserts and a single SQLCODE until the shop requires partial success.

Multi-row FETCH against a cursor declared WITH ROWSET POSITIONING is the usual pairing. A cursor without rowset positioning still FETCHes one row into scalar hosts. Mixing a rowset FETCH into scalar hosts (or the reverse) is a precompiler/SQL error. Keep the DECLARE, the FETCH, and the OCCURS counts in agreement.

LOB host variables

COBOL has no built-in BLOB type. You declare LOB hosts with USAGE SQL TYPE IS. The precompiler rewrites them into groups: a 4-byte length (PIC S9(9) COMP-5) plus data (PIC X(n) for BLOB/CLOB, PIC G for DBCLOB). In SQL you still use the name you declared. In COBOL MOVE/STRING you use the generated subordinate names.

1
2
3
4
       01  HV-RESUME    USAGE SQL TYPE IS CLOB(32K).
       01  HV-PHOTO     USAGE SQL TYPE IS BLOB(1M).
       01  HV-RESUME-LOC USAGE SQL TYPE IS CLOB-LOCATOR.
       01  HV-RESUME-FILE USAGE SQL TYPE IS CLOB-FILE.

Choose a representation:

• CLOB(n) / BLOB(n) / DBCLOB(n) — the bytes live in the program. Suffixes K, M, and G are allowed. Size the buffer for the real maximum you will fetch, not always the column max (a 2G CLOB does not fit in a COBOL WORKING-STORAGE budget).
• CLOB-LOCATOR / BLOB-LOCATOR / DBCLOB-LOCATOR — Db2 keeps the value; you hold a 4-byte locator (PIC S9(9) COMP-5 after expansion). Use locators with LOB expressions, assignment, and FREE LOCATOR. Ideal when you only pass the LOB to another SQL statement.
• CLOB-FILE / BLOB-FILE / DBCLOB-FILE — file reference variables. Db2 reads or writes a file rather than a memory buffer. Useful for huge documents.

Locators are compatible in limited ways with CHAR/VARCHAR (CLOB locator) or BINARY/VARBINARY (BLOB locator) on certain assignments. Prefer matching families. Always FREE LOCATOR when you are done if you allocate many of them in a long cursor loop.

1
2
3
4
5
6
7
8
9
10
11
12
           EXEC SQL
                SELECT RESUME
                  INTO :HV-RESUME-LOC
                  FROM HR.EMP_RESUME
                 WHERE EMPNO = :HV-EMPNO
           END-EXEC.
           EXEC SQL
                SET :HV-RESUME = SUBSTR(:HV-RESUME-LOC, 1, 1000)
           END-EXEC.
           EXEC SQL
                FREE LOCATOR :HV-RESUME-LOC
           END-EXEC.

XML host variables

XML columns are the XML type. Application hosts are declared as XML mapped onto a LOB-shaped buffer:

• SQL TYPE IS XML AS CLOB(n) — character XML in the variable CCSID
• XML AS DBCLOB(n) — graphic/UTF-16 oriented buffer
• XML AS BLOB(n) — binary XML
• File forms: XML AS CLOB-FILE, DBCLOB-FILE, BLOB-FILE

1
2
3
4
5
6
7
8
9
10
11
12
       01  HV-DOC    USAGE SQL TYPE IS XML AS CLOB(64K).
 
           EXEC SQL
                SELECT XMLDOC
                  INTO :HV-DOC
                  FROM HR.EMP_XML
                 WHERE EMPNO = :HV-EMPNO
           END-EXEC.
           EXEC SQL
                INSERT INTO HR.EMP_XML (EMPNO, XMLDOC)
                VALUES (:HV-EMPNO, :HV-DOC)
           END-EXEC.

The host is XML, not CLOB, even though the buffer looks like a CLOB. You can also move XML to character or binary hosts because XML is assignable to those families, but IBM recommends true XML hosts so parsing and validation stay on the XML path. Invalid XML on INSERT fails; do not treat the buffer as a random PIC X string you concatenate without well-formed tags.

BINARY hosts and other SQL TYPE IS forms

BINARY and VARBINARY have no native COBOL twin. Always use SQL TYPE IS, then the generated group in COBOL and the declared name in SQL. Mixing X'...' character hex literals with VARBINARY columns is a type mistake; use BX literals or binary hosts.

Decimal host variables and COMP-3

Db2 DECIMAL(p,s) (NUMERIC is a synonym) maps to packed decimal: PIC S9(p-s)V9(s) USAGE COMP-3. The V is an implied decimal point, not a stored character.

• DECIMAL(9,2) → PIC S9(7)V99 COMP-3
• DECIMAL(11,2) → PIC S9(9)V99 COMP-3
• DECIMAL(5,0) → PIC S9(5) COMP-3 (no V fraction)
• DECIMAL(4,4) → PIC SV9(4) COMP-3 (only fractional digits)

1
2
3
4
5
6
7
8
9
       01  HV-SALARY             PIC S9(7)V99 COMP-3.
       01  HV-RATE               PIC S9(1)V9(4) COMP-3.
 
           EXEC SQL
                SELECT SALARY, BONUS_RATE
                  INTO :HV-SALARY, :HV-RATE
                  FROM HR.EMPLOYEE
                 WHERE EMPNO = :HV-EMPNO
           END-EXEC.

Compatibility notes:

• COMP-3 is packed; DISPLAY signed numeric is a different encoding and is a poor SQL host
• Integer hosts (COMP-5) can receive whole-number DECIMAL values with conversion, but you lose scale—do not FETCH money into INTEGER
• DECFLOAT is not COMP-3; use the DECFLOAT host mapping your compiler/Db2 version documents, or fetch into DECIMAL/character if you must
• Always keep an indicator on nullable DECIMAL columns; packed leftover bytes plus a forgotten null is a notorious S0C7

Date host variables

DATE hosts are character strings, typically PIC X(10) for the ISO layout yyyy-mm-dd. USA, EUR, and JIS layouts are also 10 bytes in the usual IBM table (yyyy-mm-dd, mm/dd/yyyy, dd.mm.yyyy, yyyy-mm-dd). Match the precompiler date format / installation DATE setting when you INSERT from a host string. Do not FETCH DATE into PIC 9(8) COMP-3 “YYYYMMDD” unless you explicitly CAST in SQL to a decimal or string pattern you control.

1
2
3
4
5
6
7
8
9
10
11
       01  HV-HIREDATE           PIC X(10).
       01  HV-HIREDATE-IND       PIC S9(4) COMP-5.
 
           EXEC SQL
                SELECT HIREDATE
                  INTO :HV-HIREDATE:HV-HIREDATE-IND
                  FROM HR.EMPLOYEE
                 WHERE EMPNO = :HV-EMPNO
           END-EXEC.
           MOVE '2005-06-15' TO HV-HIREDATE.
           MOVE 0            TO HV-HIREDATE-IND.

Timestamp host variables

TIME is commonly PIC X(8) (hh.mm.ss or hh:mm:ss). TIMESTAMP without a time zone and with six fractional digits is PIC X(26): yyyy-mm-dd-hh.mm.ss.nnnnnn. Higher precision (up to 12 digits) needs a longer character host. TIMESTAMP WITH TIME ZONE needs a still longer picture (the zone offset is part of the external string).

1
2
3
4
5
6
7
8
       01  HV-CHG-TS             PIC X(26).
       01  HV-CHG-TS-IND         PIC S9(4) COMP-5.
 
           EXEC SQL
                SELECT CURRENT TIMESTAMP
                  INTO :HV-CHG-TS
                  FROM SYSIBM.SYSDUMMY1
           END-EXEC.

If you FETCH a TIMESTAMP(12) into PIC X(26) you truncate fractional digits (positive indicator / SQLWARN). Size the host for the column precision. For time zone columns, use the documented external length or CAST the value to TIMESTAMP without zone if your program truly does not need the offset.

Putting the mappings together

Practical order when you add a column to a COBOL program:

1. Regenerate DCLGEN for ordinary types (CHAR, VARCHAR, numbers, DATE, TIME, TIMESTAMP).
2. Hand-add SQL TYPE IS for BINARY, LOB, XML, ROWID, locators, file refs.
3. Add indicators for every nullable field, including LOB/XML.
4. If you FETCH more than one row per call, promote scalars to OCCURS arrays and use rowset FETCH.
5. Verify COMP-3 digit budgets against DECIMAL(p,s) on paper before the first bind.

Explain It Like I'm Five

A host-variable array is an egg carton: one trip from the fridge (Db2) can fill twelve cups instead of walking twelve times. LOB hosts are giant moving boxes; sometimes you only keep a claim ticket (a locator) and ask the warehouse to copy a corner of the box. XML hosts are boxes labeled “this is a special puzzle document,” even if the cardboard looks like a CLOB box. DECIMAL COMP-3 cups have painted slots for dollars and cents—if you paint the wrong number of slots, money spills. DATE and TIMESTAMP cups are labeled strips of paper with the day and the clock written in a fixed number of character boxes.

Exercises

1. Declare OCCURS 50 arrays for EMPNO and a nullable COMM, plus indicators, and write a FETCH NEXT ROWSET FOR :N ROWS INTO them.
2. Write SQL TYPE IS declarations for a 10K CLOB, a CLOB locator, and XML AS CLOB(32K).
3. Map DECIMAL(15,4) to a COMP-3 picture and explain the integer versus scale split.
4. Choose PIC X length for TIMESTAMP(0) versus TIMESTAMP(6) and say what goes wrong if you undersize.
5. Explain one reason to FETCH a CLOB into a locator instead of SQL TYPE IS CLOB(2G).