IBM Support

Can I use online backup to encrypt an existing database ?

Technical Blog Post


Abstract

Can I use online backup to encrypt an existing database ?

Body

Can I use online backup to encrypt an existing database ?
The answer is YES.
Converting a database to be encrypted is a kind of big change.
Therefore, I still strongly suggest to take an offline backup before the change if business permits the outage time to do that.
 

On the DB2 knowledge center page "Encrypting an existing database", it shows the example steps using an offline backup.
But that does not necessarily mean we can only use the offline backup.

On this blog, I will show some sample steps using online backup to be prove that.
( Indeed, there were questions about this matter. )

NOTE :
  1. Do not refer all commands to implement on your system.
      Some steps are just for showing my intention about how I will do the test.
      And the detail command could be different depending on the environment.
  2. I borrowed partial commands from other blog page "Hands-on example for a new encrypted database".
  3. I used local key manager, not by centralized key manager.
       But that does not matter for testing this topic.

 

-Created a non encrypted database for the test and make it archive mode to run online backup.

v111_01@jshadr1:~> db2 create db encdb
DB20000I  The CREATE DATABASE command completed successfully.
 
v111_01@jshadr1:~> db2 update db cfg for encdb using LOGARCHMETH1 DISK:/home/v111_01/archive
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

v111_01@jshadr1:~> db2 backup db encdb to /dev/null
 

- Run a transaction to generate archived logs.

v111_01@jshadr1:~/history> db2 -tvf 02_import.db2
import from histdata1.ixf of ixf commitcount 10000 insert into history
...

 

- Run online backup

 

v111_01@jshadr1:~/backup> db2 backup db encdb online
 
Backup successful. The timestamp for this backup image is : 20180207013745
 
- Run other transaction to generate archive logs after the last backup
v111_01@jshadr1:~/history> db2 -tvf 02_import.db2
import from histdata1.ixf of ixf commitcount 10000 insert into history
...
 

v111_01@jshadr1:~/archive/v111_01/ENCDB/NODE0000/LOGSTREAM0000/C0000000> ls -tlr
total 391224
-rw-r----- 1 v111_01 db2igrp 1859584 2018-02-07 01:23 S0000000.LOG
-rw-r----- 1 v111_01 db2igrp 4202496 2018-02-07 01:23 S0000001.LOG
-rw-r----- 1 v111_01 db2igrp 4202496 2018-02-07 01:23 S0000002.LOG
-rw-r----- 1 v111_01 db2igrp 4202496 2018-02-07 01:23 S0000003.LOG

...
-rw-r----- 1 v111_01 db2igrp 4202496 2018-02-07 01:41 S0000096.LOG
-rw-r----- 1 v111_01 db2igrp 4202496 2018-02-07 01:41 S0000097.LOG
-rw-r----- 1 v111_01 db2igrp 1667072 2018-02-07 02:02 S0000098.LOG

 

-Creating keystore and update the instance configuration.
 
v111_01@jshadr1:~> mkdir test_encrypt
v111_01@jshadr1:~> cd test_encrypt
 
v111_01@jshadr1:~/test_encrypt> /home/v111_01/sqllib/gskit/bin/gsk8capicmd_64 -keydb -create -db ccardskeystore.p12 -pw Str0ngPassw0rd -strong -type pkcs12 -stash
v111_01@jshadr1:~/test_encrypt> ls -tlr
total 4
-rw------- 1 v111_01 db2igrp 129 2018-02-07 01:45 ccardskeystore.sth
-rw------- 1 v111_01 db2igrp   0 2018-02-07 01:45 ccardskeystore.p12
 
 
v111_01@jshadr1:~/test_encrypt> db2 "update dbm cfg using keystore_type pkcs12 keystore_location /home/v111_01/test_encrypt/ccardskeystore.p12"
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
SQL1362W  One or more of the parameters submitted for immediate modification
were not changed dynamically. Client changes will not be effective until the
next time the application is started or the TERMINATE command has been issued.
Server changes will not be effective until the next DB2START command.
 
 
v111_01@jshadr1:~/test_encrypt>  db2 get dbm cfg | grep -i keystore
Keystore type                           (KEYSTORE_TYPE) = PKCS12
Keystore location                   (KEYSTORE_LOCATION) = /home/v111_01/test_encrypt/ccardskeystore.p12
 
v111_01@jshadr1:~/test_encrypt> db2 terminate
DB20000I  The TERMINATE command completed successfully.
v111_01@jshadr1:~/test_encrypt> db2stop ; db2start
02/07/2018 01:46:47     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
02/07/2018 01:46:49     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
 
- Checking the current configuration of non encrypted
v111_01@jshadr1:~/test_encrypt> db2 get db cfg for encdb | grep -i encr
Encryption Library for Backup                 (ENCRLIB) =
Encryption Options for Backup                (ENCROPTS) =
Encrypted database                                      = NO
 
 
v111_01@jshadr1:~/test_encrypt> db2 activate db encdb
DB20000I  The ACTIVATE DATABASE command completed successfully.
v111_01@jshadr1:~/test_encrypt> db2pd -db encdb -encrypt
 
Database Member 0 -- Database ENCDB -- Active -- Up 0 days 00:00:48 -- Date 2018-02-07-01.48.35.879477
 
Encryption Info:
   Object Name:               ENCDB   
   Object Type:               DATABASE
   Encyrption Key Info:       Database encryption not enabled.    <===========
   KeyStore Info:             
                 KeyStore Type: PKCS12
             KeyStore Location: /home/v111_01/test_encrypt/ccardskeystore.p12
            KeyStore Host Name: jshadr1
           KeyStore IP Address: 192.168.153.38
      KeyStore IP Address Type: IPV4
 
- Drop the database
v111_01@jshadr1:~/test_encrypt> db2 deactivate db encdb
DB20000I  The DEACTIVATE DATABASE command completed successfully.
 
v111_01@jshadr1:~/test_encrypt> db2 drop db encdb
DB20000I  The DROP DATABASE command completed successfully.
 
- Restore with encryption and rollforward the database.

 

v111_01@jshadr1:~/backup> db2 "restore db encdb from /home/v111_01/backup taken at 20180207013745 encrypt"

 

DB20000I  The RESTORE DATABASE command completed successfully.
 
v111_01@jshadr1:~/backup> db2 "rollforward db encdb to end of logs"
 
                                 Rollforward Status
 
Input database alias                   = encdb
Number of members have returned status = 1
 
Member ID                              = 0
Rollforward status                     = DB  working
Next log file to be read               = S0000099.LOG
Log files processed                    = S0000049.LOG - S0000097.LOG
Last committed transaction             = 2018-02-06-14.43.50.000000 UTC
 
DB20000I  The ROLLFORWARD command completed successfully.
 
v111_01@jshadr1:~/backup> db2 "rollforward db encdb complete"
 
                                 Rollforward Status
 
Input database alias                   = encdb
Number of members have returned status = 1
 
Member ID                              = 0
Rollforward status                     = not pending
Next log file to be read               =
Log files processed                    = S0000049.LOG - S0000097.LOG
Last committed transaction             = 2018-02-06-14.43.50.000000 UTC
 
DB20000I  The ROLLFORWARD command completed successfully.
 

- Check the encryption configuration.
 

v111_01@jshadr1:~/backup> db2 activate db encdb
DB20000I  The ACTIVATE DATABASE command completed successfully.
v111_01@jshadr1:~/backup> db2 connect to encdb
 
   Database Connection Information
 
Database server        = DB2/LINUXX8664 11.1.1.1
SQL authorization ID   = V111_01
Local database alias   = ENCDB
 
v111_01@jshadr1:~/backup> db2pd -db encdb -encrypt
 
Database Member 0 -- Database ENCDB -- Active -- Up 0 days 00:00:20 -- Date 2018-02-07-02.03.25.055336
 
Encryption Info:
   Object Name:               ENCDB   
   Object Type:               DATABASE
   Encyrption Key Info:       
          Encryption Algorithm: AES
     Encryption Algorithm Mode: CBC
         Encryption Key Length: 256
              Master Key Label: DB2_SYSGEN_v111_01_ENCDB_2018-02-07-01.59.43_14178748
Master Key Rotation Timestamp: 2018-02-07-01.59.44.000000
   Master Key Rotation Appl ID: *LOCAL.DB2.180206145837
   Master Key Rotation Auth ID: V111_01
     Previous Master Key Label: DB2_SYSGEN_v111_01_ENCDB_2018-02-07-01.59.43_14178748
   KeyStore Info:             
                 KeyStore Type: PKCS12
             KeyStore Location: /home/v111_01/test_encrypt/ccardskeystore.p12
            KeyStore Host Name: jshadr1
           KeyStore IP Address: 192.168.153.38
      KeyStore IP Address Type: IPV4
 
 
v111_01@jshadr1:~/backup> db2 get db cfg for encdb | grep -i encr
Encryption Library for Backup                 (ENCRLIB) = libdb2encr.so
Encryption Options for Backup                (ENCROPTS) = CIPHER=AES:MODE=CBC:KEY LENGTH=256
Encrypted database                                      = YES

 

We see the database is encrypted successfully now.


 

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11139968