The following new DB2 offerings are available as add-ons to different DB2 product editions
The DB2 Editions are
The features of the various new add-on offerings are:
For Purescale with DB2 Advanced Enterprise Server Edition or DB2 Advanced Workgroup Server Edition
If your DB2 pureScale configuration contains a member that is used only for administrative or
failover purposes, licensing requirements for this administrative member can be reduced.
See License Information documents
Native database encryption (encryption at rest) with no application or schema changes
Included by default in:
Available for purchase in:
Encryption works via a Database Encryption Key (DEK) which is stored and managed by the database
The Database Encryption Key (DEK) is protected via a Master Key, encrypted master keys are stored outside the database in a PKCS#12-compliant keystore and the keystore is protected via a password.
A DB2 instance is associated with 1 keystore via the keystore_type and keystore_location database manager configuration parameters
The keystore password (in obfuscated form) can be stashed to a file which is owned by the instance owner, this allows the DB2 instance to be started without manual intervention. If the password is not stashed, you cannot access an encrypted database until you provide the keystore password.
Database backup images are automatically encrypted if the encrlib and encropts database configuration parameters are set to a non-null value. The encrypted master key encrypts the backup DEK by default. For non-encrypted databases you can also use the gsk8capicmd command to add a seperate master key to the keystore and specify that label on the BACKUP DATABASE command.
The CREATE DATABASE command now includes an ENCRYPT option which includes
To encrypt a database
| Linux and UNIX 32-bit | $INSTHOME/sqllib/lib32/gskit |
| Linux and UNIX 64-bit | $INSTHOME/sqllib/lib64/gskit |
| Windows 32-bit | C:\Program Files (x86)\IBM\gsk8\lib | Windows 64-bit | 64-bit GSKit libraries C:\Program Files\IBM\gsk8\lib64, 32-bit GSKit libraries C:\Program Files (x86)\IBM\gsk8\lib |
The GSKCapiCmd User's Guide is at GSKCapiCmd User's Guide
gsk8capicmd -keydb -create -db ccardskeystore.p12 -pw Str0ngPassw0rd –strong -type pkcs12 –stash
The stash file is keystore_name.sth
gsk8capicmd –secretkey –add –db ccardskeystore.p12 –stashed –label mylabel.mydb.myinstance.myserver -file mysecretkeyfile
The master key is in the BINARY file 'mysecretkeyfile' which must be the size of the key used to encrypt the database e.g. 256 bits (32 bytes) for AES-256
This should be generated from a secure source E.g. /dev/random
db2 update dbm cfg using keystore_type pkcs12 keystore_location /home/db2/ccardskeystore.p12
Examples
First update environment to add gsk8capicmd/gsk8capicmd64 to PATH
E.g. on Linux
su - db2inst1
echo "AddRemoveString PATH /opt/ibm/db2/V10.5.0.5/gskit/bin a" >> ${INSTHOME?}/sqllib/userprofile
cat ${INSTHOME?}/sqllib/userprofile
AddRemoveString PATH /opt/ibm/db2/V10.5.0.5/gskit/bin a
Create keystore as instance owner
The gsk8capicmd_64 has good help: gsk8capicmd_64 -keydb -create -help -Command usage- -db Required -pw | -stashed Optional -type Optional-expire Optional -stash Optional -strong Optional -empty | -populate Optional -f Optional gsk8capicmd_64 -keydb -create -db /home/db2inst1/ccardskeystore.p12 -pw Str0ngPassw0rd -type pkcs12 -stash -strong ls -lrt /home/db2inst1 total 12 drwxrwxr-x 3 db2inst1 db2iadm1 4096 Mar 21 11:19 db2inst1 drwxrwsr-t 25 db2inst1 db2iadm1 4096 Mar 29 06:17 sqllib -rw------- 1 db2inst1 db2iadm1 0 Mar 29 06:17 ccardskeystore.p12 -rw------- 1 db2inst1 db2iadm1 129 Mar 29 06:17 ccardskeystore.sth
Configure instance witk keystore
db2 update dbm cfg using keystore_type pkcs12 keystore_location /home/db2inst1/ccardskeystore.p12 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. db2stop db2start db2 attach to db2inst1 db2 get dbm cfg | grep KEYSTORE Keystore type (KEYSTORE_TYPE) = PKCS12 Keystore location (KEYSTORE_LOCATION) = /home/db2inst1/ccardskeystore.p12
Create the database with the encryption option
db2 create db daveenc1 encrypt cipher AES KEY LENGTH 192 DB20000I The CREATE DATABASE command completed successfully. db2 get db cfg for DAVEENC1 | grep "Encrypted database" Encrypted database = YES
FYI List keystore contents, 1 key db2 system generated for instance db2inst1 database DAVEENC1 at 2015-03-29-06.23.50!!
gsk8capicmd_64 -cert -list all -db /home/db2inst1/ccardskeystore.p12 -stashed Certificates found * default, - personal, ! trusted, # secret key # DB2_SYSGEN_db2inst1_DAVEENC1_2015-03-29-06.23.50
FYI No expiry on keystore password:
gsk8capicmd_64 -keydb -expiry -db /home/db2inst1/ccardskeystore.p12 -pw Str0ngPassw0rd Password Expiry Time : 0NOTE: Backup encryption is automatically enabled via ENCRLIB/ENCROPTS db cfg parameters with a non-default key length of 192
db2 get db cfg for DAVEENC1 | grep ENCR Encryption Library for Backup (ENCRLIB) = libdb2encr.so Encryption Options for Backup (ENCROPTS) = CIPHER=AES:MODE=CBC:KEY LENGTH=192 The shared library is under sqllib/lib64
db2 drop database daveenc1 DB20000I The DROP DATABASE command completed successfully. db2 update dbm cfg using keystore_type NONE keystore_location NULL DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed rm /home/db2inst1/ccardskeystore.p12 /home/db2inst1/ccardskeystore.sth db2stop # Bounce only to demo definitely using new unstashed keystore below! db2start gsk8capicmd_64 -keydb -create -db /home/db2inst1/ccardskeystore.p12 -pw Str0ngPassw0rd -type pkcs12 -strong # No stash file is created! ls -lrt /home/db2inst1 total 8 drwxrwxr-x 3 db2inst1 db2iadm1 4096 Mar 21 11:19 db2inst1 drwxrwsr-t 25 db2inst1 db2iadm1 4096 Mar 29 06:17 sqllib -rw------- 1 db2inst1 db2iadm1 0 Mar 29 07:03 ccardskeystore.p12 db2stop db2 update dbm cfg using keystore_type pkcs12 keystore_location /home/db2inst1/ccardskeystore.p12 DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully. db2start OPEN KEYSTORE USING Str0ngPassw0rd 03/29/2015 07:07:55 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful. db2 create db daveenc1 encrypt DB20000I The CREATE DATABASE command completed successfully. db2 get db cfg for DAVEENC1 | grep ENCR Encryption Library for Backup (ENCRLIB) = libdb2encr.so Encryption Options for Backup (ENCROPTS) = CIPHER=AES:MODE=CBC:KEY LENGTH=256 NOTE: Backup encryption is automatically enabled via ENCRLIB/ENCROPTS db cfg parameters with a default key length of 256 Alternative instance startup using filename db2stop force Put password into /tmp/bbb1 db2start OPEN KEYSTORE PASSARG filename:/tmp/bbb1 03/29/2015 07:16:03 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful. db2 connect to daveenc1 Database Connection Information Database server = DB2/LINUXX8664 10.5.5 SQL authorization ID = DB2INST1 Local database alias = DAVEENC1 Alternative instance startup using file descriptor!! bash exec 5</tmp/bbb1 echo $$ 53088 lsof -p 53088 | egrep "COMMAND|bbb1" COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME bash 53088 db2inst1 5r REG 8,3 15 266413 /tmp/bbb1 03/29/2015 07:23:45 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful. db2 connect to daveenc1 Database Connection Information Database server = DB2/LINUXX8664 10.5.5 SQL authorization ID = DB2INST1 Local database alias = DAVEENC1 db2 "create table a (b int)" DB20000I The SQL command completed successfully.
Backup encryption
This is either enabled via:
These are set automatically if the database is created encrypted. Only a user with SECADM authorization can change the setting of the encrlib/encropts configuration parameters. encrlib must either be a full path or relative to the current working directory of the DB2 server. When the encrlib configuration parameter is set, you cannot specify any compression options with your backup operations, and the only valid encryption option that you can specify is EXCLUDE. db2 backup database daveenc1 Backup successful. The timestamp for this backup image is : 20150329073730 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).
NOTE: When database cfg parameters encrlib/encropts are only the encrypt exclude option can be used. db2 backup database daveenc1 encrypt exclude # library is not stored in the backup image Backup successful. The timestamp for this backup image is : 20150329074657 db2 backup database daveenc1 encrypt encrlib 'libdb2compr_encr.so' # Compress and encrypt 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". db2 connect to daveenc1 # Must connect to db to change encrlib/encropts Database Connection Information Database server = DB2/LINUXX8664 10.5.5 SQL authorization ID = DB2INST1 Local database alias = DAVEENC1 db2 update db cfg for daveenc1 using encrlib NULL encropts NULL DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully. db2 backup database daveenc1 encrypt encrlib 'libdb2compr_encr.so' Backup successful. The timestamp for this backup image is : 20150329075200 db2 backup database daveenc1 encrypt Backup successful. The timestamp for this backup image is : 20150329075312 # Backup with seperate master key Put password in file /tmp/ccc1 gsk8capicmd_64 -secretkey -add -db /home/db2inst1/ccardskeystore.p12 -file /tmp/ccc1 -label inst1.bkup -pw Str0ngPassw0rd db2 "backup database daveenc1 encrypt encrlib 'libdb2encr.so' encropts 'Cipher=AES:Mode=CBC:Key Length=256:Master Key Label=inst1.bkup'" Backup successful. The timestamp for this backup image is : 20150406072630 Restore database db2 terminate DB20000I The TERMINATE command completed successfully. db2 attach to db2inst1 Instance Attachment Information Instance server = DB2/LINUXX8664 10.5.5 Authorization ID = DB2INST1 Local instance alias = DB2INST1 db2 drop database daveenc1 DB20000I The DROP DATABASE command completed successfully. db2 "restore database daveenc1 taken at 20150406072630 encrypt" DB20000I The RESTORE DATABASE command completed successfully. db2 connect to daveenc1 Database Connection Information Database server = DB2/LINUXX8664 10.5.5 SQL authorization ID = DB2INST1 Local database alias = DAVEENC1Restore to another instance on another host
# Set instance to use keystore gsk8capicmd_64 -keydb -create -db /home/db2inst1/ccardskeystore.p12 -pw Str0ngPassw0rd -type pkcs12 -strong # No stash file db2 attach to db2inst1 db2 update dbm cfg using keystore_type pkcs12 keystore_location /home/db2inst1/ccardskeystore.p12 db2stop force db2start open keystore using Str0ngPassw0rd db2 attach to db2inst1 # Create encrypted database db2 create db daveenc1 encrypt cipher AES KEY LENGTH 192 # Generate new master key of length 192 for backup database dd if=/dev/random of=/tmp/ccc1 bs=1 count=24 gsk8capicmd_64 -secretkey -add -db /home/db2inst1/ccardskeystore.p12 -file /tmp/ccc1 -label inst1.bkup -pw Str0ngPassw0rd # Backup using new master key with Key Length=192 db2 connect to daveenc1 db2 update db cfg for daveenc1 using encrlib NULL encropts NULL db2 "backup database daveenc1 to /tmp/a encrypt encrlib 'libdb2encr.so' encropts 'Cipher=AES:Mode=CBC:Key Length=192:Master Key Label=inst1.bkup'" # Export key from primary keystore gsk8capicmd_64 -cert -export -db /home/db2inst1/ccardskeystore.p12 -pw Str0ngPassw0rd -label inst1.bkup -target /tmp/t.p12 -target_type pkcs12 -target_pw Tran5ferPassw0rd ### On inst2 # Import Database Backup Key gsk8capicmd_64 -keydb -create -db ~/ccardskeystore.p12 -pw Str0ngPassw0rd -type pkcs12 -strong scp 192.168.124.133:/tmp/t.p12 /tmp/t.p12 gsk8capicmd_64 -cert -import -db /tmp/t.p12 -pw Tran5ferPassw0rd -label inst1.bkup -target ~/ccardskeystore.p12 -target_type pkcs12 -target_pw Str0ngPassw0rd # Copy backup scp -r 192.168.124.133:/tmp/a /tmp # Restore backup db2 attach to db2inst2 db2 update dbm cfg using INTRA_PARALLEL YES db2 update dbm cfg using keystore_type pkcs12 keystore_location /home/db2inst2/ccardskeystore.p12 db2stop force db2start open keystore using Str0ngPassw0rd db2 "restore database daveenc1 from /tmp/a taken at 20150519052908 encropts 'show master key details'" ls ~/sqllib/db2dump/DAVEENC1.*.masterKeyDetails /home/db2inst2/sqllib/db2dump/DAVEENC1.0.db2inst1.DBPART000.20150519052908.masterKeyDetails more /home/db2inst2/sqllib/db2dump/DAVEENC1.0.db2inst1.DBPART000.20150519052908.masterKeyDetails KeyStore Type: PKCS12 KeyStore Location: /home/db2inst1/ccardskeystore.p12 KeyStore Host Name: localhost.localdomain KeyStore IP Address: ::1 KeyStore IP Address Type: IPV6 Encryption Algorithm: AES Encryption Algorithm Mode: CBC Encryption Key Length: 192 Master Key Label: inst1.bkup db2 "restore database daveenc1 from /tmp/a taken at 20150519052908 on /home/db2inst2 dbpath on /home/db2inst2 encrypt cipher AES KEY LENGTH 192 MASTER KEY LABEL inst1.bkup" SQL2523W Warning! Restoring to an existing database that is different from the database on the backup image, but have matching names. The target database will be overwritten by the backup version. The Roll-forward recovery logs associated with the target database will be deleted. Do you want to continue ? (y/n) DB20000I The RESTORE DATABASE command completed successfully.Setup HADR with Encrypted databaseInstance inst1 192.168.124.133 database daveenc1 HADR port 60000 Instance inst2 192.168.124.143 database daveenc1 HADR port 60000Setup encrypted database with keystore on first instance db2inst1
# Set instance to use keystore gsk8capicmd_64 -keydb -create -db /home/db2inst1/ccardskeystore.p12 -pw Str0ngPassw0rd -type pkcs12 -strong # No stash file db2 attach to db2inst1 db2 update dbm cfg using INTRA_PARALLEL YES db2 update dbm cfg using keystore_type pkcs12 keystore_location /home/db2inst1/ccardskeystore.p12 db2stop force db2start open keystore using Str0ngPassw0rd db2 attach to db2inst1 # Create encrypted database dd if=/dev/random of=/tmp/ccc1 bs=1 count=24 gsk8capicmd_64 -secretkey -add -db /home/db2inst1/ccardskeystore.p12 -file /tmp/ccc1 -label inst1.db -pw Str0ngPassw0rd db2 create db daveenc1 encrypt cipher AES KEY LENGTH 192 MASTER KEY LABEL inst1.db # Generate new backup master key of length 192 dd if=/dev/random of=/tmp/ccc1 bs=1 count=24 gsk8capicmd_64 -secretkey -add -db /home/db2inst1/ccardskeystore.p12 -file /tmp/ccc1 -label inst1.bkup -pw Str0ngPassw0rd # Backup using new master key with Key Length=192 db2 connect to daveenc1 db2 update db cfg for daveenc1 using encrlib NULL encropts NULL db2 update db cfg for daveenc1 using LOGARCHMETH1 LOGRETAIN mkdir /tmp/a db2 "backup database daveenc1 to /tmp/a encrypt encrlib 'libdb2encr.so' encropts 'Cipher=AES:Mode=CBC:Key Length=192:Master Key Label=inst1.bkup'" # Timestamp 20150519064234 # Poke hole in firewalls for port 60000 iptables --line -vnL iptables -I INPUT 5 -i eth0 -p tcp --dport 60000 -m state --state NEW,ESTABLISHED -j ACCEPT # Set parameters on primary database db2 update db cfg for daveenc1 using HADR_LOCAL_HOST 192.168.124.133 db2 update db cfg for daveenc1 using HADR_LOCAL_SVC 60000 db2 update db cfg for daveenc1 using HADR_REMOTE_HOST 192.168.124.143 db2 update db cfg for daveenc1 using HADR_REMOTE_SVC 60000 db2 update db cfg for daveenc1 using HADR_REMOTE_INST db2inst2 db2 update db cfg for daveenc1 using LOGINDEXBUILD ON # Export key from primary keystore gsk8capicmd_64 -cert -export -db /home/db2inst1/ccardskeystore.p12 -pw Str0ngPassw0rd -label inst1.db -target /tmp/td.p12 -target_type pkcs12 -target_pw Tran5ferPassw0rd gsk8capicmd_64 -cert -export -db /home/db2inst1/ccardskeystore.p12 -pw Str0ngPassw0rd -label inst1.bkup -target /tmp/tb.p12 -target_type pkcs12 -target_pw Tran5ferPassw0rd ### On inst2 # Poke hole in firewalls for port 60000 iptables --line -vnL iptables -I INPUT 5 -i eth0 -p tcp --dport 60000 -m state --state NEW,ESTABLISHED -j ACCEPT # Create and use keystore db2 attach to db2inst2 db2 update dbm cfg using INTRA_PARALLEL YES db2 update dbm cfg using keystore_type pkcs12 keystore_location /home/db2inst2/ccardskeystore.p12 db2stop force db2start open keystore using Str0ngPassw0rd # Imports keys scp 192.168.124.133:/tmp/t.p12 /tmp/t.p12 gsk8capicmd_64 -cert -import -db /tmp/t.p12 -pw Tran5ferPassw0rd -label inst1.bkup -target ~/ccardskeystore.p12 -target_type pkcs12 -target_pw Str0ngPassw0rd # Create and deactivate db gsk8capicmd_64 -keydb -create -db ~/ccardskeystore.p12 -pw Str0ngPassw0rd -type pkcs12 -strong scp 192.168.124.133:/tmp/td.p12 /tmp/td.p12 gsk8capicmd_64 -cert -import -db /tmp/td.p12 -pw Tran5ferPassw0rd -label inst1.db -target ~/ccardskeystore.p12 -target_type pkcs12 -target_pw Str0ngPassw0rd scp 192.168.124.133:/tmp/tb.p12 /tmp/tb.p12 gsk8capicmd_64 -cert -import -db /tmp/t.p12 -pw Tran5ferPassw0rd -label inst1.bkup -target ~/ccardskeystore.p12 -target_type pkcs12 -target_pw Str0ngPassw0rd db2 create db daveenc1 encrypt cipher AES KEY LENGTH 192 MASTER KEY LABEL inst1.db db2 connect to daveenc1 vim /tmp/c SELECT VARCHAR(STORAGE_GROUP_NAME, 30) AS STOGROUP, VARCHAR(DB_STORAGE_PATH, 40) AS STORAGE_PATH FROM TABLE(ADMIN_GET_STORAGE_PATHS('',-1)) AS T; db2 -tvf /tmp/c db2 list db directory | grep Local # Both /home/db2inst2 db2 terminate db2 attach to db2inst2 db2 update db cfg for daveenc1 using LOGARCHMETH1 LOGRETAIN db2 update db cfg for daveenc1 using LOGINDEXBUILD ON db2 deactivate database daveenc1 # Copy backup scp -r 192.168.124.133:/tmp/a /tmp # Restore backup db2 attach to db2inst2 db2 "restore database daveenc1 from /tmp/a taken at 20150519070837 encropts 'show master key details'" ls -1rt ~/sqllib/db2dump/DAVEENC1.*.masterKeyDetails /home/db2inst2/sqllib/db2dump/DAVEENC1.0.db2inst1.DBPART000.20150519064234.masterKeyDetails more /home/db2inst2/sqllib/db2dump/DAVEENC1.0.db2inst1.DBPART000.20150519064234.masterKeyDetails KeyStore Type: PKCS12 KeyStore Location: /home/db2inst1/ccardskeystore.p12 KeyStore Host Name: localhost.localdomain KeyStore IP Address: ::1 KeyStore IP Address Type: IPV6 Encryption Algorithm: AES Encryption Algorithm Mode: CBC Encryption Key Length: 192 Master Key Label: inst1.bkup db2 "restore database daveenc1 from /tmp/a taken at 20150519070837 on /home/db2inst2 dbpath on /home/db2inst2" SQL2523W Warning! Restoring to an existing database that is different from the database on the backup image, but have matching names. The target database will be overwritten by the backup version. The Roll-forward recovery logs associated with the target database will be deleted. Do you want to continue ? (y/n) DB20000I The RESTORE DATABASE command completed successfully. db2 rollforward db daveenc1 query status #Setup HADR parameters on standby and start hadr on standby db2 update db cfg for daveenc1 using HADR_LOCAL_HOST 192.168.124.143 db2 update db cfg for daveenc1 using HADR_LOCAL_SVC 60000 db2 update db cfg for daveenc1 using HADR_REMOTE_HOST 192.168.124.133 db2 update db cfg for daveenc1 using HADR_REMOTE_SVC 60000 db2 update db cfg for daveenc1 using HADR_REMOTE_INST db2inst1 db2 start hadr on database daveenc1 as standby ### On db2inst1 db2 start hadr on database daveenc1 as primary # Check HADR state from primary [db2inst1@localhost ~]$ db2pd -db daveenc1 -hadr Database Member 0 -- Database DAVEENC1 -- Active -- Up 0 days 00:02:25 -- Date 2015-05-19-07.27.00.091347 HADR_ROLE = PRIMARY REPLAY_TYPE = PHYSICAL HADR_SYNCMODE = NEARSYNC STANDBY_ID = 1 LOG_STREAM_ID = 0 HADR_STATE = PEER HADR_FLAGS = PRIMARY_MEMBER_HOST = 192.168.124.133 PRIMARY_INSTANCE = db2inst1 PRIMARY_MEMBER = 0 STANDBY_MEMBER_HOST = 192.168.124.143 STANDBY_INSTANCE = db2inst2 STANDBY_MEMBER = 0 HADR_CONNECT_STATUS = CONNECTED HADR_CONNECT_STATUS_TIME = 05/19/2015 07:26:14.821049 (1432045574) HEARTBEAT_INTERVAL(seconds) = 30 HEARTBEAT_MISSED = 0 HEARTBEAT_EXPECTED = 1 HADR_TIMEOUT(seconds) = 120 TIME_SINCE_LAST_RECV(seconds) = 16 PEER_WAIT_LIMIT(seconds) = 0 LOG_HADR_WAIT_CUR(seconds) = 0.000 LOG_HADR_WAIT_RECENT_AVG(seconds) = 0.000000 LOG_HADR_WAIT_ACCUMULATED(seconds) = 0.000 LOG_HADR_WAIT_COUNT = 0 SOCK_SEND_BUF_REQUESTED,ACTUAL(bytes) = 0, 19800 SOCK_RECV_BUF_REQUESTED,ACTUAL(bytes) = 0, 87380 PRIMARY_LOG_FILE,PAGE,POS = S0000000.LOG, 0, 44933825 STANDBY_LOG_FILE,PAGE,POS = S0000000.LOG, 0, 44933825 HADR_LOG_GAP(bytes) = 0 STANDBY_REPLAY_LOG_FILE,PAGE,POS = S0000000.LOG, 0, 44933825 STANDBY_RECV_REPLAY_GAP(bytes) = 3979691 PRIMARY_LOG_TIME = 05/19/2015 07:08:41.000000 (1432044521) STANDBY_LOG_TIME = 05/19/2015 07:08:41.000000 (1432044521) STANDBY_REPLAY_LOG_TIME = 05/19/2015 07:08:41.000000 (1432044521) STANDBY_RECV_BUF_SIZE(pages) = 4298 STANDBY_RECV_BUF_PERCENT = 0 STANDBY_SPOOL_LIMIT(pages) = 25600 STANDBY_SPOOL_PERCENT = 0 STANDBY_ERROR_TIME = NULL PEER_WINDOW(seconds) = 0 READS_ON_STANDBY_ENABLED = N ### On db2inst2 # Check HADR state from standby [db2inst2@localhost ~]$ db2pd -db daveenc1 -hadr Database Member 0 -- Database DAVEENC1 -- Standby -- Up 0 days 00:10:47 -- Date 2015-05-19-07.28.36.424548 HADR_ROLE = STANDBY REPLAY_TYPE = PHYSICAL HADR_SYNCMODE = NEARSYNC STANDBY_ID = 0 LOG_STREAM_ID = 0 HADR_STATE = PEER HADR_FLAGS = PRIMARY_MEMBER_HOST = 192.168.124.133 PRIMARY_INSTANCE = db2inst1 PRIMARY_MEMBER = 0 STANDBY_MEMBER_HOST = 192.168.124.143 STANDBY_INSTANCE = db2inst2 STANDBY_MEMBER = 0 HADR_CONNECT_STATUS = CONNECTED HADR_CONNECT_STATUS_TIME = 05/19/2015 07:26:14.810373 (1432045574) HEARTBEAT_INTERVAL(seconds) = 30 HEARTBEAT_MISSED = 0 HEARTBEAT_EXPECTED = 4 HADR_TIMEOUT(seconds) = 120 TIME_SINCE_LAST_RECV(seconds) = 22 PEER_WAIT_LIMIT(seconds) = 0 LOG_HADR_WAIT_CUR(seconds) = 0.000 LOG_HADR_WAIT_RECENT_AVG(seconds) = 0.000000 LOG_HADR_WAIT_ACCUMULATED(seconds) = 0.000 LOG_HADR_WAIT_COUNT = 0 SOCK_SEND_BUF_REQUESTED,ACTUAL(bytes) = 0, 19800 SOCK_RECV_BUF_REQUESTED,ACTUAL(bytes) = 0, 87380 PRIMARY_LOG_FILE,PAGE,POS = S0000000.LOG, 0, 44933825 STANDBY_LOG_FILE,PAGE,POS = S0000000.LOG, 0, 44933825 HADR_LOG_GAP(bytes) = 0 STANDBY_REPLAY_LOG_FILE,PAGE,POS = S0000000.LOG, 0, 44933825 STANDBY_RECV_REPLAY_GAP(bytes) = 3979691 PRIMARY_LOG_TIME = 05/19/2015 07:08:41.000000 (1432044521) STANDBY_LOG_TIME = 05/19/2015 07:08:41.000000 (1432044521) STANDBY_REPLAY_LOG_TIME = 05/19/2015 07:08:41.000000 (1432044521) STANDBY_RECV_BUF_SIZE(pages) = 4298 STANDBY_RECV_BUF_PERCENT = 0 STANDBY_SPOOL_LIMIT(pages) = 25600 STANDBY_SPOOL_PERCENT = 0 STANDBY_ERROR_TIME = NULL PEER_WINDOW(seconds) = 0 READS_ON_STANDBY_ENABLED = N
Column-organized tables are now supported on
Only a single processor group is supported. BLU Acceleration for Windows has the same recommended minimums as all other platforms, specifically 8 cores, and 64 GB RAM. In addition, a maximum of four sockets is supported. If you are running more than one socket, set performance variable DB2_RESOURCE_POLICY to AUTOMATIC. Windows Installer 3.0 is required. IBM Data Server Provider for .NET client applications and CLR server-side procedures require .NET 2.0 or later framework runtime. In an x64 environment, 32-bit IBM data server provider for .NET applications runs in the WOW64 emulation mode. If you plan to use LDAP (Lightweight Directory Access Protocol), use either a Microsoft LDAP client or the IBM Tivoli® Directory Server v6 client (also known as the IBM LDAP client which is included with DB2 database products). Before installation of the Microsoft Active Directory, you must extend your directory schema using the db2schex utility, which can be found on the installation media under the db2\Windows\utilities directory. The Microsoft LDAP client is included with Windows operating systems. The computer name of the Windows workstation on which the DB2 installation files are located, must be 15 characters or less in length. Note: DB2 database products support the hardware-enforced Data Execution Prevention (DEP) feature that is built into some Windows operating systems.
DB2 BLU and HADR are now supported on Windows
IBM Tivoli System Automation for Multiplatforms Version 4.1 is now supported with HADR
Geographically dispersed DB2 pureScale cluster are now supported on any x64 Intel compatible rack mounted server
which supports these Ethernet RoCE adapters:
Cluster caching facility (CF) self-tuning memory can now be explicitly enabled to
help optimize performance and avoid out of memory conditions.
CF self-tuning memory is enabled by setting registry variable DB2_DATABASE_CF_MEMORY to AUTO.
When enabled, CF self-tuning memory avoids out of memory conditions by tuning database memory parameters
cf_db_mem_sz, cf_gbp_sz, cf_lock_sz, and cf_sca_sz.
CF memory is shared by these parameters. The values of these four database memory parameters are interdependent.
In a multiple database environment, CF memory is configured automatically based on workload and available memory.
Databases that are already active automatically give up CF memory for newly activated databases until a workload-based
distribution of CF memory is reached.
When a database is added, there is no downtime to reconfigure CF memory allocation.
See Use of the db2dsdriver.cfg configuration file by embedded SQL applications
This enhancement can be particularly beneficial when scalar UDFs that
meet the following criteria are included in the predicate of a query.
One or more network interface cards (NICs) can now be specified through which a DB2 member can acquire exclusive access to a specific port.
NICs can be specified by IP address or by the host name that maps to that address in the nicbinding.cfg file
When a DB2 member starts, it attempts to acquire exclusive access to a specific port on all NICs on the host, any other DB2 instance on the same host must be configured to use a different port to service remote clients or drivers.
Starting in Version 10.5 Fix Pack 5, you can specify one or more NICs through which a DB2 member can acquire exclusive access to a specific port, thereby avoiding contention for the same port number.
This can be done for a subset of members
You can specify a particular NIC by its IP address or by the host name that maps to that address in the nicbinding.cfg file, which you can find in the ~/sqllib/cfg directory (or the ~/sqllib_shared/cfg directory in DB2 pureScale® environments).
To specify multiple NICs, use a multihomed host name that maps to the IP addresses for those NICs.
If the specified port on a specified NIC is already owned by another DB2 instance, or the specified host name or IP address does not represent any valid NIC on the host, the pairing is considered invalid, and SQL5043N is returned when the DB2 member starts.
Add binding information to the nicbinding.cfg file. Each line in the configuration file represents a binding between a single DB2 member and its NIC.
Here all 3 members are bound to specific NICs:
0 9.1.2.1
1 9.1.2.2
2 host1.newyork.mycompany.com
Here member 2 is not bound to a NIC so will bind to all NICs on the host:
0 host1
1 host2.chicago.mycompany.com
3 9.1.2.4
Multi-homed hostnames can be used to bind to mulitple NICs:
0 host67m1 # Multi-homed address -> 3 NICs
3 host68m1 # Multi-homed address -> 2 NICs
6 9.2.5.6 # IP address -> 1 NIC
MON_GET_INSTANCE table function can be used to get NIC binding information:
db2
"select MEMBER,
substr(NETWORK_INTERFACE_BOUND,1,16)
as NETWORK_INTERFACE_ID
from table(MON_GET_INSTANCE(-2))"
MEMBER NETWORK_INTERFACE_ID
------ --------------------
1 host67e1
0 9.2.3.3
The IBM data server driver configuration file (db2dsdriver.cfg) can be configured with information from LDAP catalog entries using the -refreshldap option
See db2cli - DB2 interactive CLI command
LDAP catalog entries can be used to register data source names (DSNs) in the Microsoft ODBC Driver Manager
with the -includeldap option.
See db2cli - DB2 interactive CLI command
IBM Tivoli System Automation for Multiplatforms (SA MP) Version 4.1 is now supported on supported AIX environments
User IDs and group IDs can now be up to 128 bytes on Linux and UNIX operating systems, this is increased from 8 characters in previous versions
Any operating system level naming restrictions still apply