I recently came across a problem where I needed to connect to a Db2 server with SSL connection from Jupyter Lab. Our environment is one on Kubernetes cluster on Cloud. Although one can easily find how to connect to Db2 from Python, I was not able to find a clean way to do what I wanted to do.
In this post, I will share how to connect to Db2 SSL from your local anaconda and if you are on a container based JupyterLab environment.
First thing that I noticed was that ibm_db way to connect to Db2 is not the best and jaydebeapi seems to be the future.
Case 1> If you are on a Jupyter Lab env on container based env in Cloud, you can use the below method
!pip install ipython-sql
!pip install jaydebeapi
import jaydebeapi
import pandas as pd
# truststore password on you local machine:
truststore_pwd = ''
# remote db user and password :
usr = 'yourUserName'
pwr = 'yourPassword'
conn=jaydebeapi.connect('com.ibm.db2.jcc.DB2Driver',
'jdbc:db2://db2.cloud.ibm.com:50001/BLUDB:sslConnection=true;sslTrustStoreLocation=/home/myName/certs/TrustStore;sslTrustStorePassword='+truststore_pwd+';',
[usr,pwr],
['/home/shared/jars/db2jcc4.jar'] ) #location of JAR file
q = "select 'connected' from sysibm.sysdummy1"
q_df = pd.read_sql( q, conn )
q_df.head()
conn.close()You should upload the db2 jar file and your keystore that you have on your PC or MAC to a directory on your Jupyter Lab on cloud and use them like above.
Case 2> If you want to connect it from Anaconda or Jupyter Lab on your local PC. Overall it’s the same , we just need to replace JAR and keystore to our local directory structure.
!pip install ipython-sql
!pip install jaydebeapi
import jaydebeapi
import pandas as pd
# truststore password on you local machine:
truststore_pwd = ''
# remote db user and password :
usr = 'yourUserName'
pwr = 'yourPassword'
conn=jaydebeapi.connect('com.ibm.db2.jcc.DB2Driver',
'jdbc:db2://db2.cloud.ibm.com:50001/BLUDB:sslConnection=true;sslTrustStoreLocation=C:/certs/TrustStore;sslTrustStorePassword='+truststore_pwd+';',
[usr,pwr],
['C:\Program Files\IBM\DS4.1.2\dsdev\jar\db2jcc4.jar'] ) #location of JAR file
q = "select 'connected' from sysibm.sysdummy1"
q_df = pd.read_sql( q, conn )
q_df.head()
conn.close()