SELECT

• Table that UNLOAD PLUS should unload
• Rows of the table to unload
• Partitions of the table to unload
• Definitions of fields of the output record
• Values in the output record

UNLOAD PLUS requires at least one SELECT statement unless you specify UNLOAD TABLESPACE. You might specify one or more SELECT statements for each table that you want to unload. When you specify multiple SELECT statements, you can specify multiple unload data sets in your JCL to direct the result of each SELECT statement to a different unload data set. For more information, see Using JCL to specify multiple unload data sets.

Place the SELECT statement as the last set of options in your command stream.

Restrictions

Note the following SELECT statement restrictions:

• You cannot specify an expression on the SELECT statement.
• You cannot specify a SELECT statement with the UNLOAD TABLESPACE option.
• If you also specify FORMAT BMCLOAD, see Data-migration-using-LOADPLUS-FORMAT-BMCLOAD for restrictions that apply to the SELECT option.
• When DIRECT YES is in effect, you cannot specify multiple SELECT statements when unloading LOB or XML data.

Additional considerations

The following considerations also apply to the SELECT statement:

• DIRECT NO supports the full range of Db2 SQL SELECT functionality.
• When DIRECT NO is in effect, Db2 enforces row- and column-level security, which might affect the results of your SELECT statement.
• UNLOAD PLUS supports the use of the double-byte character set (DBCS). You can use DBCS characters in Db2 identifiers such as tables, columns, or view names, or in the constants and field names that you specify in your SELECT statement.
• Too many concurrent tasks to a single output data set can degrade performance. When you use a single SELECT statement to unload a partitioned table space, use multiple unload data sets or use the MAXSORTS option to limit the number of concurrent tasks and reduce contention.

* (asterisk)

Specify an asterisk (*) to tell UNLOAD PLUS to select all columns in the table.

When DIRECT NO is in effect and you want to unload columns in your table that are defined as IMPLICITLY HIDDEN, you must explicitly specify the columns to unload. (For DIRECT YES, UNLOAD PLUS unloads hidden columns when you specify SELECT *.)

columnName

If you specify a column name, it must be a column in the named table. If any selected column uses a FIELDPROC, the routine must be included in your system’s LINKLIST, or your JOBLIB or STEPLIB.

constant

Specify a constant to have UNLOAD PLUS put a specific value in the output record. The constant must be a valid SQL integer, decimal, graphic, or character string, with a length of up to 255 bytes.

CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP

Specify CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP to have UNLOAD PLUS put the current date, time, or time stamp in the output record. UNLOAD PLUS derives this value from the system clock value. For each record, UNLOAD PLUS obtains a new system clock value.

You cannot specify CURRENT TIMESTAMP WITH TIME ZONE. However, if you are unloading to a TIMESTAMP WITH TIME ZONE column, UNLOAD PLUS includes the time zone from the DSNHDECP IMPLICIT_TIMEZONE value.

CURRENT RID

This option is valid only when DIRECT YES is in effect.

This option tells UNLOAD PLUS to include in your output file a field that contains the RID for the corresponding row in the named table. The output RID is either a 4- or 5-byte hexadecimal value, in the same format as an internal Db2 index entry.

Restrictions

This option has the following restrictions:

• We recommend that you do not include a field specification for the RID field on your INTO statement. Instead, specify a comma as a placeholder.

  If you choose to include a field specification, the data type must be CHAR with a length of 4 or 5.

• UNLOAD PLUS treats the RID values as if they are defined as FOR BIT DATA. As with any FOR BIT DATA values, UNLOAD PLUS does not translate these values.

Example

The following simple example SELECT statement uses the CURRENT RID option:

Assuming 4 byte-RIDs, a sample output record would be in the following format: