The FETCH that stops on NULLs — SQLCODE -305 and the indicator array

The program reads a cursor over a wide table — say eighty-two columns — and works for a while. Then, on some random row, it stops:

SQL0305  Indicator variable required.
         SQLCODE -305   SQLSTATE 22002

The instinctive reaction is to wrap every column of the SELECT in a COALESCE. With eighty-two columns that means eighty-two hand-written functions, each with its own replacement value of the right type, to be redone every time someone adds a column. It can be done in two lines, and without losing the information.

What the message is actually saying

This is not an RPG problem and not a compile problem. It is an SQL rule: a null value cannot be assigned to a host variable, because the variable has nowhere to say so. A char(10) field can hold ten blanks; it cannot hold "nothing". A second channel is needed, and that is the indicator variable: a small integer travelling alongside the variable, telling you whether the value arrived or not.

If the value is null and the indicator is missing, Db2 has no way to deliver the data and raises -305. The rule holds for FETCH, SELECT INTO, CALL, SET, VALUES INTO: anywhere a value leaves the database and enters a program variable.

The job log message is more useful than it looks: the second level names which host variable blocked the assignment and its relative position in the INTO. With eighty-two columns, that number is the fastest way to know which column is being discussed.

The damage you do not see

This is the part worth knowing before writing the fix, because it changes how serious the problem looks.

When the assignment fails, SQL does not undo what it has already done. The documentation is explicit: the offending value is not assigned, nothing further is assigned from there on, and values already placed in variables stay where they are.

In plain terms: after a -305 the structure holds the columns before the null one, taken from the current row, and every column after it holding the contents of the previous row. It is a row that never existed. If the loop only checks sqlstate < '02000' it leaves and nobody notices; if the check is looser — or if -305 is treated as a warning to skip — the program processes that invented row and writes it somewhere.

The fix: an indicator array

One indicator per column, all in an array, and the array attached to the structure in the INTO:

dcl-ds row qualified;
  ordNum  packed(7:0);
  ordCus  char(10);
  ordDat  date;
  // … the other columns
end-ds;

dcl-s isNull int(5) dim(82);

exec sql
  DECLARE C1 CURSOR FOR
  SELECT * FROM ORDERS
   ORDER BY ORDNUM
   FOR READ ONLY;

exec sql OPEN C1;

dow sqlstate < '02000';
  clear row;
  exec sql FETCH C1 INTO :row :isNull;
  if sqlstate >= '02000';
    leave;
  endif;

  if isNull(3) < 0;
    // the date is not there: not 1 January 0001, not there
  endif;
enddo;

exec sql CLOSE C1;

Three details that decide between working and not compiling:

• The indicator goes after the host variable, prefixed with a colon and separated by a blank. Not by a comma. A comma separates two distinct host variables: writing INTO :row, :isNull asks SQL to put the second column inside the array, and the result is a compile error or, worse, a program that compiles. The explicit form INTO :row INDICATOR :isNull is equivalent and reads better.
• int(5), that is a smallint: it is the type SQL expects, and it is not negotiable.
• dim must cover every column of the result. With a data structure the array is positional: the first element is the first column, the second the second, and so on. Spare elements do no harm. Having fewer is on whoever wrote it: the IBM documentation requires the array to be large enough and promises no check, so it is not something to rely on.

Why that clear

It is not generic caution, it is the line that gives you the result you expect.

The IBM documentation says that when the database returns a null value the host variable might or might not be set to the default value for its type — zero for numerics, blanks for fixed-length characters. Might: not will. In practice, the fields left out keep whatever was there before, which is the previous row's value.

Clearing the structure before each FETCH leaves null columns as blanks and zeros while the populated ones get overwritten anyway. The cost is one line; the result is identical to eighty-two COALESCEs, with the added benefit that you still know which columns were really null.

Reading the indicators without counting columns

Writing isNull(47) is correct and unreadable. The way to give every element a name is to overlay the same storage with a structure carrying the column names:

dcl-s isNull int(5) dim(82);

dcl-ds nulls based(pNulls) qualified;
  ordNum  int(5);
  ordCus  int(5);
  ordDat  int(5);
  // … one per column, in the same order as the SELECT
end-ds;

pNulls = %addr(isNull);

From there you write nulls.ordDat < 0, which reads. If there are three hundred columns and listing them by hand is out of the question, each subfield's number sits in the compile listing next to its name — and it is the same number that appears in the second level of SQL0305.

And the values: -1, -2, and the positives

The most common mistake, once the indicators are in, is testing < 0 and calling it NULL. The values are three different things:

-2 is the one that costs you an afternoon. It means the data was there, but turning it into something that fits the variable produced an error: a date outside the valid range, a division by zero in a SELECT expression, a numeric overflow, characters that cannot be converted into the target CCSID. SQL does not stop the statement — it returns null, sets -2 and carries on. Treating -2 as -1 means mistaking a dirty file for an empty field.

The positives are the other surprise: a string longer than the variable is truncated, and with no indicator no error is reported at all. The indicator is the only place that fact shows up.

Reading in blocks

On a large table the row-by-row FETCH gives way to the multiple-row fetch, and the null map becomes a two-dimensional array: one row of indicators for each row read.

dcl-ds rows extname('ORDERS') qualified dim(500)
end-ds;

dcl-ds nulls qualified dim(500);
  ind int(5) dim(82);
end-ds;

dcl-s wanted int(10) inz(%elem(rows));
dcl-s got    int(10);

exec sql FETCH C1 FOR :wanted ROWS INTO :rows :nulls;
exec sql GET DIAGNOSTICS :got = ROW_COUNT;

The indicator for the fourth column of the second row is nulls(2).ind(4). The two dim values must be the same number: the null map fills in step with the rows.

ALWNULL has nothing to do with it — and that needs saying

It is the advice you find most often when searching for SQL0305, and it is wrong: compiling with ALWNULL(*INPUTONLY) has no effect on embedded SQL.

ALWNULL is an RPG control specification keyword, and the documentation says exactly what it acts on: records coming from externally described files, that is native record-level access — CHAIN, READ, SETLL. It governs %NULLIND, which cannot even be used without ALWNULL(*USRCTL). It does not touch the EXEC SQL path, where the behaviour is decided by the presence of an indicator and by nothing else.

Two practical consequences:

• CRTSQLRPGI has no ALWNULL parameter. Looking for it on the command will not find it, and that is not an oversight: it goes in ctl-opt, or is passed to the compiler with COMPILEOPT('ALWNULL(*USRCTL)').
• The default is *NO, and changing it may serve other purposes — but it will not make a -305 go away.

When COALESCE is fine anyway

It is not a function to avoid: it is a different choice, to be made knowing what it costs.

COALESCE(ORDAMT, 0) tells the database to deliver zero in place of the null. From that moment the program has no way to tell "amount zero" from "amount not filled in", and on a total the two coincide, while on an average they do not: AVG skips nulls and counts zeros. If the distinction is not needed — a counter, a sum, a printout — COALESCE is shorter and is the right call. If it is needed, the indicator is the only way to keep it.

Db2 for i also has IFNULL(column, value), which does the same with only two arguments. COALESCE is the standard one and takes as many as you like: for the same result, you may as well write the one that travels.

"But all my columns are NOT NULL"

It happens, and it is the moment you start doubting the system. The table is not the only source of nulls: the result of the query is one too.

• A LEFT OUTER JOIN or an EXCEPTION JOIN produces nulls across every column of the table that found no match, including those declared NOT NULL.
• An aggregate over an empty set returns null: MAX, MIN, SUM, AVG. The one that does not is COUNT, which answers zero — which is why the error surprises people.
• A scalar subquery that finds no rows is null.
• A CASE with no ELSE is null when no branch matches.
• NULLIF returns null by definition.
• A conversion error gives null with indicator -2, as above.

To find out quickly which columns of a table allow nulls:

SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE
  FROM QSYS2.SYSCOLUMNS
 WHERE TABLE_SCHEMA = 'PRODLIB'
   AND TABLE_NAME   = 'ORDERS'
   AND IS_NULLABLE  = 'Y'
 ORDER BY ORDINAL_POSITION

ORDINAL_POSITION is the same position used to index the indicator array, and the same number the job log already wrote in the second level of the message.

The upstream fix, where it is available

If the table is yours and those columns should never be empty, the right place to fix it is not the program:

ALTER TABLE PRODLIB/ORDERS
  ALTER COLUMN ORDAMT SET NOT NULL

It only works if there are no null rows already, and that is a feature: the command that fails is telling you how many rows of dirty data are in production. Closing the hole in the definition removes the problem for every program reading that table, present and future, instead of for one.

Releases and verification. The rules on indicators hold on every supported release; the examples are in fully free-form RPG and were checked against the IBM i 7.5 documentation. The behaviour on partial assignment, the -1 / -2 / positive values and the scope of ALWNULL come from the IBM documentation listed below, not from memory.

Sources. Indicator variables in applications that use SQL · Indicator variables used with host structures · FETCH · References to host variables · CRTRPGMOD — ALWNULL · SYSCOLUMNS