Default language.

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 BMC AMI Load for Db2 13.1.

FORMAT (LOAD syntax)

FORMAT specifies the format of the input data in the SYSREC data set when your input data is in certain formats.

You can specify one of the following keywords:

UNLOAD

FORMAT UNLOAD is compatible with the IBM Db2 FORMAT UNLOAD. In the UNLOAD format, all variable length records are expanded to the maximum length. Each input row must contain a 6-byte record prefix. For more information, see OBID-integer.

(BMC.DB2.SPE2207) GENERATED ALWAYS columns under FORMAT UNLOAD

Loading of GENERATED ALWAYS columns from data specified in the SYSREC file is generally prohibited unless you provide one of the following option:

  1. The OBID parameter is coded
  2. The specific override type value coded in the OVERRIDE specification. 
Example

LOAD FORMAT UNLOAD INTO TABLE my.table  OBID nnnn 
Allows the loading of GENERATED ALWAYS columns including ROW CHANGE TIMESTAMP.

LOAD FORMAT UNLOAD OVERRIDE(ROWCHANGE) INTO TABLE my.table   
Allows the loading of GENERATED ALWAYS ROW CHANGE TIMESTAMP column only.


Important

If you are using an existing LOADPLUS job, be aware that this option might not function in the same way that it did in LOADPLUS.

CSV and DELIMITED

The CSV and DELIMITED options are functionally equivalent to each other, but they use different syntax. These options allow you to load data that has been unloaded in comma-separated-value (CSV) format. This data can originate from any product that generates CSV output, including BMC AMI Unload. In CSV-formatted data, a particular character separates each field from other fields. In most cases, a particular pair of characters encloses each non-numeric field.

For information specifically about CSV syntax, see CSV.

For information specifically about DELIMITED syntax, see DELIMITED.

Restrictions

The following restrictions apply when you specify FORMAT CSV or FORMAT DELIMITED:

  • LOBs are not supported.
  • Graphics are not supported.
  • (PTF BQU1353 applied) You must reference any DEFAULTIF or NULLIF clause by field name, not by position. Specifying a DEFAULTIF or NULLIF clause referenced by position causes a syntax error.

  • (PTF BQU1353 not applied) A NULLIF clause other than nil ('') is not supported in LOAD statement field specifications.

    Examples - PTF BQU1353 not applied

    The following syntax is supported:

    NULLIF A=''

    The following syntax is not supported:

    NULLIF A='NULL'

CSV

The following diagram shows CSV syntax:

LOAD_FORMAT_CSV_BQU1353.png

The following table describes CSV syntax:

CSV syntax

TERMINATEDBY

Specifies a single character used to delimit fields of data

The default is a comma (,). You cannot specify a null value for TERMINATEDBY.

FORMAT CSV TERMINATEDBY is equivalent to FORMAT DELIMITED COLDEL.

ENCLOSEDBY

Specifies a single character used on the left side to enclose non-numeric fields of data

The default is a double quotation mark (''). You can use an empty string to specify a null value for this option.

PTF BQU1353 not applied

Character strings that contain the character string delimiter repeat the character string delimiter where it is used in the character string. BMC AMI Load interprets any pair of character delimiters found between the enclosing character delimiters as a single character. For example, the phrase 'FRED''S JOB' is interpreted as 'FRED'S JOB'. BMC AMI Load recognizes these character delimiter pairs for only the CHAR and VARCHARfields.

FORMAT CSV ENCLOSEDBY is equivalent to FORMAT DELIMITED CHARDEL.

AND

This option specifies a single character used on the right side to enclose non-numeric fields of data. The value for the AND option must be the same as the value for the ENCLOSEDBY value. If you do not specify a value for the AND option, BMC AMI Load uses the value that you specified for the ENCLOSEDBY option.

When you specify FORMAT CSV, BMC AMI Load assumes that the values for the TERMINATEDBY, ENCLOSEDBY, and AND options are in EBCDIC format. 

Important

(PTF BQU1353 applied) To ensure that applications can read your CSV input, note the following considerations:

  • Because BMC AMI Unload does not duplicate escape characters, BMC AMI Load interprets unloaded data as is, without removing any escape characters.
  • When the input data specifies a non-EBCDIC encoding scheme, you do not need to specify CSV delimiters in hexadecimal notation.

(PTF BQU1353 applied) NULLIF

NULLIF is a field specification. You can specify NULLIF clauses in either hexadecimal or string notation. NULLIF must reference the field by name, not position. For more information, see NULLIF-fieldSelection .

(PTF BQU1353 applied) DEFAULTIF

DEFAULTIF is a field specification setting. You can specify DEFAULTIF clauses in either hexadecimal or string notation. DEFAULTIF must reference the field by name, not position. For more information, see DEFAULTIF-fieldSelection .

DELIMITED

You can use the following keywords with the FORMAT DELIMITED option.

FORMAT_DELIMITED_BQU1353.png

DELIMITED syntax

COLDEL

Specifies the column delimiter used in the input file

COLDEL is similar to TERMINATEDBY in CSV syntax.

The default value is a comma. For ASCII and UTF-8 data, this is X'2C'; for EBCDIC data it is X'6B'.

CHARDEL

Specifies the character string delimiter used in the input file

CHARDEL is similar to ENCLOSEDBY in CSV syntax.

BMC AMI Load interprets any pair of character delimiters found between the enclosing character delimiters as a single character. For example, the phrase 'FRED''S JOB' is interpreted as 'FRED'S JOB'. BMC AMI Load recognizes these character delimiter pairs for only the CHAR and VARCHAR fields.

Character string delimiters are required only when the string contains the CHARDEL character. However, you can put the character string delimiters around other character strings.

The default value is a double quotation mark. For ASCII and UTF-8 data this is X'22'; for EBCDIC data, it is X'3F'.

DECPT

Specifies the decimal point character used in the input file

The default value is a period. For ASCII and UTF-8, the default is X'2E'. The only allowed delimiters for DECPT are the period (.) or the comma (,), or their equivalents in ASCII or Unicode.

(PTF BQU1353 applied) NULLIF

NULLIF is a field specification setting. You can specify NULLIF clauses in either hexadecimal or string notation. NULLIF must reference the field by name, not position. For more information, see NULLIF-fieldSelection .

(PTF BQU1353 applied) DEFAULTIF

DEFAULTIF is a field specification setting. You can specify DEFAULTIF clauses in either hexadecimal or string notation. DEFAULTIF must reference the field by name, not position. For more information, see DEFAULTIF-fieldSelection.

DELIMITED

FORMAT DELIMITED specifies that the input data file is in a delimited format. When data is in a delimited format, all fields in the input data set are character strings or external numeric values. Each column in a delimited file is separated from the next column by a column delimiter character.

For each of the delimiter types that you can specify, you must ensure that the delimiter character is specified in the code page of the source data. You can specify the delimiter character as either a character or a hexadecimal constant. For example, to specify # as the delimiter, you can specify either COLDEL '#' or COLDEL X'23'. For Unicode and ASCII encodings, BMC recommends that you specify the delimiter character in the utility statement as a hexadecimal constant; otherwise, the result is unpredictable.

The following restrictions apply to FORMAT DELIMITED:

  • You cannot specify the same character for more than one type of delimiter (COLDEL, CHARDEL, or DECPT).
  • When you specify FORMAT DELIMITED, you cannot specify the CONTINUEIF or WHEN options.
  • BMC AMI Load ignores any specified POSITION statements within the field specification.

You can specify the following keywords with FORMAT DELIMITED:

COLDEL coldel

COLDEL specifies the column delimiter that is used in the input file. The default value is a comma (,). For ASCII and UTF-8 data, the default value is X'2C'. For EBCDIC data, the default value is X'6B'.

CHARDEL chardel

CHARDEL specifies the character string delimiter that is used in the input file. The default value is a double quotation mark ('). For ASCII and UTF-8 data, the default value is X'22'. For EBCDIC data, the default value is X'3F'.

The following considerations apply to the CHARDEL keyword:

  • To delimit character strings that contain the character string delimiter, repeat the character string delimiter where it is used in the character string. LOAD interprets any pair of character delimiters that are found between the enclosing character delimiters as a single character. For example, the phrase 'FRED''S JOB' is interpreted as 'FRED'S JOB'. The LOAD utility recognizes these character delimiter pairs for CHAR, VARCHAR, and CLOB fields only.
  • Character string delimiters are required only when the string contains the delimiter character. However, you can put the character string delimiters around other character strings.
  • Data unloaded in delimited format by BMC AMI Unload includes character string delimiters around all character strings.

DECPT decpt

DECPT specifies the decimal point character that is used in the input file. The default value is a period (.). The only allowed delimiters for DECPT are a period (.) or comma (,) (or their equivalents in ASCII or Unicode).

Note: The default decimal point character in a delimited file is a period (X'2E' in an ASCII or Unicode UTF-8 file).

Related topics

Basic-options

Syntax-diagrams-for-BMC-AMI-Load

FORMAT (UNLOAD syntax)

 

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