When field procedures are taken

A field procedure is invoked generally in three conditions:

• For field-definition, when the CREATE TABLE or ALTER TABLE statement that names the procedure is executed. During this invocation, the procedure is expected to:
  • Determine whether the data type and attributes of the column are valid.
  • Verify the literal list, and change it if wanted.
  • Provide the field description of the column.
  • Define the amount of working storage needed by the field-encoding and field-decoding processes.
• For field-encoding, when a column value is to be field-encoded. That occurs for any value that:
  • Is inserted in the column by an SQL INSERT statement, or loaded by the Db2 LOAD utility.
  • Is changed by an SQL UPDATE statement.
  • Is compared to a column with a field procedure, unless the comparison operator is LIKE. The value being encoded is a host variable or constant. (When the comparison operator is LIKE, the column value is decoded.)
  • Defines the limit of a partition of an index. The value being encoded follows ENDING AT in the PARTITION clause of CREATE INDEX.
  If there are any other exit routines, the field procedure is invoked before any of them.
• For field-decoding, when a stored value is to be field-decoded back into its original string value. This occurs for any value that is:
  • Retrieved by an SQL SELECT or FETCH statement, or by the unload phase of the REORG utility.
  • Compared to another value with the LIKE comparison operator. The value being decoded is from the column that uses the field procedure.
  In this case, the field procedure is invoked after any edit routine or Db2 sort.

A field procedure is never invoked to process a null value, nor for a DELETE operation without a WHERE clause on a table in a segmented table space.

Recommendation: Avoid encoding blanks in a field procedure. When Db2 compares the values of two strings with different lengths, it temporarily pads the shorter string with blanks (in EBCDIC or double-byte characters, as needed) up to the length of the longer string. If the shorter string is the value of a column with a field procedure, the padding is done to the encoded value, but the pad character is not encoded. Therefore, if the procedure changes blanks to some other character, encoded blanks at the end of the longer string are not equal to padded blanks at the end of the shorter string. That situation can lead to errors; for example, some strings that ought to be equal might not be recognized as such. Therefore, encoding blanks in a field procedure is not recommended.