db2.ipynb

{
 "cells": [
  {
   "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",
    "- -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",
    "- -i - Interactive plot mode\n",
    "- -sampledata - Create and load the EMPLOYEE and DEPARTMENT tables\n",
    "\n",
    "<p>\n",
    "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}'\n",
    "</pre>\n",
    "\n",
    "The other option is to use parameter markers. What you would need to do is use the name of the variable with a colon in front of it and the program will prepare the statement and then pass the variable to Db2 when the statement is executed. This allows you to create complex strings that might contain quote characters and other special characters and not have to worry about enclosing the string with the correct quotes. Note that you do not place the quotes around the variable even though it is a string.\n",
    "\n",
    "<pre>\n",
    "empno = '000020'\n",
    "%sql SELECT LASTNAME FROM EMPLOYEE WHERE EMPNO=:empno\n",
    "</pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Install Db2 Python Driver\n",
    "If the ibm_db driver is not installed on your system, the subsequent Db2 commands will fail. In order to check that the Db2 driver is installed, and to update the plotting library, change the %run db2.ipynb command to include the -update flag after the file name. This will force the checking of the ibm_db library and then update the pixiedust library."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sys\n",
    "if (len(sys.argv) > 1):\n",
    "    if (sys.argv[1] == \"-update\"):\n",
    "        !pip install --user ibm_db\n",
    "        !pip install --user --upgrade pixiedust"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Db2 Jupyter Extensions\n",
    "This section of code has the import statements and global variables defined for the remainder of the functions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "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 2018: George Baklarz\n",
    "# Version 2018-02-26\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",
    "_hdbi = None\n",
    "_runtime = 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL Help\n",
    "\n",
    "The calling format of this routine is:\n",
    "\n",
    "```\n",
    "sqlhelp()\n",
    "```\n",
    "\n",
    "This code displays help related to the %sql magic command. This help is displayed when you issue a %sql or %%sql command by itself, or use the %sql -h flag."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "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}a{ed}\n",
    "           {sd}Return all rows in answer set and do not limit display{ed}\n",
    "        {er}       \n",
    "        {sr}\n",
    "          {sd}d{ed}\n",
    "          {sd}Change SQL delimiter to \"@\" from \";\"{ed}\n",
    "        {er}\n",
    "        {sr}\n",
    "          {sd}h{ed}\n",
    "          {sd}Display %sql help information.{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}j{ed}\n",
    "          {sd}Create a pretty JSON representation. Only the first column is formatted{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}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}q{ed}\n",
    "          {sd}Quiet results - no answer set or messages returned from the function{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}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}sampledata{ed}\n",
    "          {sd}Create and load the EMPLOYEE and DEPARTMENT tables{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",
    "       </table>\n",
    "       \"\"\"\n",
    "    \n",
    "    helpSQL = helpSQL.format(**locals())\n",
    "        \n",
    "    pDisplay(pHTML(helpSQL))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connection Help\n",
    "\n",
    "The calling format of this routine is:\n",
    "\n",
    "```\n",
    "connected_help()\n",
    "```\n",
    "\n",
    "This code displays help related to the CONNECT command. This code is displayed when you issue a %sql CONNECT command with no arguments or you are running a SQL statement and there isn't any connection to a database yet."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "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>A CONNECT command without any parameters will attempt to re-connect to the previous database you \n",
    "       were using. If the connection could not be established, 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}Database{ed}{sd}SAMPLE{ed}\n",
    "       {er}\n",
    "       {sr}\n",
    "         {sd}Hostname{ed}\n",
    "         {sd}The default is localhost, but usually you need to supply the host name or IP address. If you are running in a Docker environment you can specify the host IP address\n",
    "             with just #x:yyyy where x represents the last number of the Docker container address (which is usually\n",
    "             172.17.0.x). If you do not supply the port number, the default will be 50000.{ed}\n",
    "       {er}\n",
    "       {sr}\n",
    "         {sd}PORT{ed}\n",
    "         {sd}50000 (the default Db2 port){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))  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Prompt for Connection Information\n",
    "If you are running an SQL statement and have not yet connected to a database, the %sql command will prompt you for connection information. In order to connect to a database, you must supply:\n",
    "\n",
    "- Database name \n",
    "- Host name (IP address or name)\n",
    "- Port number\n",
    "- Userid\n",
    "- Password\n",
    "- Maximum number of rows\n",
    "\n",
    "If you are running in a Docker container, the Host Name and port can be specified as #x:value where x is the last digits of the Docker IP address (which is usually 172.17.0.x) and value is the port number. If you specify #2 for instance, the IP address that will be generated is 172.17.0.2 and the default port is 50000.\n",
    "\n",
    "The routine is called without any parameters:\n",
    "\n",
    "```\n",
    "connected_prompt()\n",
    "```\n",
    "\n",
    "The split_ipport(in_port) function takes as its argument the string that contains either the IP address with a port number in the form xxx.xxx.xxx.xxx:yyyy (for a non-Docker host) or #x or #x:yyyy for a Docker host.\n",
    "\n",
    "```\n",
    "split_ipport(in_port)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Prompt for Connection information\n",
    "\n",
    "def connected_prompt():\n",
    "    \n",
    "    global _settings\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",
    "# Split port and IP addresses\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"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect Syntax Parser\n",
    "The parseConnect routine is used to parse the CONNECT command that the user issued within the %sql command. The format of the command is:\n",
    "\n",
    "```\n",
    "parseConnect(inSQL)\n",
    "```\n",
    "\n",
    "The inSQL string contains the CONNECT keyword with some additional parameters. The format of the CONNECT command is one of:\n",
    "\n",
    "```\n",
    "CONNECT RESET\n",
    "CONNECT TO database USER userid USING password HOST hostname PORT portnumber\n",
    "```\n",
    "\n",
    "A shortform of HOST/PORT is available so that you can specify either xxx.xxx.xxx.xxx:yyyy for the IP address and port number or #x:yyyy if you are running in a Docker environment. In a Docker environment, the IP addresses are typically 172.17.0.x so you only need to supply the last \"x\" value and leave yyyy blank if the Db2 port is 50000.\n",
    "\n",
    "In addition, supplying a question mark (?) for password will result in the program prompting you for the password rather than having it as clear text in your scripts.\n",
    "\n",
    "When all of the information is checked in the command, the db2_doConnect function is called to actually do the connection to the database.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 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",
    "                hostport = cParms[cnt+1].upper()\n",
    "                ip, port = split_ipport(hostport)\n",
    "                if (port == None): _settings[\"port\"] = \"50000\"\n",
    "                _settings[\"hostname\"] = ip\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()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to Db2\n",
    "The db2_doConnect routine is called when a connection needs to be established to a Db2 database. The command does not require any parameters since it relies on the settings variable which contains all of the information it needs to connect to a Db2 database.\n",
    "\n",
    "```\n",
    "db2_doConnect()\n",
    "```\n",
    "\n",
    "There are 4 additional variables that are used throughout the routines to stay connected with the Db2 database. These variables are:\n",
    "- hdbc - The connection handle to the database\n",
    "- hstmt - A statement handle used for executing SQL statements\n",
    "- connected - A flag that tells the program whether or not we are currently connected to a database\n",
    "- runtime - Used to tell %sql the length of time (default 1 second) to run a statement when timing it\n",
    "\n",
    "The only database driver that is used in this program is the IBM DB2 ODBC DRIVER. This driver needs to be loaded on the system that is connecting to Db2. The Jupyter notebook that is built by this system installs the driver for you so you shouldn't have to do anything other than build the container.\n",
    "\n",
    "If the connection is successful, the connected flag is set to True. Any subsequent %sql call will check to see if you are connected and initiate another prompted connection if you do not have a connection to a database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Connect to DB2 and prompt if you haven't set any of the values yet\n",
    "\n",
    "def db2_doConnect():\n",
    "    \n",
    "    global _hdbc, _hdbi, _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",
    "        _hdbi = 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",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load/Save Settings\n",
    "\n",
    "There are two routines that load and save settings between Jupyter notebooks. These routines are called without any parameters.\n",
    "\n",
    "```\n",
    "load_settings() save_settings()\n",
    "```\n",
    "\n",
    "There is a global structure called settings which contains the following fields:\n",
    "\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",
    "\n",
    "The information in the settings structure is used for re-connecting to a database when you start up a Jupyter notebook. When the session is established for the first time, the load_settings() function is called to get the contents of the pickle file (db2connect.pickle, a Jupyter session file) that will be used for the first connection to the database. Whenever a new connection is made, the file is updated with the save_settings() function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "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  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Error and Message Functions\n",
    "\n",
    "There are three types of messages that are thrown by the %db2 magic command. The first routine will print out a success message with no special formatting:\n",
    "\n",
    "```\n",
    "success(message)\n",
    "```\n",
    "\n",
    "The second message is used for displaying an error message that is not associated with a SQL error. This type of error message is surrounded with a red box to highlight the problem. Note that the success message has code that has been commented out that could also show a successful return code with a green box. \n",
    "\n",
    "```\n",
    "errormsg(message)\n",
    "```\n",
    "\n",
    "The final error message is based on an error occuring in the SQL code that was executed. This code will parse the message returned from the ibm_db interface and parse it to return only the error message portion (and not all of the wrapper code from the driver).\n",
    "\n",
    "```\n",
    "db2_error(quiet)\n",
    "```\n",
    "\n",
    "The quiet flag is passed to the db2_error routine so that messages can be suppressed if the user wishes to ignore them with the -q flag. A good example of this is dropping a table that does not exist. We know that an error will be thrown so we can ignore it. The information that the db2_error routine gets is from the stmt_errormsg() function from within the ibm_db driver. The db2_error function should only be called after a SQL failure otherwise there will be no diagnostic information returned from stmt_errormsg()."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 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",
    "    if (message != \"\"):\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",
    "    if (message != \"\"):\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     "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL Timer\n",
    "\n",
    "The calling format of this routine is:\n",
    "\n",
    "```\n",
    "count = sqlTimer(hdbc, runtime, inSQL)\n",
    "```\n",
    "\n",
    "This code runs the SQL string multiple times for one second (by default). The accuracy of the clock is not that great when you are running just one statement, so instead this routine will run the code multiple times for a second to give you an execution count. If you need to run the code for more than one second, the runtime value needs to be set to the number of seconds you want the code to run.\n",
    "\n",
    "The return result is always the number of times that the code executed. Note, that the program will skip reading the data if it is a SELECT statement so it doesn't included fetch time for the answer set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Run a command for one second to see how many times we execute it and return the count\n",
    "\n",
    "def sqlTimer(hdbc, runtime, inSQL, parms):\n",
    "    \n",
    "    count = 0\n",
    "    t_end = time.time() + runtime\n",
    "    \n",
    "    while time.time() < t_end:\n",
    "        \n",
    "        try:\n",
    "            if (len(parms) > 0):\n",
    "                stmt = ibm_db.prepare(hdbc,inSQL)\n",
    "                rc = ibm_db.execute(stmt,tuple(parms))\n",
    "                if (rc == False):\n",
    "                    print(\"SQL Execution error during timing.\")\n",
    "                    return(-1)\n",
    "            else:\n",
    "                stmt = ibm_db.exec_immediate(hdbc,inSQL) \n",
    "                \n",
    "            ibm_db.free_result(stmt)\n",
    "            \n",
    "        except Exception as err:\n",
    "            print(err)\n",
    "            db2_error(False)\n",
    "            return(-1)\n",
    "        \n",
    "        count = count + 1\n",
    "                    \n",
    "    return(count)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL Parser\n",
    "\n",
    "The calling format of this routine is:\n",
    "\n",
    "```\n",
    "sql_cmd, parameter_list, encoded_sql = sqlParser(sql_input)\n",
    "```\n",
    "\n",
    "This code will look at the SQL string that has been passed to it and parse it into four values:\n",
    "- sql_cmd: First command in the list (so this may not be the actual SQL command)\n",
    "- parameter_list: the values of the parameters that need to passed to the execute/pandas code\n",
    "- encoded_sql: SQL with the parameters removed if there are any (replaced with ? markers)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def sqlParser(sqlin):\n",
    "       \n",
    "    sql_cmd = \"\"\n",
    "    parameter_list = []\n",
    "    encoded_sql = sqlin\n",
    "    \n",
    "    firstCommand = \"(?:^\\s*)([a-zA-Z]+)(?:\\s+.*|$)\"\n",
    "    \n",
    "    findFirst = re.match(firstCommand,sqlin)\n",
    "    \n",
    "    if (findFirst == None): # We did not find a match so we just return the empty string\n",
    "        return sql_cmd, parameter_list, encoded_sql\n",
    "    \n",
    "    cmd = findFirst.group(1)\n",
    "    sql_cmd = cmd.upper()\n",
    "\n",
    "    #\n",
    "    # Scan the input string looking for variables in the format :var. If no : is found just return.\n",
    "    # Var must be alpha+number+_ to be valid\n",
    "    #\n",
    "    \n",
    "    if (':' not in sqlin): # A quick check to see if parameters are in here, but not fool-proof!         \n",
    "        return sql_cmd, parameter_list, encoded_sql    \n",
    "    \n",
    "    quoteChar = \"\"\n",
    "    inQuote = False\n",
    "    inVar = False \n",
    "    varName = \"\"\n",
    "    encoded_sql = \"\"\n",
    "    \n",
    "    for ch in sqlin:\n",
    "        if (inVar == True): # We are collecting the name of a variable\n",
    "            if (ch.upper() in \"_ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789\"):\n",
    "                varName = varName + ch\n",
    "                continue\n",
    "            else:\n",
    "                parameter_list.append(getContents(varName))\n",
    "                varName = \"\"\n",
    "                inVar = False\n",
    "               \n",
    "        if (ch == \"\\\"\" or ch == \"\\'\"): # Do we have a quote\n",
    "            if (quoteChar == \"\"):\n",
    "                quoteChar = ch\n",
    "                inQuote = True\n",
    "            else:\n",
    "                if (quoteChar == ch): # Check which quote we found to avoid quote in quote situations\n",
    "                    inQuote = False\n",
    "                    quoteChar = \"\"\n",
    "            encoded_sql = encoded_sql + ch \n",
    "        elif (ch == \":\" and inQuote == False): # This might be a variable\n",
    "             varName = \"\"\n",
    "             inVar = True\n",
    "             encoded_sql = encoded_sql + \"?\"\n",
    "        else:\n",
    "             encoded_sql = encoded_sql + ch\n",
    "        \n",
    "    # We close a quoted string if you forgot it\n",
    "    \n",
    "    if (inQuote == True):\n",
    "        encoded_sql = encoded_sql + quoteChar\n",
    "        \n",
    "    if (inVar == True):\n",
    "        parameter_list.append(getContents(varName))\n",
    "        \n",
    "    return sql_cmd, parameter_list, encoded_sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Variable Contents Function\n",
    "The calling format of this routine is:\n",
    "\n",
    "```\n",
    "value = getContents(varName)\n",
    "```\n",
    "\n",
    "This code will take the name of a variable as input and return the contents of that variable. If the variable is not found then the program will return None which is the equivalent to empty or null. Note that this function looks at the global variable pool for Python so it is possible that the wrong version of variable is returned if it is used in different functions. For this reason, any variables used in SQL statements should use a unique namimg convention if possible."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def getContents(varName):\n",
    "    \n",
    "    #\n",
    "    # Get the contents of the variable name that is passed to the routine\n",
    "    #\n",
    "    \n",
    "    varValue = None\n",
    "    \n",
    "    if (varName in globals()): # Does the variable exist?\n",
    "        temp_global = globals().copy()\n",
    "        varValue = temp_global[varName]\n",
    "   \n",
    "    return(varValue)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create the SAMPLE Database Tables\n",
    "The calling format of this routine is:\n",
    "\n",
    "```\n",
    "db2_create_sample(quiet)\n",
    "```\n",
    "\n",
    "There are a lot of examples that depend on the data within the SAMPLE database. If you are running these examples and the connection is not to the SAMPLE database, then this code will create the two (EMPLOYEE, DEPARTMENT) tables that are used by most examples. If the function finds that these tables already exist, then nothing is done. If the tables are missing then they will be created with the same data as in the SAMPLE database.\n",
    "\n",
    "The quiet flag tells the program not to print any messages when the creation of the tables is complete."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def db2_create_sample(quiet):\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",
    "    if (quiet == False): success(\"Sample tables [EMPLOYEE, DEPARTMENT] created.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check option\n",
    "This function will return the original string with the option removed, and a flag or true or false of the value is found.\n",
    "\n",
    "```\n",
    "args, flag = checkOption(option_string, option, false_value, true_value)\n",
    "```\n",
    "\n",
    "Options are specified with a -x where x is the character that we are searching for. It may actually be more than one character long like -pb/-pi/etc... The false and true values are optional. By default these are the boolean values of T/F but for some options it could be a character string like ';' versus '@' for delimiters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def checkOption(args_in, option, vFalse=False, vTrue=True):\n",
    "    \n",
    "    args_out = args_in.strip()\n",
    "    found = vFalse\n",
    "    \n",
    "    if (args_out != \"\"):\n",
    "        if (args_out.find(option) >= 0):\n",
    "            args_out = args_out.replace(option,\" \")\n",
    "            args_out = args_out.strip()\n",
    "            found = vTrue\n",
    "\n",
    "    return args_out, found"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Plot Data\n",
    "This function will plot the data that is returned from the answer set. The plot value determines how we display the data. 1=Bar, 2=Pie, 3=Line, 4=Interactive.\n",
    "\n",
    "```\n",
    "plotData(flag_plot, hdbi, sql, parms)\n",
    "```\n",
    "\n",
    "The hdbi is the ibm_db_sa handle that is used by pandas dataframes to run the sql. The parms contains any of the parameters required to run the query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def plotData(flag_plot, hdbi, sql, parms):\n",
    "       \n",
    "    try:\n",
    "        if (len(parms) != 0):                             # There are parameters we need to take care of\n",
    "            df = pandas.read_sql(sql,hdbi,params=parms)\n",
    "        else:\n",
    "            df = pandas.read_sql(sql,hdbi)\n",
    "          \n",
    "    except Exception as err:\n",
    "        db2_error(False)\n",
    "        return\n",
    "                \n",
    "    if flag_plot == 4:                                    # Plot 4 = pixiedust\n",
    "        ShellAccess.pdf = df\n",
    "        _ = display(pdf)\n",
    "        return\n",
    "\n",
    "    col_count = len(df.columns)\n",
    "                \n",
    "    if flag_plot == 1:                                    # Plot 1 = bar chart\n",
    "\n",
    "        if (col_count in (1,2,3)):\n",
    "            plt.style.use('ggplot');\n",
    "            plt.figure();   \n",
    "            \n",
    "            if (col_count == 1):\n",
    "                df.index = df.index + 1\n",
    "                _ = df.plot(kind='bar');\n",
    "            elif (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",
    "                values = df.columns.values[2]\n",
    "                columns = df.columns.values[0]\n",
    "                index = df.columns.values[1]\n",
    "                pivoted = pandas.pivot_table(df, values=values, columns=columns, index=index) \n",
    "                _ = pivoted.plot.bar(); \n",
    "                \n",
    "            plt.show();\n",
    "            \n",
    "        else:\n",
    "            ShellAccess.pdf = df\n",
    "            _ = pDisplay(pdf);\n",
    "                    \n",
    "    elif flag_plot == 2:                                  # Plot 2 = pie chart\n",
    "        \n",
    "        if (col_count in (1,2)):\n",
    "            plt.style.use('ggplot');\n",
    "            plt.figure();   \n",
    "                \n",
    "            if (col_count == 1):\n",
    "                df.index = df.index + 1\n",
    "                yname = df.columns.values[0]\n",
    "                _ = df.plot(kind='pie',y=yname);                \n",
    "            else:          \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",
    "                \n",
    "            plt.show();\n",
    "            \n",
    "        else:\n",
    "            ShellAccess.pdf = df\n",
    "            _ = pDisplay(pdf);\n",
    "                    \n",
    "    elif flag_plot == 3:                                  # Plot 3 = line chart\n",
    "            \n",
    "        if (col_count in (1,2,3)):\n",
    "            plt.style.use('ggplot');\n",
    "            plt.figure();   \n",
    "            \n",
    "            if (col_count == 1):\n",
    "                df.index = df.index + 1  \n",
    "                _ = df.plot(kind='line');          \n",
    "            elif (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",
    "                values = df.columns.values[2]\n",
    "                columns = df.columns.values[0]\n",
    "                index = df.columns.values[1]\n",
    "                pivoted = pandas.pivot_table(df, values=values, columns=columns, index=index)\n",
    "                _ = pivoted.plot();\n",
    "                \n",
    "            plt.show();\n",
    "                \n",
    "        else:\n",
    "            ShellAccess.pdf = df\n",
    "            _ = pDisplay(pdf);\n",
    "    else:\n",
    "        return"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Main %sql Magic Definition\n",
    "The main %sql Magic logic is found in this section of code. This code will register the Magic command and allow Jupyter notebooks to interact with Db2 by using this extension.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "@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, _hdbi, _connected, _runtime\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_quiet = False\n",
    "        flag_json = False\n",
    "        flag_timer = False\n",
    "        flag_plot = 0\n",
    "        flag_cell = False\n",
    "        flag_output = False\n",
    "        flag_raw = False\n",
    "        flag_dataframe = False\n",
    "        flag_variables = False \n",
    "        \n",
    "        # The parameters must be in the line, not in the cell i.e. %sql -c \n",
    "        \n",
    "        allSQL = line.strip()\n",
    "        \n",
    "        if len(allSQL) == 0:\n",
    "            if cell == None: \n",
    "                sqlhelp()\n",
    "                return\n",
    "            if len(cell.strip()) == 0: \n",
    "                sqlhelp()\n",
    "                return\n",
    "            \n",
    "        if allSQL == \"?\" or allSQL == \"-h\":                             # Are you asking for help\n",
    "            sqlhelp()\n",
    "            return\n",
    "        \n",
    "        if allSQL.upper() == \"? CONNECT\":                              # Are you asking for help on CONNECT\n",
    "            connected_help()\n",
    "            return\n",
    "        \n",
    "        sqltype, _, _ = sqlParser(allSQL)                              # If this is a CONNECT command, run it alone\n",
    "        if (sqltype == \"CONNECT\"):\n",
    "            parseConnect(allSQL)\n",
    "            return\n",
    "        \n",
    "        if (_connected == False):                                      # Check if you are connected \n",
    "            db2_doConnect()\n",
    "            if _connected == False: return\n",
    "            \n",
    "        if _settings[\"maxrows\"] == -1:                                 # Set the return result size\n",
    "            pandas.reset_option('max_rows')\n",
    "        else:\n",
    "            pandas.options.display.max_rows = _settings[\"maxrows\"]\n",
    "      \n",
    "    \n",
    "        allSQL, flag_json  = checkOption(allSQL,'-j')                 # Look for JSON formatting\n",
    "        allSQL, flag_raw   = checkOption(allSQL,'-r')                 # Place results into a 2-dim array\n",
    "        allSQL, flag_quiet = checkOption(allSQL,'-q')                 # No error messages produced\n",
    "        allSQL, flag_delim = checkOption(allSQL,'-d',';','@')         # Change the delimiter    \n",
    "        allSQL, flag_timer = checkOption(allSQL,'-t')                 # Timer flag  \n",
    "        allSQL, flag_plot  = checkOption(allSQL,'-pb',flag_plot,1)    # Bar chart\n",
    "        allSQL, flag_plot  = checkOption(allSQL,'-pp',flag_plot,2)    # Pie chart\n",
    "        allSQL, flag_plot  = checkOption(allSQL,'-pl',flag_plot,3)    # Line chart\n",
    "        allSQL, flag_plot  = checkOption(allSQL,'-i', flag_plot,4)    # Pixiedust chart        \n",
    "        \n",
    "        allSQL, flag       = checkOption(allSQL,'-a')                 # Change maximum number of rows returned\n",
    "        if (flag == True):\n",
    "            pandas.options.display.max_rows = None #reset_option('max_rows')\n",
    "\n",
    "        allSQL, flag       = checkOption(allSQL,'-sampledata')        # Create SAMPLE tables\n",
    "        if (flag == True):\n",
    "            db2_create_sample(flag_quiet)\n",
    "            return\n",
    "        \n",
    "        remainder = allSQL\n",
    "                               \n",
    "        if cell is None:                                              # Split the input (only if cell mode)\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 sqlin in sqlLines:                                        # Run each command\n",
    "            \n",
    "            sqlType, parms, sql = sqlParser(sqlin)                    # Parse the SQL\n",
    "            \n",
    "            if (sql == \"\"): continue\n",
    " \n",
    "            if (flag_timer == True):\n",
    "                cnt = sqlTimer(_hdbc, _runtime, sql, parms)            # Given the sql and parameters, clock the time\n",
    "                if (cnt >= 0): print(\"Total iterations in %s second(s): %s\" % (_runtime,cnt))                \n",
    "                return(cnt)\n",
    "            \n",
    "            elif (flag_plot != 0):                                    # We are plotting some results \n",
    "                \n",
    "                plotData(flag_plot,_hdbi, sql,parms)                   # Plot the data and return\n",
    "                return\n",
    " \n",
    "            else:\n",
    "                try:                                                  # See if we have an answer set\n",
    "                    stmt = ibm_db.prepare(_hdbc,sql)\n",
    "                    if (ibm_db.num_fields(stmt) == 0):                # No, so we just execute the code\n",
    "                        if (len(parms) == 0):                         # Check if parameters present\n",
    "                            result = ibm_db.execute(stmt)             # Run it\n",
    "                        else:                                         # Include parameters\n",
    "                            result = ibm_db.execute(stmt,tuple(parms))# Run the code and check it\n",
    "                        ibm_db.free_result(stmt)\n",
    "                        if (result == False):                         # Error executing the code\n",
    "                            db2_error(flag_quiet)\n",
    "                        continue                                      # Continue running\n",
    "                    \n",
    "                    elif (flag_raw == True or flag_json == True):           # Prefer the output as an array\n",
    "                        row_count = 0\n",
    "                        resultSet = []\n",
    "                        try:\n",
    "                            if (len(parms) == 0):                         # Check if parameters present\n",
    "                                result = ibm_db.execute(stmt)             # Run it\n",
    "                            else:                                         # Include parameters\n",
    "                                result = ibm_db.execute(stmt,tuple(parms))# Run the code and check it\n",
    "                            if (result == False):                         # Error executing the code\n",
    "                                db2_error(flag_quiet)  \n",
    "                                ibm_db.free_result(stmt)\n",
    "                                return\n",
    "                                \n",
    "                            if (flag_json == True):                        # JSON output\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",
    "                                    if row_count > 1: print()\n",
    "                                    print(\"Row: %d\" % row_count)\n",
    "                                    print(formatted_JSON)\n",
    "                                    flag_output = True                                    \n",
    "                                \n",
    "                                ibm_db.free_result(stmt)\n",
    "                                return\n",
    "                                \n",
    "                            else:\n",
    "                                result = ibm_db.fetch_tuple(stmt)\n",
    "                                while (result):\n",
    "                                    row = []\n",
    "                                    for col in result:\n",
    "                                        row.append(col)\n",
    "                                    resultSet.append(row)\n",
    "                                    result = ibm_db.fetch_tuple(stmt)\n",
    "                            \n",
    "                                ibm_db.free_result(stmt)\n",
    "                                return(resultSet)                                    \n",
    "                                \n",
    "                        except Exception as err:\n",
    "                            db2_error(flag_quiet)\n",
    "                            continue # return\n",
    "                    else:\n",
    "                        ibm_db.free_result(stmt)\n",
    "                        if (len(parms) > 0): \n",
    "                            dp = pandas.read_sql(sql, _hdbi ,params=parms)\n",
    "                        else:\n",
    "                            dp = pandas.read_sql(sql, _hdbi)\n",
    "                        flag_output = True\n",
    "                        if (cell == None):\n",
    "                            return(dp)\n",
    "                        else:\n",
    "                            pDisplay(dp)\n",
    "                        continue\n",
    " \n",
    "\n",
    "                except:\n",
    "                    db2_error(flag_quiet)\n",
    "                    continue # return\n",
    "                \n",
    "\n",
    "        if (flag_output == False and flag_quiet == False): 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 2018, 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
}