Example
BMC AMI Unload unloads stand-alone constant strings in SELECT statements as variable-length strings, regardless of whether you select DIRECT YES or DIRECT NO.
SELECT 'YES', IFNULL(COL1,'X'),COL2 CONCAT 'NO'
BMC AMI Unload treats the constants in this SELECT statement as follows:
BMC AMI Unload treats constant strings coded as part of SQL functions as fixed character strings.
If the NULL marker is binary zeros (X'00'), the column value was not null when unloaded. If the NULL marker is not binary zeros, the column value was null when unloaded and the area in the output row represented by that column contains binary zeros.
BMC AMI Unload converts numeric values from internal Db2 format to S/390 data type formats.
Each variable-length column is prefixed by a two-byte number that represents the column length excluding both the prefix itself and the null indicator field (if present). Depending on the NULL field specifications, the null indicator byte either immediately precedes or follows the nullable field.
You can set the RECFM of the output file when using the DSNTIAUL format using one of the following methods:
If you are using the UNLD command, specify the PAD-or-NOPAD option.
If you specify NOPAD, the RECFM of the output file is set to VB. This keyword does not pad the area to the right of the data value in a varying field to binary zeros.
You can also set the record format to VB by specifying DCB=RECFM=VB in the DD statement for the unload data set. BMC AMI Unload does not pad the unloaded variable fields with binary zeros. This is incompatible with specifying the PAD keyword.
Override the RECFM value in the JCL for the unload data set.
If you do not override the RECFM parameter in the JCL, specifying the PAD keyword sets the RECFM to FB. This keyword sets the unused area to the right of the data value in a varying field to binary zeros.
Example
To have BMC AMI Unload unload table NGT.TABLE1 using the DSNTIAUL format to a file with record format FB, use the following statement:
UNLOAD FORMAT DSNTIAUL FROM TABLE NGT.TABLE1
The results appear as follows:
1 5 10 15 20 25 30 35
+----+----+----+----+----+----+----+--
COLS/NULLS: | | | *| *
ROW 1: TODD ...........USE UNLOAD!!.........
(EDCC4400010135000EEC4EDDDCC55000000000)
3644000000024C00CE250453614AA000000000
ROW 2: MATTEO...............................?
(DCEECD00020500000000000000000000000006)
4133560000905C00000000000000000000000C
ROW 3: IRINA ........?.......................
(CDCDC400030000600000000000000000000000)
99951000000000C00000000000000000000000
In these results:
- A vertical bar (|) represents the start of each column in the row labeled COLS/NULLS
- An asterisk (*) represents null indicators.
Descriptions of the fields and their values are shown in the following table:
| |
|---|
| |
| |
| - X'0012345C', 4 packed decimal bytes
- X'00', a 1-byte null flag indicating NOT NULL
|
| - X'000C', a 2-byte length field
- USE UNLOAD!!, 12 bytes of characters
- X'0000000000000000', 8-bytes binary zero fill bytes
- X'00', a 1-byte null flag
|
| This column varies in length, always at offset 15 in the row. The length in ROW 2 shows a zero length. The null indicator, always at offset 37 in the row, shows a ?, indicating that this field is null. |
| This field is null (as for ROW 2, COMMENT column). The ROW 3, SALARY column is null. SALARY is at offsets 10 through 13. Offset 14 is the null indicator and shows as null. |
| The entire field is binary zeros, including the length and the null indicator. This means that the value is NOT NULL, and has a varying string length of zero length. |