DB2 Native Encryption on Windows

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!

Jim YoumansSr Database AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
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.  

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tomas Helgi JohannssonDatabase Administrator / Software EngineerCommented:

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 AdministratorAuthor Commented:
Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.