fieldSpecification

The field specification options determine the format of the input data.

Important

To see how the field specification syntax fits in the INTO TABLE option, see the diagram in INTO-TABLE.

The field specification options include the following specifications:

Field name, which is usually a column name in the table that you specify
Starting position of the field value in the input record
Data type, data format, and data length of the field
Other processing options

Requirements

The following table describes requirements that apply to field specifications:

Field specification requirements

Condition	Requirement
You are running a load job that will cause LOADPLUS to invoke DSNUTILB.	Ensure that the field specification is valid for both LOADPLUS and the IBM Db2 LOAD utility. The only exception is that LOADPLUS translates the TRIM option to the appropriate STRIP specification.
You are specifying POSITION.	Specify POSITION before the data type.
You want to load generated default values into system period start and end columns or a transaction start column.	Do not include a field specification for these columns.
The table contains columns defined as NOT NULL without a DEFAULT defined.	Include either no field specification at all (for any of the columns) or include field specifications for all of the NOT NULL columns that do not have a DEFAULT defined.
You specify one of the following options: FORMAT UNLOAD FORMAT BMC FORMAT BMCUNLOAD	Do not include any field specifications.
You specify FORMAT CSV.	Include a field specification for each input field in the order that the input field occurs in your input file. The exception to this requirement occurs when you do not want to load the last field or fields in the CSV file. LOADPLUS ignores any extra input fields at the end of your input file. To not load a field other than the last field in the CSV file, include a placeholder field specification for that input field. To have LOADPLUS interpret two consecutive delimiter characters as a null field, include the following NULLIF option in your field specification: NULLIF fieldName = '' Alternatively, you can elect to not include any field specifications. If you do not include any field specifications, LOADPLUS loads the data as if you had included a field specification that maps to every column that you are loading.
The table contains a row change timestamp column that is defined as GENERATED ALWAYS.	You cannot include this field in your field specification. However, you must include a field specification for at least one of the other input fields.

Not including field specifications

When you do not include any field specifications, note the following information:

LOADPLUS loads the data as if you had included a field specification that maps to every column that is defined on the table.
LOADPLUS expects your date, time, and timestamp data to be in external format.
LOADPLUS expects the format of all other data to be internal.

fieldName

In your field specification, you specify a field name for one of the following purposes:

To identify the data that you want to load into a column of the table
As a placeholder in the input record
To identify a field that you want to use with a predicate of a WHEN, NULLIF, or DEFAULTIF option

If the column corresponding to the named field uses a FIELDPROC, the FIELDPROC must be included in your LINKLIST, JOBLIB, or STEPLIB.

Requirements

The field name specification has the following requirements:

If you use the named field to specify the data that you want to load into a column and you specify SKIPFIELDS NO, the field name must match the corresponding column name.
If you use the named field only in the predicate of a WHEN, NULLIF, or DEFAULTIF option (and do not want to load data from that field) or only as a placeholder in the input record, the following requirements apply:
- You must specify SKIPFIELDS YES.
- The field name cannot be the name of a column in the table.
- The data type must be CHAR, VARCHAR, VARGRAPHIC, VARBINARY, or ROWID unless you specify SKIPFIELDS YES ALLTYPES (in which case the field can be any type).
- Except for variable-length fields, you must supply the length of the field, either with the POSITION(start:end) option or with the length option. The length must be less than or equal to 255.
  If the data type of the field is CHAR, you must explicitly specify a length, even if you use the POSITION option.
  If you use this field specification as a placeholder and you specify FORMAT CSV, you do not need to supply the length of the field. LOADPLUS ignores length specifications with FORMAT CSV.

POSITION(start:end)

This option defines the start (and end) position of the named field. Unless LOADPLUS is invoking DSNUTILB, you can abbreviate POSITION to POS. If you specify POSITION, it must appear before the data type on the field specification.

Important

For a DSNUTILB load, if you specify POS instead of POSITION, LOADPLUS terminates.

The variables start and end designate the locations of the first and last positions of the field. The first position of the record is column one. The start and end positions must be within the bounds of the input record.

Field length

The end position determines the length of the field, calculated as (end - start + 1). If you specify (start:end) on the POSITION option and you specify the data type, the length that is derived from (start:end) must match the explicit or implicit length of the input data type. When there is a conflict between these lengths, LOADPLUS issues message BMC50124E and the job terminates.

If you do not specify POSITION or if you do not specify end on the POSITION option when you specify the data type, LOADPLUS uses the field length to determine the length of the input value. BMC recommends that, when you specify the data type, you always specify the length or implied length from the associated column rather than specify end. To determine the field length from the data type length, see Supported-data-type-conversions-in-LOADPLUS.

CSV data

When loading CSV data, LOADPLUS currently starts from the first position of each field, ignoring any POSITION (start:end) specification. However, BMC recommends that you specify POSITION(*).

In the following descriptions, n and m are integers.

start

This option identifies the starting position of the field. The following table lists the valid values for start:

Value	Description
n	An actual column number; an absolute position in the input record
*	(default) A relative position one column after the end of the previous field
* + n	A relative position n + 1 columns after the end of the previous field

end

This option identifies the ending position of the field. The following table lists the valid values for end:

Value	Description
m	An actual column number; an absolute position in the input record You can use an absolute end position only with an absolute start position. The position that m represents must be greater than or equal to the position that n represents. The length of the field is m - n + 1.
*	(default) A relative position one column after the end of the current field The length of the field with an end position * and a start position * is 1.
* + m	A relative position m + 1 columns after the end of the current field The position that m represents must be greater than or equal to the position that n represents. You can use an end position * + m only with a start position of * or * + n. The length of the field is m - n + 1.

VALUE

The VALUE option tells LOADPLUS to load a constant value, CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP for the column or field.

Important

You can also use the VALUE option with DEFAULTIF.

Restrictions

You cannot specify VALUE for a column or field that is defined as any of the following data types:

BLOB, CLOB, DBCLOB
XML
ROWID
For ROWID, LOADPLUS uses a unique, generated value as the default.

constant

This value supplies a constant value for the specified column or field. The following table describes the requirements for specifying a constant with the VALUE option.

You can use a hexadecimal string for any data type. Hexadecimal strings have the following requirements:

The string must be enclosed in single quotes (for example, X'000C').
The number of bytes that the string represents must match the length of the column’s internal data. For a VARCHAR or VARBINARY column, the length must be the same length as the maximum length of the column.
For a VARCHAR or VARBINARY column, the string must include the two-byte hexadecimal length of the data.
The string must include a preceding null indicator if the column is nullable. For a VARCHAR or VARBINARY column, the length indicator must precede the null indicator.
The string must be FIELDPROC encoded.
The string must be in Db2 internal format.
When you are loading Unicode data, the string must be in the internal format of the table space.

Data type	Requirements
Numeric	You must use one of the following types of values: Integer Decimal Hexadecimal string (with the requirements listed in this topic) NULL (only if the column is nullable) LOADPLUS does not support floating-point constants.
Character	You must use one of the following types of values: Character string, enclosed in single quotes Hexadecimal string (with the requirements listed in this topic) NULL (only if the column is nullable)
Graphic	You must use one of the following types of values: Graphic string Hexadecimal string (with the requirements listed in this topic) NULL (only if the column is nullable)
Date, time, or timestamp	You must use one of the following types of values: Character string, enclosed in single quotes Hexadecimal string (with the requirements listed in this topic) NULL (only if the column is nullable) Precision on a timestamp constant must be less than or equal to the precision defined in the column.
Binary	You must use one of the following types of values: Hexadecimal string (with the requirements listed in this topic) Binary string Enclose the string in single quotes (for example, BX'000C').
Other	Not supported

CURRENT DATE

The CURRENT DATE option specifies the current system date. The column to which you are loading this value must be a DATE column.

All CURRENT DATE, TIME, or TIMESTAMP column values for a single row are derived from the same system clock value. For each row, LOADPLUS obtains a new system clock value.

For a two-phase load, LOADPLUS assigns the CURRENT DATE value to the column during the PRELOAD phase, not when actually loading the row during the LOAD phase.

CURRENT TIME

The CURRENT TIME option specifies the current system time. The column to which you are loading this value must be a TIME column.

All CURRENT DATE, TIME, or TIMESTAMP column values for a single row are derived from the same system clock value. For each row, LOADPLUS obtains a new system clock value.

For a two-phase load, LOADPLUS assigns the CURRENT TIME value to the column during the PRELOAD phase, not when actually loading the row during the LOAD phase.

CURRENT TIMESTAMP

The CURRENT TIMESTAMP option specifies the current system timestamp. The column to which you are loading this value must be a TIMESTAMP column.

All CURRENT DATE, TIME, or TIMESTAMP column values for a single row are derived from the same system clock value. For each row, LOADPLUS obtains a new system clock value.

For a two-phase load, LOADPLUS assigns the CURRENT TIMESTAMP value to the column during the PRELOAD phase, not when actually loading the row during the LOAD phase.

dataType

The data type option specifies the data type and, in most cases, data format and data length of the input value. For details regarding valid data types and information about using these data types, see Data-type-keywords.

If you specify options on the data type keywords, you must specify them in the order shown in the syntax diagram.

The following table describes how the data type specification operates:

Operation or condition	Details
Defaults	For a field with a corresponding column, if you do not specify a data type, LOADPLUS uses the following defaults: The default data type is the Db2 data type of the column. With the exception of date, time, and timestamp fields, the default data format is the internal format of that data type. For date, time, and timestamp fields, the default data format is external. For date, time, and timestamp fields, if you specify a data type but not a data format, the default data format is internal. The default length (or the precision and scale in the case of a DECIMAL column) is the column’s length (or precision and scale). For a field with no corresponding column, the default data type is CHARACTER(1).
Data conversion	If the field data type, format, and length are not identical to the column data type, format (always internal), and length, LOADPLUS performs a data conversion. See Supported-data-type-conversions-in-LOADPLUS for valid conversions. If the corresponding column is defined with a FIELDPROC, LOADPLUS applies the FIELDPROC to the input value after any data conversions.
Field length	You can specify a length along with the data type for most data types. In some cases, you must specify a length or POSITION(start:end) to indicate the length of the input field. In other cases, LOADPLUS uses the default length, or you cannot change the length. See Supported-data-type-conversions-in-LOADPLUS for the default lengths of the different data types.
CSV data	For CSV input data, LOADPLUS ignores any data type specification other than a date, time, or timestamp external type.

NULLIF

NULLIF is a field specification option. It determines whether the value that is assigned to a column is null, based on the condition that you specify. If the condition is satisfied, LOADPLUS assigns null to the column. The column must be nullable.

You can specify one of the following values:

Value	Description
predicate	For complete information, see predicates.
ERROR	ERROR indicates that you want LOADPLUS to assign null to the column if a conversion error occurs. If you specify MSGLEVEL(1) on your EXEC statement or in your installation options, LOADPLUS issues message BMC51501E to indicate the conversion error.

Restrictions

The following restrictions apply to the NULLIF option:

You cannot use an identity column in the predicate of a NULLIF statement on the field specification of another column.
When you specify (start:end), all constants must be of the same type (for example all character or all hexadecimal) or must have the same length.

Additional considerations

The following additional considerations apply to the NULLIF option:

You can use NULLIF only on a field specification that corresponds to a column.
If you specify multiple NULLIF options, LOADPLUS uses the first matching condition.
The RULES installation option affects the ways in which WHEN, NULLIF, and DEFAULTIF work. For details, see RULES-installation-option.

DEFAULTIF

DEFAULTIF is a field specification option that determines whether the value that is assigned to a column is the default, based on the condition that you specify. If the condition is satisfied, LOADPLUS assigns a default value to the column. The column does not have to be defined NOT NULL WITH DEFAULT.

You can specify one of the following values:

Value	Description
predicate	For complete information, see predicates.
ERROR	ERROR indicates that you want LOADPLUS to assign the default value to the column if a conversion error occurs. If you specify MSGLEVEL(1) on your EXEC statement or in your installation options, LOADPLUS issues message BMC51501E to indicate the conversion error. Warning Important Use of DEFAULTIF ERROR on a nullable column never results in a null value. The column always defaults to the column’s data type. If you want a null value for a nullable column when a conversion error occurs, use the NULLIF option.
VALUE	VALUE specifies the default value to use. The default DEFAULTIF VALUE is the same as those described by the Db2 SQL CREATE TABLE statement. The syntax for this VALUE option is the same as the syntax for VALUE as it is used for specifying a constant value for a field or column.

Restrictions

The following restrictions apply to the DEFAULTIF option:

LOADPLUS terminates if you specify DEFAULTIF on a LOB or XML column.
You cannot use an identity column in the predicate of a DEFAULTIF statement on the field specification of another column.
When you specify (start:end), all constants must be of the same type (for example all character or all hexadecimal) or must have the same length.

Additional considerations

The following additional considerations apply to DEFAULTIF:

To obtain a null value with DEFAULTIF on a nullable column, specify DEFAULTIF predicate VALUE(NULL). If you do not specify VALUE with a null indication, the column defaults to the column’s data type. Alternatively, you can use the NULLIF option to obtain the same results.
Warning
Important
You cannot obtain a null value with DEFAULTIF ERROR.
The RULES installation option affects the ways in which WHEN, NULLIF, and DEFAULTIF work. For details, see RULES-installation-option.

fieldSpecification

Requirements

Not including field specifications

fieldName

Requirements

LOADPLUS for DB2 11.2

On this page