Paul Bastide

Getting Explain to work with IBM Db2 on Cloud

Written by

Paul

in

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

More posts