BMC AMI Unload FORMAT

The FORMAT option allows you to specify the format of unloaded data. All SELECT statements of all rows use the specified format. 


Related topics

Option descriptions—UNLD syntax

UNLD-syntax-diagrams

SELECT-statement-options

FORMAT_UNLOAD_SPE2110.png

You can override the specified format for a field by specifying an explicit format for the field.

For more information about these options, see Output-file-formats.

(Default) STANDARD

STANDARD tells BMC AMI Unload to unload data of all data types in internal format except for the date, time, and timestamp data types, which it unloads in external format.

If you specify FORMAT STANDARD with DIRECT YES, BMC AMI Unload unloads string constants in SELECT statements as fixed-length strings. If you specify FORMAT STANDARD with DIRECT NO, BMC AMI Unload unloads string constants in SELECT statements as variable-length strings.

Important

To produce unloaded data that matches the IBM DSNTIAUL program, specify FORMAT DSNTIAUL.

EXTERNAL

EXTERNAL tells BMC AMI Unload to unload data for all data types in external format. If you do not specify a data type, BMC AMI Unload uses the default length. Use this option only when applications require data in this format, or when you are loading the data to a non-MVS target and you cannot use one of the other format options.

If you specify FORMAT EXTERNAL with DIRECT YES, BMC AMI Unload unloads string constants in SELECT statements as fixed-length strings. If you specify FORMAT EXTERNAL with DIRECT NO, BMC AMI Unload unloads string constants in SELECT statements as variable-length strings.

BMC AMI Unload terminates if you specify this option when you are unloading LOB or XML data.

INTERNAL

INTERNAL tells BMC AMI Unload to unload data in internal format. This option applies mainly to the date, time, and timestamp data types and is useful only if you subsequently use LOADPLUS to load the data.

Important

FORMAT INTERNAL is completely different than FORMAT INTERNAL implemented in IBM Unload. BMC AMI Load does not load BMC AMI Unload FORMAT INTERNAL.

DSNTIAUL

DSNTIAUL allows BMC AMI Unload to use its high-performance direct access to produce an output file in which the unloaded data matches the output of the IBM DSNTIAUL program.

Unlike the DSNTIAUL program, BMC AMI Unload (using DIRECT YES) supports ASCII and Unicode output.

When you specify FORMAT DSNTIAUL, BMC AMI Unload performs the following functions:

  • Provides data for all data types in internal format except for the date, time, and timestamp data types, which it provides in external format
  • Pads all variable-length fields (VARCHAR-keyword, VARGRAPHIC, and VARBINARY) with X'00' to their maximum length

  • Creates fixed-length output records with a maximum length of 32760 bytes

    Important

    If BMC AMI Unload processes a LONG VARCHAR or LONG VARGRAPHIC column type, the maximum formatted row length may exceed the allowable output record length of 32760. You can use the INTO option with a field specification to reduce the length of the LONG column and avoid this error. To reduce the number of records that BMC AMI Unload discards when they exceed the shortened length, specify the TRIM or TRUNCATE function to remove trailing spaces, or truncate the field to fit.

  • Treats a string constant as variable length
  • Puts a one-byte null indicator field after all nullable fields

The null indicator field contains either X'6F' if the field is null or X'00' if it is not null.

BMC AMI Unload unloads stand-alone constant strings in SELECT statements as variable-length strings, regardless of whether you select DIRECT YES or DIRECT NO.

Example
SELECT 'YES', IFNULL(COL1,'X'),COL2 CONCAT 'NO'

BMC AMI Unload treats the constants in this SELECT statement as follows:

Constant

Type

'YES'

VARCHAR(3)

'X'

Fixed character (1)

'NO'

Fixed character (2)

BMC AMI Unload treats constant strings coded as part of SQL functions as fixed character strings.

For more information about DSNTIAUL format, see DSNTIAUL-format.

Restrictions

Note the following restrictions when you specify FORMAT DSNTIAUL:

  • You cannot use this option when unloading LOB or XML data. BMC AMI Unload terminates in this case.
  • When you specify FORMAT DSNTIAUL, BMC AMI Unload sets the following options to the values shown:

UNLOAD

The following considerations apply to FORMAT UNLOAD:

  • Do not use DIRECT NO or DIRECT AUTO as BMC AMI Unload has not encountered any conditions that cause it to switch to DIRECT YES. It can result in a file that is unusable by BMC AMI Load.
  • You cannot specify an INTO statement when you specify FORMAT UNLOAD. For more information, see INTO.

If you specify a SELECT statement that uses a subset of columns in the table, you cannot reload the results into any table. Use this type of SELECT statement with FORMAT UNLOAD only to produce an output file that you can then reference in a WHERE or WHEN clause of a discard specification in BMC AMI Reorg, as shown in the following example:

//DDname DD DISP=SHR,DSN=filename
DISCARD FROM TABLE <tableName>WHERE(<columnName>
IN(FILE(<DDname>)FORMAT(UNLOAD)))

CSV and DELIMITED

The CSV and DELIMITED options are functionally similar and produce similar output, but they use different syntax.

For information specifically about CSV syntax, see CSV.

For information specifically about DELIMITED syntax, see DELIMITED.

Specify FORMAT CSV or FORMAT DELIMITED to produce an output file in which the unloaded data is in comma-separated-value (CSV) format. This format enables porting to other databases and applications. When you specify FORMAT CSV or FORMAT DELIMITED, BMC AMI Unload formats the unloaded data so that a specific character separates each field from other fields, and encloses each non-numeric field with a specific pair of characters.

When you specify FORMAT CSV or FORMAT DELIMITED, BMC AMI Unload performs the following functions:

  • Produces all data in external format (even if you specify an explicit data type)
  • Creates all fields as variable length except for those fields for which you specify a data type and length

Considerations

Note the following considerations when you specify FORMAT CSV or FORMAT DELIMITED:

  • If you code CNTLCARDS BMCLOAD, BMC AMI Unload generates the LOAD statement using the FORMAT CSV option. If you do not code CNTLCARDS BMCLOAD, BMC AMI Unload does not generate the LOAD statement. 

    The following examples of syntax generate LOAD FORMAT CSV because they both include CNTLCARDS BMCLOAD:

    • UNLOAD FORMAT CSV CNTLCARDS BMCLOAD
    • UNLOAD FORMAT DELIMITED CNTLCARDS BMCLOAD
    For more information, see CNTLCARDS.

  • FOR BIT DATA values are supported.
  • NULLIF X='NULL' is generated for NULLABLE columns when FORMAT CSV is coded.
  • FORMAT CSV and FORMAT DELIMITED support the WHEN clause referenced by position or field name. The WHEN clause referenced by position evaluates the condition before parsing the input record. The WHEN clause referenced by field name evaluates the condition after parsing the input string and the removal of delimiters.
  • Support BINARY and VARBINARY type columns with FORMAT DELIMITED.

Restrictions

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

  • LOBs are not supported.
  • Graphics are not supported.
  • You cannot use FORMAT CSV or FORMAT DELIMITED with the UNLOAD TABLESPACE command.

  • If you specify AUTOTAG YES, BMC AMI Unload  does not generate a WHEN clause in the LOAD statement, but generates a field at the beginning of the record following the word "WHEN". 

    Important

    To use the control cards generated by BMC AMI Unload with AUTOTAG YES, you must code IGNOREFIELDS YES on the load command.

  • A NULLIF clause other than nil ('') is not supported in LOAD statement field specifications.
  • BITDATA type columns are not supported with FORMAT CSV.
  • BINARY and VARBINARY type columns are not supported with FORMAT CSV.

CSV

When you use more than one option, CSV syntax must follow the sequence in the following syntax diagram:

LOAD_FORMAT_CSV_doc defect.png

CSV syntax

TERMINATEDBY

Specifies a single character that BMC AMI Unload uses to delimit fields of data

The default is a comma (,).

FORMAT CSV TERMINATEDBY is equivalent to FORMAT DELIMITED FORMAT COLDEL.

Important

If you run CSV on a DECIMAL=COMMA sub-system and TERMINATEDBY character is same as the decimal-point character, a syntax error occurs. Make sure that you specify a suitable TERMINATEDBY character such as a semi-colon (;).

ENCLOSEDBY

Specifies a single character that BMC AMI Unload uses on the left side to enclose non-numeric fields of data

The default is a double quotation mark ('').

FORMAT CSV ENCLOSEDBY is similar to FORMAT DELIMITED CHARDEL.

AND

This option specifies a single character that BMC AMI Unload uses 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 Unload uses the value that you specified for the ENCLOSEDBY option.

Apply the ENCLOSEDBY character on DATE, TIME, or TIMESTAMP fields. For more information, see External-DATE-TIME-and-TIMESTAMP-formats. BMC AMI Load does not interpret a pair of character delimiters found between the enclosing character delimiters as a single character.

NULLSTRING

Specifies a string (maximum of 4 bytes) that indicates a null value in the output file

You can specify NULLSTRING as an empty string. The default value is NULL.

The NULLSTRING value coded on the UNLOAD command determines the value of the NULLIF value in the LOAD command. If you want to use a non-EBCDIC encoding scheme, BMC AMI Unload translates the value to that encoding scheme and uses the value as the null indicator of the unloaded data.

RTRIM

RTRIM tells BMC AMI Unload to strip trailing blanks from input character strings. With the exceptions described in the following restrictions, BMC AMI Unload performs this operation for all CHAR and VARCHAR columns that you are unloading.

Restrictions

BMC AMI Unload ignores the RTRIM option for a column when any of the following conditions exists:

  • The column is defined as a data type other than CHAR or VARCHAR.
  • The column’s field specification includes any of the following keywords:
    • MIXED
    • TRIM
    • TRUNCATE
    • FILL

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

Important

To ensure that applications can read your CSV output, note the following considerations:

  • BMC AMI Unload does not duplicate escape characters.
  • When you are unloading data to a non-EBCDIC encoding scheme, you do not need to specify CSV delimiters in hexadecimal notation. 

DELIMITED

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

FORMAT_DELIMITED_BQU1353.png

DELIMITED syntax

COLDEL

Specifies the column delimiter to use in the unload file

COLDEL is equivalent to TERMINATEDBYif you specify 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'.

Important

If you run COLDEL on a DECIMAL=COMMA sub-system and the COLDEL character is same as the decimal-point character, a syntax error occurs. Make sure that you specify a suitable COLDEL character such as a semi-colon (;).

CHARDEL

Specifies the character string delimiter to use in the unload file

CHARDEL is similar to ENCLOSEDBYif you specify CSV syntax.

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 CHARACTER and VARCHAR-keyword 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 to use in the unload 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 UTF-8.

Restrictions

NGTU783E DELIMITED and FIXEDVARCHAR YES are mutually exclusive.

 

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