Indicator variables and null indicators in DB2 COBOL

What an indicator variable is

An indicator variable is a small integer associated with one host variable. It is itself a host variable. Db2 reads or writes the indicator when you name it in SQL next to its partner. Indicator arrays and structures do the same job for host-variable arrays and host structures.

Declare it as a halfword binary integer:

1
2
3
4
       01  HV-SALARY             PIC S9(7)V99 COMP-3.
       01  HV-SALARY-IND         PIC S9(4) COMP-5.
       01  HV-COMM               PIC S9(7)V99 COMP-3.
       01  HV-COMM-IND           PIC S9(4) COMP-5.

DCLGEN with INDVAR(YES) builds an OCCURS array of indicators for the whole structure, named with an I prefix on the table name. You can also declare one indicator per field, which makes FETCH logic easier to read.

SQL syntax: colon host colon indicator

In the SQL statement the indicator follows the host. There is no comma between them:

1
2
3
4
5
6
7
           EXEC SQL
                SELECT SALARY, COMM
                  INTO :HV-SALARY:HV-SALARY-IND,
                       :HV-COMM:HV-COMM-IND
                  FROM HR.EMPLOYEE
                 WHERE EMPNO = :HV-EMPNO
           END-EXEC.

You do not need an indicator on a host that can never be null and cannot truncate (for example a CHAR(6) NOT NULL key fetched into PIC X(6)). You do need one whenever the column is nullable. Mixing a nullable COMM column with INTO :HV-COMM and no indicator is how people discover SQLCODE -305 in production.

Checking for NULL after FETCH

After SELECT INTO or FETCH, look at SQLCODE first (0, +100, negative), then look at indicators for the columns you care about.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
           EXEC SQL
                FETCH C1
                 INTO :HV-EMPNO,
                      :HV-SALARY:HV-SALARY-IND,
                      :HV-COMM:HV-COMM-IND
           END-EXEC.
           EVALUATE SQLCODE
             WHEN 0
                IF HV-COMM-IND < 0
                   DISPLAY 'COMM is NULL'
                ELSE
                   DISPLAY 'COMM ' HV-COMM
                END-IF
                IF HV-SALARY-IND > 0
                   DISPLAY 'SALARY truncated, orig len '
                           HV-SALARY-IND
                END-IF
             WHEN +100
                DISPLAY 'End of cursor'
             WHEN OTHER
                DISPLAY 'SQLCODE ' SQLCODE
           END-EVALUATE.

When the indicator is negative, do not use the host data field for arithmetic or DISPLAY as if it were a real amount. Packed decimal hosts often contain leftover or undefined bytes; treating them as money causes S0C7 or silent wrong totals. Move a display sentinel (spaces, “N/A”) or skip the field.

Truncation also sets SQLCA warning flag SQLWARN1 to W. A VARCHAR host that is too short for the column is the usual cause. Fix the picture; do not ignore positive indicators in interfaces that must not lose characters (names, account numbers).

Indicator -2 and -3 show up when Db2 could not convert a value (bad character data in a decimal column, overflow, divide by zero in an expression). The associated host is not a clean business value. Log SQLCODE, SQLSTATE, and the indicator together.

Setting NULL on INSERT and UPDATE

On input, Db2 looks at the indicator before the host value:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
           MOVE 15000.00 TO HV-SALARY.
           MOVE 0        TO HV-SALARY-IND.
           MOVE -1       TO HV-COMM-IND.
           EXEC SQL
                INSERT INTO HR.EMPLOYEE
                       (EMPNO, LASTNAME, SALARY, COMM)
                VALUES (:HV-EMPNO, :HV-LASTNAME,
                        :HV-SALARY:HV-SALARY-IND,
                        :HV-COMM:HV-COMM-IND)
           END-EXEC.
 
           MOVE -1 TO HV-COMM-IND.
           EXEC SQL
                UPDATE HR.EMPLOYEE
                   SET COMM = :HV-COMM:HV-COMM-IND
                 WHERE EMPNO = :HV-EMPNO
           END-EXEC.

For the COMM insert, the contents of HV-COMM do not matter while the indicator is negative. Still, initialize hosts so a later bug that forgets the indicator does not write leftover data. To store a real commission, MOVE the amount and MOVE 0 to the indicator.

UPDATE of a NOT NULL column with a negative indicator fails. INSERT into a NOT NULL column without a DEFAULT also fails if you pass NULL. Check the table definition before you assume NULL is legal.

SQLCA warnings versus indicator values

Indicators and the SQLCA tell related stories. SQLWARN0 is W if any warning flag is set. SQLWARN1 is W on string truncation of a host.SQLWARN2 can indicate a null value was eliminated from a column function (that is a different null story than your FETCH indicator). Do not skip indicator checks just because SQLCODE is 0 and SQLWARN0 is blank—null is a successful FETCH of an absent value, not a warning.

WHENEVER NOT FOUND and WHENEVER SQLERROR branch on SQLCODE classes. They do not replace indicator tests. A program can GOTO a not-found paragraph on +100 and still need indicator logic on the successful FETCHes that happened earlier in the loop.

DSNTIAR (or SQLCA formatting in your framework) prints the message for a negative SQLCODE. It will not say “COMM was null.” That is your indicator IF. Put null handling next to the FETCH, not only in a generic error routine.

CICS, IMS, and batch differences

Indicator rules do not change by attachment. What changes is what you do after you see a null. In CICS you might map a screen field to spaces and an attribute that shows “N/A.” In batch you might write a report column of blanks or a literal NULL. In IMS you might skip a segment field. None of those presentations should be stored back as spaces unless the business truly wants a blank, non-null column.

Cursor-with-hold and pseudo-conversational CICS do not keep indicator values for you across a RETURN. Save whatever the user must see in COMMAREA or a channel, including a flag that the commission was null, then re-FETCH if you need a consistent row on the next task.

Extended indicators

Extended indicator variables add input meanings used in modern “sparse update” APIs:

• -1 — NULL (same idea as basic indicators)
• -5 — DEFAULT: assign the column's default (including identity/row change timestamp defaults where they apply)
• -7 — UNASSIGNED: behave as if the column were not listed in the INSERT/UPDATE, so triggers and BEFORE values see “not specified”

Enable them with the bind option EXTENDEDINDICATOR(YES) (packages) or the SQL attribute WITH EXTENDED INDICATORS on PREPARE / attribute strings. If extended indicators are not enabled, -5 and -7 are just negative values and mean NULL. Do not copy a -5 DEFAULT pattern into an old package that was bound without the option—you will store NULL instead of DEFAULT.

Indicator arrays and structures

For a host structure, you can attach one indicator structure (an array of halfwords) with the same number of elements as the host structure fields. For multi-row FETCH, each column has an indicator array with OCCURS matching the rowset size. If any element's indicator is negative, skip that element's data.

1
2
3
4
5
6
7
8
9
       01  HV-EMP-IND.
           05 HV-EMP-IND-EL      PIC S9(4) COMP-5 OCCURS 4 TIMES.
 
           EXEC SQL
                SELECT EMPNO, FIRSTNME, LASTNAME, COMM
                  INTO :DCL-EMPLOYEE:HV-EMP-IND
                  FROM HR.EMPLOYEE
                 WHERE EMPNO = :HV-EMPNO
           END-EXEC.

Map array slots to columns in SELECT-list order, not alphabetically. DCLGEN indicator arrays follow column order in the DECLARE TABLE. If you SELECT a subset of columns into elementary hosts, use elementary indicators instead of the full DCLGEN array.

Practical checklist

• Nullable column → indicator on every FETCH/SELECT INTO and every INSERT/UPDATE that might supply NULL
• After SQLCODE 0, test indicator < 0 before using the host
• After SQLCODE 0, test indicator > 0 if losing characters is unacceptable
• Never use spaces in a PIC X host as a homemade null—Db2 will store spaces, not NULL
• Name indicators clearly (suffix -IND) so reviews catch missing pairs

Explain It Like I'm Five

The host variable is a lunchbox. The indicator is a sticky note on the lunchbox. After Db2 packs lunch, a note that says “-1” means the lunchbox is empty on purpose (null)—do not eat leftover crumbs from yesterday. A note with a plus number means the sandwich was too big and got cut short. When you send lunch to Db2, you can stick “-1” on the box to mean “put nothing in that fridge slot.” Fancy kitchens (extended indicators) also understand “use the usual default meal” (-5) and “do not touch that slot” (-7).

Exercises

1. Declare SALARY and COMM hosts with indicators and write a SELECT INTO that is safe for a nullable COMM.
2. Write COBOL IF logic that prints “no commission” versus the commission amount.
3. Show MOVEs to INSERT a row with a real salary and a NULL commission.
4. Explain SQLCODE -305 in one sentence and how to prevent it.
5. When would you bind EXTENDEDINDICATOR(YES) and use -7 on an UPDATE host?