With fixpack 5 of DB2 10.5, IBM introduced Native Encryption for data at rest in DB2. This is a fairly significant new feature for introduction in a fixpack. It does require separate licensing – either the Advanced Edition of ESE or WSE or the separate purchase of the Native Encryption feature.

DB2 Native Encryption is transparent data encryption for data at rest. It does not encrypt data that is in flight or in memory. There are no application changes that are necesary, and it includes functionality for managing encryption keys. You don’t change data encryption keys, but instead can change the key used to access the data encryption keys – the key encryption key.

Planning

DB2 Native Encryption is NOT performance neutral. It is likely to impact performance, and that performance impact is expected to be “less than 100%”. There may be some areas where the impact is more noticeable than others. It largely impacts CPU time. If you implement Native Encryption on a system that already runs at 80% CPU utilization, bad things will likely happen. It is very strongly recommended that you do through performance testing before implementing it in production. The system I’m enabling it on is currently extremely over-sized, averaging LESS than 5% cpu utilization. Because of this, I’m not terribly worried about the impact, but I sure would be with a more reasonably sized system.

The client I’m working with now chose to purchase the Native Encryption feature to use with a standard WSE implementation. The program number to get from IBM is:

5725T25             IBM DB2 Encryption Offering

The code for Native Encryption is included in db2 10.5 fixpack 5, so there is nothing separate to install. To get the license file you’ll need, you’ll need to download the following part from passport advantage:

CN30DML	IBM® DB2® Encryption Offering - Quick Start and Activation
10.5.0.5 for Linux®, UNIX and Windows®

If your DB server is not already on 10.5 fixpack 5, you’ll need to upgrade to it before implementing Native Encryption.

Implementation

The steps for implementing Native Encryption are pretty well laid out in the IBM DB2 Knowledge Center page on Native Encryption. EXCEPT if you copy and paste the command for creating the keystore. I did and got this error:

CTGSK3020W Invalid object: –strong

The problem is documented in the comments on this page. No idea why IBM hasn’t fixed the documentation yet. The ‘-‘ character before two of the options on this command is incorrect in the info center, and it’s barely visable as such. In my steps below, I use the correct kind of dash, so you should be able to copy and paste the below.

Here are the steps for encrypting an existing database – you must do a backup and restore to do it at this time. All actions here are done as the DB2 instance owner.

1. Apply the license file – unzip/untar the dowloaded activation file and navigate to db2ef/db2/license, and issue:

   db2licm -a db2ef.lic

2. Ensure your PATH and library variables are set properly. To do this, I added the following lines to my DB2 instance owner’s .bash_profile (you’d use .profile on AIX):

   PATH=$PATH:$HOME/sqllib/gskit/bin
   
   export PATH
   
   LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$HOME/sqllib/lib64/gskit
   
   export LD_LIBRARY_PATH
   
   LIBPATH=$LIBPATH:$HOME/sqllib/lib64/gskit
   
   export LIBPATH
   
   SHLIB_PATH=$SHLIB_PATH:$HOME/sqllib/lib64/gskit
   
   export SHLIB_PATH
   

3. Next, issue the command to create your keystore. This is the one with the incorrect dashes in the IBM DB2 Knowledge Center:

   gsk8capicmd_64 -keydb -create -db /db2home/db2inst1/pdesignkeystore.p12 -pw MfsWq9UntZGGhe96 -strong -type pkcs12 -stash;

   There is absolutely no output returned by this command. You’ll likely want to change the location, and the password you feed into this.

4. Update the dbm cfg with the keystore location:

   $ db2 update dbm cfg using keystore_type pkcs12 keystore_location /db2home/db2inst1/pdesignkeystore.p12
   DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed
   successfully.
   

5. Backup your database

    db2 backup db sample to /db2backups compress without prompting

6. Drop your database (man, this is hard to do – I still cringe whenever using a drop command)

   $ db2 drop db sample
   DB20000I  The DROP DATABASE command completed successfully.
   

7. Restore your database, with the encrypt option

    db2 "restore db sample from /db2backups taken at 20150827182456 encrypt without rolling forward without prompting"

Your database is now encrypted, congratulations!

Backups

Remember DB2 will now encrypt every backup you take with the same encryption options you’ve set in the db cfg. This means that part of what you now need to backup is that keystore that you created. I think you’ll also want to store the keystore password somewhere, as you may need it.

I have so far found that these backups take longer than non-encrypted backups. A backup of a test database before enabling Native Encryption took 4 minutes. The one afterwards took 11 minutes. On production, this took an 18-minute backup and turned it into a 53-minute backup. These differences are significant. You may want to test backup duration as a part of your performance testing process.

Backup Compression

On this particular system, I take compressed DB2 backups to disk, which is later externalized. Immediately after enabling Native Encryption, I noticed that the backup size was much larger – that it didn’t look like my database backup was getting compressed at all. After some back and forth with some very helpful IBMers, I learned that I had missed a critical few lines on this page in the DB2 Knowledge Center. This was surprising as I had spent hours with this page while getting ready to implement Native Encryption. Here is what I missed:

To both compress and encrypt a database backup image, specify the db2compr_encr library (or the libdb2compr_encr library on non-Windows platforms) in place of db2encr (or libdb2encr).

When I then tried to specify libdb2compr_encr as the encryption library in my backup command, I got this:

$ db2 backup db sample online to /db2backups encrypt encrlib 'libdb2compr_encr.so' without prompting
SQL2459N  The BACKUP DATABASE command failed to process an encrypted database
backup or compressed database backup because of configuration or command
errors. Reason code "1".

Looking at the details of that error code, I see:

$ db2 ? SQL2459N


SQL2459N  The BACKUP DATABASE command failed to process an encrypted
      database backup or compressed database backup because of
      configuration or command errors. Reason code "".

Explanation:

Encrypted or compressed database backups require specific configuration
settings. Some of these configuration settings can be specified in the
BACKUP DATABASE command options. This message is returned with a reason
code when configuration settings and BACKUP DATABASE command options are
invalid. Reason code:

1

         The BACKUP DATABASE command specified a compression or
         encryption library or associated options. The database
         configuration parameters ENCRLIB or ENCROPTS were also set.


2

         The BACKUP DATABASE command specified both compression and
         encryption libraries.

User response:

To resolve the issues outlined in the explanation:

1

         The options to specify a compressed or encrypted backup must be
         specified by either the command options or database
         configuration parameters, not both. Run the BACKUP DATABASE
         command without specifying a compression or encryption library
         or associated options. Or, you can clear the database
         configuration parameters ENCRLIB and ENCROPTS and run the
         BACKUP DATABASE command with the original command options as
         specified again.


2

         You can specify that a backup is compressed or encrypted, not
         both. Run the BACKUP DATABASE command specifying only a
         compression or encryption library, not both.


   Related information:
   BACKUP DATABASE command

Odd – you cannot explicitly specify a value for the encryption library if you also have the database parameter ENCRLIB set. Which I do.

I set the ENCRLIB parameter because I always take compressed backups of this database:

$ db2 update db cfg for sample using ENCRLIB /db2home/dbinst1/sqllib/lib/libdb2compr_encr.so
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

Note that the file suffix varies by platform, and the file name is different on windows. You have to specify the full path, not just the file name.

And whew, finally a compressed encrypted database backup actually worked.

This led me to another question: What if I take a backup without specifying the COMPRESS keyword, but with ENCRLIB set to libdb2compr_encr. Will I get a compressed backup or an uncompressed backup?

It turns out that the backup I get is compressed. So essentially with an encrypted database backup, the COMPRESS keyword on the backup command is meaningless. Whether or not you get a compressed backup depends solely on the setting of ENCRLIB, whether specified in the DB cfg or in the BACKUP command.

HADR

An HADR standby database (principal or tertiary) can be encrypted with the same key, with a different key, or not encrypted at all. This means that you should be able to do an HADR database in a rolling fashion – though I have not tested that process. I took both primary and standby down to encrypt them because the outage time was well within acceptable time for the client I was working with. I do find it interesting though, that since encryption is for data at rest, HADR can allow this situation.