Data Build Manual

Customizing SQL-LOAD Output
Customizing Data Build
6–14 099331 Tandem Computers Incorporated
3. In the Source Record Descriptions File, for each nullable field with a NULLS
option, there is an associated indicator field which is named the same but with a
“-I” appended. This indicator field is specified by a NULLIND option and
determines if the nullable field contains a null.
If the associated indicator field contains LOW-VALUE (binary 0), the COBOL85
conversion program moves LOW-VALUE (binary 0) to the output field.
4. Based upon the description in the previous subsection, SQLUTIL loads a null
value for this field if it contains binary 0 (NULL 0) repeated in every byte position.
Now for a little analysis of what is taking place.
If the nullable field is a computational field, for example SMALLINT or INT, and an
input record contains a valid value of zero for this field, it will be loaded as a null, not
as a value of zero. If zero is a valid value for this field, see the next subsection for a
user modification to load nulls properly.
Customizing SQL-LOAD
Nulls
This applies only to computational and DATE/TIME type fields. The standard SQL-
LOAD implementation for character data will work correctly.
Although the SQL Table may allow nulls, the following is necessary only if the data
being loaded actually contains nulls. If the source data is from IBM DB2, the following
can be used to determine if a null value exists:
SELECT * FROM table WHERE col1 IS NULL
If a null value exists, then a null replacement value must be found which ideally
cannot naturally occur in the data or which does not have an instance in the current
data being loaded. The latter approach is a one time shot which will not necessarily
apply if different data is loaded which might contain this value.
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.
If all possible 256 DDL NULL values exist for a column, see the next subsection for a
variation of this technique.
For each nullable column, a value is required that has no instance in the version of the
table being converted. This value may be a valid value for the column but it is
sufficient that this value not be represented in the actual data being converted.
The basic approach follows:
1. In the Source Record Descriptions File, specify NULLS as an option for every
nullable field.