Processing mode considerations

Native processing versus SQL mode processing

Db2 HPU retrieves the data to be unloaded in two ways:
  • either by directly accessing the data from the data sets where it is stored (either the VSAM data sets handled by Db2 or the image copy data sets),
  • or by requesting Db2 to provide the data from its buffers.

The first is called the native processing (also referred to as native mode) and the latter is called the SQL processing (also referred to as SQL mode). The applicable modes depend on the kind of unload to be performed. When both modes are available for a given unload, the mode to be used can be specified via the DB2 option or its default counterpart in the Db2 HPU settings (VUU011/ULSEDB2) with the Db2 HPU syntax or with an equivalent keyword with the other syntaxes.

The table below shows which mode is applicable.
Unload request Native mode SQL mode
Physical unload (1) YES NO
Unload from an image copy (2) YES NO
Logical unload (3) with a supported SQL statement (4) YES (5) YES (5)
Logical unload (3) with an unsupported SQL statement (4) NO YES
(1) Db2 HPU UNLDDN option or equivalent feature with other syntaxes.

(2) Db2 HPU COPYDDN option or equivalent feature with other syntaxes.

(3) Db2 HPU OUTDDN option or equivalent feature with other syntaxes.

(4) Db2 HPU only supports a part of the SQL SELECT statements (see Fast select, fast listdef and fast listdeftbv select blocks syntax and description for more details).

(5) Db2 HPU selects the mode according to the instructions or preferences given by the DB2 option or its default counterpart in the Db2 HPU settings (VUU011/ULSEDB2).

SQL processing details

When Db2 HPU passes a SELECT statement to Db2 to retrieve the data (SQL mode), the following systematical or optional actions that might have an influence on the way Db2 processes the statement are performed:
  • The FOR READ ONLY clause is appended to the SELECT statement (1).
  • The USE CURRENTLY COMMITTED or WAIT FOR OUT-COME is appended to the SQL statement according to the ACCPREP specification (via the ACCPREP option in technical parameters block or the VUU061/ACCPREP parameter) (2).
  • The SQL statement, with potential changes described above is run after some special registers have been set as follows:
    • The CURRENT DEGREE special register is set according to the ULDEGREE option in technical parameters block or the VUU021/ULDEGREE parameter (1)
    • The CURRENT SQLID special register is set according to the SQLID specified via the GLOBAL OPTIONS block (2).
    • The CURRENT QUERY ACCELERATION special register is set according to the QUERY_ACCELERATION option in the OPTIONS block (2).
    • The CURRENT OPTIMIZATION HINT special register is set according to the OPTIMIZATION_HINT option in the OPTIONS block (2).
  • According to the UNLROWSET option or the VUU035/ULROWSET parameter, the SQL statement is run in either single or multi-row fetch mode (1).

(1) systematical action

(2) optional action

Db2 HPU reports any SQL error related to the processing of the above mentioned statements by reproducing the Db2 error's feedback. It also reports any positive (i.e., greater than 0) SQL code returned by the SQL processing of the PREPARE statement run for the SELECT statement involved in the unload. It is particularly and noticeably useful when using the OPTIMIZATION HINT option as it allows to check whether Db2 has exploited the specified optimization hint for processing the SELECT statement as it is reported by either an SQLCODE+394 or an SQLCODE+395.