VARIABLE block syntax and description

Use the VARIABLE block to indicate that the output data set must be compatible with the Db2 LOAD data set.

The default format of the output data set is variable block (VB), but you can specify fixed (F), fixed block (FB), or variable (V) in the JCL. Db2 HPU determines the LRECL at run time based on the following rules:
  • If you specified RECFM=F or RECFM=FB, the LRECL must be larger than or equal to the sum of the lengths of the fields.
  • If you specified RECFM=V, the LRECL must be larger than or equal to the sum of the lengths of the fields plus 4 bytes.

The length of variable-length fields is the sum of their maximum length plus 2 bytes.

The following syntax diagram shows the VARIABLE block:

VARIABLE block
Read syntax diagramSkip visual syntax diagramENDALLLIKEtable-name
END
The characteristics and the sequence of fields in the output data set correspond to the characteristics and the sequence of fields in the SELECT statement.

The fields in the data set are also like the fields in the data set in DSNTIAUL format except for the following differences:

  • The DATE, TIME, and TIMESTAMP columns are in ISO format and correspond to the following formats:
    DATE
    YYYY-MM-DD
    TIME
    HH.MM.SS
    TIMESTAMP
    YYYY-MM-DD-HH.MM.SS.NNNNNN
  • If a column accepts nulls, the null indicator is generated at the beginning of the field. This indicator contains the value X'FF' if the field is null and X'00' if the value is usable.
  • If the last-selected column is variable, the output data set is VB, and this column is written only on its effective length. The two length bytes are placed before the column.

You can override the default DATE, TIME, and TIMESTAMP formats by specifying an OPTIONS block at the SELECT level. Only an OPTIONS block at the SELECT level is considered for this format.

ALL
All the variable columns are written using their actual length.
LIKE table-name
If the LIKE table-name clause is specified, Db2 HPU uses the characteristics of the table model as parameters and formats the data set to allow this table to be loaded.

The sequence of columns in the SELECT statement must match the columns in the model table. If format conversions are required, they follow Db2 HPU format rules.

The table-name must be in the format user_ID.TABLE. If the user ID is not specified, the name of the user (CURRENT SQLID) is used.

Field types are provided by the table model. A null-byte indicator is generated at the end of the field if the column of the table model can be null.

If a column in the SELECT statement is null and if the corresponding column in the table model does not allow nulls, the field in the output sequential data set has one of the following standard default values:

  • 0 for numeric fields (INTEGER, SMALLINT, DECIMAL, FLOAT)
  • A string of blank characters (CHAR, GRAPHIC)
  • Two bytes of zero length (VARCHAR, LONG VARCHAR, VARGRAPHIC, LONG VARGRAPHIC)
  • 0001-01-01 (DATE)
  • 00.00.00 (TIME)
  • 0001-01-01-00.00.00.000000 (TIMESTAMP)

You can override the default DATE, TIME, and TIMESTAMP formats by specifying an OPTIONS block at the SELECT level. Only an OPTIONS block at the SELECT level is considered for this format.