Understanding UNLOAD PLUS execution modes

You can use the DIRECT command option to select one of two execution modes, or specify DIRECT AUTO to let UNLOAD PLUS choose between the two modes based on the options that you specify and the object that you are unloading.

The following table presents an overview of these modes and the benefits of each.

DIRECT YES

When you use DIRECT YES, UNLOAD PLUS reads data directly from the table space data set or image copy data set and uses a SELECT-like syntax for data selection. In this mode, UNLOAD PLUS does not run as part of the Db2 subsystem and you must have system authorization similar to authorization that Db2 requires.

For the syntax options that are supported with DIRECT YES, see Alphabetical listing of UNLOAD PLUS options.

Using DIRECT YES provides the following capabilities that are not available when you use DIRECT NO:

• Unloading from full or incremental image copies, DSN1COPY data sets, encrypted copies created by BMC AMI Copy, cabinet copies, inline copies, Instant Snapshot copies, online consistent copies, VSAM linear data sets, or VSAM FlashCopy image copies

• Unloading all data from a table space without requiring written SELECT statements, thereby reducing maintenance

• Dynamically eliminating the processing of partitions that do not meet the conditions of the WHERE clause

• Sorting the data by clustering key or partitioning key

• Providing various levels of data availability by using the SHRLEVEL option

• Specifying logical partitions instead of physical partitions

• Unloading binary XML data to referenced files

• Unloading LOB and XML data to variable-block spanned (VBS) output files

DIRECT NO

When DIRECT NO is in effect, UNLOAD PLUS uses Db2 dynamic SQL to process the SELECT statement. In this mode, UNLOAD PLUS uses the Db2 subsystem to read the data, which means that UNLOAD PLUS competes with other Db2 processes for subsystem resources.

DIRECT NO mode identifies many of the characteristics of selected objects differently than DIRECT YES, which might result in some objects behaving differently under each mode. If you specify DIRECT AUTO, UNLOAD PLUS determines which mode to use, which might produce results that you do not expect.

Additionally, DIRECT NO does not support all options, including the following features:

• Unloading from image copies

• User-defined types

• UNLOAD TABLESPACE command

• SHRLEVEL option

  Instead, you can use isolation levels in the Db2 SQL WITH clause to control access to objects during the unload process.

For the syntax options that are supported with DIRECT NO, see Alphabetical listing of UNLOAD PLUS options.

When you use DIRECT NO, you obtain the following benefits that are not available when you use DIRECT YES:

• The ability to use the full range of Db2 SQL SELECT functionality, including joined tables, subqueries, and so on

  Important

  You can use the INTO clause (available for both DIRECT YES and DIRECT NO) to use the UNLOAD PLUS data type conversions on the rows that were read using Db2 dynamic SQL.

• The ability to use the SET CURRENT DEGREE command before executing any dynamically executed SELECT statements when you specify CURRENTDEGREE ANY or CURRENTDEGREE 1

• The option to fetch multiple rows at one time (by using the ROWSETSZ option)

• Timely enablement of Db2 features, such as

  • Enforcement of row- and column-level security

  • Tables that are defined as ORGANIZE BY HASH

  Important

  We plan to add DIRECT YES support for these features in a later version of UNLOAD PLUS.