DB2 Backups When Using Native Encryption

I’ve recently implemented native encryption for a small database on a server that is somewhat oversized on CPU and memory. One of the things I noticed after encrypting my database was both increased backup duration and increased backup size.

Backup Size

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 Knoledge 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.

So I went to 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 filename 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.

Now, I find it a bit frustrating that it doesn’t warn me when I take a compressed backup but do not specify the right encryption library that the backup taken will simply be an uncompressed backup with no warning or error message generated.

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 keword 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.

Backup Duration

After enabling Native Encryption, I also noticed that my backup duration nearly tripled. A backup that used to take 18 minutes now takes 53 minutes. I’m still working to see if I can tune this down some with some memory and other tuning. The tablespaces in this backup are not ideal for backup parallelism.

You may also like...

4 Responses

  1. JEFFREY BENNER says:

    Ember, did you ever find a way to improve the execution time of your encrypted backup? I am dealing with the same issue.

    • Ember Crooks says:

      No, I haven’t. The main client I’m working with this on doesn’t seem to care much about duration, and they have HADR for fast recovery. The first thing I would be trying is to more evenly distribute tables across multiple tablespaces to allow more parallelism, but I haven’t tried it yet. Also, keeping up to date with fixpacks and versions may help, as IBM is working actively on improving Native Encryption. With DB2 11.1, they’re including Native encryption with all editions, so I suspect it will get wider use.

  2. BK says:

    Hi, Did anyone find a way to improve encrypted database backups?

  1. September 29, 2015

    […] DB2 Backups When Using Native Encryption […]

Leave a Reply

Your email address will not be published. Required fields are marked *