UNLOAD format

The BMC AMI Unload UNLOAD format is the same as the format that the IBM Db2 REORG utility uses with the UNLOAD PAUSE option.

This format operates based on the following rules:

RECFM is always VB.
The expansion of varying fields to their full length is based on the use of the PAD and NOPAD keywords (if you are using the UNLD command).
Variable-length field length bytes indicate the actual length of the data, not the maximum allowed length.

Related topic

Unload output file formats

Variable-length fields are prefixed with a 2-byte field containing the length of the field (excluding the prefix itself).
Column values stay in Db2 internal format.
Each row contains a 6-byte record prefix that contains a 1-byte row flag, a 2-byte length (the same for every row), a 2-byte OBID for the owning table, and a 1-byte MAP ID reference.
Null indicators precede nullable columns. BMC AMI Unload copies them without changes from the values in the table being unloaded.
You can override the blocksize selected by BMC AMI Unload by coding the blocksize keyword in the JCL.

Example

The following unload statement:

UNLOAD FORMAT(UNLOAD) FROM TABLE NGT.TABLE1

produces the following output file:

1    5    10   15   20   25   30   35   40
            +----+----+----+----+----+----+----+----+---
COLS/NULLS: || | ||     |   *    | *
ROW 1:      ......TODD ............USE UNLOAD!!........
           (002000EDCC44000100135000EEC4EDDDCC5500000000)
            00B03136440000000024C0D0E250453614AA00000000
ROW 2:      ......MATTEO................................
           (002000DCEECD00020050000F00000000000000000000)
            00B03241335600000905C01F00000000000000000000
ROW 3:      ......IRINA ................................
           (002000CDCDC40003F000000000000000000000000000)
            00B0339995100000F000001000000000000000000000

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:

Field	Value
Field 1, Record ID	X'00', 1 byte.
Field 2, Record Length	X'002B', 2 bytes long. This is the total length of the record.
Field 3, Record OBID	X'0003', 2 bytes.
Field 4, MAP ID Reference	X'01', 1 byte.
NAME	TODD, 6 bytes.
AGE	X'00000010' , 4 bytes.
SALARY	X'00' , the 1-byte null indicator, followed by X'0012345C', is 4 packed decimal bytes. The field is NOT NULL.
COMMENT	X'000D', a 2-byte length field, that includes the length of the null indicator X'00', a 1-byte null flag USE UNLOAD!!, 12 bytes of characters X'0000000000000000', 8-bytes binary zero fill bytes
ROW 2, COMMENT column	Always at offset 21 in the row, this column varies in length. The length in ROW 2 shows a length of 1 byte. Of this length, the null indicator is 1 byte and the rest is data. This field is null, so there is no data.
ROW 3, SALARY column	This field is null (as for ROW 2, COMMENT column).
ROW 3, COMMENT column	The field is 1 byte in length, which includes only the null indicator. In this example, there is a varying length string of zero length.

UNLOAD format

On this page