Conditions
For details about predicates, see predicate block.
You can specify a condition that combines multiple predicates. The following Truth table for AND, OR, NOT operators illustrates the results of a VERIFY condition on two predicates based on the truth value of each.
Result of predicate | Result of VERIFY condition | |||
|---|---|---|---|---|
p | q | p AND q | p OR q | NOT p |
T | T | T | T | F |
T | F | F | T | F |
F | T | F | T | T |
F | F | F | F | T |
T = true F = false | ||||
When CHECK PLUS determines the resulting value of a condition to be false, it stops evaluating any more predicates and issues messages BMC52470E and BMC52471I. These messages report the offset of the row that contains the failed condition and list all columns specified in the VERIFY condition along with their corresponding column values. If an index exists, CHECK PLUS also lists and flags the index key columns and corresponding values to assist you in identifying the row that is in violation.
predicate block
A predicate is a simple comparison of a single column value to a constant or list of constants. 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.
CHECK PLUS performs the comparison with the Db2 internal column value. The Db2 internal value includes FIELDPROC encoding and Db2 normalization. You can use the comparison operators described in Comparison operators.
Operator | Description |
|---|---|
< | Less than |
< = | Less than or equal |
= | Equal |
< > | Not equal |
¬ = | Not equal |
> = | Greater than or equal |
> | Greater than |
IN | Equal to any |
NOT IN | Not equal to all |
IS NULL | Column is null |
IS NOT NULL | Column is not null |
LIKE
The LIKE operator in CHECK PLUS is a subset of the Db2 LIKE operator, and its comparison rules follow those of the Db2 LIKE operator. You can use LIKE only with CHARACTER and VARCHAR columns.
CHECK PLUS treats double quotes within a string as a single quote and interprets this single quote as a literal character.
ESCAPE 'character'
Use this option with the LIKE operator to designate a single character as an escape character. You can then use this character one or more times within your search string to specify the literal occurrence of an underscore ( _ ) or a percent sign (%). To do so, precede the underscore or percent sign with the escape character that you have specified.
When you do not include the ESCAPE option, you have not specified an escape character, and CHECK PLUS recognizes underscores and percent signs as wildcard characters.
constant
The constant specifies a value to be compared to the column value. CHECK PLUS then evaluates the result based on the preceding relative operator. For the comparison, the associated constant must match the column’s data type (that is, numeric to numeric, string to string, and date/time to date/time).
Additional considerations
The following additional information applies to constants in predicates:
- For the LIKE predicate, you can use only a character, graphic, or hex string.
- For comparison, if the string is shorter than the column length, the string is padded with blanks.
- Although not prevented or restricted by Db2, you should avoid nonstandard comparisons. See rules for constants in the IBM Db2 SQL Reference.
- If you specify a list of constants, you can improve performance by specifying the constants in ascending sequential order. When you specify the list in this order, you save processing time because CHECK PLUS does not have to sort the list.
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 strings | 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 except that the length is limited to 255 bytes |
date/time strings | Identical to Db2 SQL date/time strings CHECK PLUS allows only ISO, USA, EUR, and JIS formats. |
binary strings | Identical to Db2 SQL binary strings |
other | Not supported |
NULL
You can specify the keyword NULL as a constant for columns that can be set to null. NULL is generally used with the =, < >, ¬ =, and IS/IS NOT operators, but you can specify it with any of the allowed operators. The null value is always higher than all other values. When a column is set to NULL, each nullable field in the output is followed by a null indicator field.
If a column value is NULL, a predicate (other than IS/IS NOT or where the constant=NULL) does not evaluate to “unknown” as it does in Db2. Normally, this produces the same result as Db2 because an “unknown” value for a predicate is treated as “false”. However, using NOT with a predicate that evaluates to “unknown” is treated as “true”, which is different than Db2.
CURRENT DATE
The CURRENT DATE option describes the current date. CHECK PLUS takes the CURRENT DATE from the local time-of-day clock during the UTILINIT phase.
labeledDuration
Use this option to describe the current date minus a duration in either days, months, or years. The rules for date/time arithmetic using labeled durations are identical to the DB2 SQL rules.
CURRENT TIMESTAMP
The CURRENT TIMESTAMP option describes the current timestamp. CHECK PLUS takes the CURRENT TIMESTAMP from the local time-of-day clock during the UTILINIT phase.
labeledDuration
Use this option to describe the current timestamp minus a duration in either days, months, or years. The rules for date/time arithmetic using labeled durations are identical to the Db2 SQL rules.