Connect_to_Db2_database_on_Cloud_using_Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center>\n",
    "    <img src=\"https://s3-api.us-geo.objectstorage.softlayer.net/cf-courses-data/CognitiveClass/Logos/organization_logo/organization_logo.png\" width=\"300\" alt=\"cognitiveclass.ai logo\"  />\n",
    "</center>\n",
    "\n",
    "# Connect to Db2 database on Cloud using Python\n",
    "\n",
    "Estimated time needed: **15** minutes\n",
    "\n",
    "## Objectives\n",
    "\n",
    "After completing this lab you will be able to:\n",
    "\n",
    "*   Import the ibm_db Python library\n",
    "*   Enter the database connection credentials\n",
    "*   Create the database connection\n",
    "*   Close the database connection\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note:** Please follow the instructions given in the first Lab of this course to Create a database service instance of Db2 on Cloud and retrieve your database Service Credentials.\n",
    "\n",
    "## Import the `ibm_db` Python library\n",
    "\n",
    "The `ibm_db` [API ](https://pypi.python.org/pypi/ibm_db/?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01) provides a variety of useful Python functions for accessing and manipulating data in an IBM® data server database, including functions for connecting to a database, preparing and issuing SQL statements, fetching rows from result sets, calling stored procedures, committing and rolling back transactions, handling errors, and retrieving metadata.\n",
    "\n",
    "We first import the ibm_db library into our Python Application\n",
    "\n",
    "Execute the following cell by clicking within it and then\n",
    "press `Shift` and `Enter` keys simultaneously\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting ibm_db==3.1.0\n",
      "  Using cached ibm_db-3.1.0-cp37-cp37m-linux_x86_64.whl\n",
      "Collecting ibm_db_sa==0.3.7\n",
      "  Downloading ibm_db_sa-0.3.7.tar.gz (30 kB)\n",
      "  Preparing metadata (setup.py) ... \u001b[?25ldone\n",
      "\u001b[?25hCollecting sqlalchemy>=0.7.3\n",
      "  Downloading SQLAlchemy-1.4.27-cp37-cp37m-manylinux_2_5_x86_64.manylinux1_x86_64.manylinux_2_17_x86_64.manylinux2014_x86_64.whl (1.6 MB)\n",
      "     |████████████████████████████████| 1.6 MB 12.5 MB/s            \n",
      "\u001b[?25hCollecting importlib-metadata\n",
      "  Downloading importlib_metadata-4.8.2-py3-none-any.whl (17 kB)\n",
      "Collecting greenlet!=0.4.17\n",
      "  Downloading greenlet-1.1.2-cp37-cp37m-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (150 kB)\n",
      "     |████████████████████████████████| 150 kB 95.1 MB/s            \n",
      "\u001b[?25hCollecting typing-extensions>=3.6.4\n",
      "  Downloading typing_extensions-4.0.1-py3-none-any.whl (22 kB)\n",
      "Collecting zipp>=0.5\n",
      "  Downloading zipp-3.6.0-py3-none-any.whl (5.3 kB)\n",
      "Building wheels for collected packages: ibm-db-sa\n",
      "  Building wheel for ibm-db-sa (setup.py) ... \u001b[?25ldone\n",
      "\u001b[?25h  Created wheel for ibm-db-sa: filename=ibm_db_sa-0.3.7-py3-none-any.whl size=29318 sha256=f824f0b2eb4abe93560a1cc2f7aa95df64f4ed45631e7759b8652347d0b3ba12\n",
      "  Stored in directory: /home/jupyterlab/.cache/pip/wheels/4a/e9/e7/0ee334a6cb2f09ec45978e05837b66f59494b482ef38c7ae33\n",
      "Successfully built ibm-db-sa\n",
      "Installing collected packages: zipp, typing-extensions, importlib-metadata, greenlet, sqlalchemy, ibm-db, ibm-db-sa\n",
      "  Attempting uninstall: zipp\n",
      "    Found existing installation: zipp 3.6.0\n",
      "    Uninstalling zipp-3.6.0:\n",
      "      Successfully uninstalled zipp-3.6.0\n",
      "  Attempting uninstall: typing-extensions\n",
      "    Found existing installation: typing-extensions 4.0.1\n",
      "    Uninstalling typing-extensions-4.0.1:\n",
      "      Successfully uninstalled typing-extensions-4.0.1\n",
      "  Attempting uninstall: importlib-metadata\n",
      "    Found existing installation: importlib-metadata 4.8.2\n",
      "    Uninstalling importlib-metadata-4.8.2:\n",
      "      Successfully uninstalled importlib-metadata-4.8.2\n",
      "  Attempting uninstall: sqlalchemy\n",
      "    Found existing installation: SQLAlchemy 1.3.24\n",
      "    Uninstalling SQLAlchemy-1.3.24:\n",
      "      Successfully uninstalled SQLAlchemy-1.3.24\n",
      "  Attempting uninstall: ibm-db\n",
      "    Found existing installation: ibm-db 3.1.0\n",
      "    Uninstalling ibm-db-3.1.0:\n",
      "      Successfully uninstalled ibm-db-3.1.0\n",
      "  Attempting uninstall: ibm-db-sa\n",
      "    Found existing installation: ibm-db-sa 0.3.3\n",
      "    Uninstalling ibm-db-sa-0.3.3:\n",
      "      Successfully uninstalled ibm-db-sa-0.3.3\n",
      "Successfully installed greenlet-1.1.2 ibm-db-3.1.0 ibm-db-sa-0.3.7 importlib-metadata-4.8.2 sqlalchemy-1.4.27 typing-extensions-4.0.1 zipp-3.6.0\n"
     ]
    }
   ],
   "source": [
    "!pip install --force-reinstall ibm_db==3.1.0 ibm_db_sa==0.3.7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import ibm_db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When the command above completes, the `ibm_db` library is loaded in your notebook.\n",
    "\n",
    "## Identify the database connection credentials\n",
    "\n",
    "Connecting to dashDB or DB2 database requires the following information:\n",
    "\n",
    "*   Driver Name\n",
    "*   Database name\n",
    "*   Host DNS name or IP address\n",
    "*   Host port\n",
    "*   Connection protocol\n",
    "*   User ID (or username)\n",
    "*   User Password\n",
    "\n",
    "**Notice:** To obtain credentials please refer to the instructions given in the first Lab of this course\n",
    "\n",
    "Now enter your database credentials below and execute the cell with `Shift` + `Enter`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Replace the placeholder values with your actual Db2 hostname, username, and password:\n",
    "dsn_hostname = \"55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud\" # e.g.: \"54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud\"\n",
    "dsn_uid = \"wzw37676\"        # e.g. \"abc12345\"\n",
    "dsn_pwd = \"7tdf7kLUUuJtCwLR\"      # e.g. \"7dBZ3wWt9XN6$o0J\"\n",
    "\n",
    "dsn_driver = \"{IBM DB2 ODBC DRIVER}\"\n",
    "dsn_database = \"bludb\"            # e.g. \"BLUDB\"\n",
    "dsn_port = \"31929\"                # e.g. \"32733\" \n",
    "dsn_protocol = \"TCPIP\"            # i.e. \"TCPIP\"\n",
    "dsn_security = \"ssl\"              #i.e. \"SSL\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create the DB2 database connection\n",
    "\n",
    "Ibm_db API uses the IBM Data Server Driver for ODBC and CLI APIs to connect to IBM DB2 and Informix.\n",
    "\n",
    "Lets build the dsn connection string using the credentials you entered above\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=bludb;HOSTNAME=55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud;PORT=31929;PROTOCOL=TCPIP;UID=wzw37676;PWD=7tdf7kLUUuJtCwLR;SECURITY=ssl;\n"
     ]
    }
   ],
   "source": [
    "#DO NOT MODIFY THIS CELL. Just RUN it with Shift + Enter\n",
    "#Create the dsn connection string\n",
    "dsn = (\n",
    "    \"DRIVER={0};\"\n",
    "    \"DATABASE={1};\"\n",
    "    \"HOSTNAME={2};\"\n",
    "    \"PORT={3};\"\n",
    "    \"PROTOCOL={4};\"\n",
    "    \"UID={5};\"\n",
    "    \"PWD={6};\"\n",
    "    \"SECURITY={7};\").format(dsn_driver, dsn_database, dsn_hostname, dsn_port, dsn_protocol, dsn_uid, dsn_pwd,dsn_security)\n",
    "\n",
    "#print the connection string to check correct values are specified\n",
    "print(dsn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now establish the connection to the database\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connected to database:  bludb as user:  wzw37676 on host:  55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud\n"
     ]
    }
   ],
   "source": [
    "#DO NOT MODIFY THIS CELL. Just RUN it with Shift + Enter\n",
    "#Create database connection\n",
    "\n",
    "try:\n",
    "    conn = ibm_db.connect(dsn, \"\", \"\")\n",
    "    print (\"Connected to database: \", dsn_database, \"as user: \", dsn_uid, \"on host: \", dsn_hostname)\n",
    "\n",
    "except:\n",
    "    print (\"Unable to connect: \", ibm_db.conn_errormsg() )\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Congratulations if you were able to connect successfuly. Otherwise check the error and try again.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DBMS_NAME:  DB2/LINUXX8664\n",
      "DBMS_VER:   11.05.0600\n",
      "DB_NAME:    BLUDB\n"
     ]
    }
   ],
   "source": [
    "#Retrieve Metadata for the Database Server\n",
    "server = ibm_db.server_info(conn)\n",
    "\n",
    "print (\"DBMS_NAME: \", server.DBMS_NAME)\n",
    "print (\"DBMS_VER:  \", server.DBMS_VER)\n",
    "print (\"DB_NAME:   \", server.DB_NAME)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DRIVER_NAME:           libdb2.a\n",
      "DRIVER_VER:            11.05.0500\n",
      "DATA_SOURCE_NAME:      BLUDB\n",
      "DRIVER_ODBC_VER:       03.51\n",
      "ODBC_VER:              03.01.0000\n",
      "ODBC_SQL_CONFORMANCE:  EXTENDED\n",
      "APPL_CODEPAGE:         1208\n",
      "CONN_CODEPAGE:         1208\n"
     ]
    }
   ],
   "source": [
    "#Retrieve Metadata for the Database Client / Driver\n",
    "client = ibm_db.client_info(conn)\n",
    "\n",
    "print (\"DRIVER_NAME:          \", client.DRIVER_NAME) \n",
    "print (\"DRIVER_VER:           \", client.DRIVER_VER)\n",
    "print (\"DATA_SOURCE_NAME:     \", client.DATA_SOURCE_NAME)\n",
    "print (\"DRIVER_ODBC_VER:      \", client.DRIVER_ODBC_VER)\n",
    "print (\"ODBC_VER:             \", client.ODBC_VER)\n",
    "print (\"ODBC_SQL_CONFORMANCE: \", client.ODBC_SQL_CONFORMANCE)\n",
    "print (\"APPL_CODEPAGE:        \", client.APPL_CODEPAGE)\n",
    "print (\"CONN_CODEPAGE:        \", client.CONN_CODEPAGE)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Close the Connection\n",
    "\n",
    "We free all resources by closing the connection. Remember that it is always important to close connections so that we can avoid unused connections taking up resources.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ibm_db.close(conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summary\n",
    "\n",
    "In this tutorial you established a connection to a DB2 database on Cloud database from a Python notebook using ibm_db API.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Author\n",
    "\n",
    "<a href=\"https://www.linkedin.com/in/ravahuja?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01\">Rav Ahuja</a>\n",
    "\n",
    "## Change Log\n",
    "\n",
    "| Date (YYYY-MM-DD) | Version | Changed By | Change Description                 |\n",
    "| ----------------- | ------- | ---------- | ---------------------------------- |\n",
    "| 2021-11-17        | 2.2     | Lakshmi    | Updated library                    |\n",
    "| 2021-07-09        | 2.1     | Malika     | Updated the connection string      |\n",
    "| 2020-08-28        | 2.0     | Lavanya    | Moved lab to course repo in GitLab |\n",
    "\n",
    "<hr>\n",
    "\n",
    "## <h3 align=\"center\"> © IBM Corporation 2020. All rights reserved. <h3/>\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python",
   "language": "python",
   "name": "conda-env-python-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.12"
  },
  "widgets": {
   "state": {},
   "version": "1.1.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}