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