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 UNLOAD PLUS for DB2 13.1.

FORMAT

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

FORMAT.png

You can override the specified format for a field by specifying an explicit format for the field. For more information, see field-specification.

For more information about using the different output formats and the restrictions associated with each option, see Output-format.

STANDARD

STANDARD (the default) tells UNLOAD PLUS 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.

Important

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

EXTERNAL

EXTERNAL tells UNLOAD PLUS to unload data for all data types in external format. If you do not specify a data type, UNLOAD PLUS uses the default length.

UNLOAD PLUS terminates if you specify this option when you are unloading LOB or XML data.

INTERNAL

INTERNAL tells UNLOAD PLUS 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. UNLOAD PLUS does not invoke date and time exit routines.

For restrictions associated with this option, see Db2-output-with-internal-date-and-time-FORMAT-INTERNAL.

BMCLOAD

This option is valid only when DIRECT YES is in effect.

BMCLOAD produces a high speed unload and an output file that only LOADPLUS can use when you are moving data to tables with a nearly identical table structure. This option is useful for migrating identical data to duplicate tables or development databases.

Using FORMAT BMCLOAD instead of standard processing may help reduce CPU cycles and elapsed time. However, complicated table structures can cause variations in the CPU cycles and elapsed time.

For additional information, including restrictions associated with this option, see Data-migration-using-LOADPLUS-FORMAT-BMCLOAD.

DSNTIAUL

DSNTIAUL allows UNLOAD PLUS 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. For additional information, including restrictions associated with using this option, see DSNTIAUL-formatted-output-FORMAT-DSNTIAUL.

CSV

CSV allows you to output your data in a comma-separated-value (CSV) format that enables porting to other databases and applications. When you specify FORMAT CSV, UNLOAD PLUS formats the unloaded data so that a specific character separates each field from other fields, and encloses each nonnumeric field with a specific pair of characters.

For additional information, including restrictions associated with using this option, see CSV-output-FORMAT-CSV.

CSV options are TERMINATEDBY, ENCLOSEDBY, AND, NULLSTRING, and RTRIM. When you use more than one option, the syntax must follow the sequence in the syntax diagram. For example, if you use TERMINATEDBY, that option must appear before ENCLOSEDBY or NULLSTRING.

TERMINATEDBY

TERMINATEDBY specifies a single character that UNLOAD PLUS uses to delimit fields of data. The default is a comma (,). You cannot specify a null value for TERMINATEDBY.

ENCLOSEDBY

ENCLOSEDBY specifies a single character that UNLOAD PLUS uses on the left side to enclose 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 that UNLOAD PLUS uses on the right side to enclose fields of data. If you do not specify a value for this option, UNLOAD PLUS uses the value that you specified for the ENCLOSEDBY option or a double quotation mark if you did not specify a value for the ENCLOSEDBY option. You can use an empty string to specify a null value for this option.

NULLSTRING

NULLSTRING specifies up to a four-character string that indicates a null value in the output file. You can specify NULLSTRING as an empty string. The default is NULL.

RTRIM

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

Restrictions

UNLOAD PLUS 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 a length and the length differs from the actual length of the data.
  • The column’s field specification includes any of the following keywords:
    • MIXED
    • TRIM
    • TRUNCATE
    • FILL

XML

XML allows you to unload your data in a format that enables porting to other databases and applications. When you specify the XML option, UNLOAD PLUS formats the unloaded data so that each field is enclosed by a specific pair of XML tags corresponding to the unloaded column name.

For additional information, including restrictions associated with using this option, see XML-output-FORMAT-XML.

XML options are SELECT_ELEMENT, ENCLOSEDBY, AND, and NULLSTRING. When you use more than one option, the syntax must follow the sequence in the syntax diagram. For example, if you specify SELECT_ELEMENT, that option must appear before ENCLOSEDBY or NULLSTRING.

SELECT_ELEMENT

Specify SELECT_ELEMENT to identify characteristics of the XML tags that enclose each unloaded row.

AUTO

Specify AUTO to enclose each unloaded record with an XML tag. UNLOAD PLUS assigns a value to the tag name based on the following criteria listed in the order of precedence:

  • The value of the INTO NAME option on the SELECT statement (if specified)
  • A generated table name (if you specify DIRECT NO processing)
  • The name of the table from which the unloaded row was extracted

The following examples illustrate the unloaded data when specifying AUTO with different options:

Specifying AUTO with DIRECT YES, DIRECT NO, and INTO NAME

Option

UNLOAD command statement

Unloaded data

DIRECT YES

DIRECT YES
FORMAT XML
SELECT_ELEMENT AUTO
SELECT NAME,ADDRESS,CITY,STATE FROM MY.CONTACTS;
<contacts><name>'BMC Software, Inc.'</name><address>'10431 MORADO CIRCLE'</address><city>'AUSTIN'</city><state>'TX'</state></contacts>

DIRECT NO

DIRECT NO
FORMAT XML
SELECT_ELEMENT AUTO
SELECT NAME,ADDRESS,CITY,STATE FROM MY.CONTACTS;
<$TABLE1><name>'BMC Software, Inc.'</name><address>'10431 MORADO CIRCLE'</address><city>'AUSTIN'</city><state>'TX'</state><!--$TABLE1-->

INTO NAME

DIRECT NO
FORMAT XML
SELECT_ELEMENT AUTO
SELECT NAME,ADDRESS,CITY,STATE INTO NAME 'CUSTOMER' FROM MY.CONTACTS;
<customer><name>'BMC Software, Inc.'</name><address>'10431 MORADO CIRCLE'</address><city>'AUSTIN'</city><state>'TX'</state></customer>

NONE

Specify NONE if you do not want to enclose each unloaded record with an XML tag. UNLOAD PLUS still encloses each individual field by the appropriate XML tag (for example, <COL1>data</COL1> ).

The following example illustrates the unloaded data when specifying NONE with DIRECT YES processing:

Unloaded data when specifying NONE with DIRECT YES

UNLOAD command statement

Unloaded data

DIRECT YES
FORMAT XML
SELECT_ELEMENT NONE
SELECT NAME,ADDRESS,CITY,STATE FROM MY.CONTACTS;
<name>'BMC Software, Inc.'</name><address>'10431 MORADO CIRCLE'</address><city>'AUSTIN'</city><state>'TX'</state>


‘string’

Specify a name to identify the name of the XML tag with which you want UNLOAD PLUS to enclose each unloaded record.

Important

UNLOAD PLUS does not validate the string value for conformance to any XML standard.

The following example illustrates the unloaded data when you specify the string value ‘TRANSACTION’ with DIRECT YES processing:

Unloaded data when specifying a string value with DIRECT YES

UNLOAD command statement

Unloaded data

DIRECT YES
FORMAT XML
SELECT_ELEMENT 'TRANSACTION'
SELECT NAME,ADDRESS,CITY,STATE FROM MY.CONTACTS;
<transaction><name>'BMC Software, Inc.'</name><address>'10431 MORADO CIRCLE'</address><city>'AUSTIN'</city><state>'TX'</state></transaction>



ENCLOSEDBY

ENCLOSEDBY specifies the character that UNLOAD PLUS uses on the left side to enclose 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 the character that UNLOAD PLUS uses on the right side to enclose fields of data. If you do not specify a value for this option, UNLOAD PLUS uses the value that you specified for the ENCLOSEDBY option or a double quotation mark if you did not specify a value for the ENCLOSEDBY option. You can use an empty string to specify a null value for this option.

NULLSTRING

(Default) NULLSTRING specifies up to a four-character string that indicates a null value in the output file. You can specify NULLSTRING as an empty string.

Related topic

Basic-processing-options


 

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