A few years ago, DB2 for i introduced a new feature that can be used to apply security/encryption to our columns within our database.
This enhancement is called Field Procedures, or FieldProcs throughout this article.
DB2 FieldProcs have been around since 7.1 of IBM i in April 2010.
Before FieldProcs, it was common to see database triggers being used to encrypt columns.
What IBM has given us is the ability to take a field, or column, and let us apply security, or encryption, to that field/column. It is up to your FieldProc program to perform the measures you have taken to achieve this task.
This function allows us to register a program to a column.
What your program decides to do with the value in that column, is totally dependent upon your FieldProc program. You are not just limited to using encryption; the choice is yours.
Once in place, nobody can access the decrypted data without the FieldProc program being involved!
For example, you FieldProc could just reverse a string column, for example, formaserve could be encrypted in your FieldProc to be stored as evresamrof, or we could use AES encryption on the column, the choice is yours – thankfully IBM have made this very flexible.
A great aspect of this is, changing a table to use FieldProcs does not change the file format ID, so we do not have to re-compile our applications programs.
FieldProcs are not only restricted to SQL tables, we can also use them on our DDS files.
This article used the sample RPG program at the IBM DeveloperWorks website, which can be found here.
Table Example
From the figure below we can see that our Employee table has four columns that have FieldProcs associated with them. It also shows, that different programming languages can be used.
Field Procedure Program
So, what is a field procedure program?
This program will encode and decode column values.
If a record is written to a file, the column value will be passed and it is the function of the FieldProc to encode the value and pass it back to the database.
And to follow on, if a record is read, the encrypted column will be passed to the FieldProc program enabling it to be decoded before passing back so it can then be used.
The first step we have to take to implement FieldProc’s on our database is the development of the FieldProc program.
This program has to exist before we make any changes to the table.
There are certain rules that a FieldProc program must conform to, before it can be used as a FieldProc.
The list includes:
- The FieldProc must be an ILE *PGM
- Service Programs, OPM Programs & JAVA objects cannot be used
- The FieldProc cannot contain any SQL statements (Bit of strange one!)
- The program has to be capable of running in a thread
- In RPG, specify Serialize to the Thread keyword on our control specification
– Ctl-Opt Thread( *Serialize );
- In RPG, specify Serialize to the Thread keyword on our control specification
- The program must run in a named Activation Group – We cannot specify a *NEW activation group
DB2 passes nine parameters to our FieldProc program. The table below explains these.
| Parameter Number | Length | Input or Output | Description |
| 1 | Two-byte integer | I | Function code (See details below) |
| 2 | Data Structure | For Function Code; 0 & 4 = IO 8 = O |
FieldProc parameter value list |
| 3 | Data Structure | I | Column attributes |
| 4 | Character 512 bytes | For Function Code; 0 & 8 = I 4 = O |
Decoded value |
| 5 | Data Structure | For Function Code; 0 & 4 = I 8 = O |
Internal Encoded Data Attribute |
| 6 | Character 512 bytes | For Function Code; 8 & 4 = I 0 = O |
Encoded value |
| 7 | Character 5 bytes | IO | SQLSTATE – Can be used to indicate an error to the database |
| 8 | VarCharacter 1,000 bytes | IO | Message Text – Can be used to send a message text back when an SQLSTATE error has occurred |
| 9 | Data Structure 128 bytes | I | Error data structure – set by DB2 before calling the field procedure. For field procedures that mask data. |
Looking at the example RPG program on DeveloperWorks, here is the program parameter list, which heavily uses the QSYSINC/QRPGLESRC.SQLFP source.
Dcl-Pi *n ExtPgm(‘FP_EXV1RPG’);
FuncCode Uns(5) Const;
OptionalParms LikeDs(T_optional);
DecodedDataType LikeDs(SQLFPD);
DecodedData LikeDs(T_DECODED_DATA);
EncodedDataType LikeDs(SQLFPD);
EncodedData LikeDs(T_ENCODED_DATA);
SqlState Char(5);
Msgtext VarChar(1000);
End-Pi;
Please note – The example program on developerworks was written for version 7.1 of IBM i, which only used 8 parameters. This is still valid on 7.3.
Function Codes
The functional code, passed as the first parameter in the list, informs us of the functionality our program as to perform.
At this time, it only has three values, zero, four and eight.
The graphic below explains these codes.
Let me explain these in more detail.
Function Code 8
If our program receives a function code of eight, we have to return the encoded value’s length, type and CCSID back to the database.
Function Code 0
Function code 0 will let us know that we have to encrypt the original value and return this value back to the database manager.
Function Code 4
With function code 4, we will be passed the encrypted value for the column, it us up to our FieldProc program to decrypt the value and return it to DB2.
Field Procedure Includes
IBM has provided some FieldProc definitions for us in the QSYSINC /QRPGLESRC source file. Check out member SQLFP.
It’s advisable to use a copybook statement to use these definitions within your program.
/Copy QSYSINC/QRPGLESRC,SQLFP
Enabling Field Procedures
Now we have our FieldProc program, we need to link it to a specific column where it was designed to perform the encryption/decryption functionality.
Let me say firstly, that the following column types cannot use FieldProcs;
- Identity Column
- RowID Column
- Change Timestamp Column
And also, only one large object (LOB) column in a table can use a Field Procedure.
We use the SQL ALTER TABLE to link our FieldProc to a column on a table.
The following SQL statement uses our RPG program FS015FP, in library FORMASERVE, and is linked to the Credit_Card column.
— Create new table with a FieldProc
CREATE OR REPLACE TABLE FormaServe.CreditCard(
Invoice_No Int,
Inv_Date Date,
Credit_Card VarChar( 512 ) FieldProc FormaServe.FS015FP
) ;
Now, anything that needs to display records in this table, whether it be SQL, RPG, RunQry, etc. will be passed to our program FS015FP.
Top Tip – Use a single ALTER TABLE statement to add all FieldProcs to the table at once, as shown below.
— Create new table with 3 FieldProc’s
CREATE OR REPLACE TABLE FormaServe.SalesPay(
Invoice_No Int,
Inv_Data Date,
Credit_Card VarChar( 512 ) FieldProc FormaServe.FS001FP,
Order_Total VarChar(10) FieldProc FormaServe.FS002FP,
Pay_Limit VarChar(10) FieldProc FormaServe.FS003FP
) ;
Remove FieldProc
To remove a FieldProc from a column we use the ALTER TABLE SQL function.
This function performs a mass decryption of the field values, and potentially, take some time to run, so submit to batch maybe?
Alter table FormaServe.CreditCard alter column Credit_Card Drop fieldproc ;
Performance
Adding FieldProcs to our tables does have a performance hit, they are comparable to that of an external program call.
If the FieldProc is using encryption & decryption processing, these are notorious for the amount of CPU cycles that they demand.
It is advisable to include performance analysis as part of your field procedure implementation.
Testing
So, as we now know, our FieldProc will take a value in field and convert it to whatever we want and when we retrieve a record, it will take the field value and call the FieldProc to decrypt it for us. So, how do we know if the FieldProc is working at all?
I’m afraid you are going to have to wait for my next article, where I show one method of testing our FieldProc.
Where are they used?
IBM has provided us with a view called SYSFIELDS in QSYS2 which shows which tables are using Field Procs.
The following SQL statement can be used to inform us where FieldProcs are being used.
Select FIELD_PROC AS Field_Proc, Column_Name as Column, TRIM(TABLE_SCHEMA) || ‘/’ || TABLE_NAME AS Table
FROM QSYS2.SYSFIELDS ;
From the figure below, we can see the output from this SQL statement.
Additionally, the Display File Field Description (DSPFFD) command shows any field that has a FieldProc attached to it.
Conclusion
IBM has provided a very flexible method to allow us to control the encryption of our database columns.
A thank-you to the guys at Developerworks for the explanation of how FieldProcs work.
It was great to meet up with old friends and colleagues at the International i Power 2017 conference in June. Even my son Nick attended, and was amazed at the enthusiasm that is still around for our favourite platform.
The next UK user group meeting is on Thursday 2nd November at The Mount Hotel in Wolverhampton, full details can be found on the iUG site here.
Andy Youens is an IBM i consultant/instructor at Milton Keynes, UK-based FormaServe Systems.