Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted August 16, 2018


The California Consumer Privacy Act: What You Need to Know

High-Performance Data Encryption and Db2

By Lockwood Lyon

IBM's Db2 Version 12 for z/OS was designed to synergize with new IBM z14 hardware, which includes several new and updated options for hardware-assisted data encryption and compression. These features can be used by the database management system to store and retrieve encrypted and compressed data transparently without application knowledge or intervention.

This is a significant advance from prior software-based processes, which could be extremely CPU-intensive. By offloading encryption and compression work to specialized hardware, Db2 can reduce CPU costs while also shortening transaction times.

Encryption Basics

As children, many of us played with secret codes and messages, including substitution ciphers (A for B, B for C, etc.). Simple ciphers and codes allow for easy encoding and decoding of messages. In today's world, message encryption is quite sophisticated; however, the price of sophistication is in the large number of CPU cycles necessary for encryption and decryption. Implementing encryption even on a small scale requires capacity planning for the additional CPU resources required.

This relatively high resource cost is unfortunate, as encryption is one potential solution for several common information technology problems. Consider the value of data compression. Much of the data that we use is in text form, and data compression can reduce the amount of data we transmit across networks and store on disks by a factor of two or more. Since most data compression algorithms rely on substituting small bit strings for commonly occurring sequences of characters, data compression is a form of encryption. In fact, many data encryption algorithms reduce the size of messages, especially text, making them a possible method for data compression as well.

Another common problem is that of data protection. Many governmental entities and organizations require that personal, financial or health care data be protected from unauthorized access, and one method of doing so is to encrypt that data.

When making decisions about what data to encrypt and how to implement it, there are four areas to consider:

  • How much data to encrypt (important fields, entire records, complete files, or more);
  • Where to encrypt the data (when acquired, across the network, in-memory, in storage);
  • How to encrypt (basic algorithms, regulatory requirements, etc.);
  • What resources to use (software-based, hardware-assisted, etc.).

In addition, since encryption requires using keys, key management is another area that must be addressed.

Pervasive Encryption

Pervasive encryption is the concept of encrypting all data, everywhere. All input files, all output files, all databases are encrypted. Importantly, the process of encryption and decryption should be automatic and transparent to applications and users. Pervasive encryption is typically not implemented across a data center or for an entire database; rather, specific sets of files, tables and indexes are selected based on their inclusion of fields that contain personal, financial, health care data, or other data where a requirement exists for data protection. We will address the CPU resource cost of doing so much work on so much data shortly.

In order to implement a workable and security-compliant encryption scheme, IT must have already implemented metadata management. Such a system records where data elements are located, their classification (personal identifiers, financial, etc.), how the data is accessed (read, update), and security profiles indicating what individuals, groups, applications and administrators have what levels of access. There will also be a component of event management, where the system records who accesses what data elements using what processes, whether data was changed, and includes notification of any security issues. When you start using encryption you must also add key management, since keys will now be subject to  security as well.

One last advantage of using pervasive encryption is that it can be used as evidence that the business is mitigating security risks. IT can thus avoid multiple complex systems of field-level or record-level encryption that would necessarily require software control both of multiple categories of keys and changing data security requirements.

Encryption and Decryption CPU Costs

As noted previously, one of the big disadvantages of encrypting large amounts of data is the CPU cost. Every access to every table row, every record read from or written to a file, and every network transmission will require CPU resources for encryption or decryption. This will be most relevant for read-only access such as reporting. Consider a simple customer list. This will require decrypting each of the  records/rows accessed. Indeed, for this reason many algorithms are optimized to be faster or cheaper during decompression or decryption.

Except for a few hybrid or special-purpose hardware systems, most encryption/decryption algorithms were implemented in software. In early versions this required specific invocation by the user or application; later, as systems matured, such processes became automated and transparent. Still, executing this software uses CPU cycles, which then competes with other applications in the system for CPU resources.

IBM z14 Cryptographic Hardware

The IBM z14 server includes several features that are specialized for cryptographic processing. They include the following:

  • A cryptographic coprocessor chip that works synchronously with the central processors;
  • A Crypto Express card that is placed in the I/O drawer and interfaces to storage media;
  • A Trusted Key Entry (TKE) workstation for key management;
  • An Integrated Cryptographic Service Facility (ICSF), a software component of the operating system, that provides the application programming interface to request cryptographic services.

The heart of the operation is the coprocessor assist for cryptographic functions (CPACF). It supports pervasive encryption. Since this chip executes synchronously with the regular CPU, cryptographic processing will not compete for CPU resources with applications or system processes. Thus, IT can implement a pervasive encryption solution without concern for resource constraints.

Db2 version 12 for z/OS has been enhanced to take advantage of this new operating system feature. First, IT must apply maintenance to the base level Db2 software. This support level is called Function Level 502. The database administrator can use policy controls to specify which data objects at which points are subject to encryption. This includes decisions on whether or not to encrypt the following:

  • Tables
  • Indexes
  • Db2 log files
  • The Db2 catalog and directory
  • Database backups
  • Utility input and output datasets

Note that cryptographic processing also includes the option for data compression. With this feature enabled, Db2 will compress table data transparently using the specialty coprocessor before storage on disk.

Future Features

Future maintenance upgrades to Db2 version 12 will include another high-performance feature: order-preserving index compression.

By its very nature, data compression changes data into something unreadable by the human eye. While this saves disk space and reduces network transfer times, the compressed data loses many of the features of the original data, including any concept of sort order. Consider a customer table that we wish to maintain in sequence ascending by customer ID. We load the table from a file containing records that are pre-sorted in that order. Db2 will load these records sequentially into the table, although the data rows are compressed.

However, consider now an index on customer ID. Since indexes have an internal tree structure that is used to maintain sequencing the index data must be stored as-is, i.e. uncompressed. This may lead to a situation where sensitive data is compressed (or encrypted) in the table but appears clearly in the index. This may make the index dataset a security risk, particularly if customer ID is defined (in metadata management) as a personal identity field that must be protected. Therefore, the ability to compress index data while preserving index entry order not only allows for smaller indexes (and hence faster access) but also for a simple form of data encryption.


Pervasive encryption can have high costs and requires good metadata management. IBM's hardware-assisted solution featured on its z14 server platform can be used for files and Db2 databases without contributing to CPU resource constraints. Implementing pervasive encryption thus allows IT and the business to take a major step towards mitigating security risks without significantly affecting the performance of critical operational systems.

# # #

IBM z14 Technical Guide (SG24-8451-00)

See all articles by Lockwood Lyon

DB2 Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.