DIRECT

The DIRECT option allows you to specify how BMC AMI Utilities process table data.

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, BMC AMI Utilities terminate.
You must specify DIRECT YES when you are performing data translation during the unload process.
You must specify DIRECT NO when you are unloading LOB or XML data to standard unload data sets (SYSREC).
Important
You can specify DIRECT YES to unload LOB data to VBS unload data sets.

YES

If you specify DIRECT YES, BMC AMI Utilities use its SELECT 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 BMC AMI Utilities UNLOAD syntax, the utility terminates. This option provides high-performance unloads of Db2 table data, but might limit the functionality that the SELECT statement provides.

BMC AMI Utilities terminate when you specify DIRECT YES for any of the structures and data types that require DIRECT NO.

If you specify FORMAT STANDARD or FORMAT EXTERNAL with DIRECT YES, BMC AMI Utilities unload string constants in SELECT statements as fixed-length strings.

If you specify DIRECT NO, BMC AMI Utilities use 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.

DIRECT NO is not a high-performance solution for unloading large volumes of data.

Note the following restrictions and other considerations for 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, BMC AMI Utilities generate the control statements as if the column is defined as GENERATED BY DEFAULT.
BMC AMI Utilities do not support all options when you specify DIRECT NO. For example, BMC AMI Utilities ignore ORDER YES.For a full list of the options that are not available with DIRECT NO, see the list of options in BMC AMI Unload option descriptions—UNLOAD syntax.
The default table name supplied in the INTO statement of the generated control statements is derived from the first table name in the SELECT statement. To generate control statements with the correct table name, use INTO NAME ownerName.tableName. For more information, see INTO.
If you specify FORMAT STANDARD or FORMAT EXTERNAL with DIRECT NO, BMC AMI Utilities unload string constants in SELECT statements as variable-length strings.

ROWSETSZ

The ROWSETSZ option allows you to tell BMC AMI Utilities 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:

Value	Description
0 or 1	Tells BMC AMI Utilities to fetch a single row at a time
2 through 32767	Tells BMC AMI Utilities to fetch the specified number of rows (as a rowset) BMC recommends a value of 100 for most jobs.

Restriction

BMC AMI Utilities ignore ROWSETSZ when you are unloading LOB or XML data.

AUTO

If you specify DIRECT AUTO, BMC AMI Utilities choose 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 BMC AMI Utilities SELECT-like syntax, BMC AMI Utilities use Db2 dynamic SQL to read the data when processing the SELECT statement.

If BMC AMI Utilities detect incompatibility between the unloaded data formats generated by DIRECT YES and DIRECT NO, it chooses DIRECT NO.

Example

The following syntax, which includes DIRECT NO, generates a nullable integer:

UNLOAD DIRECT NO SELECT 1+1 FROM SYSIBM.SYSDUMMY1;

The following syntax, which includes DIRECT YES, generates a non-nullable integer:

UNLOAD DIRECT YES SELECT 1+1 FROM SYSIBM.SYSDUMMY1;

Because of this discrepancy, when you select DIRECT AUTO, BMC AMI Utilities choose DIRECT NO, resulting in a nullable integer.

Tip

To ensure consistent behavior of your BMC AMI Utilities jobs, specify DIRECT YES or DIRECT NO. BMC AMI Utilities enhancements could change the behavior of DIRECT AUTO.

DIRECT

On this page