Connecting to db2

Data Science & AI Workbench enables you to connect to an IBM Db2 relational database management system (RDMS), to access the data stored in it without leaving the platform.

Before you can do so, you’ll need to build the ibm_db package, which contains the driver required to connect to Db2:

1. Run the following command to generate a boilerplate or skeleton recipe, which you can then edit:

   conda skeleton pypi ibm_db
   

2. Locate the meta.yaml file within the ibm_db directory that’s created.

3. To build the package for a specific Python version, edit the meta.yaml file and specify the version. For example, to build the package for Python 3.6, it would look like this:

   .. code-block:: yaml
   

   requirements:

   • host:
     • pip
     • python=3.6
   • run:
     • python=3.6

4. Run the following command to build the package:

   conda build ibm_db
   

5. When complete, the output of the process will display the path to the package. Now you can upload it to your preferred channel, to make it available for platform users. For more information, see uploading a package to a channel.

To install the package, use the following command:

conda install ibm_db

Any packages you install from the command line are available during the current session only. If you want them to persist, add them to the project’s anaconda-project.yml file. For more information, see Project configurations.

Then you can use code such as this to connect to the Db2 database from within a notebook session:

import ibm_db
import json

"""
Get credentials from Kubernetes. The credentials were set up as a dictionary. For example:
{
    "username": "USERNAME",
    "password": "PASSWORD"
}
"""
credentials = None
with open('/var/run/secrets/user_credentials/db2_credentials') as f:
    credentials = json.load(f)

# Verify the credentials were pulled correctly
if credentials:
    username = credentials.get('username')
    password = credentials.get('password')
    hostname = credentials.get('hostname')

    # Setup the connection to the database
    connection = ibm_db.connect(
        f"DATABASE=testing;HOSTNAME={hostname};PORT=50000;PROTOCOL=TCPIP;UID={username};PWD={password};",
        "",
        ""
    )

    # Statement you want to execute
    query = ibm_db.exec_immediate(connection, "select * from testing.employee")

    # Loop through the results and print out the query
    result = ibm_db.fetch_both(query)
    while result:
        print(result)
        # Fetch the next item from the query
        result = ibm_db.fetch_both(query)

    # Close the connection
    ibm_db.close(connection)

See Secrets for information about adding credentials to the platform, to make them available in your projects. Any secrets you add will be available across all sessions and deployments associated with your user account.