My team has been running more workloads on IBM Cloud, more specifically with IBM Db2. Our daily tools are slightly different to work with in the cloud, less administrative access and tools we can access on the host – db2batch, db2advis, db2expln and other native tools.
That’s when I ran across some great references that lead me in a direction that works for my team.
- Create a User (with Password)
- Catalog the Remote Database
- Run db2expln
Login to the IBM Cloud console
Click Open Console
Expand Settings
Click Manage Users
Click Add
Click Add User
Enter the relevant details for the user
Click Create
I use my db2 docker container
Setup the SSL
mkdir -p /database/config/db2inst1/SSL_CLIENT
chmod -R 755 /database/config/db2inst1/SSL_CLIENT
/database/config/db2inst1/sqllib/gskit/bin/gsk8capicmd_64 -keydb \
-create -db "/database/config/db2inst1/SSL_CLIENT/ibmca.kdb" \
-pw "passw0rd" -stash
/database/config/db2inst1/sqllib/gskit/bin/gsk8capicmd_64 -cert \
-add -db "/database/config/db2inst1/SSL_CLIENT/ibmca.kdb" \
-pw "passw0rd" -file sqllib/cfg/DigiCertGlobalRootCA.arm
chmod 775 /database/config/db2inst1/SSL_CLIENT/ibmca.kdb
chmod 775 /database/config/db2inst1/SSL_CLIENT/ibmca.sth
Configure the database
db2 update dbm cfg using SSL_CLNT_KEYDB \
/database/config/db2inst1/SSL_CLIENT/ibmca.kdb
db2 update dbm cfg using SSL_CLNT_STASH
/database/config/db2inst1/SSL_CLIENT/ibmca.sth
db2 update dbm cfg using keystore_location
/database/config/db2inst1/SSL_CLIENT/ibmca.kdb
Restart the database
db2stop
db2start
Catalog the database
db2 catalog tcpip node cdtdb1 remote \
dashdb-txn-flex-yp-xxxx-xxxx.services.dal.bluemix.net server 50001 security ssl
db2 catalog db bludb as fhirblu4 at node cdtdb1
db2 connect to fhirblu4 user testpaul using ^PASSWORD^
If you have a problem connecting, log out of db2inst1 and log back in. It’ll activate the db2profile again.
Run db2expln
db2expln -d fhirblu4 -u testpaul "^PASSWORD^" -graph -f 1.sql \
-terminator ';' -o 1.out
Optimizer Plan:
Rows
Operator
(ID)
Cost
10
RETURN
( 1)
412211
|
10
TBSCAN
( 2)
412211
|
10
SORT
( 3)
412211
|
77909.3
HSJOIN
( 4)
412164
/------------------------/ \-----------------------\
88591.5 311638
TBSCAN TBSCAN
( 5) ( 8)
410438 1594.97
+---------------------------++--------------------------------+ |
354367 0.00309393 1 311638
Table: IXSCAN IXSCAN Table:
FHIRDATA2 ( 6) ( 7) FHIRDATA2
OBSERVATION_RESOURCES 7.52927 7.57425 OBSERVATION_LOGICAL_RESOURCES
| |
2.18146e+06 1.24649e+06
Index: Index:
FHIRDATA2 FHIRDATA2
IDX_OBSERVATION_TOKEN_VALUES_RPS IDX_OBSERVATION_STR_VALUES_RPS
Relevant References https://www.ibm.com/cloud/blog/how-to-use-an-api-key-or-access-token-to-connect-to-ibm-db2-on-cloud https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.admin.sec.doc/doc/t0053518.html https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.sec.doc/doc/c0070395.html https://developer.ibm.com/recipes/tutorials/ssl-how-to-configure-it-on-db2/ https://www.ibm.com/support/producthub/db2/docs/content/SSEPGG_11.5.0/com.ibm.db2.luw.admin.sec.doc/doc/t0012036.html https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.security.doc/doc/iam.html https://developer.ibm.com/recipes/tutorials/ssl-how-to-configure-it-on-db2/#r_step8
Addendum
These are the containers settings for SSL:
db2inst1@4dda34a66a99 ~]$ db2 get dbm cfg | grep -i ssl
SSL server keydb file (SSL_SVR_KEYDB) = /database/config/db2inst1/SSL_CLIENT/ibmca.kdb
SSL server stash file (SSL_SVR_STASH) = /database/config/db2inst1/SSL_CLIENT/ibmca.sth
SSL server certificate label (SSL_SVR_LABEL) =
SSL service name (SSL_SVCENAME) =
SSL cipher specs (SSL_CIPHERSPECS) =
SSL versions (SSL_VERSIONS) =
SSL client keydb file (SSL_CLNT_KEYDB) = /database/config/db2inst1/SSL_CLIENT/ibmca.kdb
SSL client stash file (SSL_CLNT_STASH) = /database/config/db2inst1/SSL_CLIENT/ibmca.sth
Keystore location (KEYSTORE_LOCATION) = /database/config/db2inst1/SSL_CLIENT/ibmca.kdb
Db2 Top with remote db
db2top -d fhirpdm -n pdmperf -u bluadmin -p password-removed
Run with setup.sql
db2expln -d fhirdb -setup setup.sql -g -z \; -f uniq.sql -o plan.txt