Townsend Security Data Privacy Blog

IBM i FieldPROC Encryption, IBM Query, and Encrypted Indexes

Posted by Patrick Townsend on Jan 29, 2018 8:31:08 AM

The IBM i DB2 database team implemented column level encryption through Field Procedures (FieldProc) in release 7.1 of the IBM i operating system. It was a great step forward for those IBM i customers who need to encrypt sensitive data to meet compliance regulations and to improve overall security. With release 7.1 it was now possible to implement DB2 encryption without modifying user applications.

IBM i Encryption with FieldProc Prior to this enhancement to DB2 in release 7.1, implementing encryption could be a laborious process of modifying applications and performing extensive regression testing. This approach did not work well with some types of fields (date, time, etc.) and many IBM and third-party query utilities just did not work correctly. So the DB2 enhancement for Field Procedures was a great step forward.

While FieldProc worked well with native SQL applications in release 7.1, there were limitations for older RPG and COBOL applications, and many IBM query utilities did not work correctly with encrypted indexes. Many IBM i customers use IBM and third-party query programs for rapid development of reports and displays of data. Some customers that I’ve talked to have thousands of queries in their application mix, so limitations of IBM query with FieldProc represented an insurmountable challenge for many. When FieldProc was used to encrypt an index or key field, queries just would not work correctly and data was missing or out of order in reports and displays.

But there is some good news!

Starting with the 7.2 release of the IBM i operating system, many of the IBM query applications were updated to work with the native DB2 SQL Query Engine (SQE) by default. The SQL Query Engine has never had a problem with encrypted indexes. This means that the IBM query applications now work seamlessly with data that is encrypted with FieldProc in DB2. You can fully deploy column level encryption across multiple index columns with FieldProc, and your queries will work fine.

Many IBM i customers experimented with FieldProc in the first release in version 7.1 of the operating system and decided to take a pass. If you had that experience it is time to take another look at DB2 FieldProc encryption. The current version of the IBM i operating system is 7.3 and most IBM i customers have upgraded to this release. You now have good support for IBM queries, the SQL Query Engine, and DB2 FieldProc encryption.

While some challenges remain for older OPM and ILE RPG applications, we’ve been able to help a number of customers meet these challenges.

Encryption of data is a core part of a defense-in-depth strategy. We have to do a lot of things to protect our IBM i data, and one of those things is to encrypt the data at rest with industry standard encryption algorithms. DB2 Field Procedures provides the path to achieving this.

To read more about IBM i support for SQL Query Engine in query applications such as RUNQRY, OPNQRYF, and others, see this link.

Our Alliance AES/400 Encryption solution provides full support for DB2 Field Procedures, is easy to deploy, and affordable for every IBM i customer. 

For industry standard encryption key management you can deploy our Alliance Key Manager solution which is seamlessly integrated with DB2 Field Procedure encryption.

Patrick

IBM i Encryption with FieldProc

Topics: Encryption, IBM i, FIELDPROC