More DB2 Family Security Best Practices Part 4

The next part (Part 4) of this DB2 Family Security Best Practices blog talks about the many aspects and issues around DB2 LUW and DB2 z/OS encryption.  Encryption needs to be discussed extensively with your security department and various applications because it has long term impacts on operations, maintenance, and applications.

First, DB2 encryption protects your data at rest. It does it without any additional hardware, software, or application changes.  When DB2 encryption is used, the encryption routines are embedded inside, native in the DB2 system, and automatically encrypt the data and logs before writing them to disk and de-crypting the data as it is accessed through the various application interfaces.  The DB2 encryption is established through an administrator creating a keystore and a database master key to be used for your database instance.  The database master key can be rotated after an appropriate time period, or if you think it has been compromised, to protect the database environment with a new key.

Next, encryption has some performance overhead because of all the encryption and decryption operations.  Some of the encryption operational overhead can be done within special processing CPUs and specialized storage devices.  For example, within the z/OS environment some of the encryption and decryption algorithm processing can be assigned a processor affinity to use a dedicated or semi-dedicated CPU encryption resource.  Within Linux or UNIX environments that run on the x86 architecture, the new Intel Haswell chipsets have incorporated several new instruction sets that improve the performance of cryptographic algorithm processing.  Encryption costs additional processing resources that can be mitigated with special processors and configurations.

Next, encryption can be implemented against all environments.  Older databases can have encryption implemented very easily.  For example, with older DB2 LUW databases, encryption for a database can be implemented through a redirected restore type operation.  First create the keystore, configure the keystore to the DB2 instance, backup your database, Drop it, and then restore your database with the new ENCRPT keyword.

To use DB2 LUW encryption, it is easy to setup with the ENCRYPT phase when the database is created.


Also, in addition to the using DB2 encryption for the database, it is a good practice to compress and encrypt your database backups through the following BACKUP command.


DB2 LUW native encryption uses the best and some of the toughest encryption algorithms–FIPS 140-2 certified cryptographic modules that are compliant with the NIST SP 800–131 standards.  As stated before, these DB2 encryption native algorithms can have their performance improved via the Intel chipsets that leverage the INTEL AES-NI architecture.  This helps both encryption and decryption performance within your DB2 encryption implementation.

DB2 z/OS encryption can be implemented at the storage level through storage devices such as the DS8000, at the table level, or at the column level.  Each of these different levels of encryption have different configurations, performance overhead factors, and application programming impacts.
For researching the DB2 encryption via the storage DS8000 considerations read through the IBM Redbook “IBM System Storage Data Encryption” found here. For the DB2 encryption at the table level or column level, check out the IBM Redbook “Security Functions of IBM DB2 10 for z/OS” found here within the Appendix B. “Introduction to cryptography”.  The DB2 Version 11 for z/OS manual “Managing Security” is a good place to start learning how to implement DB2 encryption. Other security manuals help you understand security considerations.

When wanting to add full table row encryption to DB2 z/OS tables, an EDITPROC referencing encryption routines is currently the standard solution.  Unfortunately, adding an EDITPROC to a table definition restricts many of the non-disruptive ALTER table capabilities that have been enhanced in DB2 in Version 10 and 11.  This is why many z/OS DB2 encryption implementations utilize the storage alternative DS8000 or encrypt only the table columns necessary.  It is probably better to encrypt only the sensitive data to be hampered by encryption performance overhead as little as possible.

To encrypt any type of data, the column definition and its encrypted and decrypted format needs to be fully realized.  Depending on the original data type, the resulting decrypted data can be Binary, BIT, Character, VARBINARY, or VARGRAPHIC data.  Since encrypting and decrypting the data can cause the column size to increase, sometimes up to three times as big as the original data size, research, analysis, and testing is needed to analyze results against your data definitions.  For example 16-digit credit card numbers can expand to 16*3= 48 bytes, and then, depending on the Coded Character Set Identifier (CCSID), it can expand even further.  The three times expansion can occurs sometime in a worst case when the source data is an ASCII or EBCDIC column is converted to a Unicode UTF-8 data item.  Depending on the CCSID and the encryption output format, the stored column can take up additional space. That extra storage needs to be planned within all aspects of the table design and application plans.

The column size also needs to be taken into consideration as it is referenced by different application programming code and translated across different CCSID servers or distributed connections.  Research needs to done to handle the encrypted password, possible hint, decrypted data type, and its expanded length so it can be fully realized against all the programming considerations and processing scenarios.

The access of the encrypted information needs to be referenced through secure authorized connections.  These incoming remote connections must be processed through verification and RACF consideration.  These extra security parameters are configured through system settings for the remote connection through the SYSIBM.SYSLUNAMES security definitions.  Get your security department and your RACF administrators to help verify these settings so that your remote connection is through a properly configured, fully trusted, context connection.

Within the database design the encrypted columns need to be evaluated for indexing and referential integrity.

Due to the flexibility of being able to implement DB2 z/OS encryption at the table or column level a major design consideration is to be consistent with DB2 encryption across your referential integrity relationship definitions.  By being consistent and using the same decryption passwords across the tables or columns in the relationship, your application can minimize the performance overhead impact of constant conversion.

Within application SQL, a WHERE clause using an encrypted column can requires decryption to evaluate values.  This can end up causing DB2 to decrypt and evaluate every row value, causing huge performance overhead.  SQL table joins using encrypted columns can sometimes cause the SQL predicate to become a stage 2 predicate, severely degrading application and SQL performance. So make sure your database table design, parent and child table referential integrity, and application SQL requirements are fully prototyped to understand all the encryption and performance considerations within your overall DB2 encryption strategy.

DB2 encryption is a tremendous deterrent to hackers.  As long as your database design, referential integrity, and application SQL is researched and implemented properly, encryption is a great way to defend your systems and data from any unauthorized discovery of your private data assets.

Support IDUG through the European IDUG conference in Dublin, Ireland November 15-20.  For more details go to

I authored two CIO DB2 Security Audit white papers and a technical DB2 Security Audit white paper for SEGUS. Also at the link is a webinar talking about the SEGUS security and DB2 audit considerations. The How to establish a Security Audit for DB2 z/OS white paper can be found here (” and the Is 339° Security Audit Sufficient? White paper is here ( and the Powerpoint webinar discussion presentation with SEGUS is here (

Here’s a list with links to other posts that may help you improve your DB2 performance, security, and ease-of-use:

5 More DB2 SQL Performance Tips
Another 5 More DB2 SQL Performance Tips
5 Big Data SQL Performance Tips – Fixing Generated SQL
DB2 11 SQL Performance Improvements
5 More SQL Performance Tips for your Big Data
Hadoop SQL: 4 Reasons Why BigInsights Is the Best
Vital Java DB2 SQL Performance Considerations
More DB2 Family Security Best Practices Part 7: Preventing SQL Injection
DB2 SQL Security Audits

Dave Beulke is a system strategist, application architect, and performance expert specializing in Big Data, data warehouses, and high performance internet business solutions.  He is an IBM Gold Consultant, Information Champion, President of DAMA-NCR, former President of International DB2 User Group, and frequent speaker at national and international conferences. His architectures, designs, and performance tuning techniques help organization better leverage their information assets, saving millions in processing costs.  Follow him on Twitter here (@DBeulke) or connect through LinkedIn here (

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>