OPTIONS block syntax and description

The effect of the options that are specified in the OPTIONS block at the GLOBAL level or at the UNLOAD level depends on the value that is specified for the VUU057/OPALLFMT PARMLIB parameter:

• If NO is specified for the VUU057/OPALLFMT parameter, all options that you specify in a GLOBAL block or an UNLOAD block apply only to the USER format except for the LOADOPT, FLOAT, UNLROWSET, LOADINDDN, AUTOTAG, and IFERROR options. The LOADOPT, UNLROWSET, LOADINDDN, AUTOTAG, and IFERROR options apply to all formats. The FLOAT option applies to DSNTIAUL, USER, and VARIABLE formats.
• If YES is specified for the VUU057/OPALLFMT parameter, all options that you specify in an OPTIONS block at the GLOBAL level or the UNLOAD level apply to all formats except for some specific options that have no meaning for some formats and for LENGTHBYTE and LENGTH options that are specific to the USER format.

The following table shows the default values for options that are specified in the OPTIONS block. Each option can have a different default value based on the output format.

NULL

Indicates whether the null indicator is generated in the output data set. You can specify NULL in the SELECT statement in the FORMAT USER syntax also.

The following values can be specified for NULL. Although value-1 and value-2 can be specified as 1 - 4 bytes or characters, you must specify both value-1 and value-2 as the same number of bytes or characters. You can specify value-1 and value-2 in character ('c') or hexadecimal (X'hh') formats.

value-1

The value of the null indicator when the column value is NULL.

value-2

The value of the null indicator when the column value is NOT NULL.

OFF

A null indicator is not generated.

When FORMAT DELIMITED is specified, the default value is OFF. You cannot change this value in the OPTIONS block even when it is specified at the SELECT level.

The default values for formats are shown in Default values per output format for options that are specified in the OPTIONS block.

You can override the DATE option at the column level with the TYPE keyword in a SELECT statement for the USER format, an INTO clause, or a REFORMAT clause.

DATE DATE_x

Specifies the default output format for the DATE columns, where x is any uppercase alphabetic character A - R or an integer 0 - 7 or DB2.

When a date column is converted into the CHAR format by specifying FORMAT USER or by using either a REFORMAT clause or an INTO clause, the DATE format that is specified in the OPTIONS block is not used. Instead, the default date format of your environment is used.

The default values for formats are shown in Default values per output format for options that are specified in the OPTIONS block.

You can override the DATE option at the column level with the TYPE keyword in a SELECT statement for the USER format, an INTO clause, or a REFORMAT clause.

DATE datefmt

datefmt is a string that describes how a date value must be formatted. It must be specified according to the rules indicated in section Syntax rules for the user-defined date, time or timestamp formats.

DATEDELIM value

Specifies the default delimiter that is used in external date representations, where value must be one character and must be 1 byte long, regardless of the literal CCSID.

The default delimiter that is used when DATE columns are unloaded depends on the output format that is used, as shown in Default values per output format for options that are specified in the OPTIONS block.

TIME TIME_x

Specifies the default conversion for time representations, where x is any uppercase alphabetic character A - E or the integer 0 or DB2.

When a time column is converted into a CHAR data type by specifying FORMAT USER or by using either a REFORMAT clause or an INTO clause, the time format that is specified in the OPTIONS block is not used. Instead, the default time format of your renvironment is used.

The default values for formats are shown in Default values per output format for options that are specified in the OPTIONS block.

You can override the TIME option at the column level by specifying the TYPE keyword in a SELECT statement for the USER format, an INTO clause, or a REFORMAT clause.

TIME timefmt

timeformat is a string that describes how a time value must be formatted. It must be specified according to the rules indicated in section Syntax rules for the user-defined date, time or timestamp formats.

TIMEDELIM value

Indicates the default delimiter that is used in external time representations, where value must be one character and must be 1 byte long, regardless of the literal CCSID.

The default values for other formats are described in Default values per output format for options that are specified in the OPTIONS block.

TIMESTAMP TIMESTAMP_x

Specifies the default conversion for the TIMESTAMP columns, where x is an uppercase alphabetic character A - G or the integer 0.

When a timestamp column is converted into a CHAR data type by specifying FORMAT USER or by using either a REFORMAT clause or an INTO clause, the timestamp format that is specified in the OPTIONS block is not used. Instead, the Db2 standard timestamp external format is used.

The default values for formats are shown in Default values per output format for options that are specified in the OPTIONS block.

TIMESTAMP timestampfmt

timestampfmt is a string that describes how a timestamp value must be formatted. It must be specified according to the rules indicated in section Syntax rules for the user-defined date, time or timestamp formats.

Syntax rules for the user-defined date, time or timestamp formats

A user-defined date format (datefmt), time format (timefmt) or timestamp format (timestampfmt) must be specified according to the following rules.

1. The format string must begin with one of the allowed format elements listed in the table below
2. The format string can contain only elements allowed for the related format
3. The format string can contain only a subset of the elements allowed for the related format
4. Each format element can be specified only once
5. Separators can be any character or character string but must not contain any character string used as format elements as well as the ambiguous MM string, regardless of the related format.

The data is formatted by replacing the format elements as indicated in the table below and by keeping the other characters from the string format, i.e. the separators, as specified. If necessary the whole output field, including separators, is converted into the output CCSID.

Table: Format elements for a format string

Table 2. Format elements for a format string

format element	the element is replaced by	allowed in datefmt (1)	allowed in timefmt (1)	allowed in timestampfmt (1)
DD	two digits corresponding to the day part (2)	Y	N	Y
MO	two digits corresponding to the month part (2)	Y	N	Y
YY(3)	the least significant two digits of the year part (2)	Y	N	Y
YYYY(3)	four digits corresponding to the year part (2)	Y	N	Y
HH	two digits corresponding to the hours part (2) with a time coded over 24 hours. N	N	Y	Y
MI	two digits corresponding to the minutes part (2)	N	Y	Y
SS	two digits corresponding to the seconds part (2)	N	Y	Y
NN(4)	two digits corresponding to the fractional seconds part (5)	N	N	Y
NNNN(4)	four digits corresponding to the fractional seconds part (5)	N	N	Y
NNNNNN(4)	six digits corresponding to the fractional seconds part (5)	N	N	Y

• (1) any element indicated as not allowed (N) is forbidden for the related for the related format, even as a separator.
• (2) leading zeroes are added if necessary.
• (3) only one element among YY and YYYY can be specified.
• (4) only one element among NN and NNNN and NNNNNN can be specified.
• (5) trailing zeroes are added if necessary.

PIC

Defines the numeric data display format that is used when numeric values are converted for external representation. The PIC keyword has four parameters. You must specify the first three parameters. The fourth parameter is optional.

• The first parameter specifies the rules for printing the sign:

  -

  Indicates that the minus character (-) is present if the number is negative. Otherwise, the sign character is a blank.

  +

  Indicates that the sign is always present. Positive values have a plus character (+), and negative values have a minus character (-).

  P

  Indicates that the padding character is used for positive values and the minus character (-) is used for negative values.

  V

  The sign byte used depends on the data type as follows:

  • for non-float numeric data:
    • no sign byte is written for values greater than or equal to zero.
    • the minus sign (-) is written for values less than zero.
  • for float data:
    • the plus sign (+) is written for values greater than or equal to zero.
    • the minus sign (-) is written for values less than zero.

• The second parameter specifies the position the sign relative to the column.

  LEAD

  The sign is placed in front of the numeric value. LEAD is ignored for floating point numbers.

  TRAIL

  The sign is placed after the numeric value. TRAIL is ignored for floating point numbers.

• The third parameter specifies the decimal separator.

  .

  Use a period as the decimal separator.

  ,

  Use a comma as the decimal separator.

• The optional fourth parameter indicates the formatting rules that are used to display non-significant zeros for DECIMAL, SMALLINT, and INTEGER values.
  The following table lists the valid values and explains their impact:

  • The Left padding column indicates whether the number is padded on the left with non-significant zeros to fill the output field. The sign character is added to the left of these zeros.
  • The Left zero column indicates whether a zero is displayed on the left of the decimal separator when the integer part of the number is 0.
  • The Decimal separator column indicates whether the decimal separator is displayed.
  • The Right padding column indicates whether the number is padded on the right with zeros up to the number of digits of the decimal scale.

  Notes:

  • The Decimal separator column applies only to decimal data. The decimal separator is never displayed for SMALLINT or INTEGER values.
  • The ULMASK variable has no impact on the formatting of values for FLOAT.

  Table 3. Nonsignificant zero values for DECIMAL and SMALLINT/INTEGER

  ULMASK value	Left padding	Left zero	Decimal separator	Right padding
  *.*	No	Only when the value is 0	Only if the decimal value is not 0	No
  0.*	No	Always	Only if the decimal value is not 0	No
  *.0	No	Only when the decimal scale is 0	Only if the decimal scale is not 0	Yes
  0.0	No	Always	Only if the decimal scale is not 0	Yes
  00.0	Yes	Always	Only if the decimal scale is not 0	Yes
  *.Z	No	Only when the decimal scale is 0	Always	Yes
  0.Z	No	Always	Always	Yes
  00.Z	Yes	Always	Always	Yes

  The following table contains examples of the effect of the mask parameter when the following decimal values are converted:

  DEC(5.2) converted to a CHAR(8)

  Table 4. Mask parameter effects when the decimal values 5,2 are converted

  Mask/value	001.40	000.40	123.00	-123.45	000.00
  *.*	1.4	.4	123	-123.45	0
  0.*	1.4	0.4	123	-123.45	0
  *.0	1.40	.40	123.00	-123.45	.00
  0.0	1.40	0.40	123.00	-123.45	0.00
  00.0	0001.40	0000.40	0123.00	-0123.45	0000.00
  *.Z	1.40	.40	123.00	-123.45	.00
  0.Z	1.40	0.40	123.00	-123.45	0.00
  00.Z	0001.40	0000.40	0123.00	-0123.45	0000.00

  The following table contains examples of the effect of the mask parameter when decimal values are converted:

  DEC(5,0) converted to a CHAR(8)

  Table 5. Mask parameter effects when the decimal values 5,0 are converted

  Mask/value	12345.	00001.	-12345.	00000.
  *.*	12345	1	-12345	0
  0.*	12345	1	-12345	0
  *.0	12345	1	-12345	0
  0.0	12345	1	-12345	0
  00.0	0012345	0000001	-0012345	0000000
  *.Z	12345.	1.	-12345.	0.
  0.Z	12345.	1.	-12345.	0.
  00.Z	012345.	000001.	-012345.	000000.

  The following table contains examples of the effect on the mask parameter when decimal values are converted:

  DEC(5,5) converted to a CHAR(9)

  Table 6. Mask parameter effects when the decimal values 5,5 are converted

  Mask/value	.12345	.10000	-.12345	.00000
  *.*	.12345	.1	-.12345	0
  0.*	0.12345	0.1	-0.12345	0
  *.0	.12345	.10000	-.12345	.00000
  0.0	0.12345	0.10000	-0.12345	0.00000
  00.0	00.12345	00.10000	-00.12345	00.00000
  *.Z	.12345	.10000	-.12345	.00000
  0.Z	0.12345	0.10000	-0.12345	0.00000
  00.Z	00.12345	00.10000	-00.12345	00.00000

The default value of the PIC keyword that is used depends on the output format that is used, as shown in Default values per output format for options that are specified in the OPTIONS block.

LOADOPT

Modifies the options of the Db2 LOAD command. Specify the options that you want Db2 HPU to place in the LOAD SYSIN that is created during the unload process.

The value of the LOADOPT keyword is created by merging values that are specified in the PARMLIB, the GLOBAL block, the UNLOAD block, and the SELECT block. However, if LOADOPT is also specified in the FORMAT specification, the LOADOPT keyword is used as is. It is not merged with previous levels.

tableoptions

Options to specify at the table level

partoptions

Options to specify at the partition level

tablespaceoptions

Options to specify at the table space level

The variables tablespaceptions, partoptions and tableoptions values are positional, and the commas are required even if you do not want to set the value for one subparameter. For example, to specify the value NUMRECS for tableoptions, specify LOADOPT (,,NUMRECS).

The following syntax diagram shows the LOADOPT keywords that are recognized by Db2 HPU:

loadopt keywords

Notes:

• ¹ &SORTKEYS is replaced with the actual value after data is unloaded except when EXECUTE NO is specified. When EXECUTE NO is specified, &SORTKEYS is replaced with 0.
• ² Repeat up to three times.
• ³ only recognized as part of a tableoptions block.

Important: Make sure to specify these keywords at the intended and applicable level (tablespaceoptions, partoptions or tableoptions). Refer to the Db2 publication for details on applicability and meaning of these keywords.

The load options in the PARMLIB and any OPTIONS blocks that you specify in the GLOBAL block or the UNLOAD block are merged. If you specify the same option in two or more places, they are merged with the following priority:

1. UNLOAD block
2. GLOBAL block
3. PARMLIB

Any keywords that you specify that are not shown in the LOADOPT syntax diagram are added to the total option sequence. They are not merged with other options.

Important: If a LOADOPT keyword is specified in the FORMAT block, it is used as is, and it is not merged.

Note: If the Db2 HPU spanned YES option is specified, do not specify the FORMAT keyword in the LOADOPT option as this can lead to a wrong LOAD statement notified at LOAD time by either a syntax error message or by an error during the load processing.

LOADDDL TABLE

Requests the CREATE TABLE statement of the unloaded tables be written into the data set specified by the LOADDDN option. Use this option to get the CREATE TABLE statement of all the tables involved in a given load command, inserted before its related load command. The CREATE TABLE statement describes all the columns of the unloaded tables whether they are involved in the SELECT statement or not. If the LOAD command contains several tables, the content of the LOADDDN data set looks as follows:

CREATE TABLE me.mytable_1_in_LOAD_COMMAND_1
 ...
 CREATE TABLE me.mytable_2_in_LOAD_COMMAND_1
 ...
 LOAD DATA
 ...
 INTO TABLE me.mytable_1_in_LOAD_COMMAND_1
 ...
 INTO TABLE me.mytable_2_in_LOAD_COMMAND_1
 ...
 CREATE TABLE me.mytable_3_in_LOAD_COMMAND_2
 ... 
 CREATE TABLE me.mytable_4_in_LOAD_COMMAND_2
 ...
 LOAD DATA
 ...
 INTO TABLE me.mytable_3_in_LOAD_COMMAND_2
 ...
 INTO TABLE me.mytable_4_in_LOAD_COMMAND_2
 ...
 CREATE TABLE me.mytable_n_in_LOAD_COMMAND_N
 ...
 CREATE TABLE me.mytable_m_in_LOAD_COMMAND_N
 ... 
 LOAD DATA
 ...
 INTO TABLE me.mytable_n_in_LOAD_COMMAND_N
 ...
 INTO TABLE me.mytable_m_in_LOAD_COMMAND_N

where me.mytable_n_in_LOAD_COMMAND_N is one of the tables involved in the Nth load command.

Note: In SQL mode, the CREATE TABLE statement is only generated when the table can be identified from the SELECT statement. For example, for a SELECT statement which contains a table JOIN, no CREATE TABLE statement is generated.

Note: The CREATE TABLE DDL is only generated when the unload refers to a table, not a view, a Materialized Query Table or any other object. The column TYPE from the CATALOG table SYSTABLES must be equal to 'T'.

Note: The generated CREATE TABLE contains fieldproc, editproc and partitioned clause for tablespace partitioned by the table.

Note: The default value for graphical column is not generated in the DDL.

LENGTHBYTE

Specifies whether the two length bytes for variable-length columns are written to the output data set. LENGTHBYTE applies only when FORMAT USER is specified. It is ignored when other output formats are specified.

YES

Writes the two length bytes.

NO

Does not write the two length bytes.

The default value is YES when FORMAT USER is specified. You can change the default value by specifying an OPTIONS block at the GLOBAL or SELECT level or by changing LENGTHBYTE for a specific column in the FORMAT USER definition.

The following rules apply when you specify FORMAT USER:

• When LENGTHBYTE YES is specified, the default value for the LENGTH keyword is REAL.
• When LENGTHBYTE NO is specified, the default value for the LENGTH keyword is MAX.
• When LENGTHBYTE NO and LENGTH REAL are specified, Db2 HPU cannot generate a LOADDDN and issues the INZU203E error message if a LOADDDN file was requested.

When you specify FORMAT DELIMITED, the default value is NO. You cannot change this value by specifying an OPTIONS block, even when it is specified at the SELECT level.

When you specify FORMAT DSNTIAUL, FORMAT EXTERNAL, or FORMAT VARIABLE, the default value is YES. You cannot change this value by specifying an OPTIONS block, even when it is specified at the SELECT level.

LENGTH

Specifies whether to use the real or maximum length for variable-length fields. This keyword applies only for variable-length fields and for the USER and DSNTIAUL output formats. It is ignored when other output formats are specified.

REAL

The length of the field does not change (value of the two length bytes).

MAX

The output field is padded to its maximum length by adding binary zeros.

When you specify FORMAT USER, the default value for the LENGTH keyword depends on the setting of the LENGTHBYTE keyword. You can change the default value for the USER format by specifying an OPTIONS block at the GLOBAL or SELECT level or by changing the LENGTH keyword for a specific column in the FORMAT USER definition.

When you specify FORMAT DSNTIAUL, the default value is MAX. You can change the default value for the DSNTIAUL format by specifying an OPTIONS block at the SELECT level.

When you specify FORMAT VARIABLE or FORMAT DELIMITED, the default value is REAL. You cannot change this value by specifying an OPTIONS block, even when it is specified at the SELECT level.

When you specify FORMAT EXTERNAL, the default value is MAX. You cannot change this value by specifying an OPTIONS block, even when it is specified at the SELECT level.

NULLID

Specifies whether a null indicator byte is to be added at the beginning of an output field. You can also specify NULLID in the SELECT statement in the FORMAT USER syntax.

YES

The null indicator is created. If the column is null, the indicator is X'FF. Otherwise, the indicator is X'00'. The Db2 LOAD command uses the indicator to load null values into a table. You can change the values of the null indicator by specifying the NULL keyword.

NO

The null indicator is not created.

The default values for formats are shown in Default values per output format for options that are specified in the OPTIONS block.

NULLPOS

Specifies the position of the NULL indicator. You can also specify NULLPOS in the SELECT statement in the FORMAT USER syntax. This parameter does not apply to a LOB column unloaded in a spanned format (SPANNED YES option) in which case the NULL indicator is written before the output field (containing the data) whatever the value of NULLPOS.

BEFORE

The null indicator is placed before the field containing the related data.

AFTER

The null indicator is placed after the field containing the related data.

FRONT

The null indicators are gathered at the front of each unloaded record. The sequence of the NULL indicators is the same as the sequence of the related columns.

The default value of the NULLPOS option that is used depends on the requested output format, as shown in Default values per output format for options that are specified in the OPTIONS block.

LOADINDDN

Specifies whether INDDN ddname cards are automatically inserted into the Db2 LOAD command according to the ddnames specified by the UNLOAD command for the data sets containing the unloaded data.

YES

INDDN ddname cards are automatically generated. If a TEMPLATE is used to allocate the output file, Db2 HPU also generates a TEMPLATE statement to allocate the ddname used by the INDDN cards generated by the LOADINDDN(YES) feature. To avoid any invalid duplicate specification of INDDN cards, any INDDN option specified via the tableoptions of a LOADOPT option from the OPTIONS block or via the “Options for global LOAD statement (VUU019/ULOPTLDT)” parmlib parameter is ignored.

NO

No INDDN card is automatically generated. This allows to disable a LOADINDDN(YES) setting made at the parmlib level and, subsequently, to ensure INDDN option specified via the tableoptions of a LOADOPT option from the OPTIONS block or via the “Options for global LOAD statement (VUU019/ULOPTLDT)” parmlib parameter are taken into account.

As the generated LOAD command does not contain any INDDN option, the single SYSREC ddname is assumed as input ddname regardless of the unload being requested at the global or partition level. Accordingly, only the load options specified for the table space level (i.e., the tablespaceoptions suboption of the LOADOPT option) are taken into account and any load options specified at the partition or table option (i.e., the partoptions or tablesoption suboption of the LOADOPT option) are ignored.

The default value is either NO or the value that is specified in the VUU030/ULOPTNS parameter, with parameter LOADINDDN.

FLOAT

Specifies the output format of the numeric floating point data. The FLOAT keyword applies only to the binary output format.

S390

Indicates that the binary floating point data is written to the output records in the S/390® internal format (hexadecimal floating point or HFP).

IEEE

Indicates that the binary floating point data is written to the output records in the IEEE format (binary floating point or BFP).

The default value is S390.

Restriction: The same output format is used for all FLOAT columns in one SELECT statement. You cannot specify the FLOAT keyword at the column level in the USER format.

UNLROWSET

Specifies the number of rows that a single SQL FETCH statement retrieves. Specifying the number of rows to retrieve improves the performance of SELECT statements that are processed by Db2 when you specify DB2 FORCE or DB2 YES with SELECT statements that are not supported by Db2 HPU. Valid values are 1 - 32767. When you specify 1, a single-row FETCH statement is used instead of a multiple-row FETCH statement. The multiple-row FETCH statement is available only in Db2 Version 8 NFM or later. The UNLROWSET keyword is ignored for earlier Db2 versions.

The default value is the value of the VUU035/ULROWSET parameter.

NULLPAD

Specifies whether the padding character is used to fill the output field when a null value is unloaded.

NO

When a null value is unloaded, the output field is filled with binary zeros (x'00').

YES

When a null value is unloaded, the output field is filled with the padding character.

The default values for formats are shown in Default values per output format for options that are specified in the OPTIONS block.

When you specify FORMAT DELIMITED, the value of the NULLPAD keyword is always YES, and you cannot change it.

AUTOTAG size

Allows you to add a tag, which is an external numeric value, to each record that is unloaded. The output data for each SELECT statement has its own tag. If you specify AUTOTAG at the GLOBAL level or at the UNLOAD level, the AUTOTAG keyword applies to all SELECT statements if they are written in a common file.

If you specify AUTOTAG at the SELECT level, Db2 HPU requires all SELECT statements that write in a common output file to use the same number of digits. File records with an autotag cannot be mixed with file records without an autotag.

The generated tag starts at 1 and is automatically incremented for each SELECT statement (either explicitly coded or generated by a LISTDEF) of the same SYSIN for which AUTOTAG is specified, either explicitly coded or generated by a LISTDEF or LISDEFTBV.

The AUTOTAG keyword applies only to logical unloads, which use SELECT statements to unload Db2 data. It does not apply to physical unloads.

size

Specifies the number of digits in the tag. Valid values are 0 - 8. If you specify 0, an AUTOTAG is not generated for the corresponding SELECT statements.

Important: If the value of the tag that is computed for a SELECT statement exceeds the number of digits that you specified for the AUTOTAG keyword, Db2 HPU stops.

If you specify AUTOTAG and LOADDDN is requested, the LOADDDN file that is generated automatically includes a WHEN clause for each SELECT statement to reload into the corresponding tables. In this case, the unloaded tables correspond with the value of the autotag.

The default value is the value of the VUU046/ULAUTAG parameter.

IFERROR

Defines the value that is assigned to an output field if a conversion error occurs while the output field is formatted or if an error occurs while an expression that is natively processed by Db2 HPU is evaluated. The IFERROR keyword applies only to conversion or formatting that was made during the unload process. The IFERROR keyword does not apply to conversion errors when constants are specified in the SELECT statement and these constants are converted into a different data type by using an INTO clause, a REFORMAT clause, or by specifying FORMAT USER. These constants are converted during the initialization process. If an error occurs during this process, a syntax error is issued, and processing stops.

The value that you specify applies to all output fields for the corresponding SELECT statements.

If an expression error for a SELECT statement that is processed by Db2 occurs, Db2 issues an SQL error message, and the default value does not apply.

DEFAULT

The default value is assigned. This value depends on the data type.

NULL_OR_DEFAULT

If the output field allows a null value, the NULL value is assigned to the field. Otherwise, the default value is used.

TRIM

Allows you to suppress the trailing blank characters when data is unloaded into VARCHAR, VARGRAPHIC, CLOB, and DBCLOB output fields expect if SPANNED YES is used. The length of the output field is adjusted to match the effective number of characters that are written.

If you specify FORMAT DELIMITED, the TRIM keyword applies to output fields with CHAR and GRAPHIC data types.

If you specify the TRIM keyword at the GLOBAL level, it applies only to the USER format unless YES is specified for the VUU057/OPALLFMT PARMLIB parameter. If you specified YES for the VUU057/OPALLFMT PARMLIB parameter or if you specify the TRIM keyword at the SELECT level, the TRIM keyword applies to any output format.

The TRIM keyword does not apply when numeric data is converted to numeric external.

NO

Trailing blank characters are not suppressed.

YES

Trailing blank characters are suppressed.

The default value is the value of the VUU059/ULTRIM PARMLIB parameter.

PADDING

Specifies the padding character that Db2 HPU uses at the column level or the record level.

Column

The padding character is used to pad the null values when you specify NULLPAD YES to pad the output field when the specified value is shorter than the field or to pad numeric data that is converted to character data.

Record

The padding character is used to pad the end of the output record up to the length of the physical record when the format of the DCB (RECFM) is fixed and when the output record is shorter than the LRECL.

The following syntaxes are supported:

PADDING ( SBCS padding [, DBCS padding [, record padding ] ] )

Specifies the padding characters for SBCS and DBCS characters when a character string is converted to a longer character string and when the generated record is padded to its specified length.

SBCS padding

The default value is ' ' (an EBCDIC SBCS blank character). You can specify the value in the following ways:

'c'

Specifies an EBCDIC SBCS character. This character can be converted to match the output CCSID.

X'hh'

Specifies an SBCS character in hexadecimal format. This character is used as-is, regardless of the output CCSID.

DBCS padding

The default value is the DBCS space character in the output CCSID. To obtain this DBCS space character, the UTF16 character UX'3000' is converted into the output CCSID. You can specify the value in the following ways:

X'hhhh'

Specifies a DBCS character in a 2-byte hexadecimal format. This character is used as-is, regardless of the output CCSID.

G'SI_graphic_char_SO'

Specifies a graphic character as a graphic constant that is expressed in EBCDIC. Shift-out/shift-in bytes are included. This character can be converted to match the output CCSID.

GX'hhhh'

Specifies a graphic character as a hexadecimal EBCDIC DBCS constant. This character can be converted to match the output CCSID.

UX'hhhh'

Specifies a graphic character as a hexadecimal UTF16 Unicode constant. This character can be converted to match the output CCSID.

Record padding

Depending on the output format that is used to unload the data, the default value can be either of the following values:

• X'00' for the DSNTIAUL format and the EXTERNAL format,
• The SBCS blank character that was converted in the output CCSID for all other formats.

You can specify the value in the following ways:

'c'

Specifies an EBCDIC SBCS character. This character can be converted to match the output CCSID.

X'hh'

Specifies an SBCS character in hexadecimal format. This character is used as-is, regardless of the output CCSID.

If you do not specify the PADDING keyword, the following values are default values for padding at the character level and the record level:

SBCS characters

• X'00' for variable-length columns in the DSNTIAUL format and the EXTERNAL format,
• The SBCS blank character that was converted to match the output CCSID for all other formats and, if padding at the column level is not specified, for columns that use an INTO clause or a REFORMAT keyword.

DBCS characters

UTF16 value UX'3000' that was converted to match the output CCSID

Record padding character

• X'00' for the DSNTIAUL format and the EXTERNAL format,
• The SBCS blank character that was converted to match the output CCSID for all other formats.

Important: When you specify FORMAT DSNTIAUL or FORMAT EXTERNAL, PADDING () does not give the same result as omitting the PADDING keyword because the SBCS padding is x'00' when the PADDING keyword is omitted, and it is the SBCS blank character when the PADDING keyword is specified.

REFORMAT

Defines the default reformatting for all columns that correspond to a specific Db2 definition.

internal-data-type

Specifies a Db2 data type as it is specified in the CREATE TABLE statement. For columns that allow a length or length and scale specification, such as CHAR or DECIMAL columns, you can specify the data type with or without the length. Specifying the data type without the length defines a default conversion for all columns with this data type. If you specify a data type plus a length, such as CHAR(8), the default conversion applies only to columns that are defined with this precise data type. The following diagram shows the syntax of the internal data type:

internal-data-type

output-data-type

The following syntax diagram shows the output data type:

output-data-type

Notes:

• ¹ The DATE, TIME, and TIMESTAMP data types without the EXTERNAL keyword correspond to the internal DATE/TIME/TIMESTAMP format, and DATE_x, TIME_x, and TIMESTAMP_x correspond to the formats that are defined in the OPTIONS description.

column-format-option

Use the column format option block in the REFORMAT clause of the OPTIONS block, in the FORMAT USER statement, or in the INTO clause of the SELECT statement. The following diagram shows the syntax of the column format option:

column-format-option

For example, you can use the REFORMAT keyword to specify that you want all CLOB columns that are processed are changed into CLOBF columns by using a specific TEMPLATE statement without having to define it in an INTO clause or in a FORMAT USER statement. You can also use the REFORMAT keyword to convert all DECIMAL(10,0) columns in INTEGER format.

The REFORMAT keyword is especially useful to make format conversions when you use LISTDEF or LISTDEFTBV because the INTO clause is not supported in such a case and using the USER format is either not possible.

The formatting options that are specified in the REFORMAT keyword are overridden by the options that are defined in the INTO clause of the fast select block by the LIKE keyword that is used in the DSNTIAUL format or the VARIABLE format and by the conversion option that is defined in the USER format.

PADDING padding-character

Specifies the padding character, where padding-character is the padding character to be used when padding is required for a column.

Padding is used to convert characters to a longer string. Padding is typically applied to the end of character strings. If you specify JUST RIGHT, padding is added at the beginning of the string.

For SBCS data, you can specify the padding character in the following ways:

'c'

Specifies an EBCDIC SBCS character. This character can be converted to match the output CCSID.

X'hh'

Specifies an SBCS character in hexadecimal format. This character is used as-is, regardless of the output CCSID.

For DBCS data, you can specify the padding character in the following ways:

X'hhhh'

Specifies a DBCS character in a 2-byte hexadecimal format. This character is used as-is, regardless of the output CCSID.

G'SI_graphic_char_SO'

Specifies a graphic character as a graphic constant that is expressed in EBCDIC. Shift-out/shift-in bytes are included. This character can be converted to match the output CCSID.

GX'hhhh'

Specifies a graphic character as a hexadecimal EBCDIC DBCS constant. This character can be converted to match the output CCSID.

UX'hhhh'

Specifies a graphic character as a hexadecimal UTF16 Unicode constant. This character can be converted to match the output CCSID.

You must specify a single-byte character ('c' or X'hh') for SBCS data and a double-byte character (G'SI_graphic_char_SO' or GX'hhhh' or UX'hhhh') for DBCS data. Any other combination is rejected.

The following examples show how you can specify padding:

• PADDING '*'
• PADDING X'00' (binary zero)

For SBCS data, the default padding character is an SBCS blank character that is converted in the output CCSID, if needed.

For DBCS data, the default padding character is the UTF16 character UX'3000' that is converted in the output CCSID, if needed.

DELIM literal

When this keyword specified in a USER block, it indicates the delimiter to be used in external DATE or TIME fields. The literal must be a single character and must be one byte long, regardless of the literal CCSID.

For DATE fields, a hyphen (-) is the default value.

For TIME fields, a period (.) is the default value.

For the TIMESTAMP column, both delimiters are used.

LENGTHBYTE

Specifies whether to write the two length bytes for variable-length columns to the output data set.

YES

The two length bytes are written.

NO

The two length bytes are not written.

The default value is YES.

LENGTH

Specifies whether the real or maximum length is to be used for variable-length fields.

REAL

The length of the field does not change (value of the two length bytes).

MAX

The output field is padded to its maximum length with binary zeros.

The LENGTH keyword is useful only for variable-length fields.

The default value is REAL.

NULLID

Specifies whether a null indicator byte is added to the beginning of an output field. You can specify NULLID in the OPTIONS block also.

YES

The null indicator is created. If the column is null, this indicator is set to the X'FF' value. Otherwise, the indicator is set to X'00'. The indicator can be used by the Db2 LOAD command to load null values into a table. You can change values of the null indicator by using the NULL keyword.

NO

The null indicator is not created.

The default value is YES.

JUST

Specifies whether to align the output character string. The JUST keyword specifies right or left justification for extended numeric values or for character strings when strings are converted to a greater length.

RIGHT

Justify the output character string to the right.

LEFT

Justify the output character string to the left.

The default value depends on the type of string. For conversion between character strings, the default value is LEFT. For numeric conversions in strings, the default value is RIGHT.

PIC block

For a description of the PIC block, see the PIC keyword.

NULL

Indicates whether the null indicator is generated in the output data set. You can specify NULL in the OPTIONS block also.

value-1

Indicates the value of the null indicator when the column value is NULL. It can be specified in character ('C') or hexadecimal (X'hh') format.

value-2

Indicates the value of the null indicator when the column value is NOT NULL. It can be specified in character ('c') or hexadecimal (X'hh') format.

OFF

No null indicator is generated.

The default is the value that is specified for the VUU014/ULNULL PARMLIB parameter.

CCSID

Specifies that a CCSID conversion will be done on the output field. The CCSID conversion between the CCSID of the unloaded data and the CCSID that was specified in this parameter must be defined. When a LOB file reference is specified, the specific CCSID conversion is applied on the LOB data. The LOBFILE DSNAME that is generated in the output record is kept in the default CCSID of the output file.

ccsid

Specify a valid CCSID value for the CCSID of the output field.

UTF8

Unicode Transformation Format, 8-bit encoding form is used. UTF8 is equivalent to 1208.

UTF16

Unicode Transformation Format, 16-bit encoding form is used. UTF16 is equivalent to 1200.

BLOBF

Specifies that the output field will contain the name of the file in which the BLOB is to be unloaded without CCSID conversion.

CLOBF

Specifies that the output field will contain the name of the file in which the CLOB will be unloaded with any required CCSID conversion.

DBCLOBF

Specifies that the output field will contain the name of the file in which the DBLOB will be unloaded with any required CCSID conversion.

template-statement-name

Specifies the name of a TEMPLATE statement that is used to allocate the file that will contain the LOB data. The DSN of the TEMPLATE statement that is used must contain either the &UNIQ variable that is explicitly coded or automatically generated for a PDS or PDS/E, or a user-defined variable that is associated with a TEMPLATESET to the :RECNUM predefined variable.

BLOBF, CLOBF, and DBCLOBF output types can be used only when converting BLOB, CLOB and DBCLOB columns. The output type that is defined with the TYPE keyword must be either CHAR or VARCHAR, and the corresponding column must be large enough to contain the generated data set name.

To use LOB file reference with a table that comes from a LISTDEF or a LISTDEFTBV, use BLOBF, CLOBF, or DBCLOBF in the REFORMAT keyword of the OPTIONS block. This option specifies that all LOB data will be unloaded by using LOB file reference. The associated TEMPLATE statement must distinguish the generated files to prevent duplicates. When you unload data from a LISTDEF or a LISTDEFTBV with tables that contain potentially more than one LOB column, the TEMPLATE statement that is used in the REFORMAT keyword can contain a user-defined variable that is associated with the :COLNUM or :COLNAME predefined variables. When you specify the &TS or &SN variables in a TEMPLATE statement that is used with BLOBF, CLOBF, or DBCLOBF, Db2 HPU substitutes the name of the table space that stores the LOB column value, not the base table space name. By substituting the name of the table space, Db2 HPU can generate unique data set names for each LOB column.

IFERROR

Defines the value that must be assigned to the output field if a conversion error occurs when the output field is formatted or if an error occurs when an expression is evaluated by Db2 HPU. IFERROR applies only to conversion or formatting that was made during the unload process. IFERROR does not apply to conversion errors when constants are specified in the SELECT statement and these constants are converted into a different data type by using an INTO clause, a REFORMAT keyword, or a FORMAT USER specification. These constants are converted during the initialization process. If an error occurs during this process, a syntax error is issued, and processing stops.

If an expression error for a SELECT statement that was processed by Db2 occurs, Db2 issues an SQL error message, and a default value does not apply.

value

Specifies a value to assign to the output field in a conversion error or an error that occurs when an expression is evaluated. The following constant values are valid:

Character or hexadecimal constants

Character constants or hexadecimal constants are accepted for any type of output field. In CCSID conversion, character constants are converted from EBCDIC into the output CCSID, but hexadecimal constants are not converted into the output CCSID.

When you use the following output data types, Db2 HPU uses CCSID conversion for character constants:

• CHAR
• VARCHAR
• CLOB
• GRAPHIC
• GRAPHIC EXTERNAL
• VARGRAPHIC
• DBCLOB
• SMALLINT EXTERNAL
• INTEGER EXTERNAL
• BIGINT EXTERNAL
• FLOAT EXTERNAL
• DECIMAL EXTERNAL
• DECFLOAT EXTERNAL
• DATE_EXTERNAL
• DATE_A to DATE_P
• DATE_DB2
• TIME EXTERNAL
• TIME_A to TIME_E
• TIME_DB2
• TIMESTAMP EXTERNAL
• TIMESTAMP_A to TIMESTAMP_G

When you use the following output data types, Db2 HPU does not use CCSID conversion for character constants:

• BINARY
• VARBINARY
• BLOB
• SMALLINT
• INTEGER
• BIGINT
• FLOAT
• DECIMAL PACKED
• DECIMAL ZONED
• DECFLOAT
• DATE
• DATE_Q - DATE_R
• DATE_0 - DATE_7
• TIME
• TIME_0
• TIMESTAMP
• TIMESTAMP_0.

Numeric constants

Use numeric constants only if the output field corresponds to numeric data in internal format. Otherwise, use a character constant.

The following table shows the conversions that are accepted:

Table 7. Accepted conversion types

Output data type	Type of numeric constant accepted
SMALLINT	Integer constant
INTEGER	Integer constant
BIGINT	Integer or BIGINT constant
DECIMAL	Integer constant that fits in the DECIMAL output field or a decimal constant with scale and precision that is smaller or equal to the scale and precision of the output field
FLOAT	Integer, BIGINT, decimal, or float constants
DECFLOAT	Any numeric type

DEFAULT

The default value is assigned to the field. The default value depends on the data type.

NULL_OR_DEFAULT

If the output field allows a null value, the NULL value is assigned to the field. Otherwise, the default value is assigned.

NULL

The NULL value is assigned to the field. The output field must include a null indicator.

CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP

The assigned date, time, or timestamp is identical for each row, and its value is obtained at the beginning of the process. In internal or external format, CURRENT_DATE is allowed only for the DATE field; CURRENT_TIME is allowed only for the TIME field; and CURRENT_TIMESTAMP is allowed only for the TIMESTAMP field.

TRIM

Allows you to suppress the trailing blank characters when you unload data into VARCHAR, VARGRAPHIC, CLOB, and DBCLOB output fields. If you specify FORMAT DELIMITED, the TRIM keyword applies to output fields with CHAR and GRAPHIC data types. The length of the output field is adjusted to match the effective number of characters that are written.

The TRIM option does not apply when numeric data is converted to numeric external.

NO

Trailing blank characters are not suppressed.

YES

Trailing blank characters are suppressed.

The default value is the value of the VUU059/ULTRIM PARMLIB parameter.

TEMPLATESET

Use the TEMPLATESET block to assign values to any user-defined variable that is used in a TEMPLATE definition. Assign values by using string constants or predefined variables that are set by Db2 HPU at run time. A colon (:) must precede predefined variable names. The following diagram shows the syntax of the TEMPLATESET block:

A predefined variable can be one of the following variables:

:COLNUM

Specifies the position of the column in the SELECT statement. This variable is a 5-digit numeric value and is generally used when LOB columns are unloaded by using a LOB file reference (CLOBF, BLOBF, or DBLOBF data types).

:SELNUM

Specifies a unique number for the SELECT statement regarding:

• the set of SELECT statements from a given UNLOAD statement for explicit SELECT statements
• the set of all the tables involved in a LISTDEF statement for a SELECT FROM LIST statement [HPU 5.1 if the VUU030/ULOPTNS parmlib parameter specifies SELNUM_PER_TS_IF_LISTDEF(NO)]
• the set of tables of a table space involved in a LISTDEF statement for a SELECT FROM LIST statement [in HPU 5.1 only if the VUU030/ULOPTNS parmlib parameter specifies SELNUM_PER_TS_IF_LISTDEF(YES)].

This variable, is a 5-digit numeric value that facilitates creating distinct dataset names when large or unpredictable number of implicit or explicit SELECT statements are specified in a single UNLOAD statement. This is especially useful for an UNLOAD statement that involves a lot of SELECT statements against the same table space as the tablespace name, through the &TS variable, is not enough to get a unique data set name.

Important: Do not use this variable if you need to generate a predictable name for a given SELECT statement as its computation only ensures the uniqueness but neither its absolute value especially regarding its relationship with any of the other variables for TEMPLATE dataset names. As a consequence, use this feature only if your post-processing can determine the data set names generated by Db2 HPU at run time or if you use the Db2 HPU LOADDDN feature with the LOADINDDN YES option so that the generated LOAD control cards take the data set names of the unload data into account.

:UNLNUM

Designates the sequential number of the UNLOAD statement. This variable is a 5-digit numeric value determined as follows:

• it takes the reading sequence into account: numbers are incremented along with the reading sequence.
• if the UNLOAD statement does not contain any SELECT statement with a FROM LIST clause, its number is the number of the previous UNLOAD statement (regarding the reading sequence in the SYSIN data set) plus one.
• if the UNLOAD statement contains a SELECT statement with a FROM LIST clause, the single UNLOAD statement is expanded into a sequence of one UNLOAD statement per table space described by the LISTDEF statement. Each table space is associated with a :UNLNUM value, starting with 1 and incremented by one for each table space. Be aware that, as the ULNUM value assigned to a given table space is not predictable, only use the ULNUM variable to distinguish the output datasets related to a given table space) and to associate the output datasets (e.g. OUTDDN and LOADDDN) related to the same tablespace but do not use this variable as a part of an input dataset name (e.g. DDLDDN or COPYDDN) as this could lead Db2 HPU to process the unload of some table spaces with wrong input datasets.

:RECNUM

The record number is a 15-digit numeric value and is internally processed. By default, the variable that is associated with :RECNUM is replaced by the last seven digits of the record number. A substring can be applied to this variable to obtain another part of the record number, or to retrieve fewer digits. This variable is generally used when LOB columns are unloaded by using a LOB file reference (CLOBF, BLOBF, or DBLOBF data types).

:COLNAME

This predefined variable contains the first characters of the column name. When the associated variable is replaced by its value, it is truncated to make it a valid qualifier in the generated DSNAME. Valid values are 1 - 8 characters. This variable is generally used when LOB columns are unloaded by using a LOB file reference (CLOBF, BLOBF, or DBLOBF data types).

When these variables cannot be evaluated, :COLNUM and :SELNUM are set to 00000; :RECNUM is set to 0000000; and :COLNAME is set to COLn, where n is a sequential number. This sequential number ensures unicity, but it does not have a specific meaning regarding the actual database or the table space that it represents.

:SELNUM, does not apply when the TEMPLATE statement is used with UNLDDN.

:COLNUM, :COLNAME, and :RECNUM, do not apply when the TEMPLATE statement is used outside of a LOB file reference.

You can code the TEMPLATESET block in the OPTIONS or GLOBAL OPTIONS block. When the TEMPLATESET block is used with LOB columns that use a LOB file reference, the TEMPLATESET block can be used in the column format option block. The COLUMN FORMAT OPTIONS block can be used in the REFORMAT keyword, in the INTO clause, or in the FORMAT USER definition.

All TEMPLATESET definitions that are coded in high-level syntax are available at a lower level. For example, if TEMPLATESET is coded to define a user variable in the GLOBAL OPTIONS block, this variable will be available to build data set names in the templates that are used in all UNLOAD commands and for all SELECT statements in these UNLOAD commands. However, if TEMPLATESET is defined only in an OPTIONS statement that is coded in a SELECT statement, the variable definition will be available only for the TEMPLATE statements that are used in this specific SELECT statement. The definition of a user variable with a TEMPLATESET that is made at a lower level overrides the definition of the same user-defined variable that was made at a higher level but only at this lower level. For example, if a user variable is set to :SELNUM in the GLOBAL OPTIONS block and set to ABC in the OPTIONS statement that is coded in one of the SELECT statements, the replaced value will be :SELNUM for all other SELECT statements, and ABC for the SELECT statement that overrides the variable definition.

HIDDEN

Specifies whether hidden columns are unloaded when a SELECT * statement is used. You can also specify this keyword as the third parameter after the UTILID in the PARM field in the EXEC statement. When the HIDDEN parameter is specified and this keyword is specified in SYSIN, the value that is specified in SYSIN is used.

NO

Specifies that hidden columns are not unloaded when a SELECT * statement is used.

YES

Specifies that hidden columns are unloaded when a SELECT * statement is used. For SELECT statements that are processed by Db2, the HIDDEN keyword is ignored.

If the HIDDEN keyword is not specified in the SYSIN, the value for HIDDEN that is specified on the EXEC card is the default value. If the HIDDEN keyword is not specified on the EXEC card, the default value is the value of the VUU042/ULHIDDEN PARMLIB parameter.

SPANNED

For logical unload statement (using a SELECT clause) which result table has LOB columns and one of the CLOB, BLOB or DBCLOB format is applied to these columns. This option specifies whether the output data must be unloaded into a VBS data set in spanned record format compliant with the Db2 LOAD utility used with the FORMAT SPANNED YES option.

NO

The records of the output data set do not have the spanned format.

YES

The records of the output data set have the spanned format. The content of the LOB data is unloaded along with the data from the other columns.

Note:

• SPANNED YES only applies to LOB data being unloaded as LOB but not if a conversion to any other type is requested.
• SPANNED YES can only be used when the FORMAT option specifies one of the following:
  • VARIABLE
  • USER provided the related LOAD statement can be generated
• SPANNED YES cannot be used with DELIMITED, INTERNAL and EXTERNAL format.
• when SPANNED YES is specified, the parameter TRIM YES does not apply for LOB columns.
• SPANNED YES cannot be used if XML data is to be unloaded.

ENFORCE_COLUMN_ORDER

If SPANNED YES option is requested, the LOB data will be placed at the end of the result table (defined by the column list of the SELECT clause and by the optional INTO clause). This condition might not be fulfilled because of the sequence of the explicit expressions that define the result table or because of the underlying table definition when a SELECT * is specified. Db2 HPU can rearrange the columns of the result table to ensure the LOB columns are gathered at the end of the record. The ENFORCE_COLUMN_ORDER specifies if the reordering feature is disabled, i.e. if the native column sequence of the result table is kept.

YES

The order of the column in the select statement is kept. If the LOB columns are not the last (rightmost) columns of the result table, the processing stops and an error message is issued.

NO

If necessary, Db2 HPU rearranges the columns of the specified result table to ensure the LOB columns are gathered at the end of the record.

XMLSET

XMLSET specifies the tags to be used for the XML output. Both columns and records can be tagged. You can specify the way columns (by specifying the COLUMN keyword) and/or records (by specifying the RECORD keyword) are tagged.

Specify either an xmltag if you want a tag to be generated or OFF if you want no tag to be generated.

• COLUMN: specifies that the next keywords apply to columns
• RECORD: specifies that the next keywords apply to records

If XMLSET is not specified, the default value XMLSET (COLUMN = < &COLNAME. > , RECORD = OFF ) applies.

The XMLSET options specified at different level are not merged. Only the more local XMLSET specification is taken into account.

For example, if XMLSET (RECORD = <&TSNAME.>) is specified at the UNLOAD level and XMLSET (COLNAME=<&COLNAME.>) is specified at the SELECT level, the output data set is generated as if the XMLSET specified at he unload level did not exist. Therefore, the default for records (RECORD=OFF) applies (i.e. no tag for records are generated).

The following diagram shows the syntax of the XMLSET block:

A non-delimited-string can be one of the following variables:

DBNAME

Name of the data base the unloaded table belongs to. If the data base name cannot be determined (unsupported select statements), the data base name is substituted with "DBnnn" where nnn is a unique number.

TSNAME

Name of the table space the unloaded table belongs to. If the data base name cannot be determined (unsupported select statements), the data base name is substituted with "TSnnn" where nnn is a unique number.

TBCREATOR

This variable is substituted with the following value according to the precedence order of the list below:

• the table creator specified via the INTO clause ;
• the name of the creator of the table to be unloaded ;
• "CRnnn" where nnn is a unique number of none of the above can be determined.

TBNAME

This variable is substituted with the following value according to the precedence order of the list below:

• the table name specified via the INTO clause ;
• the name of the table to be unloaded ;
• "TBnnn" where nnn is a unique number of non of the above can be determined.

COLNAME

This variable is substituted with the following value according to the precedence order of the list below:

• "UNDEFINED" if not applicable (i.e. used to specify the RECORD xmltag) ;
• the column name specified via the INTO clause ;
• the name of the column to be unloaded ;
• "COLnnn" where nnn is a unique number of none of the above can be determined.

Table 8. Variables usable in xml-templates

Variable	Alias	Description	Scope	Default
UNLNUM	UNLN	Number of the UNLOAD command as affected by Db2 HPU	RECORD & COLUMN	No default
DBNAME	DB	Name of the data base to which the unloaded table belongs	RECORD & COLUMN	DBnn(*)
TSNAME	TS	Name of the table space to which the unloaded table belongs	RECORD & COLUMN	TSnn(*)
SELNUM	SELN	Number of the SELECT statement as affected by Db2 HPU	RECORD & COLUMN	No default
TBCREATOR	TBC	Name of the creator of the unloaded table	RECORD & COLUMN	CRnn(*)
TBNAME	TBN	Name of the unloaded table	RECORD & COLUMN	TBnn(*)
COLNAME	CNAM	Name of the unload column	COLUMN	COLnnn where nnn is the rank of the column in the SELECT statement
COLNUM	CNUM	Rank of the column in the SELECT statement	COLUMN	-1
COLTYPE	CTYP	Db2 data type of the unloaded column	COLUMN	UNDEF
COLLENGTH	CLEN	Length of the unloaded column	COLUMN	-1
COLPREC	CPRE	Precision of the column. Applies only to a decimal column	COLUMN, type decimal	-1
COLSCALE	CSCA	Scale of the column. APplies only to a decimal column	COLUMN, type decimal	-1
COLNULL	CNUL	Nullability attribute of the column (the value is either NULL, NOT NULL or UNDEF if Db2 HPU cannot determine the attribute.	COLUMN	UNDEF

OPTIMIZATION_HINT

Allows to process an SQL statement: SET CURRENT OPTIMIZATION HINT 'string constant' when processing the unload in SQL mode. This option is only available if DB2 parameter is set to FORCE. For example, in Db2 HPU syntax, OPTIMIZATION_HINT 'NOHYB' generates the SQL statement: SET CURRENT OPTIMIZATION HINT = 'NOHYB'

You can clear the CURRENT OPTIMIZATION HINT special register by specifying an empty string. For example, in Db2 HPU syntax: OPTIMIZATION_HINT generates the SQL statement: SET CURRENT OPTIMIZATION HINT =' '

Db2 HPU reports whether DB2 has used the specified optimization hint when processing the SELECT statement by reproducing the SQLCODE+394 or SQLCODE+395 messages issued by Db2 when the use of optimization hints is requested.

QUERY_ACCELERATION value

Allows to have the following SQL statement run when a logical unload is processed in SQL mode:

SET CURRENT SQL ACCELERATION = 'value'

The value can be one of the following values: NONE, ENABLE, ENABLE WITH FAILBACK, ELIGIBLE and ALL. It refers to the relevant Db2 publication for a description of the effect of the QUERY_ACCELERATION special register.

Important: This option is only available if DB2 parameter is set to FORCE.

See the Db2 Universal Database for z/OS SQL Reference for syntax and definitions for Db2 full select.

Note: If QUERY_ACCELERATION parameter is specified (except for NONE value) and if the parameter UNLROWSET or its default VUU0035/ULROWSET is set to 1, the multi row fetch mode is forced to 100 rows.

CHARACTER_LOSS

It specifies the behavior of the product when significant data from character column is lost during unload formatting because of an INTO or REFORMAT clause and an ssss syntax is specified.

Db2 HPU considers a data loss has occurred for a character column if the data truncation removes any character that does not belong to the padded part of the character string, i.e. a sequence of padding characters that ends at the right end of the character string.

The data loss is checked for the following data types:

• CHAR
• VARCHAR
• GRAPHIC
• VARGRAPHIC
• BINARY
• VARBINARY

Specify one of the following values:

IGNORE

No control is done.

The return code of the job is 0 unless another issue occurs.

WARNING

A warning message is issued for any column affected by a data loss.

The return code is 4 unless another issue occurs.

REJECT

An error message is issued for any column affected by a data loss.

The return code of the job is 8 unless another issue occurs.

The default value is the value of the VUU071/LOSSCHAR PARMLIB parameter.

NUMERIC_LOSS

It specifies the behavior of the product when significant data from numeric column is lost during unload formatting ( via INTO or REFORMAT clause) and an ssss syntax is specified.

Db2 HPU considers a data loss has occurred for a numeric column if the numeric value has changed.

The data loss is checked for the following data types:

• SMALLINT
• INTEGER
• BIGINT
• DECIMAL

Specify one of the following values:

IGNORE

No control is done.

The return code of the job is 0 unless another issue occurs.

WARNING

A warning message is issued for any column affected by a data loss.

The return code is 4 unless another issue occurs.

REJECT

An error message is issued for any column affected by a data loss.

The return code of the job is 8 unless another issue occurs.

The default value is the value of the VUU072/LOSSNUM PARMLIB parameter.

ZONED_DEC_SIGN

Specifies the value of the sign bytes used for zoned-decimal numeric values. The first character represents the positive sign, the second one the negative sign.

The default value is ZONED_DEC_SIGN (C,D).

UNICODE_EXPANSION_RATIO

Specifies what expansion ratio (i.e. target data length divided by source data length) to apply to char/varchar data when a conversion from a non-UTF8 CCSID to a UTF8 CCSID is done.

The allowed values are integer from 1 to 4.

The default value is the value of the VUU068/ULUNIEXP parameter.

CONVERSION_TRUNCATION_ALLOWED

Specifies what policy to apply when data truncation is needed because of a CCSID conversion.

YES

Truncation of the output field after a CCSID conversion is allowed.

NO

Truncation of the output field after a CCSID conversion is forbidden. Records requiring truncation are discarded and the return code is set to 4.

The default value is the value of the VUU067/ULCNVTRC parameter.

HANDLE_RECORD_ID

Allows to get more detailed information when formatting errors occur so that identifying accurately the record involved in the error is possible.

When such an error occurs, if HANDLE_RECORD_ID is not specified, the error is reported by a message formatted as follows:

 * error_code * error_type * output_ddname * output_area * content

where:

• error_code is the code for the error type
• error_type is the type of error that was encountered
• output_ddname is the output ddname into which the record causing the error is to be written
• output_area designates the area of the output record causing the error
• content is the hexadecimal representation of the output area to be formatted

The information displayed by this standard/default error message is sometimes insufficient for trouble shooting as it does not allows to accurately determine which record is involved.

If HANDLE_RECORD_ID is specified and if the unload is processed in native mode, the messages are formatted as follows:

* error_code * error_type * output_ddname * output_area * PART partition_number PGNUM page_number ID id_map *  DB :  database  TS : tablespace

where the additional following information is displayed:

• partition_number: the partition number if the table space is partitioned
• page_number: the page number - in hexadecimal representation - in the LDS
• id_map: the ID map - in hexadecimal representation - in the page
• database,tablespace: the identifier of the table space that contains the record that caused the error

The same additional information is also displayed if a $HRECID dd statement is specified for the Db2 HPU exec statement which makes this feature available even if the Db2 HPU native syntax is not used.

Note: requesting the more detailed message to be issued decreases the performance of the unload job run in native mode as Db2 HPU handles longer records when it is specified. As the amount of extra information in the record does not depend on the data to unload, the short the output record the bigger the impact on performance.

Examples of formatting error messages:

The message below gives an example of detailed error message issue if either HANDLE_RECORD_ID or $HRECID dd statement is specified:

* 234 * DATA LOST * OUT      * C1                             * C4E2D5C4C2F0F440
* 234 * DATA LOST * OUT      * PART 0001 PGNUM 00000002 ID 01 *  DB : DSNDB06  TS : SYSTSDBA      

and the corresponding message issued otherwise:

* 234 * DATA LOST * OUT      * C1                             * C4E2D5C4C2F0F440

INTO_RULES

This option allows to change the behavior of the INTO clause that applies when the field-name is explicitly specified but its output-data-type is not specified.

COLTYPE

The output-data-type is taken from the column type of the resulting table and the column-format-options specified through the INTO clause apply. Any REFORMAT clause is ignored.

REFORMAT

If a REFORMAT clause is applicable to the column type of the resulting table, the output-data-type specified through the REFORMAT clause applies as well as the associated column-format-options except for the items that are also specified in the INTO clause that take precedence. If no REFORMAT clause is applicable to the resulting column, the output-data-type is that of the column and the related column-format-options applies.

The default value is the value of the VUU089/INTORUL1 parameter.