IBM Support

[Db2] How to decrypt an encrypted database.

How To


Summary

Users can create an encrypted database by using ENCRYPT option in the CREATE DATABASE command, or RESTORE DATABASE command.
This note describes how to decrypt existing encrypted database.

Environment

Db2 10.5 Fix Pack 5 or later.
An encrypted database by Native encryption.

Steps

Backup database command decrypts data before backup. So users can restore the backup image to encrypted databases or no encrypted databases. 
BACKUP DATABASE command
> Note: For databases that are natively encrypted, data is decrypted before backup.
The following steps illustrate on how to restore database as no encrypt option.
  1. Take an offline backup 
    $ db2 backup db encdb to /work
Backup successful. The timestamp for this backup image is : 20230120174024
  2. Drop existing database (only if you want to replace the existing database) 
    $ db2 drop db encdb
DB20000I  The DROP DATABASE command completed successfully.
  3. Restore the backup image with NO ENCRYPT option.
    The restore database command returns SQL1744N in case existing target database is encrypted.
    If users do no specify NO ENCRYPT nor ENCRYPT options in case the backup is taken from an encrypted database, the restore database command returns SQL1743N. ​​​​ 
    $ db2 restore db encdb from /work taken at 20230120174024 no encrypt without rolling forward
DB20000I  The RESTORE DATABASE command completed successfully.
  4. Check whether the database is not encrypted. 
    $ db2 connect to encdb

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.5.8.0
 SQL authorization ID   = DB2INST1
 Local database alias   = ENCDB

$ db2 get db cfg | grep -i encr
 Encryption Library for Backup                 (ENCRLIB) =
 Encryption Options for Backup                (ENCROPTS) =
 Encrypted database                                      = NO
NOTE: The backup image is encrypted if the database is natively encrypted. As you can use an encrypted backup image to restore not encrypted database, you can take a not encrypted backup image by the following steps.
  1. Update ENCLIB database configuration to NULL 
    $ db2 connect to encdb

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.5.8.0
 SQL authorization ID   = DB2INST1
 Local database alias   = ENCDB

$ db2 update db cfg using ENCRLIB NULL
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
  2. Take an offline backup 
    $ db2 backup db encdb to /work
Backup successful. The timestamp for this backup image is : 20230120174710
  3. Check whether the backup image is not encrypted.
    The Compression returns 2 (Encrypted) if the backup image is encrypted. 
    $ db2ckbkp -h /work/ENCDB.0.db2v115.DBPART000.20230120174710.001 | grep -i comp
        Compression                    -- 0 (No Library Applied)

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PmnAAE","label":"Security and Plug-Ins-\u003EEncryption"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.5.0;11.1.0;11.5.0"}]

Document Information

Modified date:
20 January 2023

UID

ibm16856901

Page Feedback