IBM introduced the DB2 Field Procedures (FieldProc) column level encryption interface in V7R1 of the operating system. It has been a great way for IBM i (iSeries, AS/400) customers to protect sensitive data in their DB2 for i files and tables, but customers often have questions about how this new capability works. One of the most common questions is “Can I encrypt index fields and will they work correctly?”

The answer to the first part of the question is YES you can encrypt index fields, and the answer to the second part of the question is THAT DEPENDS. Let’s take a deeper look at encrypted indexes with FieldProc.

First, let’s look at DB2 FieldProc strictly from a SQL point of view. Remember that SQL is IBM’s preferred interface to the DB2 relational database. So let’s start there:

The first thing to understand is that FieldProc is fundamentally a SQL construct. That is, it is designed for and implemented as a SQL facility. You can specify a FieldProc program on the SQL CREATE TABLE or ALTER TABLE commands, but you can’t specify FieldProc on traditional DDS source descriptions. FieldProc works great on index fields in your SQL applications! Your SQL statements will work just as you would hope and you will have a great new facility for implementing automatic encryption. With very few limitations you will find that encrypted indexes work without any issues for your SQL applications. I’ve rarely found a customer who was unhappy with IBM’s implementation of FieldProc in native SQL applications. This includes SQLRPG applications that use native SQL for the database interface.

But, of course, most IBM i customers are running a lot of legacy RPG or COBOL applications that do not use SQL. And this is where there are some significant restrictions on encrypted indexes.

First, you CAN use FieldProc on traditional database files created with DDS. It is not necessary to convert the database files to SQL in order to use FieldProc. Of course, FieldProc application support is installed using SQL statements, but they will work on traditional DDS created files with some minor limitations. So this part is not complicated.

Next, you CAN encrypt indexes that are created with DDS. However, you do have some significant limitations when using FieldProc with DDS files. For example, some join logical files that join on encrypted index fields will not work. You simply won’t be able to create join logical files that link using fields encrypted under FieldProc.

A more fundamental problem is that legacy RPG and COBOL applications will not work correctly with most encrypted indexes. Since the legacy file interface is not SQL, the legacy applications will not work as expected in many cases. For example, it is very common to use the Set Lower Limits (SETLL) command with the Read (READ) command to read a range of values in a table. In these legacy applications the SETLL value will be converted to the encrypted value by FieldProc, and then the next record will be read using the encrypted key value. But encrypted values will not be in the same order as the original plaintext values. This will lead to empty subfiles and empty or incorrect information on reports.

For many IBM i customers the limitations on encrypted indexes are not a big problem and they live with them. For many others encrypted indexes with legacy RPG applications is a significant problem that will make the use of FieldProc impossible.

Is there a solution for this problem? Well, of course you can convert all of your legacy databases and applications to SQL databases and SQL RPG applications, or even to native SQL applications. But this represents a major investment by many customers. But there is an alternative provided in the Open Access for RPG (OAR) implementation by IBM.

The Open Access for RPG implementation allows you to define a handler for file operations using one F specification in your RPG program. With this implementation the legacy file operations are handled by your new handler application. And that can be a set of SQL functions! This means that a legacy RPG program can become enabled for true SQL operations with a simple change and re-compile of the application. Of course, you must have the SQL handler functions ready to take over the file operations. I won’t go into creating SQL handlers in this blog, but be aware that creating SQL handlers is not for the faint of heart. You need to have extensive experience with SQL and understand the OAR architecture. If you’ve not done this before the IBM Lab Services team can provide assistance.

In summary, FieldProc is a great new facility and it is already helping a lot of IBM i DB2 customers to protect data with strong encryption. It works great with native SQL applications, but you need to be aware of some limitations when used with legacy RPG and COBOL applications.

Our Alliance AES/400 solution provides everything you need to implement FieldProc.