Numerous options are available for implementing encryption in DB2 for
z/OS environments. However, each one has its restrictions and limitations. The
encryption solution you choose (column, row, built in functions, edit procs,)
comes down to what it is you're trying to accomplish.
My preferred encryption option has always been the built-in SQL
function, ENCRYPT. In my initial testing I chose a character column to encrypt,
and then used this set of SQL statements to test it out:
SET ENCRYPTION PASSWORD = 'ABC’;
INSERT INTO EMP(EMPNO,FIRSTNME,
(ENCRYPT('12346'),'TROY,'L', 'COLEMAN') #
This generated the following error:
DSNT408I SQLCODE = -20144, ERROR:
THE ENCRYPTION IS INVALID BECAUSE THE
LENGTH OF THE PASSWORD
WASS LESS THAN 6 BYTES OR GREATER THAN 128
I quickly realized I needed to increase the password from three
characters (ABC) to six (ABCDEF). But then I encountered this error:
SQLCODE = -433, ERROR: VALUE
506FFE4F232B640B11451BA29F7C506A24249436CF'X IS TOO LONG
With additional research, I found that the column needs to be varchar.
The 6-character column actually needed to be a varchar 64 byte column. This is
made up of 6 bytes for maximum length of unencrypted data plus 2 additional
bytes to round up to 8-byte boundary. Then there's 24 bytes for the password
phrase plus 32 bytes for the password hint.
This experience made me realize that I've been underestimating the
critical role database design considerations play when it comes to implementing
encryption. For instance, many third-party applications and reporting tools
limit the use of DB2 SQL encryption language. Even a basic statement like SET
ENCRYPTION PASSWORD might not be allowed. So unless you can get your vendor to
add in support for DB2 SQL encryption statements, you should look at other
With this in mind, here are some excellent resources on encryption.
* First is this presentation given by Roger Miller at IDUG EMEA 2009. It
provides an overview of various encryption solutions, including ICSF, DB2
fieldprocs, DB2 editprocs, user-defined functions, SQL functions. Encryption is
also discussed as it relates to DRDA, tape and disk systems.
* The IBM Redbook, Security Functions of IBM DB2 10 for z/OS,
also covers an
array of encryption options.
* Another IBM Redbook, Securing and Auditing Data on DB2 for z/OS,
provides step by step details on implementing an editproc. It also covers key
restrictions (e.g., a table that uses an editproc cannot be altered).
* This 2008 article by Ulf Mattsson explains how to encrypt columns,
rows, indexes and disk in DB2 for z/OS.
If you use encryption on DB2 for z/OS, please share your experiences in