Excerpt from the eBook "IBM i Encryption with FieldProc - Protecting Data at Rest."

FieldProc Architecture

FieldProc is a type of column-level exit point that is implemented directly in the DB2 database. As is typical with any of the other IBM exit points, IBM provides the architecture for the exit point to invoke a user application, but IBM does not provide that application. Customers or vendors can create a FieldProc application based on the documented architecture of the exit point. Townsend Security is one vendor who provides such software.

A note on terminology:
We will use the term “column” when referring to a field in a file, and we will use the term “table”
to refer to a physical file. For the purposes of discussing FieldProc these terms are interchangeable and we will use the more modern terms “column” and “table”. In a similar way we will use the term “index” to refer to a column that is either a primary or secondary key. A secondary key would include a key de ned in a logical file.

The exit point architecture is very simple. There are only two commands and three functions that are supported. The two commands are:

• Start FieldProc
• End FieldProc

The three functions that are handled by a FieldProc program are:

• Initialization
• Encode (Encrypt)
• Decode (Decrypt)

When FieldProc is started on a column the FieldProc program is called for Initialization, and then called for each row in the table to provide for the encryption of the column. When FieldProc is ended the FieldProc program is called for each row to decrypt the data. All other normal read, change, and insert operations call the FieldProc program to provide for encryption or decryption as needed. FieldProc is not invoked for a delete operation on a row.

FieldProc Implementation

FieldProc is a SQL feature and is implemented through SQL commands. That is, FieldProc is not implemented through DDS, but only through SQL. Through SQL you create or alter a table to have the FIELDPROC attribute like this:

ALTER TABLE employee alter column
salary set FieldProc Userpgm

Likewise you can remove a FieldProc attribute using a SQL DROP statement like this:

ALTER TABLE employee alter column
salary Drop FieldProc

As noted above when you start FieldProc with the CREATE TABLE or ALTER TABLE commands, the effect is immediate. Starting FieldProc causes the FieldProc program to be called for each row in the table to perform encryption. Dropping the FieldProc attribute of a column causes the FieldProc program to be called for each row to decrypt the data.

Certain operations will cause FieldProc to be invoked even if the column data is not being used. For example, a legacy RPG program might read a file from beginning to end but not use a particular column that is under FieldProc control. Even though the column is not used in the RPG program FieldProc will be invoked to decrypt the value of the column for each row.

Note that a native SQL SELECT statement that does not include the encrypted column will NOT invoke FieldProc for decryption. Some work management operations are difficult with the current IBM implementation of FieldProc. For example, save and restore operations when the restore library is different than the save library can be problematic. Additionally, change management operations where there are changes to column attributes can be difficult to manage and require decrypting the database, applying the change, and then re-encrypting the database. These limitations are especially true in legacy RPG applications.

Supported Field (Column) Types

Most column types are support for FieldProc including character, numeric, date, time, and timestamp  fields. You can use FieldProc on null-capable  fields and double-byte character  fields. Some type of derived and counter  fields do not support FieldProc, but IBM i customers will  find that all normal application  fields are supported. With FieldProc there is no need to change the  eld size or attribute, nor is there any need to change or re-compile applications.

Legacy DDS Files

Many IBM i customers have the impression that legacy DDS  files will not support FieldProc encryption. This is not true! You can readily implement FieldProc encryption on  files created with DDS and it is not necessary to convert them to DDL and SQL tables. As IBM notes there are some advantages to doing so, but it is not necessary and the large majority of FieldProc users continue to use DDS  files. As noted above, you can only start and stop FieldProc using SQL commands, but these SQL commands work  ne on DDS-created  files.

Encrypting Multiple Fields in One File

DB2 FieldProc control can be started on multiple columns in one database table. Three is no practical limit on the number of columns you can select for FieldProc implementation. Of course, there are performance implications for encrypting multiple columns as the FieldProc program will be called independently for each column under FieldProc control. But many IBM i customers place multiple columns in a table under FieldProc encryption control. In some cases customers place hundreds of columns under FieldProc control. FieldProc’s support for multiple columns includes columns that are Primary or Secondary keys to the data. Please see the following sections for a discussion of limitations related to encrypted indexes and legacy RPG applications.