BMC AMI Load FORMAT

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.

Related topics

Basic-options

BMC-AMI-Load-Syntax-diagrams

FORMAT (UNLOAD syntax)

 GENERATED ALWAYS columns under FORMAT UNLOAD

(BMC.DB2.SPE2207)

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.
  • 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.

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.

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

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.

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 .

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.

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.

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.

 

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