Handle non-numeric characters in system i5 numeric fields - Connect_CDC - connect_cdc_mimix_share - Latest

This section details how Connect CDC handles non-numeric characters that System i5 captures in numeric fields.

• For any numeric data value (for type NUMERIC and DECIMAL), the value will be checked to ensure that all the digits are numeric. The characters “.”, "+", and “-” representing, the decimal point, the plus sign, and the minus sign, will be considered valid. If any non-valid character is encountered either an internal exception will be raised or the “invalid” condition will be detected, the actual value retrieved will be ignored, and a numeric value of “0” will be sent in its place.

• Whenever an “invalid” condition is detected the following information will be written to the kernel log:

• A message is written to the kernel to indicate that a “bad” numeric value was encountered. This will be a WARNING level message and it will be written as an alert.

• If possible, the table and column in error and its value will be indicated in a kernel log message.

• A separate message will be written to the kernel log: if there is information about the primary key defined for the table, the retriever will list the key column value pairs associated with the data row in error.

• Any corrective action will be detailed in a kernel log message.

• Special processing will be done for spaces (the Unicode character x'20' originating from an EBCDIC space x'40') encountered in the replication retriever: the numeric column value will be scanned from left to right and any space encountered will be considered valid and replaced with a numeric zero (“0”). Note that a space imbedded in the middle of a value will cause the value to be considered “invalid” (for example. 1234_56 where “_” represents a space) and the “invalid” condition will be raised. The value “___123456” will be converted to “000123456” and no “invalid” condition will be raised. A WARN level message will be written to the kernel log, but it will not be an alert.

• You can use the following methods to check for the handling of non-numeric data:

  • "boolean isnumeric(char)"—This method will return true if the character input contains all numeric characters, otherwise false. Note that this method would return false if the input character string contained the plus ('+') or negative sign ('-') or the decimal point ('.'); even though these particular characters might be legal in a decimal number. Nonetheless, this method can be useful in checking for non-numeric data if a string needs to be converted to a numeric value.

  • You can use the following three functions, depending on the desired input and return type, to protect against bad data by providing a default value if the input string contains bad data:

1. “decimal todecimal(char, decimal);”,

2. “double todouble(char, double);”,

3. “int tonumber(char, int);”

Compare the following examples:

• The function “todecimal('-100.00', 0.0);” returns the value '-100' because it is a valid decimal.

• However, the function “todecimal('@100000', 0.0);” returns the value '0.0' because the input string contains a character ('@') that is not valid in a numeric string.

• You can use the nullto method to protect against input NULL values which cause exceptions to occur in functions that handle numeric values. See Processing NULL Values in the Expression Handler. Below is an example that converts a NULL value to -1 if attempting to convert a character string to a numeric and the character column “C1” contains NULL:

  • tonumber(nulltostring(C1, '-1'), 0);

The message is EXPR07086 - WARNING: The "NULL allowed" column (...) is being used and unknown results are possible."