Limited supportBMC 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 UNLOAD PLUS for DB2 13.1.

field specification

The field specification option defines a field in the output record.

field_spec1.png

field_spec2.png

The field specification identifies the following information:

  • The field name
  • (optional) The data type, data format, and data length of the field
  • (optional) The value to assign to the field under certain conditions

You do not have to specify a field for each SELECT list item, but, if you do not include a field specification for an item, you must specify a comma (,) as a place holder. In this case, the description of the field defaults to that of the SELECT list item. If the INTO list has too many fields, UNLOAD PLUS terminates. If the INTO list has too few, the fields default to that of the SELECT list item.

Restrictions

Note the following restrictions when including a field specification:

  • NAME and RECORDID are reserved words that you cannot use to name fields. If you want to use SQL reserved words or UNLOAD PLUS command options as object names, you must delimit them with quotation marks to prevent syntax errors.
  • If you are using FORMAT BMCLOAD, you cannot use the field specification list.
  • If you include a field specification and you also specify FORMAT CSV with the RTRIM option, UNLOAD PLUS ignores the RTRIM option for the associated column when any of the following conditions exists:
    • The column is defined with a data type other than CHAR or VARCHAR.
    • For a CHAR or VARCHAR column
      • You specify a length, but the specified length differs from the actual length of the data.
      • You specify any of the other keywords allowed for a CHAR or VARCHAR column.

  • If you specify SELECT CURRENT RID, we recommend that you do not include a field specification for the RID field. Instead, specify a comma as a placeholder.

    If you choose to include a field specification, the data type must be CHAR with a length of 4 or 5.

  • See SmartSwitch limitations and restrictions regarding data type processing via INTO.

fieldName

The field name identifies a field in the output record. The field name does not have to match the corresponding column name in the SELECT list if a column name is used. If the column is nullable and you do not specify the IF NULL option, a one-byte character field follows immediately after the specified field. This one-byte field is set to X'00' if the value is not null and is set to a question mark (?) if it is null.

dataType

The data type option specifies the data type and format of the output value and can also indicate the length of the output value. If you do not specify the data type of the field, UNLOAD PLUS uses the data type and data length of the column or constant from the SELECT list. UNLOAD PLUS performs no conversions in this case, except for denormalization and FIELDPROC decoding.

The default data format depends on the FORMAT option that is in effect. For example, when FORMAT STANDARD (the default) is in effect, the default data format is DB2 internal, except for date, time, and timestamp columns, which default to external format.

For details about valid data types and general rules for data types, see Data-type-keywords. For allowable conversions, see Supported-data-type-conversions.

IF

The IF option determines the value of the field based on the specified condition.

Restrictions

The following restrictions apply to the IF option:

  • UNLOAD PLUS terminates when you specify the IF option and you are unloading LOB or XML data.
  • UNLOAD PLUS does not support CURRENT DATE, CURRENT TIMESTAMP, and LIKE for the IF predicate option.
  • When DIRECT NO is in effect, you can use only the equal or not equal comparison operators to compare the following types of columns:
    • Numeric
    • DATE in USA or EUR formats
    • TIME in USA format
  • When DIRECT NO is in effect, date or timestamp constants must use a forward slash (/) as the date separator.

Additional considerations

Although UNLOAD PLUS uses the standard rules for comparison with the majority of predicate block constants, DIRECT YES and DIRECT NO have different behaviors when used with the IF predicate:

  • When using DIRECT YES, IF predicate comparisons are made against the raw Db2 row data and predicate evaluation behavior is standard.
  • When using DIRECT NO, IF predicate comparisons are made against the Db2 dynamic SQL host variable data.

Depending on your subsystem configuration, some IF predicate comparison results will differ between DIRECT YES and DIRECT NO, specifically those that involve the following types of comparisons:

  • DATE, TIME, and TIMESTAMP fields that are subject to local formatting options
  • FOR BIT DATA fields and hexadecimal constants in an environment where more than one character code set is used

NULL

The IF NULL option determines the value of the field if the column value is null. The column must be nullable; if you specify IF NULL on the field, UNLOAD PLUS does not generate a null indicator on the field. If you use the IF NULL option, you cannot use the NULL value or any related predicates.

ERROR

The IF ERROR option determines the value of the field if an error occurs when converting the column value to the field’s data type.

predicate

The IF predicate option determines the value of the field if UNLOAD PLUS determines that the predicate is true. See the description of the WHERE predicate option for rules about specifying the predicate.

VALUE

This option specifies that you want to place a constant value, null, current date, current time, or current time stamp in the field if the condition is true.

constant

You can use only integer, decimal, character string, hexadecimal string, binary string or graphic string constants. UNLOAD PLUS does not support floating-point constants. The constants must match the field’s data type (that is, numeric to numeric, string to string, and date/time to date/time). However, you can use a hexadecimal string with a binary field.

Additional considerations

Note the following considerations:

  • Character, hexadecimal, binary, and graphic string constants cannot exceed 255 bytes.
  • Decimal string constants require a decimal point.
  • If you use CHARACTER for a numeric or date, time, or timestamp column, the value’s data type must also be CHARACTER. Conversely, if you use type EXTERNAL, the constant’s value must be a value that is compatible with the data type.

NULL

This option specifies that you want a NULL value for the field if the condition is true. You may use NULL only if the corresponding column is nullable and you did not include IF NULL on the field specification.

CURRENT DATE

This option specifies the current date. The field must be the DATE data type.

CURRENT TIME

This option specifies the current time. The field must be the TIME data type.

CURRENT TIMESTAMP

This option specifies the current timestamp. The field must be the TIMESTAMP or TIMESTAMP WITH TIME ZONE data type.

You cannot specify CURRENT TIMESTAMP WITH TIME ZONE. However, if you are unloading to a TIMESTAMP WITH TIME ZONE column, UNLOAD PLUS includes the time zone from the DSNHDECP IMPLICIT_TIMEZONE value.

Related topic

SELECT-options

 

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