What is IBM i Field Level Encryption? The DB2 Field Procedure—generally referred to as “FieldProc”—was built into the IBM i operating system with the release of the i 7.1 operating system in 2010. FieldProc allows a field-level exit point routine to replace extensive changes in application code to create column-level encryption of sensitive data. With column-level encryption, you only encrypt one or more database fields (columns), rather than the entire database. FieldProc also enables an encryption process that previously wasn’t possible without a significant application or database architecture change.
By taking care of encryption during database reads and writes, FieldProc eliminates the need for additional files to be written for each program. For shops running older, non-SQL-based ILE programs, this is a significant reduction in complexity. Programs can be quickly written to encrypt data for a write operation and automatically decrypt data for a read operation. All types of encoding can be performed by a FieldProc program, but using encryption algorithms such as the Advanced Encryption Standard (AES) are the most common.
How FieldProc works
Although the FieldProc program can be implemented in any ILE language, ILE programs cannot run SQL statements and the program must contain logic to handle three types of program calls: FieldProc registration, field encode, and field decode.
The FieldProc program determines the type of call by examining the DB2 parameter list that exists on every call to the FieldProc program. This provides development simplicity because no changes to the application or table definition are required. The attributes on the column or field definition define the application’s view of the data value, while the FieldProc Registration call defines the attributes that DB2 will use for the internal storage of the encoded data.
FieldProc, SQL, and System Commands
Note that registration uses SQL, but the SQL interface requirement does not exclude FieldProc support for physical files. However, there are reasons to convert physical files to SQL tables that pertain to changing, adding, or removing columns while maintaining the FieldProc registration. There’s also a methodology for converting physical files to SQL tables without recompiling application programs. So, look into this before making any hard and fast decisions.
FieldProc programs are not limited to application data access interfaces. System commands that write or read data–such as Copy File (CPYF), Display Physical File Member (DSPPFM), or Reorganize Physical File Member (RGZPFM)– will result in FieldProc requests from DB2. Indexes or keyed logical files can also be associated with FieldProc programs, but there may be issues using encrypted fields in join logical files.
How Wise Programmers Approach Data Encryption
Regardless of the interface, prudent use of data encryption is a wise idea. Performance and sorting restrictions are two reasons to limit encryption to truly sensitive data. System processing is required when encrypting and decrypting data, so system performance will be affected when applications search for specific values. In certain instances, depending on the encryption method, applications may decrypt the value stored in the table or encrypt the search value before DB2 can compare like values. This upfront conversion requirement can slow performance. Also keep in mind that encrypted data that is frequently referenced in sorting or lookup operations by an application will impact performance. So, make sure not to encrypt data that isn’t actually sensitive. And note that access to non-sensitive data does not have to be encrypted or decrypted, which eases the strain on system performance.
Six downsides and tips for using field level encryption
Before implementing IBM i file level encryption, there are six specific downsides and tips to understand and use.
- Registering field procedure programs magnifies overhead, creating performance deterioration. A FieldProc program call operates like an external program call. Every interface that writes to or reads values from an encrypted column is affected. Encryption and decryption processing carries a CPU cycles penalty. Our advice is that performance testing be part of your field procedure implementation. Make sure to test all applications that write, update, or both write and update the field. Also test any apps and queries that read the file.
- Whenever a new FieldProc is added to a file, it runs that file’s existing FieldProcs to decode and re-encode the values. By using an ALTER TABLE statement, all FieldProcs will be added to the file in a single procedure.
- Keep in mind that a FieldProc program must begin with an ILE program. There are no exceptions and no SQL allowed.
- It is very dangerous to use field procedures that are not deterministic. When field procedures do not behave the same way when given the same input, the encoded data might not be able to be decoded back to its original value.
- Also, be aware that field procedure programs must contain logic to return the encoded value’s length, type and CCSID to DB2 for i. Programs must contain logic to both encrypt the original value and return it to the database, and to decrypt the encoded value and return it to the database.
- When the Change Physical File (CHGPF) command is used (with the Source File parameter, SRCFILE) to change the field definition of a physical file, the CHGPF command will remove all registered field procedures on that physical file without warning. CHGPF allows programmers to change many attributes of physical files. It is often used to add fields or change existing fields without recompiling the files.
FieldProc is for everybody
A FieldProc program can be called regardless of the application or interface used. Third-party software providers, including SEA with its iSecurity Encryption software, support the DB2 FieldProc interface.
