DB2 Field Procedures (FieldProc) were introduced in IBM i 7.1. A FieldProc is a user-written exit routine that encodes values in a single DB2 file column. FieldProc enables column-level encryption for sensitive IBM i data. They allow you to encrypt and decrypt one or more database fields (columns) rather than encrypting the entire database, protecting sensitive data such as credit card numbers, social security IDs, and other Personally Identifiable Information (PII) from unauthorized disclosure.
With FieldProc, column-level encryption occurs during database reads and writes. For shops running older, non-SQL-based ILE programs, this is a significant reduction in complexity. A FieldProc can be written to encrypt data for a write operation and automatically decrypt data for a read operation. Many types of encoding can be performed by a FieldProc program, but using encryption algorithms for column-level encoding is the most common application.
How FieldProc works
FieldProc are assigned to a DB2 for i table by using the FIELDPROC clause of the CREATE TABLE and ALTER TABLE statements.
Although a 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 a FieldProc.
DB2 Field Procedures simplify development because they don’t require any changes to the application or table definition. 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 internal storage of the encoded data.
FieldProc, SQL, and System Commands
Note that FieldProc registration uses SQL, but the SQL interface requirement does not exclude FieldProc support for physical files. 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. Consider physical file conversion when implementing FieldProc.
FieldProc 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 also result in FieldProc requests from DB2.
Efficiency and Indexing Considerations
Efficiency and indexing restrictions are two reasons to limit encryption only to truly sensitive data. System processing is required when encrypting and decrypting data, so system performance could be affected when applications search for specific values. However, with the massive improvements in IBM Power server hardware architecture, CPU performance degradation on newer boxes may be hardly noticeable with FieldProc implementations. Performance impacts will vary depending on what IBM Power hardware you’re using (POWER8, POWER9, POWER10, etc.).
To determine if your system will suffer any performance impacts, consider adding performance testing to your FieldProc implementation. Test applications that read, write, or update the column. Also test any apps and queries that read the file.
Non-sensitive data does not have to be encrypted or decrypted. Note that any file indexes built over columns using FieldProc are maintained using the column’s encoded value. No relevant database sorting for SQL commands, joins, queries, reports, etc. can occur using encoded columns. By only encrypting truly sensitive data, you will also maintain the integrity of your database indexing strategies.
Downsides and tips for using FieldProc encryption
Before implementing IBM i FieldProc encryption, there are four specific downsides and tips to understand and use.
1. Whenever a new FieldProc is added to a file, it runs that file’s existing FieldProc programs to decode and re-encode the values. When using an ALTER TABLE statement, all FieldProc will be added to the file in a single procedure.
2. It is very dangerous to use FieldProc that are not deterministic. FieldProc programs must return the same values every time they are called for an item. Results cannot vary depending on different variables used. When FieldProc 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.
3. FieldProc 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.
4. 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 FieldProc on that physical file. 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. Avoid using CHGPF for files containing FieldProc.
FieldProc is for everybody
A FieldProc program can be called regardless of the application or interface used. Third-party software providers including iSecurity Encryption, support and simplify using the DB2 Field Procedure (FieldProc) interface to secure sensitive data. Please Contact SEA if you’d like to learn more about using FieldProc for IBM i systems.
