predicate
A predicate is a simple comparison of a single column value to a constant or list of constants.
Related topic
The column name must be a column of the named table, and the length of the column must be less than or equal to 255. You can use a predicate on a WHERE clause or with the IF option in a field specification.
comparison operator
UNLOAD PLUS performs the comparison with the Db2 internal column value, which includes FIELDPROC encoding and Db2 normalization. You can use the comparison operators shown in the following table:
Comparison operators
Operator | Description |
|---|---|
< | Less than |
¬< | Not less than |
< = | Less than or equal |
= | Equal |
< > | Not equal |
¬ = | Not equal |
> | Greater than |
¬> | Not greater than |
> = | Greater than or equal to |
IN | Equal to any |
NOT IN | Not equal to all |
IS NULL | Column is null |
IS NOT NULL | Column is not null |
LIKE 'string' | Valid only in conjunction with the WHERE clause This operator is a subset of the Db2 LIKE operator. Only CHARACTER, VARCHAR, BINARY, VARBINARY, GRAPHIC, and VARGRAPHIC columns are allowed. UNLOAD PLUS treats double quotes within a string as a single quote, and interprets this single quote as a literal character. |
Restrictions
The following restrictions apply when using a predicate with the IF option. For more considerations when using a predicate with the IF option, see the description of the IF option.
- UNLOAD PLUS does not support CURRENT DATE, CURRENT TIMESTAMP, and LIKE for the IF predicate option in the field specifications.
- When DIRECT NO is in effect, you can use only the equal or not equal to operators to compare the following types of columns: numeric, DATE in USA or EUR formats, or TIME in USA format.
ESCAPE 'character' | Use this option to designate a single character as an escape character that you can use one or more times within your LIKE string to specify the literal occurrence of an underscore ( _ ) or a percent sign (%). To use this character in your LIKE string, put the escape character before the underscore or percent sign. If you do not include an ESCAPE clause, you have not specified an escape character. When you include an ESCAPE clause, UNLOAD PLUS recognizes underscores and percent signs as wildcard characters. |
|---|
constant
The constant specifies a value to be compared to the column value. UNLOAD PLUS then evaluates the result based on the preceding relative operator. For the comparison, the associated constant must match the data type of the column (that is, numeric to numeric, string to string, and date/time to date/time).
Additional considerations
The following table describes the requirements for constants in predicates. In addition, consider the following information:
- For the comparison, if the string is shorter than the column length, UNLOAD PLUS pads the string with blanks. However, UNLOAD PLUS processes VARCHAR comparisons like Db2 processes them.
- Although not prevented or restricted by Db2, we recommend that you avoid nonstandard comparisons. See rules for constants in the IBM Db2 SQL Reference.
- If you specify a list of constants, you can use ascending sequential order to improve performance. When you specify the list in this order, you save processing time because UNLOAD PLUS does not sort the list.
- For more considerations when using constants with the IF option, see the description of the IF option.
Rules for constants
Type of constant | Additional details |
|---|---|
Integer | Identical to Db2 SQL integer constants The constant must be within the range of the column’s data type. |
Decimal | Identical to Db2 SQL decimal constants The constant must be within the range of the column’s data type. |
Character string | Identical to Db2 SQL strings except that the length is limited to 255 bytes Use ' ' to denote an empty string. |
Hexadecimal strings | Identical to Db2 SQL hexadecimal strings with the following exceptions:
|
Binary strings | Identical to Db2 SQL binary string constants except that the length is limited to 255 |
Graphic string | Identical to Db2 SQL graphic string constants except that the length is limited to 255 |
Date/time string | With the following exceptions, identical to Db2 SQL date/time strings:
|
Other | Not supported |
NULL
You can specify the keyword NULL as a constant for columns that are set to null. You can specify NULL with any of the allowed operators. The null value is always higher than all other values. When a column is set to NULL, a null indicator field follows each nullable field in the output. See Example 1 — Unloading partitions to separate data sets for sample output.
Consideration
If a column value is NULL, a predicate (other than IS/IS NOT or where the constant is equal to NULL) does not evaluate to 'unknown' as it does in Db2. Normally this produces the same result as in Db2, because UNLOAD PLUS treats an 'unknown' value for a predicate as 'false.' However, using NOT with a predicate that evaluates to 'unknown' is 'true,' which is different in Db2.
CURRENT DATE - labeledDuration
This option describes the current date or, optionally, the current date minus a duration in days, months, or years. The rules for date/time arithmetic using labeled durations are identical to the Db2 SQL rules. UNLOAD PLUS takes the current date from the local time-of-day clock during the UTILINIT phase.
CURRENT TIMESTAMP - labeledDuration
This option describes the current timestamp or, optionally, the current timestamp minus a duration in days, months, or years. The rules for date/time arithmetic using labeled durations are identical to the Db2 SQL rules. UNLOAD PLUS takes the current timestamp from the local time-of-day clock during the UTILINIT phase.
You cannot specify CURRENT TIMESTAMP WITH TIME ZONE. However, if you are comparing the current timestamp to a TIMESTAMP WITH TIME ZONE column, UNLOAD PLUS uses the time zone from the DSNHDECP IMPLICIT_TIMEZONE value.