RULES installation option

You set the RULES installation option during installation and you cannot override it at runtime. You can specify either RULES=STANDARD or RULES=BMC.

Important

If you specify FORMAT UNLOAD, FORMAT BMC, or FORMAT BMCUNLOAD, LOADPLUS changes the value of this option to BMC, regardless of the value that you specify.

If you specify FORMAT CSV, LOADPLUS changes the value of this option to STANDARD, regardless of the value that you specify.

The information in this topic uses a single sample table to illustrate the RULES option. The following statement creates this sample table:

CREATE TABLE SAMPLE_TABLE
  (CHAR_COL    CHAR(1)      NOT NULL WITH DEFAULT,
   DEC_COL     DECIMAL(5,0),
   DATE_COL    DATE)
IN DATABASE.TABLESPACE

The following sections describe the RULES installation option of the LOAD PLUS, including how handling differs with the different values, and how to avoid error conditions:

Comparison operators allowed
WHEN, NULLIF and DEFAULTIF constants handling
WHEN, NULLIF, and DEFAULTIF processing order
Error conditions with RULES=STANDARD
Error conditions with RULES=BMC

Comparison operators allowed

RULES=STANDARD has more restrictions on comparison operators than RULES=BMC.

Each option permits different comparison operators for use in WHEN, NULLIF, and DEFAULTIF predicates, as shown in the following table:

Operators	Description	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

WHEN, NULLIF and DEFAULTIF constants handling

The RULES option affects how LOADPLUS handles constants in your WHEN, DEFAULTIF, or NULLIF clauses.

These differences are as follows:

With RULES=BMC, LOADPLUS expects the constant to be in EBCDIC format and translates the constant to the CCSID of the table space. If the constant is specified as a hexadecimal string, the string must be in the internal format of the output.
With RULES=STANDARD, LOADPLUS expects the constant to be in EBCDIC format and translates the constant to the CCSID of the input. If the constant is specified as a hexadecimal string, the string must be in the internal format of the input.

WHEN, NULLIF, and DEFAULTIF processing order

The RULES option affects the order in which LOADPLUS performs WHEN, NULLIF, and DEFAULTIF processing.

These differences are as follows:

With RULES=BMC, LOADPLUS first completes, on the input data, any data conversion and conversion due to NULLIF and DEFAULTIF conditions. LOADPLUS then compares values to the WHEN condition based on the type of comparison:
- For predicates with column names on the left side, LOADPLUS compares the WHEN condition to the converted data.
- For predicates with (start: end) or field names on the left side, LOADPLUS compares the WHEN condition to the original input data.
With RULES=STANDARD, LOADPLUS compares the values in the input file to the WHEN condition. LOADPLUS then completes, on the resulting records, any data conversion and conversion due to NULLIF and DEFAULTIF conditions.

The discussion in this section uses the following example:

LOAD DATA REPLACE INTO TABLE SAMPLE_TABLE
  WHEN CHAR_COL = 'X'
  (CHAR_COL POS(1:1) DEFAULTIF CHAR_COL='X'
   DEC_COL POS(2:4) DEC(5,0),
   DATE_COL POS(5:8) DATE NULLIF DATE_COL=' ')

With RULES=BMC in effect, LOADPLUS performs the NULLIF or DEFAULTIF processing first to determine whether to set CHAR_COL to a blank because it is defined as NOT NULL WITH DEFAULT. Next, LOADPLUS performs the WHEN comparison to determine whether to load the record into the table. For any record with an 'X' in column 1, LOADPLUS changes the 'X' to a blank and discards the record.

With RULES=STANDARD in effect, the opposite process occurs. LOADPLUS first performs the WHEN comparison to determine whether to load the record into the table and, if so, continues with the NULLIF or DEFAULTIF data conversion. For any record with an 'X' in column 1, LOADPLUS changes the 'X' to a blank and loads the record into the table.

Error conditions with RULES=STANDARD

This section describes some of the errors that you might encounter under RULES=STANDARD and how to avoid them.

Invalid operator

Under RULES=STANDARD, you might encounter an invalid operator error.

The following statement creates an error condition when RULES=STANDARD is the installation option because you cannot use the less-than operator (<):

LOAD DATA REPLACE INTO TABLE SAMPLE_TABLE
(CHAR_COL POS(1:1) CHAR(1)
DEC_COL POS(2:4) DEC(5,0)NULLIF DEC_COL<'5',
DATE_COL POS(5:8) DATE)

LOADPLUS issues the following error:

BMC51415E FOR 'NULLIF/DEFAULTIF' FIELD 'DEC_COL', ONLY '=','¬ =','<>', 'IN', AND 'NOT IN' COMPARISONS ARE ALLOWED.

Because RULES=BMC allows the use of the less-than operator, you can avoid this error by changing your RULES specification.

Invalid constant in predicate

RULES=STANDARD requires that all constants used in WHEN, NULLIF, or DEFAULTIF predicates be character strings or hexadecimal strings enclosed in quotes.

The following statement produces an error because LOADPLUS converts the character string '1' to a numeric value for comparison with the decimal column, resulting in a constant that does not meet the requirement for RULES=STANDARD:

LOAD DATA REPLACE INTO TABLE SAMPLE_TABLE
(CHAR_COL POS(1:1) CHAR(1)
DEC_COL POS(2:4) DEC(5,0)NULLIF DEC_COL='1',
DATE_COL POS(5:8) DATE)

LOADPLUS issues the following error:

BMC51416E FOR 'NULLIF/DEFAULTIF' FIELD 'DEC_COL', CONSTANT MUST BE A CHARACTER STRING OR A HEX STRING

Because RULES=BMC does not have this requirement, you can avoid this error by changing your RULES specification.

Error conditions with RULES=BMC

The following sections describe some of the errors that you might encounter under RULES=BMC and how to avoid them:

Invalid constant in WHEN statement
Invalid constant in NULLIF statement
Preventing these errors

Invalid constant in WHEN statement

Under RULES=BMC, you might encounter an invalid constant error in your WHEN statement.

In the following example, LOADPLUS attempts to compare a character string constant (' ') with a numeric column:

LOAD DATA REPLACE INTO SAMPLE_TABLE
WHEN DEC_COL= ' '

LOADPLUS issues the following error message:

BMC50122E FOR 'WHEN' COLUMN 'DEC_COL', CONSTANT IS WRONG TYPE OR COLUMN IS NOT NULLABLE: ' '

Invalid constant in NULLIF statement

Under RULES=BMC, you might encounter an invalid constant error in your NULLIF statement.

In the following example, LOADPLUS attempts to perform NULLIF processing by comparing a character string constant (' ') with a date column:

LOAD DATA REPLACE INTO TABLE SAMPLE_TABLE
  (CHAR_COL POS(1:1) CHAR(1),
   DEC_COL POS(2:4) DEC(5,0),
   DATE_COL POS(5:8) DATE NULLIF DATE_COL=' ')

LOADPLUS issues the following error message:

BMC50123E FOR 'NULLIF/DEFAULTIF' COLUMN 'DATE_COL', CONSTANT IS NOT IN RANGE OF COLUMN DATA TYPE: ' '

Preventing these errors

You can use one of the following methods to avoid errors when RULES=BMC:

Use the RULES=STANDARD installation option.
Use starting and ending (start: end) column positions in your LOAD command as shown in the following example:

LOAD DATA REPLACE INTO TABLE SAMPLE_TABLE
  (CHAR_COL POS(1:1) CHAR(1),
   DEC_COL POS(2:4) DEC(5,0),
   DATE_COL POS(5:8) DATE NULLIF (5:8)=' ')