When field procedures are invoked

• 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 desired.
  • Provide the field description of the column.
• For field-encoding, when a column value is to be encoded. Encoding occurs for any value that:
  • Is inserted in the column by an SQL INSERT statement, SQL MERGE statement, or native write operation.
  • Is changed by an SQL UPDATE statement, SQL MERGE statement, or native update operation.
  • If the data needs to be copied and the target column has a field procedure, it is possible that the field procedure may be invoked to encode the copied data. Examples include the SQL statements ALTER TABLE or CREATE TABLE (with a LIKE or as-result-table clause) and the CL commands CPYF or RGZPFM.
  • Is compared to a column with a field procedure. The QAQQINI option FIELDPROC_ENCODED_COMPARISON is used by the optimizer to decide if the column value is decoded or if the variable, constant, or join column is encoded.
  • At CREATE or ALTER TABLE time for the DEFAULT value, if the column has a field procedure.

  If there are any after or read triggers, the field procedure is invoked before any of these triggers. For before triggers, there may be multiple invocations of the field procedure with encode and decode operations. The number of calls to the field procedure depends on many factors including the type of trigger and if the trigger changes the data in the trigger buffer. The database manager will ensure that the field procedure is called to encode the data that will be inserted into the table.

• For field-decoding, when a stored value is to be field-decoded back into its original value. This occurs for any value that is:
  • Retrieved by an SQL SELECT or FETCH statement, or by a native read operation.
  • If the data needs to be copied and the source column has a field procedure, it is possible that the field procedure may be invoked to decode the data prior to making the copy. Examples include the SQL statements ALTER TABLE or CREATE TABLE (with a LIKE or as-result-table clause) and the CL commands CPYF or RGZPFM.
  • Is compared to a column with a field procedure. The QAQQINI option FIELDPROC_ENCODED_COMPARISON is used by the optimizer to decide if the column value is decoded or if the variable or constant is encoded.

  A field procedure is never invoked to process a null value. It is also not invoked for a DELETE operation without a WHERE clause when the table has no DELETE triggers. The field procedure is invoked for empty strings.

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 the appropriate blank characters (for example, EBCDIC or double-byte blanks) up to the length of the longer string. If the shorter string is the value of a column with a field procedure, 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.