Data Build Manual

Customizing SQL-LOAD Output
Customizing Data Build
099331 Tandem Computers Incorporated 6–13
How Standard SQLCI
Loads Nulls
SQLCI indirectly supports loading null values as follows:
The SQLCI LOAD command must include the USESQLNULLS and SOURCEREC
options.
The SOURCEREC option identifies the DDL record definition in the local DDL
Dictionary (or a remote dictionary if the SOURCEDICT option is included). This
record definition is for the input to SQLCI, which is the same as the output from
the conversion program.
A NULL clause on a field in the DDL record definition specifies a numeric value
(from 0 to 255) which represents a single byte value that can be thought of as a
NULL replacement value.
The SQLCI process (actually the SQLUTIL process started by SQLCI) loads a null
value if the input field contains the DDL NULL value repeated in every byte of the
field.
For example, if “NULL 1” is specified for a field in the DDL record definition,
SQLUTIL will load a null value if the field contains:
For a binary 16 value: %000401 (hex H’0101’) or decimal 257
For a 2-byte character field: also H’0101’, which is not a valid character but would
have to be constructed in the COBOL85 program via a REDEFINE.
The DDL NULL value (from 0 to 255) has a maximum of 256 possible null replacement
values for each nullable column. The trick is to find one of these values that does not
exist in the table.
For a character column, since there are many such values that are not valid
character field values, it should be easy to pick a value that has no instance where
all bytes contain the chosen NULL value. Binary zero is an easy and excellent
choice.
For a numeric (COMPUTATIONAL) column, if the source data is an IBM DB2
table, a set of DB2 statements such as:
SELECT * FROM table WHERE col1 = null-replace-value1
SELECT * FROM table WHERE col1 = null-replace-value2
...
could be used, before the data is unloaded, to determine if any of the 256 DDL
NULL values is present for a column. The first statement that returns no rows will
determine a value that can be used for the DDL NULL value.
Loading SQL Nulls With the
SQL-LOAD Option
This is how Data Build handles loading SQL NULLS with the SQL-LOAD option:
1. The SQLCI LOAD command in file ZZLOAD contains the USESQLNULLS and
SOURCEREC options whether or not any SQL columns are nullable.
2. In the BUILD (F2) step, for each field in the Source Record Descriptions File which
contains a NULLS option, a “NULL 0” clause is added to the field in the DDL
output record definition.