Data Build Manual
Customizing SQL-LOAD Output
Customizing Data Build
6–16 099331 Tandem Computers Incorporated
DEM2-DATE2 in RECDEF02 Source Record Descriptions File is a nullable field
because it contains a NULLS option. DEM2-DATE2 is a DATE column which does not
naturally contain binary zero. If DEM2-DATE2 was an INTEGER column and if zero
was a valid value represented in the data, the SQL-LOAD and SQL-INSERT would not
produce identical results; all DEM2-DATE2 zero values would load as nulls for SQL-
LOAD.
The following is an example of a Data Build user modification which applies to the
sample data. The procedure described in the subsection, “Customizing SQL-LOAD
Nulls” is followed. This will yield exactly the same table data as the standard SQL-
LOAD samples program and thus validates the customization technique.
1. DDLDEF02, the DDL file created in the BUILD DDL (F2) step for RECDEF02 is
modified to change the NULL clause in the output record DEM2-DATE2 field as
follows:
?SECTION OUT-DEMO02
RECORD OUT-DEMO02.
FILE IS $DATA.KORIPM1.DATAOT02 ENTRY-SEQUENCED.
02 DEMO02.
06 DEM2-IDNUMBER-X.
07 DEM2-IDNUMBER PIC S9(4) COMP.
...
06 DEM2-DATE2-X.
07 DEM2-DATE2 PIC S9(18) COMP NULL 1.
...
END.
2. The following is added to USERLIB.
a. NULL-VALUE-COMPARE, in working storage, is a an input data value which
represents a null. In the sample data a binary zero value for DEM2_DATE2
represents a null.
b. NULL-VALUE-REPLACE, in working storage, is a null replacement value that
is converted to a null value by SQLCI. It represents the DDL NULL value
(binary 1) repeated in every position of the field. DEM2-DATE2 in the output
record is an 8 byte field, PIC S9(18) COMP. NULL-VALUE-REPLACE
redefines four "PIC 9(4) COMP VALUE 257" fields, each of which is a 2 byte
field of value %000401 (hex H'0101').
c. NULL-VALUE-IND, in working storage, is a program switch.
d. U-IN-DEMO02-DEM2-DATE2 user exit sets the NULL-VALUE-IND switch if
DEM2-DATE2 in the input record contains NULL-VALUE-COMPARE. This
exit is prior to any processing for this field. If null, SPACES are moved to
DEM2-DATE2 in the input record in order to avoid a problem processing this
field. Standard Data Build conversion is performed and cannot be bypassed
with only a change to USERLIB.