Excerpt from the eBook "IBM i Encryption with FieldProc - Protecting Data at Rest."
Encrypted Indexes
The DB2 FieldProc implementation fully supports the encryption of columns which are indexes (keys) to the data in a native SQL context - this includes RPGSQL applications. However, there are some severe limitations with legacy RPG applications around encrypted index order. If you are like most IBM i users it is important to understand these limitations to implement DB2 encryption in RPG.
Legacy RPG applications use record-oriented operations and not set-oriented operations that are typical of SQL. Many record oriented operations on encrypted indexes in RPG will work as expected. For example, a CHAIN operation on an encrypted index to retrieve a record from a DB2 table will work. If the record exists it will be retrieved and FieldProc will decrypt the value. However, many range and data ordering operations will not work as expected with legacy RPG programs leaving you with empty reports and subfiles. Consider the following logic:
SETLL (position to an particular location in the index for a file)
WHILE (some condition)
READ (Read the next record by the index) END WHILE
The SETLL (Set lower limit) operation will probably work if the particular index value exists. However, the program logic will then read the next records based on that position in the file. IBM i developers are surprised to learn that they will be reading the next record based on the ENCRYPTED value, and not on the decrypted value which is what they might expect. The result is often empty subfiles and printed reports. This is very common logic in applications where indexes are encrypted. Note that your RPG program will not get a file I/O error, it just won’t produce the results you expect.
In simpler applications this side-effect of encrypted indexes is not significant, or easily programmed around. However, in some applications where sensitive data is encrypted across a large number of tables (think social security number in banking applications) this can be a significant limitation.
Don’t despair, keep reading.
Overcoming Encrypted Index Limitations in RPG
The limitations of encrypted indexes in legacy RPG applications often lead IBM i customers to abandon their encryption projects. The prospect of converting a large number of legacy RPG applications to newer SQL interfaces can be daunting. Their legacy RPG applications contain a lot of valuable business logic, and the effort to make the conversion can be quite large.
Wouldn’t it be great if you could wave a magic wand and make legacy RPG applications use SQL without any changes?
IBM opened a path to this type of solution with Open Access for RPG, or OAR. OAR allows for the substitution of traditional file I/O operations with user- written “Handlers”. In essence, you can replace the native file I/O operations of RPG with your own code. No change to program file handling or business logic! The OAR capability spawned a number of user interface modernization products, and other solutions that take advantage of this. Imagine if your RPG screen handling I/O with Execute Format (EXFMT) could be replaced with a web-based GUI library. Instant modernization of the UI! There are now many solutions that leverage OAR for UI modernization.
Join Logical Files with DDS
One limitation of logical files created with DDS specifications involves join logical files. You will not be able to create DDS join logical file where the join involves an encrypted eld with FieldProc. You will get an error about invalid data type usage. This is an IBM limitation and there is no known workaround for this issue. Note that this limitation only applies to DDS join logical les and does not apply to SQL joins using encrypted indexes. Most IBM i customers will need to change their RPG or COBOL program logic to avoid the use of DDS join logical les which use encrypted indexes.
Application Changes
Legacy RPG applications that use encrypted indexes often need re-design and re-programming to avoid the problems of encrypted indexes. You can avoid these issues if you are using an Open Access for RPG (OAR) solution that maps the legacy RPG record-based file operations to native SQL and the SQL Query Engine (See the note above about Townsend Security’s OAR/SQL offering).
If you need to re-design your RPG applications to avoid encrypted indexes consider putting all of your sensitive data in a table that is indexed by some non-sensitive value such as a sequence number or customer number, and use FieldProc to encrypt that table. There are many approaches to application re- design and you should be able to find a method that works for you.
Change Management
IBM i customers who have deployed change management solutions can encounter challenges with FieldProc implementations. While most of these challenges are surmountable, it will take effort on the part of the systems management team to integrate FieldProc controls into their change management strategy. Unfortunately the implementation of FieldProc does not provide much in the way of support for change management systems. The activation of FieldProc changes an attribute on the column in the table, but change management systems generally are not aware of this attribute. It can be challenging to promote table and column level changes and properly retain the FieldProc attribute of the data. Look for a FieldProc implementation that provides a command-level interface to stop, start, and change FieldProc definitions. These commands can help you integrate FieldProc encryption into your change management strategy.
