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.

DIRECT

The DIRECT option allows you to specify how UNLOAD PLUS processes table data.

DIRECT_new.png

Requirements and restrictions

The following requirements and restrictions apply to the DIRECT option:

  • In your SYSIN command stream, place any DIRECT option specification immediately after your UNLOAD or UNLOAD TABLESPACE option.
  • If you specify the DIRECT option more than once in your JCL, UNLOAD PLUS terminates.
  • You must specify DIRECT NO when either of the following conditions exists:

  • You are unloading LOB or XML data to standard unload data sets (SYSREC and SYSRED)

    Important

    You can specify DIRECT YES to unload LOB or XML data to VBS unload data sets. You can specify either DIRECT YES or DIRECT NO to unload LOB or XML data to files that are referenced in your standard unload data sets. For more information, see Unloading-LOB-and-XML-data

YES

If you specify DIRECT YES, UNLOAD PLUS uses its SELECT-like syntax to read table data directly from the table space data set. If the SELECT statement contains syntax that is not within the scope of the UNLOAD PLUS syntax, the utility terminates. This option provides high-performance unloads of Db2 table data, but might limit the functionality that the SELECT statement provides.

UNLOAD PLUS ignores the CURRENTDEGREE and MAXCONNECT options when DIRECT YES is in effect. Additionally, UNLOAD PLUS terminates when you specify DIRECT YES for any of the structures and data types that require DIRECT NO.

For more information about DIRECT YES mode, see DIRECT YES.

NO

If you specify DIRECT NO, UNLOAD PLUS uses Db2 dynamic SQL to process the SELECT statement and read the table data. The DIRECT NO option enables the full range of functionality that the DB2 SQL SELECT statement provides.

Important

UNLOAD PLUS also uses the DIRECT NO option to enable new Db2 features quickly. UNLOAD PLUS generally will provide DIRECT YES support for these features in a later version.

DIRECT NO is not a high-performance solution for unloading large volumes of data. For more information about DIRECT NO mode, see DIRECT NO.

Restrictions

Note the following restrictions on the DIRECT NO option:

  • Dynamic SQL processing cannot occur on identity columns or ROWID data types that are defined as GENERATED ALWAYS. If generating load control statements and DIRECT NO is in effect, UNLOAD PLUS generates the control statements as if the column is defined as GENERATED BY DEFAULT.
  • When DIRECT NO is in effect, UNLOAD PLUS bypasses ANALYZE processing.

  • UNLOAD PLUS does not support all options when you specify DIRECT NO. Following are examples:

    • UNLOAD PLUS terminates when you specify DIRECT NO with FORMAT BMCLOAD.
    • UNLOAD PLUS ignores ORDER YES when you specify DIRECT NO.

    For a full list of the options that are not available with DIRECT NO, see the table of options in Alphabetical-listing-of-UNLOAD-PLUS-options.

Additional consideration

The following additional considerations apply when DIRECT NO is in effect:

  • When you are dynamically allocating output data sets, you must also specify the ESTROWS, LIMIT, or SPACE command option.
  • The default table name supplied in the INTO statement of the generated control statements is userID.$TABLEn. To generate control statements with the correct table name, use INTO NAME ownerName.tableName.

ROWSETSZ

The ROWSETSZ option allows you to tell UNLOAD PLUS how many rows to include in a rowset for a single FETCH request. You can specify one of the values described in the following table:

Values for the ROWSETSZ option

Value

Description

0 or 1

Tells UNLOAD PLUS to fetch a single row at a time

2 through 200

Tells UNLOAD PLUS to fetch the specified number of rows (as a rowset)

We recommend a value of 100 for most jobs. However, see Specifying-command-options-for-optimal-performance for more recommendations.

Restriction

UNLOAD PLUS ignores ROWSETSZ when you are unloading LOB or XML data.

Specifying the default

You can specify the default for the ROWSETSZ command option in your installation options module by using the ROWSETSZ installation option. UNLOAD PLUS was shipped with a default value of 100 for this option. The command option overrides the default that is in the installation options module.

AUTO

If you specify DIRECT AUTO, UNLOAD PLUS chooses between processing the job as if you specified DIRECT YES, or processing it as if you specified DIRECT NO. If a SELECT statement contains statements that are not within the scope of the UNLOAD PLUS SELECT-like syntax, UNLOAD PLUS uses Db2 dynamic SQL to read the data when processing the SELECT statement.

To ensure consistent behavior of your UNLOAD PLUS jobs, specify DIRECT YES or DIRECT NO. UNLOAD PLUS enhancements could change the behavior of DIRECT AUTO.

Important

"WITH" is treated as an unexpected token if found within a SELECT statement and causes DIRECT AUTO to select DIRECT NO for the unload.

Restrictions

UNLOAD PLUS terminates when you specify DIRECT AUTO and any of the following conditions exists:

  • You also specify FORMAT BMCLOAD.
  • You are unloading any of the types of data that require DIRECT NO, as described in NO.
  • The SELECT statement is processing a view that does not conform to UNLOAD PLUS restrictions for view processing.

Related topic

Basic-processing-options

 

 

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