Skip to Content

Default language.

Information
Limited support BMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see BMC AMI Unload for Db2 13.1.

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.
  • 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.
Information
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.

Related topic

Unload output file formats 

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC AMI Unload for Db2 12.1