db2.ipynb

{
 "cells": [
  {
   "cell_type": "raw",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DB2 Jupyter Notebook Extensions\n",
    "\n",
    "This code is imported as a Jupyter notebook extension in any notebooks you create with DB2 code in it. Place the following line of code in any notebook that you want to use these commands with:\n",
    "<pre>\n",
    "&#37;run db2.ipynb\n",
    "</pre>\n",
    "\n",
    "This code defines a Jupyter/Python magic command called %sql which allows you to execute DB2 specific calls to \n",
    "the database. There are other packages available for manipulating databases, but this one has been specifically\n",
    "designed for demonstrating a number of the SQL features available in DB2.\n",
    "\n",
    "There are two ways of executing the %sql command. A single line SQL statement would use the\n",
    "line format of the magic command:\n",
    "<pre>\n",
    "%sql SELECT * FROM EMPLOYEE\n",
    "</pre>\n",
    "If you have a large block of sql then you would place the %%sql command at the beginning of the block and then\n",
    "place the SQL statements into the remainder of the block. Using this form of the %%sql statement means that the\n",
    "notebook cell can only contain SQL and no other statements.\n",
    "<pre>\n",
    "%%sql\n",
    "SELECT * FROM EMPLOYEE\n",
    "ORDER BY LASTNAME\n",
    "</pre>\n",
    "You can have multiple lines in the SQL block (%%sql). The default SQL delimiter is the semi-column (;).\n",
    "If you have scripts (triggers, procedures, functions) that use the semi-colon as part of the script, you \n",
    "will need to use the -d option to change the delimiter to an at \"@\" sign. \n",
    "<pre>\n",
    "%%sql -d\n",
    "SELECT * FROM EMPLOYEE\n",
    "@\n",
    "CREATE PROCEDURE ...\n",
    "@\n",
    "</pre>\n",
    "\n",
    "The %sql command allows most DB2 commands to execute and has a special version of the CONNECT statement. \n",
    "A CONNECT by itself will attempt to reconnect to the database using previously used settings. If it cannot \n",
    "connect, it will prompt the user for additional information. \n",
    "\n",
    "The CONNECT command has the following format:\n",
    "<pre>\n",
    "%sql CONNECT TO &lt;database&gt; USER &lt;userid&gt; USING &lt;password | ?&gt; HOST &lt;ip address&gt; PORT &lt;port number&gt;\n",
    "</pre>\n",
    "If you use a \"?\" for the password field, the system will prompt you for a password. This avoids typing the \n",
    "password as clear text on the screen. If a connection is not successful, the system will print the error\n",
    "message associated with the connect request.\n",
    "\n",
    "If the connection is successful, the parameters are saved on your system and will be used the next time you\n",
    "run a SQL statement, or when you issue the %sql CONNECT command with no parameters.\n",
    "\n",
    "In addition to the -d option, there are a number different options that you can specify at the beginning of \n",
    "the SQL:\n",
    "\n",
    "- -d - Delimiter: Change SQL delimiter to \"@\" from \";\"\n",
    "- -q - Quiet: Quiet results - no answer set or messages returned from the function\n",
    "- -n - No result set: Execute all of the SQL as commands rather than select statements (no answer sets) \n",
    "- -s - SQL: Execute everything as SELECT statements. By default, SELECT, VALUES, and WITH are considered part of an answer set, but it is possible that you have an SQL statement that does not start with any of these keywords but returns an answer set.\n",
    "- -r - Return the result set as an array of values instead of a dataframe\n",
    "- -t - Time: Time the following SQL statement and return the number of times it executes in 1 second\n",
    "- -j - JSON: Create a pretty JSON representation. Only the first column is formatted\n",
    "- -a - All: Return all rows in answer set and do not limit display\n",
    "- -pb - Plot Bar: Plot the results as a bar chart\n",
    "- -pl - Plot Line: Plot the results as a line chart\n",
    "- -pp - Plot Pie: Plot the results as a pie chart\n",
    "- -sampledata - Create and load the EMPLOYEE and DEPARTMENT tables\n",
    "\n",
    "One final note. You can pass python variables to the %sql command by using the \\{\\} braces with the name of the\n",
    "variable inbetween. Note that you will need to place proper punctuation around the variable in the event the\n",
    "SQL command requires it. For instance, the following example will find employee '000010' in the EMPLOYEE table.\n",
    "<pre>\n",
    "empno = '000010'\n",
    "%sql SELECT LASTNAME FROM EMPLOYEE WHERE EMPNO='{empno}'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Install Db2 Python Driver\n",
    "In the event that you do not have ibm_db installed on your system, this command will attempt to load it for you. If the %sql command does not work, it may be that this library is failing. To review the error messages, remove the %%capture clause."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%capture\n",
    "!pip install ibm_db\n",
    "!pip install --user --upgrade pixiedust"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Install Db2 Extensions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Pixiedust database opened successfully\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "        <div style=\"margin:10px\">\n",
       "            <a href=\"https://github.com/ibm-watson-data-lab/pixiedust\" target=\"_new\">\n",
       "                <img src=\"https://github.com/ibm-watson-data-lab/pixiedust/raw/master/docs/_static/pd_icon32.png\" style=\"float:left;margin-right:10px\"/>\n",
       "            </a>\n",
       "            <span>Pixiedust version 1.1.2</span>\n",
       "        </div>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<p style=\"border:2px; border-style:solid; border-color:#008000; background-color:#e6ffe6; padding: 1em;\">DB2 Extensions Loaded.</p>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#\n",
    "# Set up Jupyter MAGIC commands \"sql\". \n",
    "# %sql will return results from a DB2 select statement or execute a DB2 command\n",
    "#\n",
    "# IBM 2017: George Baklarz\n",
    "# Version 2017-11-15\n",
    "#\n",
    "\n",
    "import ibm_db\n",
    "import pandas\n",
    "import ibm_db_dbi\n",
    "import json\n",
    "import matplotlib.pyplot as plt\n",
    "import getpass\n",
    "import os\n",
    "import pickle\n",
    "import time\n",
    "import sys\n",
    "import re\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "# Override the name of display, HTML, and Image in the event you plan to use the pixiedust library for\n",
    "# rendering graphics.\n",
    "\n",
    "from IPython.display import HTML as pHTML, Image as pImage, display as pDisplay\n",
    "from __future__ import print_function\n",
    "from IPython.core.magic import (Magics, magics_class, line_magic,\n",
    "                                cell_magic, line_cell_magic)\n",
    "from pixiedust.display import *\n",
    "from pixiedust.utils.shellAccess import ShellAccess\n",
    "\n",
    "# Python Hack for Input between 2 and 3\n",
    "\n",
    "try: \n",
    "    input = raw_input \n",
    "except NameError: \n",
    "    pass \n",
    "\n",
    "plt.style.use('ggplot')\n",
    "\n",
    "settings = {\n",
    "     \"maxrows\"  : 10,    \n",
    "     \"database\" : \"\",\n",
    "     \"hostname\" : \"localhost\",\n",
    "     \"port\"     : \"50000\",\n",
    "     \"protocol\" : \"TCPIP\",    \n",
    "     \"uid\"      : \"DB2INST1\",\n",
    "     \"pwd\"      : \"password\"\n",
    "}\n",
    "\n",
    "# Connection settings for statements \n",
    "\n",
    "connected = False\n",
    "hdbc = None\n",
    "hstmt = None\n",
    "runtime = 1\n",
    "\n",
    "def sqlhelp():\n",
    "    \n",
    "    sd = '<td style=\"text-align:left;\">'\n",
    "    ed = '</td>'\n",
    "    sh = '<th style=\"text-align:left;\">'\n",
    "    eh = '</th>'\n",
    "    sr = '<tr>'\n",
    "    er = '</tr>'\n",
    "    \n",
    "    helpSQL = \"\"\"\n",
    "       <h3>SQL Options</h3> \n",
    "       <p>The following options are available as part of a SQL statement. The options are always preceded with a\n",
    "       minus sign (i.e. -q).\n",
    "       <table>\n",
    "        {sr}\n",
    "           {sh}Option{eh}\n",
    "           {sh}Description{eh}\n",
    "        {er}\n",
    "        {sr}\n",
    "          {sd}d{ed}\n",
    "          {sd}Change SQL delimiter to \"@\" from \";\"{ed}\n",
    "        {er}\n",
    "        {sr}\n",
    "          {sd}q{ed}\n",
    "          {sd}Quiet results - no answer set or messages returned from the function{ed}\n",
    "        {er}\n",
    "        {sr}\n",
    "          {sd}n{ed}\n",
    "          {sd}Execute all of the SQL as commands rather than select statements (no answer sets){ed}\n",
    "        {er}\n",
    "        {sr}\n",
    "          {sd}s{ed}\n",
    "          {sd}Execute everything as SELECT statements. By default, SELECT, VALUES, and WITH are considered part of an answer set, but it is possible that you have an SQL statement that does not start with any of these keywords but returns an answer set.\n",
    "          {ed} \n",
    "        {er}\n",
    "        {sr}  \n",
    "          {sd}r{ed}\n",
    "          {sd}Return the result set as an array of values{ed}\n",
    "        {er}\n",
    "        {sr}\n",
    "          {sd}t{ed}\n",
    "          {sd}Time the following SQL statement and return the number of times it executes in 1 second{ed}\n",
    "        {er}\n",
    "        {sr}\n",
    "          {sd}j{ed}\n",
    "          {sd}Create a pretty JSON representation. Only the first column is formatted{ed}\n",
    "        {er}\n",
    "        {sr}\n",
    "          {sd}a{ed}\n",
    "          {sd}Return all rows in answer set and do not limit display{ed}\n",
    "        {er}\n",
    "        {sr}\n",
    "          {sd}i{ed}\n",
    "          {sd}Return the data in a pixiedust display to view the data and optionally plot it.{ed}\n",
    "        {er}\n",
    "        {sr}\n",
    "          {sd}pb{ed}\n",
    "          {sd}Plot the results as a bar chart{ed}\n",
    "        {er}\n",
    "        {sr}\n",
    "          {sd}pl{ed}\n",
    "          {sd}Plot the results as a line chart{ed}\n",
    "        {er}\n",
    "        {sr}\n",
    "          {sd}pp{ed}\n",
    "          {sd}Plot Pie: Plot the results as a pie chart{ed}\n",
    "        {er}\n",
    "        {sr}\n",
    "          {sd}sampledata{ed}\n",
    "          {sd}Create and load the EMPLOYEE and DEPARTMENT tables{ed}\n",
    "        {er}\n",
    "       </table>\n",
    "       \"\"\"\n",
    "    \n",
    "    helpSQL = helpSQL.format(**locals())\n",
    "        \n",
    "    pDisplay(pHTML(helpSQL))\n",
    "    \n",
    "def connected_help():\n",
    "    \n",
    "    sd = '<td style=\"text-align:left;\">'\n",
    "    ed = '</td>'\n",
    "    sh = '<th style=\"text-align:left;\">'\n",
    "    eh = '</th>'\n",
    "    sr = '<tr>'\n",
    "    er = '</tr>'\n",
    "        \n",
    "    helpConnect = \"\"\"\n",
    "       <h3>Connecting to DB2</h3> \n",
    "       <p>The CONNECT command has the following format:\n",
    "       <pre>\n",
    "       %sql CONNECT TO &lt;database&gt; USER &lt;userid&gt; USING &lt;password|?&gt; HOST &lt;ip address&gt; PORT &lt;port number&gt;\n",
    "       </pre>\n",
    "       If you use a \"?\" for the password field, the system will prompt you for a password. This avoids typing the \n",
    "       password as clear text on the screen. If a connection is not successful, the system will print the error\n",
    "       message associated with the connect request.\n",
    "       <p>\n",
    "       Note: When prompted for input, you can use the format ip:port or #x:port where #x represents \n",
    "       the last digits of the containers IP address. For instance, if the Db2 server is found on 172.17.0.2 then\n",
    "       you would use the value #2 to represent 172.17.0.2:50000. \n",
    "       <p>\n",
    "       If the connection is successful, the parameters are saved on your system and will be used the next time you\n",
    "       run an SQL statement, or when you issue the %sql CONNECT command with no parameters.\n",
    "       <p>If you issue CONNECT RESET, all of the current values will be deleted and you will need to \n",
    "       issue a new CONNECT statement. \n",
    "       <p>CONNECT without any parameters will cause the program to prompt you for\n",
    "       the values. To use the default values, just hit return for each input line. The default values are: \n",
    "       <table>\n",
    "       {sr}\n",
    "         {sh}Setting{eh}\n",
    "         {sh}Description{eh}\n",
    "       {er}\n",
    "       {sr}\n",
    "         {sd}DB2 Driver{ed}\n",
    "         {sd}IBM DB2 ODBC Driver (this requires a manual change){ed}\n",
    "       {er}\n",
    "       {sr}\n",
    "         {sd}Database{ed}{sd}SAMPLE{ed}\n",
    "       {er}\n",
    "       {sr}\n",
    "         {sd}Hostname{ed}\n",
    "         {sd}localhost (enter an IP address if you need to connect to a remote server){ed}\n",
    "       {er}\n",
    "       {sr}\n",
    "         {sd}PORT{ed}\n",
    "         {sd}50000 (this is the default but it could be different){ed}\n",
    "       {er}\n",
    "       {sr}         \n",
    "         {sd}PROTOCOL{ed}\n",
    "         {sd}TCPIP (You could have a local connection in Windows){ed}\n",
    "       {er}\n",
    "       {sr}         \n",
    "         {sd}Userid{ed}\n",
    "         {sd}DB2INST1{ed} \n",
    "       {er}\n",
    "       {sr}                  \n",
    "         {sd}Password{ed}\n",
    "         {sd}No password is provided so you have to enter a value{ed}\n",
    "       {er}\n",
    "       {sr}       \n",
    "         {sd}Maximum Rows{ed}\n",
    "         {sd}10 lines of output are displayed when a result set is returned{ed}\n",
    "       {er}\n",
    "       </table>\n",
    "       \"\"\"\n",
    "    \n",
    "    helpConnect = helpConnect.format(**locals())\n",
    "        \n",
    "    pDisplay(pHTML(helpConnect))    \n",
    "    \n",
    "def split_ipport(in_port):\n",
    "\n",
    "    # Split input into an IP address and Port number\n",
    "\n",
    "    checkports = in_port.split(':')\n",
    "    ip = checkports[0]\n",
    "    if (len(checkports) > 1):\n",
    "        port = checkports[1]\n",
    "    else:\n",
    "        port = None\n",
    "\n",
    "    if (ip[:1] == '#'):\n",
    "        ip = \"172.17.0.\" + ip[1:]\n",
    "\n",
    "    return ip, port    \n",
    "\n",
    "\n",
    "def load_settings():\n",
    "\n",
    "    # This routine will load the settings from the previous session if they exist\n",
    "    \n",
    "    global settings\n",
    "    \n",
    "    fname = \"db2connect.pickle\"\n",
    "\n",
    "    try:\n",
    "        with open(fname,'rb') as f: \n",
    "            settings = pickle.load(f) \n",
    "            \n",
    "    except: \n",
    "        pass\n",
    "    \n",
    "    return\n",
    "\n",
    "def save_settings():\n",
    "\n",
    "    # This routine will save the current settings if they exist\n",
    "    \n",
    "    global settings\n",
    "    \n",
    "    fname = \"db2connect.pickle\"\n",
    "    \n",
    "    try:\n",
    "        with open(fname,'wb') as f:\n",
    "            pickle.dump(settings,f)\n",
    "            \n",
    "    except:\n",
    "        errormsg(\"Failed trying to write DB2 Configuration Information.\")\n",
    " \n",
    "    return  \n",
    "\n",
    "# DB2 Connection information Help\n",
    "\n",
    "def db2_create_sample():\n",
    "    \n",
    "    create_department = \"\"\"\n",
    "      BEGIN\n",
    "        DECLARE FOUND INTEGER; \n",
    "        SET FOUND = (SELECT COUNT(*) FROM SYSIBM.SYSTABLES WHERE NAME='DEPARTMENT' AND CREATOR=CURRENT USER); \n",
    "        IF FOUND = 0 THEN \n",
    "           EXECUTE IMMEDIATE('CREATE TABLE DEPARTMENT(DEPTNO CHAR(3) NOT NULL, DEPTNAME VARCHAR(36) NOT NULL, \n",
    "                              MGRNO CHAR(6),ADMRDEPT CHAR(3) NOT NULL)'); \n",
    "           EXECUTE IMMEDIATE('INSERT INTO DEPARTMENT VALUES \n",
    "             (''A00'',''SPIFFY COMPUTER SERVICE DIV.'',''000010'',''A00''), \n",
    "             (''B01'',''PLANNING'',''000020'',''A00''), \n",
    "             (''C01'',''INFORMATION CENTER'',''000030'',''A00''), \n",
    "             (''D01'',''DEVELOPMENT CENTER'',NULL,''A00''), \n",
    "             (''D11'',''MANUFACTURING SYSTEMS'',''000060'',''D01''), \n",
    "             (''D21'',''ADMINISTRATION SYSTEMS'',''000070'',''D01''), \n",
    "             (''E01'',''SUPPORT SERVICES'',''000050'',''A00''), \n",
    "             (''E11'',''OPERATIONS'',''000090'',''E01''), \n",
    "             (''E21'',''SOFTWARE SUPPORT'',''000100'',''E01''), \n",
    "             (''F22'',''BRANCH OFFICE F2'',NULL,''E01''), \n",
    "             (''G22'',''BRANCH OFFICE G2'',NULL,''E01''), \n",
    "             (''H22'',''BRANCH OFFICE H2'',NULL,''E01''), \n",
    "             (''I22'',''BRANCH OFFICE I2'',NULL,''E01''), \n",
    "             (''J22'',''BRANCH OFFICE J2'',NULL,''E01'')');      \n",
    "           END IF;\n",
    "      END\"\"\"\n",
    "  \n",
    "    %sql -d -q {create_department} \n",
    "    \n",
    "    create_employee = \"\"\"\n",
    "     BEGIN\n",
    "        DECLARE FOUND INTEGER; \n",
    "        SET FOUND = (SELECT COUNT(*) FROM SYSIBM.SYSTABLES WHERE NAME='EMPLOYEE' AND CREATOR=CURRENT USER); \n",
    "        IF FOUND = 0 THEN \n",
    "          EXECUTE IMMEDIATE('CREATE TABLE EMPLOYEE(\n",
    "                             EMPNO CHAR(6) NOT NULL,\n",
    "                             FIRSTNME VARCHAR(12) NOT NULL,\n",
    "                             MIDINIT CHAR(1),\n",
    "                             LASTNAME VARCHAR(15) NOT NULL,\n",
    "                             WORKDEPT CHAR(3),\n",
    "                             PHONENO CHAR(4),\n",
    "                             HIREDATE DATE,\n",
    "                             JOB CHAR(8),\n",
    "                             EDLEVEL SMALLINT NOT NULL,\n",
    "                             SEX CHAR(1),\n",
    "                             BIRTHDATE DATE,\n",
    "                             SALARY DECIMAL(9,2),\n",
    "                             BONUS DECIMAL(9,2),\n",
    "                             COMM DECIMAL(9,2)\n",
    "                             )');\n",
    "          EXECUTE IMMEDIATE('INSERT INTO EMPLOYEE VALUES\n",
    "             (''000010'',''CHRISTINE'',''I'',''HAAS''      ,''A00'',''3978'',''1995-01-01'',''PRES    '',18,''F'',''1963-08-24'',152750.00,1000.00,4220.00),\n",
    "             (''000020'',''MICHAEL''  ,''L'',''THOMPSON''  ,''B01'',''3476'',''2003-10-10'',''MANAGER '',18,''M'',''1978-02-02'',94250.00,800.00,3300.00),\n",
    "             (''000030'',''SALLY''    ,''A'',''KWAN''      ,''C01'',''4738'',''2005-04-05'',''MANAGER '',20,''F'',''1971-05-11'',98250.00,800.00,3060.00),\n",
    "             (''000050'',''JOHN''     ,''B'',''GEYER''     ,''E01'',''6789'',''1979-08-17'',''MANAGER '',16,''M'',''1955-09-15'',80175.00,800.00,3214.00),\n",
    "             (''000060'',''IRVING''   ,''F'',''STERN''     ,''D11'',''6423'',''2003-09-14'',''MANAGER '',16,''M'',''1975-07-07'',72250.00,500.00,2580.00),\n",
    "             (''000070'',''EVA''      ,''D'',''PULASKI''   ,''D21'',''7831'',''2005-09-30'',''MANAGER '',16,''F'',''2003-05-26'',96170.00,700.00,2893.00),\n",
    "             (''000090'',''EILEEN''   ,''W'',''HENDERSON'' ,''E11'',''5498'',''2000-08-15'',''MANAGER '',16,''F'',''1971-05-15'',89750.00,600.00,2380.00),\n",
    "             (''000100'',''THEODORE'' ,''Q'',''SPENSER''   ,''E21'',''0972'',''2000-06-19'',''MANAGER '',14,''M'',''1980-12-18'',86150.00,500.00,2092.00),\n",
    "             (''000110'',''VINCENZO'' ,''G'',''LUCCHESSI'' ,''A00'',''3490'',''1988-05-16'',''SALESREP'',19,''M'',''1959-11-05'',66500.00,900.00,3720.00),\n",
    "             (''000120'',''SEAN''     ,'' '',''O`CONNELL'' ,''A00'',''2167'',''1993-12-05'',''CLERK   '',14,''M'',''1972-10-18'',49250.00,600.00,2340.00),\n",
    "             (''000130'',''DELORES''  ,''M'',''QUINTANA''  ,''C01'',''4578'',''2001-07-28'',''ANALYST '',16,''F'',''1955-09-15'',73800.00,500.00,1904.00),\n",
    "             (''000140'',''HEATHER''  ,''A'',''NICHOLLS''  ,''C01'',''1793'',''2006-12-15'',''ANALYST '',18,''F'',''1976-01-19'',68420.00,600.00,2274.00),\n",
    "             (''000150'',''BRUCE''    ,'' '',''ADAMSON''   ,''D11'',''4510'',''2002-02-12'',''DESIGNER'',16,''M'',''1977-05-17'',55280.00,500.00,2022.00),\n",
    "             (''000160'',''ELIZABETH'',''R'',''PIANKA''    ,''D11'',''3782'',''2006-10-11'',''DESIGNER'',17,''F'',''1980-04-12'',62250.00,400.00,1780.00),\n",
    "             (''000170'',''MASATOSHI'',''J'',''YOSHIMURA'' ,''D11'',''2890'',''1999-09-15'',''DESIGNER'',16,''M'',''1981-01-05'',44680.00,500.00,1974.00),\n",
    "             (''000180'',''MARILYN''  ,''S'',''SCOUTTEN''  ,''D11'',''1682'',''2003-07-07'',''DESIGNER'',17,''F'',''1979-02-21'',51340.00,500.00,1707.00),\n",
    "             (''000190'',''JAMES''    ,''H'',''WALKER''    ,''D11'',''2986'',''2004-07-26'',''DESIGNER'',16,''M'',''1982-06-25'',50450.00,400.00,1636.00),\n",
    "             (''000200'',''DAVID''    ,'' '',''BROWN''     ,''D11'',''4501'',''2002-03-03'',''DESIGNER'',16,''M'',''1971-05-29'',57740.00,600.00,2217.00),\n",
    "             (''000210'',''WILLIAM''  ,''T'',''JONES''     ,''D11'',''0942'',''1998-04-11'',''DESIGNER'',17,''M'',''2003-02-23'',68270.00,400.00,1462.00),\n",
    "             (''000220'',''JENNIFER'' ,''K'',''LUTZ''      ,''D11'',''0672'',''1998-08-29'',''DESIGNER'',18,''F'',''1978-03-19'',49840.00,600.00,2387.00),\n",
    "             (''000230'',''JAMES''    ,''J'',''JEFFERSON'' ,''D21'',''2094'',''1996-11-21'',''CLERK   '',14,''M'',''1980-05-30'',42180.00,400.00,1774.00),\n",
    "             (''000240'',''SALVATORE'',''M'',''MARINO''    ,''D21'',''3780'',''2004-12-05'',''CLERK   '',17,''M'',''2002-03-31'',48760.00,600.00,2301.00),\n",
    "             (''000250'',''DANIEL''   ,''S'',''SMITH''     ,''D21'',''0961'',''1999-10-30'',''CLERK   '',15,''M'',''1969-11-12'',49180.00,400.00,1534.00),\n",
    "             (''000260'',''SYBIL''    ,''P'',''JOHNSON''   ,''D21'',''8953'',''2005-09-11'',''CLERK   '',16,''F'',''1976-10-05'',47250.00,300.00,1380.00),\n",
    "             (''000270'',''MARIA''    ,''L'',''PEREZ''     ,''D21'',''9001'',''2006-09-30'',''CLERK   '',15,''F'',''2003-05-26'',37380.00,500.00,2190.00),\n",
    "             (''000280'',''ETHEL''    ,''R'',''SCHNEIDER'' ,''E11'',''8997'',''1997-03-24'',''OPERATOR'',17,''F'',''1976-03-28'',36250.00,500.00,2100.00),\n",
    "             (''000290'',''JOHN''     ,''R'',''PARKER''    ,''E11'',''4502'',''2006-05-30'',''OPERATOR'',12,''M'',''1985-07-09'',35340.00,300.00,1227.00),\n",
    "             (''000300'',''PHILIP''   ,''X'',''SMITH''     ,''E11'',''2095'',''2002-06-19'',''OPERATOR'',14,''M'',''1976-10-27'',37750.00,400.00,1420.00),\n",
    "             (''000310'',''MAUDE''    ,''F'',''SETRIGHT''  ,''E11'',''3332'',''1994-09-12'',''OPERATOR'',12,''F'',''1961-04-21'',35900.00,300.00,1272.00),\n",
    "             (''000320'',''RAMLAL''   ,''V'',''MEHTA''     ,''E21'',''9990'',''1995-07-07'',''FIELDREP'',16,''M'',''1962-08-11'',39950.00,400.00,1596.00),\n",
    "             (''000330'',''WING''     ,'' '',''LEE''       ,''E21'',''2103'',''2006-02-23'',''FIELDREP'',14,''M'',''1971-07-18'',45370.00,500.00,2030.00),\n",
    "             (''000340'',''JASON''    ,''R'',''GOUNOT''    ,''E21'',''5698'',''1977-05-05'',''FIELDREP'',16,''M'',''1956-05-17'',43840.00,500.00,1907.00),\n",
    "             (''200010'',''DIAN''     ,''J'',''HEMMINGER'' ,''A00'',''3978'',''1995-01-01'',''SALESREP'',18,''F'',''1973-08-14'',46500.00,1000.00,4220.00),\n",
    "             (''200120'',''GREG''     ,'' '',''ORLANDO''   ,''A00'',''2167'',''2002-05-05'',''CLERK   '',14,''M'',''1972-10-18'',39250.00,600.00,2340.00),\n",
    "             (''200140'',''KIM''      ,''N'',''NATZ''      ,''C01'',''1793'',''2006-12-15'',''ANALYST '',18,''F'',''1976-01-19'',68420.00,600.00,2274.00),\n",
    "             (''200170'',''KIYOSHI''  ,'' '',''YAMAMOTO''  ,''D11'',''2890'',''2005-09-15'',''DESIGNER'',16,''M'',''1981-01-05'',64680.00,500.00,1974.00),\n",
    "             (''200220'',''REBA''     ,''K'',''JOHN''      ,''D11'',''0672'',''2005-08-29'',''DESIGNER'',18,''F'',''1978-03-19'',69840.00,600.00,2387.00),\n",
    "             (''200240'',''ROBERT''   ,''M'',''MONTEVERDE'',''D21'',''3780'',''2004-12-05'',''CLERK   '',17,''M'',''1984-03-31'',37760.00,600.00,2301.00),\n",
    "             (''200280'',''EILEEN''   ,''R'',''SCHWARTZ''  ,''E11'',''8997'',''1997-03-24'',''OPERATOR'',17,''F'',''1966-03-28'',46250.00,500.00,2100.00),\n",
    "             (''200310'',''MICHELLE'' ,''F'',''SPRINGER''  ,''E11'',''3332'',''1994-09-12'',''OPERATOR'',12,''F'',''1961-04-21'',35900.00,300.00,1272.00),\n",
    "             (''200330'',''HELENA''   ,'' '',''WONG''      ,''E21'',''2103'',''2006-02-23'',''FIELDREP'',14,''F'',''1971-07-18'',35370.00,500.00,2030.00),\n",
    "             (''200340'',''ROY''      ,''R'',''ALONZO''    ,''E21'',''5698'',''1997-07-05'',''FIELDREP'',16,''M'',''1956-05-17'',31840.00,500.00,1907.00)');                             \n",
    "        END IF;\n",
    "     END\"\"\"\n",
    "    \n",
    "    %sql -d -q {create_employee}    \n",
    "    \n",
    "    success(\"Sample tables [EMPLOYEE, DEPARTMENT] created.\")\n",
    "    \n",
    "def connected_prompt():\n",
    "    \n",
    "    global settings\n",
    "\n",
    "    \n",
    "    settings[\"database\"] = input(\"Enter the database name [SAMPLE]: \") or \"SAMPLE\";\n",
    "    hostport = input(\"Enter the HOST IP address and PORT in the form ip:port or #x:port [localhost:50000].\") or \"localhost:50000\";\n",
    "    ip, port = split_ipport(hostport)\n",
    "    if (port == None): port = \"50000\"\n",
    "    settings[\"hostname\"] = ip\n",
    "    settings[\"port\"]     = port\n",
    "    settings[\"uid\"]      = input(\"Enter Userid on the DB2 system [DB2INST1]: \").upper() or \"DB2INST1\";\n",
    "    settings[\"pwd\"]      = getpass.getpass(\"Password [password]: \") or \"password\";\n",
    "    settings[\"maxrows\"]  = input(\"Maximum rows displayed [10]: \") or \"10\";\n",
    "    settings[\"maxrows\"]  = int(settings[\"maxrows\"])\n",
    "\n",
    "# Connect to DB2 and prompt if you haven't set any of the values yet\n",
    "\n",
    "def db2_doConnect():\n",
    "    \n",
    "    global hdbc, hstmt, connected, runtime\n",
    "    global settings  \n",
    "\n",
    "    if connected == False: \n",
    "        \n",
    "        if len(settings[\"database\"]) == 0:\n",
    "            connected_help()\n",
    "            connected_prompt()\n",
    "    \n",
    "    dsn = (\n",
    "           \"DRIVER={{IBM DB2 ODBC DRIVER}};\"\n",
    "           \"DATABASE={0};\"\n",
    "           \"HOSTNAME={1};\"\n",
    "           \"PORT={2};\"\n",
    "           \"PROTOCOL=TCPIP;\"\n",
    "           \"UID={3};\"\n",
    "           \"PWD={4};\").format(settings[\"database\"], settings[\"hostname\"], settings[\"port\"], settings[\"uid\"], settings[\"pwd\"])\n",
    "\n",
    "    # Get a database handle (hdbc) and a statement handle (hstmt) for subsequent access to DB2\n",
    "\n",
    "    try:\n",
    "        hdbc  = ibm_db.connect(dsn, \"\", \"\")\n",
    "    except Exception as err:\n",
    "        errormsg(str(err))\n",
    "        connected = False\n",
    "        settings[\"database\"] = ''\n",
    "        return\n",
    "    \n",
    "    try:\n",
    "        hstmt = ibm_db_dbi.Connection(hdbc)\n",
    "    except Exception as err:\n",
    "        errormsg(str(err))\n",
    "        connected = False\n",
    "        settings[\"database\"] = ''\n",
    "        return        \n",
    "    \n",
    "    connected = True\n",
    "    \n",
    "    # Save the values for future use\n",
    "    \n",
    "    save_settings()\n",
    "    \n",
    "    success(\"Connection successful.\")\n",
    "    \n",
    "# Parse the CONNECT statement and execute if possible \n",
    "\n",
    "def parseConnect(inSQL):\n",
    "    \n",
    "    global settings, connected\n",
    "\n",
    "    connected = False\n",
    "    \n",
    "    cParms = inSQL.split()\n",
    "    cnt = 0\n",
    "    \n",
    "    while cnt < len(cParms):\n",
    "        if cParms[cnt].upper() == 'TO':\n",
    "            if cnt+1 < len(cParms):\n",
    "                settings[\"database\"] = cParms[cnt+1].upper()\n",
    "                cnt = cnt + 1\n",
    "            else:\n",
    "                errormsg(\"No database specified in the CONNECT statement\")\n",
    "                return\n",
    "        elif cParms[cnt].upper() == 'USER':\n",
    "            if cnt+1 < len(cParms):\n",
    "                settings[\"uid\"] = cParms[cnt+1].upper()\n",
    "                cnt = cnt + 1\n",
    "            else:\n",
    "                errormsg(\"No userid specified in the CONNECT statement\")\n",
    "                return\n",
    "        elif cParms[cnt].upper() == 'USING':\n",
    "            if cnt+1 < len(cParms):\n",
    "                settings[\"pwd\"] = cParms[cnt+1]   \n",
    "                if (settings[\"pwd\"] == '?'):\n",
    "                    settings[\"pwd\"] = getpass.getpass(\"Password [password]: \") or \"password\"\n",
    "                cnt = cnt + 1\n",
    "            else:\n",
    "                errormsg(\"No password specified in the CONNECT statement\")\n",
    "                return\n",
    "        elif cParms[cnt].upper() == 'HOST':\n",
    "            if cnt+1 < len(cParms):\n",
    "                settings[\"hostname\"] = cParms[cnt+1].upper()\n",
    "                cnt = cnt + 1\n",
    "            else:\n",
    "                errormsg(\"No hostname specified in the CONNECT statement\")\n",
    "                return\n",
    "        elif cParms[cnt].upper() == 'PORT':                           \n",
    "            if cnt+1 < len(cParms):\n",
    "                settings[\"port\"] = cParms[cnt+1].upper()\n",
    "                cnt = cnt + 1\n",
    "            else:\n",
    "                errormsg(\"No port specified in the CONNECT statement\")\n",
    "                return\n",
    "        elif cParms[cnt].upper() == 'RESET': \n",
    "             settings[\"database\"] = ''\n",
    "             success(\"Connection reset.\")\n",
    "             return\n",
    "        else:\n",
    "            cnt = cnt + 1\n",
    "                     \n",
    "    db2_doConnect()\n",
    "\n",
    "# Find a keyword in a SQL string\n",
    "\n",
    "def findKeyword(keywords,keyword):\n",
    "    \n",
    "    if len(keywords) == 0: return False\n",
    "    if len(keyword) == 0: return False\n",
    "    \n",
    "    uKeywords = keywords.upper()\n",
    "    uKeyword  = keyword.upper()\n",
    "    \n",
    "    if uKeywords.find(uKeyword.strip()) >= 0:\n",
    "        return True\n",
    "    else:\n",
    "        return False\n",
    "    \n",
    "# Run a command for one second to see how many times we execute it and return the count\n",
    "\n",
    "def sqlTimer(flag_cmd, inSQL):\n",
    "    \n",
    "    global hdbc, hstmt, runtime\n",
    "\n",
    "    db2Block = 2\n",
    "    count = 0\n",
    "    t_end = time.time() + runtime\n",
    "    while time.time() < t_end:\n",
    "        if (flag_cmd == db2Block):\n",
    "            try:\n",
    "                stmt = ibm_db.exec_immediate(hdbc,inSQL)\n",
    "            except Exception as err:\n",
    "                db2_error(False)\n",
    "                return(-1)\n",
    "        else:\n",
    "            try:\n",
    "                stmt = ibm_db.exec_immediate(hdbc,inSQL)\n",
    "                while( ibm_db.fetch_row(stmt) ): pass\n",
    "            except Exception as err:\n",
    "                db2_error(False)\n",
    "                return(-1)\n",
    "        \n",
    "        count = count + 1\n",
    "        \n",
    "    return(count)\n",
    "\n",
    "# Print out the DB2 error generated by the last executed statement\n",
    "\n",
    "def db2_error(quiet):\n",
    "    \n",
    "    if quiet == True: return\n",
    "\n",
    "    html = '<p style=\"border:2px; border-style:solid; border-color:#FF0000; background-color:#ffe6e6; padding: 1em;\">'\n",
    "\n",
    "    errmsg = ibm_db.stmt_errormsg().replace('\\r',' ')\n",
    "    errmsg = errmsg[errmsg.rfind(\"]\")+1:].strip()\n",
    "    pDisplay(pHTML(html+errmsg+\"</p>\"))\n",
    "    \n",
    "# Print out an error message\n",
    "\n",
    "def errormsg(message):\n",
    "    \n",
    "    html = '<p style=\"border:2px; border-style:solid; border-color:#FF0000; background-color:#ffe6e6; padding: 1em;\">'\n",
    "    pDisplay(pHTML(html + message + \"</p>\"))    \n",
    "    \n",
    "def success(message):\n",
    "    \n",
    "    print(message)\n",
    "#\n",
    "#    Optional Code to print green border around success statements\n",
    "#\n",
    "#    html = '<p style=\"border:2px; border-style:solid; border-color:#008000; background-color:#e6ffe6; padding: 1em;\">'\n",
    "#            \n",
    "#    if message != \"\":\n",
    "#        pDisplay(pHTML(html + message + \"</p>\"))\n",
    "        \n",
    "    return     \n",
    "        \n",
    "@magics_class\n",
    "class DB2(Magics):\n",
    "      \n",
    "    import pixiedust  \n",
    "    \n",
    "    @line_cell_magic\n",
    "    def sql(self, line, cell=None):\n",
    "            \n",
    "        # Before we event get started, check to see if you have connected yet. Without a connection we \n",
    "        # can't do anything. You may have a connection request in the code, so if that is true, we run those,\n",
    "        # otherwise we connect immediately\n",
    "        \n",
    "        # If your statement is not a connect, and you haven't connected, we need to do it for you\n",
    "    \n",
    "        global settings \n",
    "        global hdbc, hstmt, connected\n",
    "        \n",
    "        select = [\"SELECT\", \"WITH\", \"VALUES\"] \n",
    "        noBlock = 0\n",
    "        sqlBlock = 1\n",
    "        db2Block = 2\n",
    "            \n",
    "        # If you use %sql (line) we just run the SQL. If you use %%SQL the entire cell is run.\n",
    "        \n",
    "        flag_delim = \";\"\n",
    "        flag_results = True\n",
    "        flag_sqlType = noBlock\n",
    "        flag_quiet = False\n",
    "        flag_json = False\n",
    "        flag_timer = False\n",
    "        flag_plot = 0\n",
    "        flag_cell = False\n",
    "        flag_output = False\n",
    "        flag_resultset = False\n",
    "        flag_dataframe = False\n",
    "        \n",
    "        # The parameters must be in the line, not in the cell i.e. %sql -c \n",
    "        \n",
    "        Parms = line.strip()\n",
    "        \n",
    "        if len(Parms) == 0:\n",
    "            if cell == None: \n",
    "                sqlhelp()\n",
    "                return\n",
    "            if len(cell.strip()) == 0: \n",
    "                sqlhelp()\n",
    "                return\n",
    "            \n",
    "        # Check of you just want help\n",
    "        \n",
    "        if Parms == \"?\":\n",
    "            sqlhelp()\n",
    "            return\n",
    "        \n",
    "        if Parms.upper() == \"? CONNECT\":\n",
    "            connected_help()\n",
    "            return\n",
    "        \n",
    "        # If you issue a CONNECT statement in %sql then we run this first before auto-connecting\n",
    "        if findKeyword(Parms,\"CONNECT\") == True: \n",
    "            parseConnect(Parms)\n",
    "            return\n",
    "        \n",
    "        # We need to check to see if we are connected before running any SQL\n",
    "        if connected == False:\n",
    "            db2_doConnect()\n",
    "            if connected == False: return\n",
    "            \n",
    "        # Default result set size\n",
    "        if settings[\"maxrows\"] == -1:\n",
    "            pandas.reset_option('max_rows')\n",
    "        else:\n",
    "            pandas.options.display.max_rows = settings[\"maxrows\"]\n",
    "      \n",
    "        # Display rows as JSON structure\n",
    "        if Parms.find(\"-j\") >= 0:\n",
    "            flag_json = True\n",
    "            Parms = Parms.replace(\"-j\",\" \")\n",
    "          \n",
    "        # Load sample tables for scripts\n",
    "        if Parms.find('-sampledata') >= 0:\n",
    "            db2_create_sample()\n",
    "            return\n",
    "        \n",
    "        # Execute the SQL so that it behaves like a SELECT statement\n",
    "        if Parms.find(\"-s\") >= 0:\n",
    "            flag_sqlType = sqlBlock\n",
    "            Parms = Parms.replace(\"-s\",\" \")\n",
    "            \n",
    "        # Execute the SQL but return the results in an array (basically a two-dimensional array)\n",
    "        if Parms.find(\"-r\") >= 0:\n",
    "            flag_resultset = True\n",
    "            Parms = Parms.replace(\"-r\", \" \")\n",
    "                     \n",
    "        # Execute the SQL so that it behaves like an INSERT, DELETE, UPDATE or no result set\n",
    "        if Parms.find(\"-n\") >= 0:\n",
    "            flag_sqlType = db2Block\n",
    "            Parms = Parms.replace(\"-n\",\" \")\n",
    "            \n",
    "        # Quiet execution (no errors or completed messages)\n",
    "        if Parms.find(\"-q\") >= 0:\n",
    "            flag_quiet = True\n",
    "            Parms = Parms.replace(\"-q\",\" \")\n",
    "\n",
    "        # Retrieve all rows (do not use the default limit)\n",
    "        if Parms.find(\"-a\") >= 0:\n",
    "            pandas.reset_option('max_rows')\n",
    "            Parms = Parms.replace(\"-a\",\" \")\n",
    "          \n",
    "        # Set the delimiter to @ instead of a semi-colon for procedures, triggers, and functions\n",
    "        if Parms.find(\"-d\") >= 0:\n",
    "            flag_delim = \"@\"\n",
    "            Parms = Parms.replace(\"-d\",\" \")        \n",
    "            \n",
    "        # Timer function (not that useful, but worth a try)\n",
    "        if Parms.find(\"-t\") >= 0:\n",
    "            flag_timer = True\n",
    "            Parms = Parms.replace(\"-t\",\" \")\n",
    "          \n",
    "        # Plot functions -pb = bar, -pp = pie, -pl = line\n",
    "        if Parms.find(\"-pb\") >= 0:\n",
    "            flag_plot = 1\n",
    "            Parms = Parms.replace(\"-pb\",\" \")\n",
    "          \n",
    "        if Parms.find(\"-pp\") >= 0:\n",
    "            flag_plot = 2\n",
    "            Parms = Parms.replace(\"-pp\",\" \")\n",
    "                                \n",
    "        if Parms.find(\"-pl\") >= 0:\n",
    "            flag_plot = 3\n",
    "            Parms = Parms.replace(\"-pl\",\" \") \n",
    "            \n",
    "        if Parms.find(\"-i\") >= 0:\n",
    "            flag_plot = 4\n",
    "            Parms = Parms.replace(\"-i\",\" \")             \n",
    "      \n",
    "        remainder = Parms.strip()\n",
    "                    \n",
    "        # Split the line according to your delimiter\n",
    "            \n",
    "        if cell is None:\n",
    "            sqlLines = [remainder]\n",
    "            flag_cell = False\n",
    "        else:\n",
    "            cell = re.sub('.*?--.*$',\"\",cell,flags=re.M)\n",
    "            remainder = cell.replace(\"\\n\",\" \")\n",
    "            sqlLines = remainder.split(flag_delim)\n",
    "            flag_cell = True\n",
    "                      \n",
    "        # For each line figure out if you run it as a command (db2) or select (sql)\n",
    "         \n",
    "        for sql in sqlLines:\n",
    "\n",
    "            # Split the line so we know what the first keyword is. We only look at the first one. There may\n",
    "            # be SQL that returns output that we may not know about\n",
    "            \n",
    "            keywords = sql.split()\n",
    "            if len(keywords) == 0: continue\n",
    "            \n",
    "            sqlcmd = keywords[0].upper()\n",
    "            \n",
    "            if (flag_timer == True):\n",
    "                    \n",
    "                count = sqlTimer(flag_sqlType, sql)\n",
    "                 \n",
    "                if flag_quiet == False and count != -1:\n",
    "                    print(\"Total iterations in %s second(s): %s\" % (runtime,count))\n",
    "                    \n",
    "                return(count)\n",
    "            \n",
    "            elif (flag_plot != 0):\n",
    "                \n",
    "                try:\n",
    "                    df = pandas.read_sql(sql,hstmt)\n",
    "                except Exception as err:\n",
    "                    db2_error(False)\n",
    "                    return\n",
    "                \n",
    "                if flag_plot == 4:\n",
    "                    \n",
    "                    ShellAccess.pdf = df\n",
    "                    display(pdf)\n",
    "\n",
    "                    return\n",
    "                \n",
    "                plt.style.use('ggplot')\n",
    "                plt.figure()\n",
    "                col_count = len(df.columns)\n",
    "                \n",
    "                if flag_plot == 1:\n",
    "\n",
    "                    # Bar Chart\n",
    "                    if (col_count >= 2):\n",
    "                        xlabel = df.columns.values[0]\n",
    "                        ylabel = df.columns.values[1]\n",
    "                        _ = df.plot(kind='bar',x=xlabel,y=ylabel);\n",
    "                    else:\n",
    "                        _ = df.plot(kind='bar');\n",
    "                        \n",
    "                    \n",
    "                elif flag_plot == 2:\n",
    "                    \n",
    "                    # Pie \n",
    "                    if (col_count >= 2):\n",
    "                        xlabel = df.columns.values[0]\n",
    "                        xname = df[xlabel].tolist()\n",
    "                        yname = df.columns.values[1]\n",
    "                        _ = df.plot(kind='pie',y=yname,labels=xname);\n",
    "                    else:\n",
    "                        yname = df.columns.values[0]\n",
    "                        _ = df.plot(kind='pie',y=yname);\n",
    "                    \n",
    "                elif flag_plot == 3:\n",
    "                    \n",
    "                    # Line Chart\n",
    "                    if (col_count >= 2):                    \n",
    "                        xlabel = df.columns.values[0]\n",
    "                        ylabel = df.columns.values[1]\n",
    "                        _ = df.plot(kind='line',x=xlabel,y=ylabel) ;                   \n",
    "                    else:\n",
    "                        _ = df.plot(kind='line') ;                      \n",
    "                        \n",
    "                else:\n",
    "                    return\n",
    "                \n",
    "                plt.show()\n",
    "                return\n",
    " \n",
    "            elif (flag_sqlType == sqlBlock) or (sqlcmd in select and flag_sqlType != db2Block):\n",
    "                \n",
    "                if flag_json == True:\n",
    "                    try: \n",
    "                        stmt = ibm_db.exec_immediate(hdbc,sql);\n",
    "                        row_count = 0\n",
    "                        while( ibm_db.fetch_row(stmt) ):\n",
    "                            row_count = row_count + 1\n",
    "                            jsonVal = ibm_db.result(stmt,0)\n",
    "                            formatted_JSON = json.dumps(json.loads(jsonVal), indent=4, separators=(',', ': '))\n",
    "                        \n",
    "                            # Print JSON Structure\n",
    "                        \n",
    "                            if row_count > 1: print()\n",
    "                            print(\"Row: %d\" % row_count)\n",
    "                            print(formatted_JSON)\n",
    "                            flag_output = True\n",
    "                \n",
    "                    except Exception as err:\n",
    "                        db2_error(flag_quiet)\n",
    "                    \n",
    "                else:  \n",
    "                    if flag_resultset == True:\n",
    "                        row_count = 0\n",
    "                        resultSet = []\n",
    "                        try:\n",
    "                            stmt = ibm_db.exec_immediate(hdbc,sql)\n",
    "                            result = ibm_db.fetch_tuple(stmt)\n",
    "                            while (result):\n",
    "                                row = []\n",
    "                                for col in result:\n",
    "                                    row.append(col)\n",
    "                            \n",
    "                                resultSet.append(row)\n",
    "                                result = ibm_db.fetch_tuple(stmt)\n",
    "                            \n",
    "                            return(resultSet)                                    \n",
    "                                \n",
    "                        except Exception as err:\n",
    "                                db2_error(False) \n",
    "                        \n",
    "                    else:\n",
    "                        try:\n",
    "                        \n",
    "                            dp = pandas.read_sql(sql, hstmt)\n",
    "                            if flag_dataframe == True:\n",
    "                                return(dp)\n",
    "                            else:\n",
    "                                # pDisplay(dp)\n",
    "                                flag_output = True\n",
    "                                return(dp)\n",
    "                \n",
    "                        except Exception as err:\n",
    "                            db2_error(flag_quiet)\n",
    "                \n",
    "            else:\n",
    "                \n",
    "                try: \n",
    "                    ibm_db.exec_immediate(hdbc,sql);\n",
    "                    if flag_cell == False and flag_quiet == False:\n",
    "                        print(\"Command completed.\")\n",
    "                \n",
    "                except Exception as err:\n",
    "                    db2_error(flag_quiet)\n",
    "                    \n",
    "        if flag_cell == True and flag_output == False:\n",
    "            print(\"Command completed.\")\n",
    "            \n",
    "# Register the Magic extension in Jupyter    \n",
    "ip = get_ipython()          \n",
    "ip.register_magics(DB2)\n",
    "load_settings()\n",
    "success(\"DB2 Extensions Loaded.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set the table formatting to left align a table in a cell. By default, tables are centered in a cell. Remove this cell if you don't want to change Jupyter notebook formatting for tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%html\n",
    "<style>\n",
    "  table {margin-left: 0 !important; text-align: left;}\n",
    "</style>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Credits: IBM 2017, George Baklarz [baklarz@ca.ibm.com]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "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.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}