Space announcement

   

This space provides the same content as before, but the organization of the home page has changed. The content is now organized based on logical branches instead of legacy book titles. We hope that the new structure will help you quickly find the content that you need.

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.


Execution modes

Mode type

Description

Benefits

DIRECT YES

UNLOAD PLUS reads data directly from the table space data set or image copy data sets to unload the data, using a SELECT-like syntax for data selection. SELECT functionality is a limited subset of the functionality that is normally available in DB2 SQL.

The primary benefit of the DIRECT YES mode is maximum performance when unloading large volumes of data.

Additionally, some features are available in DIRECT YES mode that are not available in DIRECT NO mode.

DIRECT NO

UNLOAD PLUS processes the SELECT statement and reads the data using DB2 dynamic SQL. This mode is not a high-performance solution for unloading large volumes of data.

The benefit of the DIRECT NO mode is a full range of DB2 SQL SELECT functionality, including joined tables, subqueries, and so on.

This functionality includes many of the features that are available in UNLOAD PLUS including the following features:

  • DB2 parallelism

  • Data type conversions

  • Output formatting

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 COPY PLUS, 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

    Note

    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

    Note

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

Was this page helpful? Yes No Submitting... Thank you

Comments