Data type keywords (for BMC AMI Load)

Use the following data type keywords to identify the input data format.

The data type of the input data does not have to correspond to the data type of the target Db2 table column if the conversion between the input data type and the target data type is allowed, as described in Data type conversions.

CHARACTER

CHARACTER defines a fixed-length character field. You can optionally specify a length attribute with CHARACTER. You can abbreviate this keyword as CHAR.

BMC AMI Load accepts all characters in a CHARACTER field. BMC AMI Load does not remove leading or trailing blanks.

You can optionally specify MIXED (indicating mixed character set data) or BIT (indicating BIT data).

TRIM

(SPE2104) TRIM truncates trailing blanks from the input string value before BMC AMI Load attempts to assign the value to a CHAR or VARCHAR column. If the string is still too long, a conversion error occurs. BMC AMI Load performs TRIM before TRUNCATE.

TRUNCATE

(SPE2104) TRUNCATE truncates a string if it is longer than the column length (after trimming). BMC AMI Load performs TRUNCATE after TRIM. When loading Unicode data, AMI LOAD performs truncation after translation.

VARCHAR

VARCHAR defines a varying-length character field. A two-byte length field that tells BMC AMI Load the length of the column must precede the data. The length field specifies the data length of the data, not including the two-byte length field or any padding characters.

You can optionally specify MIXED (indicating mixed character set data) or BIT (indicating BIT data).

BMC AMI Load does not remove leading or trailing blanks.

TRIM

(SPE2104) TRIM truncates trailing blanks from the input string value before BMC AMI Load attempts to assign the value to a CHAR or VARCHAR column. If the string is still too long, a conversion error occurs. BMC AMI Load performs TRIM before TRUNCATE.

TRUNCATE

(SPE2104) TRUNCATE truncates a string if it is longer than the column length (after trimming). BMC AMI Load performs TRUNCATE after TRIM. When loading Unicode data, AMI LOAD performs truncation after translation.

GRAPHIC

GRAPHIC defines a fixed-length graphic field. BMC AMI Load supports the GRAPHIC data type in the same way that Db2 supports it.

VARGRAPHIC

VARGRAPHIC defines a variable-length graphic field. BMC AMI Load supports the VARGRAPHIC data type in the same way that Db2 supports it.

SMALLINT

SMALLINT indicates a two-byte signed integer.

INTEGER

INTEGER indicates a four-byte signed integer. You can abbreviate this keyword as INT.

INTEGER EXTERNAL

INTEGER EXTERNAL defines a field that contains a character representation of integer data. The data may have a plus or minus sign preceding or following the data.

Leading or trailing blanks or blanks between the sign and the number are allowed. BMC AMI Load removes them during conversion to internal data types.

You can optionally specify a length attribute with INTEGER EXTERNAL. You can abbreviate EXTERNAL as EXT.

BIGINT

BIGINT indicates an eight-byte binary number. Negative numbers are in two's complement notation.

DECIMAL or DECIMAL PACKED

DECIMAL or DECIMAL PACKED indicates the input data is in a packed decimal format that conforms to the rules of packed decimal numbers. BMC AMI Load considers DECIMAL and DECIMAL PACKED data types to be identical, and treats them the same for conversion purposes. You may abbreviate DECIMAL as DEC.

With PTF BQU2139 applied, you can specify a precision and a scale.

• Precision (P) is a number between 1 and 31 (both included) to signify the precision of the input field. BMC AMI Load ignores this precision if a starting and ending POSITION is also specified in the field selection.
• Scale (S) is a number between 0 and 31 (both included), with a value not larger than P, to signify the scale of the input number.

DECIMAL ZONED

DECIMAL ZONED indicates that the input data is in the zoned format. For example:

ZN stands for zone and number. The zone character may be any EBCDIC character and is ignored for the purposes of conversion. The last byte, SN, stands for sign and number. The sign character (highest four bits) must be in the range A-F . Characters A, C, E, and F represent a positive sign; characters B and D represent a negative sign.

With PTF BQU2139 applied, you can specify a precision and a scale. 

• Precision (P) is a number between 1 and 31 (both included) to signify the precision of the input field. BMC AMI Load ignores this precision if a starting and ending POSITION is also specified in the field selection.
• Scale (S) is a number between 0 and 31 (both included), with a value not larger than P, to signify the scale of the input number.

DECIMAL ZONED is the only numeric format for external data in which leading and trailing blanks are not removed.

DECIMAL EXTERNAL

All rules for data formats that apply to INTEGER EXTERNAL also apply to DECIMAL EXTERNAL, with the exception that the numeric portion of the input field can contain a decimal point.

The (length,scale) qualifier is optional. The length field designates the input field length. The scale field designates the implied scale of the decimal point in the input data. A decimal point in input data overrides the implied scale specification of the DEC EXT designation.

BINARY

(PTF BQU2139 applied) BINARY defines a fixed-length binary string.

(length)

Length indicates the number of single-byte characters in this field.

TRIM

TRIM truncates trailing binary zeros from the input string value before BMC AMI Load attempts to assign the value to a BINARY column. If the string is still too long, a conversion error occurs. BMC AMI Load performs TRIM before TRUNCATE.

TRUNCATE

TRUNCATE truncates a string if it is longer than the column length (after trimming). BMC AMI Load performs TRUNCATE after TRIM.

VARBINARY

(PTF BQU2139 applied) VARBINARY indicates a varying-length binary string.

A two-byte length field precedes the string. This length field contains the length of the binary string. The length is the number of single-byte characters and does not include the two length bytes.

You can optionally specify this data type as BINARY VARYING.

TRIM

TRIM truncates trailing binary zeros from the input string value before BMC AMI Load attempts to assign the value to a VARBINARY column. If the string is still too long, a conversion error occurs. BMC AMI Load performs TRIM before TRUNCATE.

TRUNCATE

TRUNCATE truncates a string if it is longer than the column length (after trimming). BMC AMI Load performs TRUNCATE after TRIM.

FLOAT, REAL, or DOUBLE PRECISION

These data types indicate four- or eight-byte signed data that conforms to the format of S/370-S/390 floating point numbers. A designation of REAL denotes a length of four bytes; a designation of DOUBLE PRECISION denotes a length of eight bytes. You can shorten DOUBLE PRECISION to DOUBLE.

FLOAT EXTERNAL

FLOAT EXTERNAL allows the most flexibility of input formats of numeric data. The rules for data formats that apply to DECIMAL EXTERNAL apply also to FLOAT EXTERNAL, and the input number may be in scientific or exponential notation. Blanks can be anywhere except in the middle of the numeric portion of the data.

BMC AMI Load considers the examples to be valid FLOAT EXTERNAL values:

DECFLOAT

DECFLOAT indicates a 64-bit or 128-bit decimal floating-point number. The length value must be either 16 or 34. If the length is 16, the number is in 64-bit decimal floating-point number format. If the length is 34, the number is in 128-bit decimal floating-point format. If the length is not specified, the number is in 128-bit decimal floating-point format.

DECFLOAT EXTERNAL

DECFLOAT EXTERNAL indicates a character string that represents a number. The format is an SQL numeric constant. If you do not specify a length, BMC AMI Load determines the string length from the POSITION specification. If you do not specify a length or POSITION, BMC AMI Load uses the DECFLOAT default length.

DATE EXTERNAL

DATE EXTERNAL indicates a character string that represents a date. The default length is 10 characters. Date formats may be in any of the following formats:

BMC AMI Load calculates the input data format for DATE at each occurrence of a DATE field in the input data. Therefore, you may mix formats in your input data.

DATE-2E EXTERNAL

(PTF BQU2139 applied) DATE-2E EXTERNAL indicates a character string that represents a date. The default length is 8 bytes. DATE-2E supports the following format:

DATE-3E EXTERNAL

(PTF BQU2139 applied) DATE-3E EXTERNAL indicates a character string that represents a date. The default length is 7 bytes. DATE-3E supports the following format:

DATE-D2 EXTERNAL

(PTF BQU2139 applied) DATE-D2 EXTERNAL indicates a character string that represents a date. The default length is 8 bytes. DATE-D2 supports the following format:

DATE-D5 EXTERNAL

(PTF BQU2139 applied) DATE-D5 EXTERNAL indicates a character string that represents a date. The default length is 8 bytes. DATE-D5 supports  the following format:

DATE-D5E EXTERNAL

(PTF BQU2139 applied) DATE-D5E EXTERNAL indicates a character string that represents a date. The default length is 10 bytes. DATE-D5E supports the following format:

TIME EXTERNAL

TIME EXTERNAL indicates a character string that represents a time of day. The default length is 8 bytes. Time formats may be in any of the following formats:

BMC AMI Load calculates the TIME input data format at each occurrence of a TIME field in the input data. Therefore, you may mix formats in your input data.

TIME-1   EXTERNAL

(PTF BQU2139 applied) TIME-1 EXTERNAL indicates a character string that represents a time of day. The default length is 6 bytes. TIME-1 supports the following format:

TIME-2   EXTERNAL

(PTF BQU2139 applied) TIME-2 EXTERNAL indicates a character string that represents a time of day. The default length is 4 bytes. TIME-2 supports the following format:

TIME-D1  EXTERNAL

(PTF BQU2139 applied) TIME-D1 EXTERNAL indicates a character string that represents a time of day. The default length is 8 bytes. TIME-D1 supports the following format:

TIMESTAMP EXTERNAL

TIMESTAMP EXTERNAL indicates a character string that represents a time stamp. The default length is 26 bytes. Valid lengths are 16 through 26 bytes. Time stamps have the following format:

The nnnnnn microseconds value is optional. If not specified, it defaults to 000000. If the nnnnnn value has trailing zeroes, you can omit them.

You can omit leading zeroes from the month (mm), day (dd), and hour (hh) subfields.

TIMESTAMP-1E  EXTERNAL

(PTF BQU2139 applied) TIMESTAMP-1E EXTERNAL indicates a character string that represents a time stamp. The default length is 14 bytes. TIMESTAMP-1E supports the following format:

TIMESTAMP-2E  EXTERNAL

(PTF BQU2139 applied) TIMESTAMP-2E EXTERNAL indicates a character string that represents a time stamp. The default length is 21 bytes. TIMESTAMP-2E supports the following format:

*nnnnnnn can be any number of microseconds from 1 through 12.

TIMESTAMP WITH TIME ZONE EXTERNAL

TIMESTAMP WITH TIME ZONE EXTERNAL indicates a character string that represents a time stamp with a time zone indication. The default length is 33 bytes. Valid lengths are 26 through 39 bytes. Time stamps with time zones have the following format:

The nnnnnn microseconds value is optional. If not specified, it defaults to 000000. If the nnnnnn value has trailing zeroes, you can omit them.

You can omit leading zeroes from the month (mm), day (dd), and hour (hh) subfields.

ROWID

ROWID indicates a hexadecimal number that represents a Db2 internal row ID.

CLOB

CLOB indicates a character-based large object (LOB).

BLOB

BLOB indicates a binary-value-based LOB.

DBCLOB

DBCLOB indicates a double-byte character-based LOB.