Using field procedures to provide column level encryption

Field procedures can provide column level encryption in Db2 for i.

A field procedure is a user-written exit routine to transform values in a single column. When values in the column are changed, or new values inserted, the field procedure is invoked for each value. The field procedure 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 encoded value. The field procedure decodes each value back to the original value. Any indexes defined on a column that uses a field procedure are built with encoded values.

Field procedures are assigned to a table by the FIELDPROC clause of CREATE TABLE and ALTER TABLE.

A field procedure that is specified for a column is invoked in three general situations:

  • 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.
  • For field-encoding, when a column value is field-encoded. That occurs for any value that:
    • is inserted in the column by an SQL INSERT statement, SQL MERGE statement, or native write.
    • is changed by an SQL UPDATE statement, SQL MERGE statement, or native update.
    • is the target column for a copy from a column with an associated field procedure. The field procedure might be invoked to encode the copied data. Examples include SQL Statements ALTER TABLE or CREATE TABLE LIKE/AS and CL commands CPYF and RGZPFM.
    • is compared to a column with a field procedure. The QAQQINI option FIELDPROC_ENCODED_COMPARISON is used to determine if the column value is decoded, or the host variable, constant, or join column is encoded.
    • is the DEFAULT value for a column with an associated field procedure in a CREATE or ALTER TABLE statement.

    If there are any after or read triggers, the field procedure is invoked before any of these triggers. If there are any before triggers, the field procedure is invoked after the before trigger.

  • For field-decoding, when a stored value is field-decoded back into its original value. Field-decoding occurs for any value that:
    • is retrieved by an SQL SELECT or FETCH statement, or by a native read.
    • is a column with an associated field procedure that is copied. The field procedure might be invoked to decode the data before making the copy. Examples include SQL Statements ALTER TABLE, CREATE TABLE LIKE/AS, and CL commands CPYF and 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 host 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.

Improving performance

For queries that use field procedures, the path length is longer due to the additional processing of calling the field procedure. In order to improve performance of queries, the SQE optimizer:

  • attempts to remove decoding operations, based on the QAQQINI FIELDPROC_ENCODED COMPARISON setting.
  • matches existing indexes over columns that have an associated field procedure.
  • creates and uses MTIs over columns with field procedures. The MTI will be created as non-resuable which means it will not be shared between queries.
  • creates statistics over the encoded values through statistics collection.

The SQE optimizer attempts to do the following optimizations:

  • optimization of predicates that compare a field procedure column to a constant or host variable. For example, predicate FP1(4, C1) = ‘abc' is optimized as C1 = FP1(0,‘abc'). With this specific example, the optimization is done as long as the QAQQINI option is not *NONE.
  • remove field procedure decoding operations from join predicates when the same field procedure is applied to both sides of the join predicate, and no compatibility conversion is required. For example, join predicate FP1(4,T1.C1) > FP1(4,T2.C1) is rewritten as T1.C1 > T2.C1. With this specific example, the optimization is done as long as the QAQQINI option is either *ALLOW_RANGE or *ALL. This technique is also applied to = predicates when the QAQQINI option is *ALLOW_EQUAL.
  • remove field procedures from GROUP BY and ORDER BY clauses. For example, ORDER BY FP1(4,C1) is rewritten as ORDER BY C1 if the QAQQINI setting is either *ALLOW_RANGE or *ALL

The CQE optimizer does not look at the QAQQINI option, which means it always runs in *NONE mode. *NONE mode requires that all references to the column are decoded before any other operation is performed. A CQE query does not match any existing indexes when the column has an associated field procedure. If an index is required, a temporary index is built with the index keys decoded.