Limited supportBMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see LOADPLUS for DB2 13.1.

predicates

Predicates are comparisons of the contents of a field, column, or (start:end) string to a constant, as shown in the following examples.

predicate.png

LOADPLUS uses predicates as part of WHEN, NULLIF, and DEFAULTIF syntax. Predicate operation differs based on the value of the RULES installation option.

Predicate examples

CITY='AUSTIN'
(1:6)¬=X'00000000000C'
INT_COL > 5

 The following table defines how we use the terms fieldName and columnName when describing the left side of a predicate. The sections that follow this table describe these specifications in more detail.

Name

Description

fieldName

A name included in a field specification that does not correspond to a column in the table that you are loading

columnName

A column of the table that you are loading

This column must be included in a field specification unless you also specify FORMAT BMC, FORMAT UNLOAD, or FORMAT BMCUNLOAD.

fieldName

When you specify a name on your predicate and no column of the same name exists in the table that you are loading, LOADPLUS assumes that you have specified a field name. This field name must be included in your field specification and the length of the field must be less than or equal to 255.

Restrictions

Field names in predicates have the following restrictions:

  • You cannot specify a row ID or identity column.
  • You cannot specify a field name when you use WHEN with FORMAT BMC, FORMAT UNLOAD, or FORMAT BMCUNLOAD. You must specify a column name.
  • For restrictions on the constants that you can specify with a field name in a predicate, see Table 3.

columnName

If you specify a column name, it must be a column of the named table, and the length of the column must be less than or equal to 255. Column names must have corresponding field specifications unless you also specify FORMAT BMC, FORMAT UNLOAD, or FORMAT BMCLOAD. LOADPLUS accepts column names that include DBCS characters.

Restrictions

Column names in predicates have the following restrictions:

  • You cannot specify a row ID or identity column.
  • For restrictions on the constants that you can specify with a column name in a predicate, see Table 3.

(start:end)

If you specify (start:end), the string defined by (start:end) must be contained entirely in the input record. The length indicated by (start:end), must not be greater than 255 or less than the length of the string. If you do not specify the ending position, LOADPLUS uses the length of the constant for the comparison.

Restrictions

Note the following restrictions when using (start:end):

  • If you specify WHEN with FORMAT BMC, FORMAT UNLOAD, FORMAT BMCUNLOAD, or FORMAT CSV, you cannot specify (start:end). For FORMAT BMC, FORMAT UNLOAD, and FORMAT BMCUNLOAD, you must specify a column name. For FORMAT CSV, you must specify a field name or column name.
  • For restrictions on the constants that you can specify with (start:end) in a predicate, see the table in constant section.

comparison operators

Use comparison operators in LOADPLUS predicates to indicate how you want LOADPLUS to compare the left and right sides of predicates.

Comparison rules govern the availability of operators and how processing works. Two sets of comparison rules are available, based on the value of the RULES installation option, as described in the following table: 

Operators

RULES=STANDARD

RULES=BMC

<

Less than

 

X

<=

Less than or equal

 

X

=

Equal

X

X

<>

Not equal

X

X

¬ =

Not equal

X

X

>=

Greater than or equal

 

X

>

Greater than

 

X

IN

Equal to any

X

X

NOT IN

Not equal to all

X

X

constant

The right side of a predicate is a constant. Two different sets of rules apply to constants based on the value of the RULES option that is in effect.

Additional considerations

The following table describes the requirements for constants in predicates. In addition, consider the following information:

  • Although not prevented or restricted by Db2, you should avoid nonstandard comparisons.

    See rules for constants in the IBM Db2 SQL Reference.

  • LOADPLUS compares up to 71 bytes of each constant against the field, column, or (start:end) value.
  • LOADPLUS does not validate character data or hexadecimal string data against the current CCSID.
  • If you specify a list of constants, note the following information:
    • You can improve performance by using ascending sequential order. When you specify the list in this order, you save processing time because LOADPLUS does not sort the list.
    • When you specify (start:end) with NULLIF or DEFAULTIF, the constants must all be of the same type (for example all character or all hexadecimal) or must all have the same length.

RULES value

Left side of predicate 1

Constant type

Requirements

STANDARD

fieldName

Character string

Enclose the string in single quotes.

Hexadecimal string

  • Enclose the string in single quotes (for example, X'000C').
  • The number of bytes that the string represents must match the length of the field data in the input file (SYSREC).
  • When the predicate references a Unicode field, the string must be in the internal format of the input.

Other

Other types of constants are not supported.

(start:end)

Character string

  • Enclose the string in single quotes.
  • The length of the string must match the length specified by (start:end).

Hexadecimal string

  • Enclose the string in single quotes (for example, X'000C').
  • The number of bytes that the string represents must match the length specified by (start:end).
  • When the predicate references a Unicode field, the string must be in the internal format of the input.

Other

Other types of constants are not supported.

columnName

Character string

  • Enclose the string in single quotes.
  • Precision on a timestamp constant must be less than or equal to the precision defined in the column.

Hexadecimal string

  • Enclose the string in single quotes (for example, X'000C').
  • The number of bytes that the string represents must match the length of the field data in the input file (SYSREC).
  • When the predicate references a Unicode column, the string must be in the internal format of the input.

Other

Other types of constants are not supported.

BMC with fieldName or (start:end)

fieldName

Character string

Enclose the string in single quotes.

Hexadecimal string

  • Enclose the string in single quotes (for example, X'000C').
  • When the predicate references a Unicode field, the string must be in the internal format of the output.

Binary string

Enclose the string in single quotes (for example, BX'000C').

Other

Other types of constants are not supported.

(start:end)

Character string

  • Enclose the string in single quotes.
  • The length of the string must match the length specified by (start:end).
  • The corresponding table column can be any data type.

Hexadecimal string

  • Enclose the string in single quotes (for example, X'000C').
  • The number of bytes that the string represents must match the length specified by (start:end).
  • The corresponding table column can be any data type.
  • When the predicate references a Unicode field, the string must be in the internal format of the output.

Other

Other types of constants are not supported.

BMC with columnName

columnName

Integer

The column must be a numeric data type.

Decimal

The column must be a numeric data type.

Important

To determine whether your site has set the decimal point to a comma, check the Db2 subsystem DSNHDECP setting. To enable LOADPLUS to distinguish comma decimal points from comma separators, include a space after any comma that you use as a separator.

Character string

  • Enclose the string in single quotes.
  • The column must be a character, date, time, or timestamp data type.
  • Precision on a timestamp constant must be less than or equal to the precision defined in the column.

Hexadecimal string

  • Enclose the string in single quotes (for example, X'000C').
  • The column can be any data type.
  • The number of bytes that the string represents must match the length of the column’s internal data. For a VARCHAR or VARBINARY column, the length must be the same length as the maximum length of the column.
  • For a VARCHAR or VARBINARY column, include the two-byte hexadecimal length of the data.
  • Include a preceding null indicator if the column is nullable. For a VARCHAR or VARBINARY column, the length indicator must precede the null indicator.
  • The column must be FIELDPROC encoded.
  • The string must be in Db2 internal format.
  • When the predicate references a Unicode column, the string must be in the internal format of the output.

BMC with columnName (continued)

columnName (continued)

Graphic string

The column must be a graphic data type.

LOADPLUS does not support graphic strings with Unicode data, including the hexadecimal graphic string constants UX'xxxx' and GX'xxxx'.

Important

LOADPLUS does not validate graphic input data to determine whether it contains valid graphic coded character set identifier (GCCSID) values.

NULL

The column must be nullable.

Note: If used with an inequality operator, NULL always collates higher than all other values.

Binary string

  • Enclose the string in single quotes (for example, BX'000C').
  • The column must be a binary data type.

Other

Other constant types are not supported.

1  See the Definition of fieldName and columnName for predicates table for a description of the difference between field name and column name specifications.

Related topic

INTO-TABLE-options

 

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*