LOAD and UNLOAD Enhancements [DB2 9 for z/OS]

This week we continue our exploration of the features and functionality of DB2 9 for z/OS with a look at what’s new in the IBM LOAD and UNLOAD utilities. The first question people seem to ask with regard to LOAD is “Has performance been improved?” And the answer is yes, due mostly to improvements in handling indexes. IBM reports that performance can improve up to 24 percent if a table has mostly variable keys.

Handling DECFLOAT Data

In terms of functionality, the first change we’ll discuss is how to deal with DECFLOAT data type in LOAD (and UNLOAD). If you do not recall what the DECFLOAT data type is, refresh your memory here.

The DECFLOAT data type is compatible with SMALLINT, INTEGER, BIGINT, DECIMAL, FLOAT, DECFLOAT. Of course, you will have to concern yourself with how the data is handled if it is loaded to or from DECFLOAT. You specify how to handle this manipulation using the DECFLOAT_ROUNDMODE parameter, which can be used in both the LOAD and UNLOAD utilities.

The following DECFLOAT_ROUNDMODE specifications are allowed:

• ROUND_CEILING - Round towards positive infinity.
• ROUND_DOWN - Round towards zero.
• ROUND_FLOOR - Round towards negative infinity.
• ROUND_HALF_DOWN - Round to nearest; if equidistant, round down.
• ROUND_HALF_EVEN - Round to nearest; if equidistant, round so that the final digit is even.
• ROUND_HALF_UP - Round to nearest; if equidistant, round up.
• ROUND_UP - Round away from zero.

If you do not specify DECFLOAT_ROUNDMODE, the LOAD statement uses the DFPDEFDM value in DSNHDECP as the default value.

Loading LOBs

Loading and unloading LOBs has been improved. For LOAD, an input field value can contain the name of the file that contains a LOB column value. The LOB column value will then be loaded from that file. For UNLOAD, you can store the value of a LOB column in a file and record the name of the file in the unloaded record in the base table.

LOAD, LOBs, and SORTKEYS NO

When a LOAD SHRLEVEL NONE of a table with a LOB column, fails, it can only be restarted with RESTART(CURRENT). This has been the case since LOBs were introduced to DB2.

However, when the SORTKEYS keyword was introduced in V5, a restriction with its use was that a LOAD that abended in the RELOAD, SORT, or BUILD phases could only be restarted with RESTART(PHASE). Restart will convert any RESTART option to RESTART(PHASE) when SORTKEYS is specified. This situation limited our options when LOBs were involved.

In V8 SORTKEYS became the default, so SORTKEYS and LOB restart processing were incompatible. In V9 this is fixed:

• You can turn SORTKEYS off by specifying SORTKEYS NO.
• And you can now use RESTART(PHASE) for LOAD REPLACE of a table with a LOB.

If you run LOAD REPLACE SHRLEVEL NONE on a table with a LOB, specifying SORTKEYS NO means that you can restart with either RESTART(CURRENT) or RESTART(PHASE). Without the SORTKEYS NO you can only use RESTART(PHASE).

If you run LOAD RESUME YES SHRLEVEL NONE on a table with a LOB, you must specify SORTKEYS NO if you want the job to be restartable; only RESTART(CURRENT) will be available.

Skipping Locked Rows

Recall from an earlier post here that you can skip locked rows in your transactions using the SKIP LOCKED DATA option within your SQL statements. Well, guess what, you can skip locked rows when running an UNLOAD, too.

If you are running an UNLOAD with SHRLEVEL CHANGE ISOLATION CS, you can also specify SKIP LOCKED DATA. This will cause the UNLOAD utility to skip rows on which incompatible locks are held by other transactions. This option only applies to table spaces with row level or page level locking.