DB2 Native Encryption on Windows

Jim Youmans
Jim Youmans used Ask the Experts™
DB2 10.5 on Windows

Does anyone have any experience with Native Encryption for DB2 on Windows.  Or Linux?  From what I have read, it should be transparent to the users but I have to assume there is overhead involved somewhere.  

The main thing I have read is that backups can take up to 3x longer.

Any insights would be appreciated!

Thank you!

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Data Warehouse / Database Architect
Hi Jim,

I've never used DB2's Native Encryption, but I do have a long background with DB2 and other encryption protocols.

You're correct that the encryption is (mostly) transparent to the user.  For the most part your SQL won't decrypt the data unless it needs to be displayed or tested in unencrypted form.  (Testing A<B on the encrypted data probably doesn't do what the user wants.  Testing A=B does properly compare for equality.)  You probably don't want a clustered index on an encrypted column.  If the sort order is based on the encrypted value you lose the intended order and its benefits.  If the sort order is based on the displayed data, the indexes have to be decrypted for each access.  Both are problematic.

Backups do take longer.  I wouldn't have expected triple, but my research shows that it can take that long.  Depending on the underlying hardware, copying a normal data block can be as efficient as a single instruction.  Every bit of the overhead is I/O.  Encrypting the block as part of the copy process moves the overhead from I/O to serial CPU.  The encryption process doesn't lend itself to parallelism, so the limiting factor becomes the processing power of a single CPU/core.  Again, I'm surprised that the overhead would triple the backup time as CPUs can execute a LOT of instructions on in-cache data!

I hope this little insight is helpful.  
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

One thought on this besides the points Kent mentioned. As Kent mentioned backups with encryption will take longer and the backupimage will take significantly more space if you don't specify also specify compress or use the db2compr_encr library.
A good key-management procedures and storage will have to be in place as well before using encryption to be able to restore encrypted backup images. If the masterkey or keystore is missing or unusable then the database or backup-image is unusable as well.


    Tomas Helgi
Jim YoumansSr Database Administrator


Thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial