Defining field procedures

Field procedures are assigned to a table by the FIELDPROC clause of the CREATE TABLE and ALTER TABLE statements. A field procedure is a user-written exit routine that transforms values in a single column.

When values in the column are changed, or new values inserted, the field procedure is invoked for each value, and can transform that value (encode it) in any way. The encoded value is then stored. When values are retrieved from the column, the field procedure is invoked for each value, which is encoded, and must decode it back to the original value. Any indexes defined on a non-derived column that uses a field procedure are built with encoded values.

The transformation your field procedure performs on a value is called field-encoding. The same routine is used to undo the transformation when values are retrieved; that operation is called field-decoding. Values in columns with a field procedure are described to DB2® in two ways:
  1. The description of the column as defined in CREATE TABLE or ALTER TABLE appears in the catalog table QSYS2.SYSCOLUMNS. That is the description of the field-decoded value, and is called the column description.
  2. The description of the encoded value, as it is stored in the database, appears in the catalog table QSYS2.SYSFIELDS. That is the description of the field-encoded value, and is called the field description.

Important: The field-decoding function must be the exact inverse of the field-encoding function. For example, if a routine encodes 'ALABAMA' to '01', it must decode '01' to 'ALABAMA'. A violation of this rule can lead to unpredictable results. See General guidelines for writing field procedures.

Field procedures can also perform masking of data when decoded (retrieved). In this case, the field procedure would decode '01' to 'ALABAMA’ for certain users or environments and for other users or environments may return a masked value such as ’XXXXXXXX’ instead. See Guidelines for writing field procedures that mask data.