Connecting to a database instance.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "d7814655-6893-43bf-a51b-5a4b61c90cb4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# These libraries are pre-installed in SN Labs. If running in another environment please uncomment lines below to install them:\n",
    "# !pip install --force-reinstall ibm_db==3.1.0 ibm_db_sa==0.3.3\n",
    "# Ensure we don't load_ext with sqlalchemy>=1.4 (incompadible)\n",
    "# !pip uninstall sqlalchemy==1.4 -y && pip install sqlalchemy==1.3.24\n",
    "# !pip install ipython-sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "139e9a56-ee10-4843-b89c-91bca91a81dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "#When the command above completes, the ibm_db library is loaded in your notebook.\n",
    "\n",
    "import ibm_db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f4de6ae8-f650-4515-a393-dadecd40aaee",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Replace the placeholder values with your actual Db2 hostname, username, and password:\n",
    "\n",
    "dsn_hostname = \"19af6446-6171-4641-8aba-9dcff8e1b6ff.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud\" # e.g.: \"54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud\"\n",
    "dsn_uid = \"prj63437\"        # e.g. \"abc12345\"\n",
    "dsn_pwd = \"HHrZHS29hKohRSpb\"      # e.g. \"7dBZ3wWt9XN6$o0J\"\n",
    "\n",
    "dsn_driver = \"{IBM DB2 ODBC DRIVER}\"\n",
    "dsn_database = \"BLUDB\"            # e.g. \"BLUDB\"\n",
    "dsn_port = \"30699\"                # e.g. \"32733\" \n",
    "dsn_protocol = \"TCPIP\"            # i.e. \"TCPIP\"\n",
    "dsn_security = \"SSL\"              #i.e. \"SSL\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa025d95-1c3a-494f-9d3d-da9635e269e2",
   "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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ce9b1e6f-6aff-4ee8-b480-6e7099b47756",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=BLUDB;HOSTNAME=19af6446-6171-4641-8aba-9dcff8e1b6ff.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud;PORT=30699;PROTOCOL=TCPIP;UID=prj63437;PWD=HHrZHS29hKohRSpb;SECURITY=SSL;\n"
     ]
    }
   ],
   "source": [
    "#Create the dsn connection string\n",
    "\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": "code",
   "execution_count": 5,
   "id": "cc8ac7f1-d545-4aaf-be25-beb90140e403",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connected to database:  BLUDB as user:  prj63437 on host:  19af6446-6171-4641-8aba-9dcff8e1b6ff.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud\n"
     ]
    }
   ],
   "source": [
    "#Now establish the connection to the database\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": "code",
   "execution_count": 6,
   "id": "ba2a726a-6d0a-452d-814a-be8694aa3bfc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DBMS_NAME:  DB2/LINUXX8664\n",
      "DBMS_VER:   11.05.0700\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,
   "id": "55ac131c-8e64-418c-a6cf-7802c863fbbd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DRIVER_NAME:           libdb2.a\n",
      "DRIVER_VER:            11.05.0600\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",
   "id": "441f3682-dc4d-45ce-8dc9-4352cc4c5df3",
   "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."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "720aa0fa-a218-4221-a9a0-89f1e476715c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ibm_db.close(conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3912dfc4-5996-46b0-ab03-df74a60061db",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}