db2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DB2 Jupyter Notebook Extensions\n",
    "Version: 2021-07-13"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "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."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "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, -delim` - Change SQL delimiter to \"`@`\" from \"`;`\"\n",
    "   - `-q, -quiet` - Quiet results - no messages returned from the function\n",
    "   - `-r, -array` - 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` - Format the first character column of the result set as a JSON record\n",
    "   - `-json` - Return result set as an array of json records\n",
    "   - `-a, -all` - Return all rows in answer set and do not limit display\n",
    "   - `-grid` - Display the results in a scrollable grid\n",
    "   - `-pb, -bar` - Plot the results as a bar chart\n",
    "   - `-pl, -line` - Plot the results as a line chart\n",
    "   - `-pp, -pie` - Plot the results as a pie chart\n",
    "   - `-e, -echo` - Any macro expansions are displayed in an output box \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": [
    "## Development SQL\n",
    "The previous set of `%sql` and `%%sql` commands deals with SQL statements and commands that are run in an interactive manner. There is a class of SQL commands that are more suited to a development environment where code is iterated or requires changing input. The commands that are associated with this form of SQL are:\n",
    "- AUTOCOMMIT\n",
    "- COMMIT/ROLLBACK\n",
    "- PREPARE \n",
    "- EXECUTE\n",
    "\n",
    "Autocommit is the default manner in which SQL statements are executed. At the end of the successful completion of a statement, the results are commited to the database. There is no concept of a transaction where multiple DML/DDL statements are considered one transaction. The `AUTOCOMMIT` command allows you to turn autocommit `OFF` or `ON`. This means that the set of SQL commands run after the `AUTOCOMMIT OFF` command are executed are not commited to the database until a `COMMIT` or `ROLLBACK` command is issued.\n",
    "\n",
    "`COMMIT` (`WORK`) will finalize all of the transactions (`COMMIT`) to the database and `ROLLBACK` will undo all of the changes. If you issue a `SELECT` statement during the execution of your block, the results will reflect all of your changes. If you `ROLLBACK` the transaction, the changes will be lost.\n",
    "\n",
    "`PREPARE` is typically used in a situation where you want to repeatidly execute a SQL statement with different variables without incurring the SQL compilation overhead. For instance:\n",
    "```\n",
    "x = %sql PREPARE SELECT LASTNAME FROM EMPLOYEE WHERE EMPNO=?\n",
    "for y in ['000010','000020','000030']:\n",
    "    %sql execute :x using :y\n",
    "```\n",
    "`EXECUTE` is used to execute a previously compiled statement. \n",
    "\n",
    "To retrieve the error codes that might be associated with any SQL call, the following variables are updated after every call:\n",
    "\n",
    "* SQLCODE\n",
    "* SQLSTATE\n",
    "* SQLERROR - Full error message retrieved from Db2"
   ]
  },
  {
   "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 install the Db2 driver, issue the following command from a Jupyter notebook cell:\n",
    "```\n",
    "!pip install --user ibm_db\n",
    "```"
   ]
  },
  {
   "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": {},
   "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 2021: George Baklarz\n",
    "# Version 2021-07-13\n",
    "#\n",
    "\n",
    "from __future__ import print_function\n",
    "from IPython.display import HTML as pHTML, Image as pImage, display as pdisplay, Javascript as Javascript\n",
    "from IPython.core.magic import (Magics, magics_class, line_magic,\n",
    "                                cell_magic, line_cell_magic, needs_local_scope)\n",
    "import ibm_db\n",
    "import pandas\n",
    "import ibm_db_dbi\n",
    "import json\n",
    "import matplotlib\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",
    "\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "# Python Hack for Input between 2 and 3\n",
    "\n",
    "try: \n",
    "    input = raw_input \n",
    "except NameError: \n",
    "    pass \n",
    "\n",
    "_settings = {\n",
    "     \"maxrows\"  : 10,\n",
    "     \"maxgrid\"  : 5,\n",
    "     \"runtime\"  : 1,\n",
    "     \"display\"  : \"PANDAS\",\n",
    "     \"database\" : \"\",\n",
    "     \"hostname\" : \"localhost\",\n",
    "     \"port\"     : \"50000\",\n",
    "     \"protocol\" : \"TCPIP\",    \n",
    "     \"uid\"      : \"DB2INST1\",\n",
    "     \"pwd\"      : \"password\",\n",
    "     \"ssl\"      : \"\"\n",
    "}\n",
    "\n",
    "_environment = {\n",
    "     \"jupyter\"  : True,\n",
    "     \"qgrid\"    : True\n",
    "}\n",
    "\n",
    "_display = {\n",
    "    'fullWidthRows': True,\n",
    "    'syncColumnCellResize': True,\n",
    "    'forceFitColumns': False,\n",
    "    'defaultColumnWidth': 150,\n",
    "    'rowHeight': 28,\n",
    "    'enableColumnReorder': False,\n",
    "    'enableTextSelectionOnCells': True,\n",
    "    'editable': False,\n",
    "    'autoEdit': False,\n",
    "    'explicitInitialization': True,\n",
    "    'maxVisibleRows': 5,\n",
    "    'minVisibleRows': 5,\n",
    "    'sortable': True,\n",
    "    'filterable': False,\n",
    "    'highlightSelectedCell': False,\n",
    "    'highlightSelectedRow': True\n",
    "}\n",
    "\n",
    "# Connection settings for statements \n",
    "\n",
    "_connected = False\n",
    "_hdbc = None\n",
    "_hdbi = None\n",
    "_stmt = []\n",
    "_stmtID = []\n",
    "_stmtSQL = []\n",
    "_vars = {}\n",
    "_macros = {}\n",
    "_flags = []\n",
    "_debug = False\n",
    "\n",
    "# Db2 Error Messages and Codes\n",
    "sqlcode = 0\n",
    "sqlstate = \"0\"\n",
    "sqlerror = \"\"\n",
    "sqlelapsed = 0\n",
    "\n",
    "# Check to see if QGrid is installed\n",
    "\n",
    "try:\n",
    "    import qgrid\n",
    "    qgrid.set_defaults(grid_options=_display)\n",
    "except:\n",
    "    _environment['qgrid'] = False\n",
    "    \n",
    "# Check if we are running in iPython or Jupyter\n",
    "\n",
    "try:\n",
    "    if (get_ipython().config == {}): \n",
    "        _environment['jupyter'] = False\n",
    "        _environment['qgrid'] = False\n",
    "    else:\n",
    "        _environment['jupyter'] = True\n",
    "except:\n",
    "    _environment['jupyter'] = False\n",
    "    _environment['qgrid'] = False\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Options\n",
    "There are four options that can be set with the **`%sql`** command. These options are shown below with the default value shown in parenthesis.\n",
    "- **`MAXROWS n (10)`** - The maximum number of rows that will be displayed before summary information is shown. If the answer set is less than this number of rows, it will be completely shown on the screen. If the answer set is larger than this amount, only the first 5 rows and last 5 rows of the answer set will be displayed. If you want to display a very large answer set, you may want to consider using the grid option `-g` to display the results in a scrollable table. If you really want to show all results then setting MAXROWS to -1 will return all output.\n",
    "\n",
    "- **`MAXGRID n (5)`** - The maximum size of a grid display. When displaying a result set in a grid `-g`, the default size of the display window is 5 rows. You can set this to a larger size so that more rows are shown on the screen. Note that the minimum size always remains at 5 which means that if the system is unable to display your maximum row size it will reduce the table display until it fits.\n",
    "\n",
    "- **`DISPLAY PANDAS | GRID (PANDAS)`** - Display the results as a PANDAS dataframe (default) or as a scrollable GRID\n",
    "\n",
    "- **`RUNTIME n (1)`** - When using the timer option on a SQL statement, the statement will execute for **`n`** number of seconds. The result that is returned is the number of times the SQL statement executed rather than the execution time of the statement. The default value for runtime is one second, so if the SQL is very complex you will need to increase the run time.\n",
    "\n",
    "- **`LIST`** - Display the current settings\n",
    "\n",
    "To set an option use the following syntax:\n",
    "```\n",
    "%sql option option_name value option_name value ....\n",
    "```\n",
    "The following example sets all options:\n",
    "```\n",
    "%sql option maxrows 100 runtime 2 display grid maxgrid 10\n",
    "```\n",
    "The values will **not** be saved between Jupyter notebooks sessions. If you need to retrieve the current options values, use the LIST command as the only argument:\n",
    "```\n",
    "%sql option list\n",
    "```\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def setOptions(inSQL):\n",
    "\n",
    "    global _settings, _display\n",
    "\n",
    "    cParms = inSQL.split()\n",
    "    cnt = 0\n",
    "\n",
    "    while cnt < len(cParms):\n",
    "        if cParms[cnt].upper() == 'MAXROWS':\n",
    "            \n",
    "            if cnt+1 < len(cParms):\n",
    "                try:\n",
    "                    _settings[\"maxrows\"] = int(cParms[cnt+1])\n",
    "                except Exception as err:\n",
    "                    errormsg(\"Invalid MAXROWS value provided.\")\n",
    "                    pass\n",
    "                cnt = cnt + 1\n",
    "            else:\n",
    "                errormsg(\"No maximum rows specified for the MAXROWS option.\")\n",
    "                return\n",
    "            \n",
    "        elif cParms[cnt].upper() == 'MAXGRID':\n",
    "            \n",
    "            if cnt+1 < len(cParms):\n",
    "                try:\n",
    "                    maxgrid = int(cParms[cnt+1])\n",
    "                    if (maxgrid <= 5):                      # Minimum window size is 5\n",
    "                        maxgrid = 5\n",
    "                    _display[\"maxVisibleRows\"] =  int(cParms[cnt+1])\n",
    "                    try:\n",
    "                        import qgrid\n",
    "                        qgrid.set_defaults(grid_options=_display)\n",
    "                    except:\n",
    "                        _environment['qgrid'] = False\n",
    "                        \n",
    "                except Exception as err:\n",
    "                    errormsg(\"Invalid MAXGRID value provided.\")\n",
    "                    pass\n",
    "                cnt = cnt + 1\n",
    "            else:\n",
    "                errormsg(\"No maximum rows specified for the MAXROWS option.\")\n",
    "                return            \n",
    "            \n",
    "        elif cParms[cnt].upper() == 'RUNTIME':\n",
    "            if cnt+1 < len(cParms):\n",
    "                try:\n",
    "                    _settings[\"runtime\"] = int(cParms[cnt+1])\n",
    "                except Exception as err:\n",
    "                    errormsg(\"Invalid RUNTIME value provided.\")\n",
    "                    pass\n",
    "                cnt = cnt + 1\n",
    "            else:\n",
    "                errormsg(\"No value provided for the RUNTIME option.\")\n",
    "                return \n",
    "            \n",
    "        elif cParms[cnt].upper() == 'DISPLAY':\n",
    "            if cnt+1 < len(cParms):\n",
    "                if (cParms[cnt+1].upper() == 'GRID'):\n",
    "                    _settings[\"display\"] = 'GRID'\n",
    "                elif (cParms[cnt+1].upper()  == 'PANDAS'):\n",
    "                    _settings[\"display\"] = 'PANDAS'\n",
    "                else:\n",
    "                    errormsg(\"Invalid DISPLAY value provided.\")\n",
    "                cnt = cnt + 1\n",
    "            else:\n",
    "                errormsg(\"No value provided for the DISPLAY option.\")\n",
    "                return  \n",
    "        elif (cParms[cnt].upper() == 'LIST'):\n",
    "            print(\"(MAXROWS) Maximum number of rows displayed: \" + str(_settings[\"maxrows\"]))\n",
    "            print(\"(MAXGRID) Maximum grid display size: \" + str(_settings[\"maxgrid\"]))\n",
    "            print(\"(RUNTIME) How many seconds to a run a statement for performance testing: \" + str(_settings[\"runtime\"]))\n",
    "            print(\"(DISPLAY) Use PANDAS or GRID display format for output: \" + _settings[\"display\"]) \n",
    "            return\n",
    "        else:\n",
    "            cnt = cnt + 1\n",
    "            \n",
    "    save_settings()"
   ]
  },
  {
   "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": {},
   "outputs": [],
   "source": [
    "def sqlhelp():\n",
    "    \n",
    "    global _environment\n",
    "    \n",
    "    if (_environment[\"jupyter\"] == True):\n",
    "        sd  = '<td style=\"text-align:left;\">'\n",
    "        ed1 = '</td>'\n",
    "        ed2 = '</td>'\n",
    "        sh  = '<th style=\"text-align:left;\">'\n",
    "        eh1 = '</th>'\n",
    "        eh2 = '</th>'\n",
    "        sr  = '<tr>'\n",
    "        er  = '</tr>'\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{eh1}{sh}Description{eh2}\n",
    "         {er}\n",
    "         {sr}\n",
    "            {sd}a, all{ed1}{sd}Return all rows in answer set and do not limit display{ed2}\n",
    "         {er}       \n",
    "         {sr}\n",
    "           {sd}d{ed1}{sd}Change SQL delimiter to \"@\" from \";\"{ed2}\n",
    "         {er}\n",
    "         {sr}\n",
    "           {sd}e, echo{ed1}{sd}Echo the SQL command that was generated after macro and variable substituion.{ed2}\n",
    "         {er}\n",
    "         {sr}\n",
    "           {sd}h, help{ed1}{sd}Display %sql help information.{ed2}\n",
    "         {er}        \n",
    "         {sr}\n",
    "           {sd}j{ed1}{sd}Create a pretty JSON representation. Only the first column is formatted{ed2}\n",
    "         {er}\n",
    "         {sr}\n",
    "           {sd}json{ed1}{sd}Retrieve the result set as a JSON record{ed2}\n",
    "         {er} \n",
    "         {sr}\n",
    "           {sd}pb, bar{ed1}{sd}Plot the results as a bar chart{ed2}\n",
    "         {er}\n",
    "         {sr}\n",
    "           {sd}pl, line{ed1}{sd}Plot the results as a line chart{ed2}\n",
    "         {er}\n",
    "         {sr}\n",
    "           {sd}pp, pie{ed1}{sd}Plot Pie: Plot the results as a pie chart{ed2}\n",
    "         {er}        \n",
    "         {sr}\n",
    "           {sd}q, quiet{ed1}{sd}Quiet results - no answer set or messages returned from the function{ed2}\n",
    "         {er}\n",
    "         {sr}  \n",
    "           {sd}r, array{ed1}{sd}Return the result set as an array of values{ed2}\n",
    "         {er}\n",
    "         {sr}\n",
    "           {sd}sampledata{ed1}{sd}Create and load the EMPLOYEE and DEPARTMENT tables{ed2}\n",
    "         {er}        \n",
    "         {sr}\n",
    "           {sd}t,time{ed1}{sd}Time the following SQL statement and return the number of times it executes in 1 second{ed2}\n",
    "         {er}\n",
    "         {sr}\n",
    "           {sd}grid{ed1}{sd}Display the results in a scrollable grid{ed2}\n",
    "         {er}       \n",
    "        \n",
    "        </table>\n",
    "       \"\"\"        \n",
    "    else:\n",
    "        helpSQL = \"\"\"\n",
    "SQL Options\n",
    "\n",
    "The following options are available as part of a SQL statement. Options are always \n",
    "preceded with a minus sign (i.e. -q).\n",
    "\n",
    "Option     Description\n",
    "a, all     Return all rows in answer set and do not limit display \n",
    "d          Change SQL delimiter to \"@\" from \";\" \n",
    "e, echo    Echo the SQL command that was generated after substitution \n",
    "h, help    Display %sql help information\n",
    "j          Create a pretty JSON representation. Only the first column is formatted \n",
    "json       Retrieve the result set as a JSON record \n",
    "pb, bar    Plot the results as a bar chart \n",
    "pl, line   Plot the results as a line chart \n",
    "pp, pie    Plot Pie: Plot the results as a pie chart \n",
    "q, quiet   Quiet results - no answer set or messages returned from the function \n",
    "r, array   Return the result set as an array of values \n",
    "sampledata Create and load the EMPLOYEE and DEPARTMENT tables \n",
    "t,time     Time the SQL statement and return the execution count per second\n",
    "grid       Display the results in a scrollable grid \n",
    "       \"\"\"        \n",
    "    helpSQL = helpSQL.format(**locals())\n",
    "    \n",
    "    if (_environment[\"jupyter\"] == True):\n",
    "        pdisplay(pHTML(helpSQL))\n",
    "    else:\n",
    "        print(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": {},
   "outputs": [],
   "source": [
    "def connected_help():\n",
    "    \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",
    "    if (_environment['jupyter'] == True):\n",
    "        \n",
    "        helpConnect = \"\"\"\n",
    "       <h3>Connecting to Db2</h3> \n",
    "       <p>The CONNECT command has the following format:\n",
    "       <p>\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; &lt;SSL&gt;\n",
    "       %sql CONNECT CREDENTIALS &lt;varname&gt;\n",
    "       %sql CONNECT CLOSE\n",
    "       %sql CONNECT RESET\n",
    "       %sql CONNECT PROMPT - use this to be prompted for values\n",
    "       </pre>\n",
    "       <p>\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",
    "       The <b>CREDENTIALS</b> option allows you to use credentials that are supplied by Db2 on Cloud instances.\n",
    "       The credentials can be supplied as a variable and if successful, the variable will be saved to disk \n",
    "       for future use. If you create another notebook and use the identical syntax, if the variable \n",
    "       is not defined, the contents on disk will be used as the credentials. You should assign the \n",
    "       credentials to a variable that represents the database (or schema) that you are communicating with. \n",
    "       Using familiar names makes it easier to remember the credentials when connecting. \n",
    "       <p>\n",
    "       <b>CONNECT CLOSE</b> will close the current connection, but will not reset the database parameters. This means that\n",
    "       if you issue the CONNECT command again, the system should be able to reconnect you to the database.\n",
    "       <p>\n",
    "       <b>CONNECT RESET</b> will close the current connection and remove any information on the connection. You will need \n",
    "       to issue a new CONNECT statement with all of the connection information.\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 cancel the connection attempt, enter a blank value for any of the values. The connection \n",
    "       panel will request the following values in order to connect to Db2: \n",
    "       <table>\n",
    "       {sr}\n",
    "         {sh}Setting{eh}\n",
    "         {sh}Description{eh}\n",
    "       {er}\n",
    "       {sr}\n",
    "         {sd}Database{ed}{sd}Database name you want to connect to.{ed}\n",
    "       {er}\n",
    "       {sr}\n",
    "         {sd}Hostname{ed}\n",
    "         {sd}Use localhost if Db2 is running on your own machine, but this can be an IP address or host name. \n",
    "       {er}\n",
    "       {sr}\n",
    "         {sd}PORT{ed}\n",
    "         {sd}The port to use for connecting to Db2. This is usually 50000.{ed}\n",
    "       {er}\n",
    "       {sr}\n",
    "         {sd}SSL{ed}\n",
    "         {sd}If you are connecting to a secure port (50001) with SSL then you must include this keyword in the connect string.{ed}\n",
    "       {sr}         \n",
    "         {sd}Userid{ed}\n",
    "         {sd}The userid to use when connecting (usually 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",
    "        </table>\n",
    "       \"\"\"\n",
    "    else:\n",
    "        helpConnect = \"\"\"\\\n",
    "Connecting to Db2\n",
    "\n",
    "The CONNECT command has the following format:\n",
    "\n",
    "%sql CONNECT TO database USER userid USING password | ? \n",
    "                HOST ip address PORT port number SSL\n",
    "%sql CONNECT CREDENTIALS varname\n",
    "%sql CONNECT CLOSE\n",
    "%sql CONNECT RESET\n",
    "\n",
    "If you use a \"?\" for the password field, the system will prompt you for a password.\n",
    "This avoids typing the password as clear text on the screen. If a connection is \n",
    "not successful, the system will print the error message associated with the connect\n",
    "request.\n",
    "\n",
    "The CREDENTIALS option allows you to use credentials that are supplied by Db2 on \n",
    "Cloud instances. The credentials can be supplied as a variable and if successful, \n",
    "the variable will be saved to disk for future use. If you create another notebook\n",
    "and use the identical syntax, if the variable is not defined, the contents on disk\n",
    "will be used as the credentials. You should assign the credentials to a variable \n",
    "that represents the database (or schema) that you are communicating with. Using \n",
    "familiar names makes it easier to remember the credentials when connecting. \n",
    "\n",
    "CONNECT CLOSE will close the current connection, but will not reset the database \n",
    "parameters. This means that if you issue the CONNECT command again, the system \n",
    "should be able to reconnect you to the database.\n",
    "\n",
    "CONNECT RESET will close the current connection and remove any information on the\n",
    "connection. You will need to issue a new CONNECT statement with all of the connection\n",
    "information.\n",
    "\n",
    "If the connection is successful, the parameters are saved on your system and will be\n",
    "used the next time you run an SQL statement, or when you issue the %sql CONNECT \n",
    "command with no parameters. If you issue CONNECT RESET, all of the current values \n",
    "will be deleted and you will need to issue a new CONNECT statement. \n",
    "\n",
    "A CONNECT command without any parameters will attempt to re-connect to the previous \n",
    "database you were using. If the connection could not be established, the program to\n",
    "prompt you for the values. To cancel the connection attempt, enter a blank value for\n",
    "any of the values. The connection panel will request the following values in order \n",
    "to connect to Db2: \n",
    "\n",
    "  Setting    Description\n",
    "  Database   Database name you want to connect to\n",
    "  Hostname   Use localhost if Db2 is running on your own machine, but this can \n",
    "             be an IP address or host name. \n",
    "  PORT       The port to use for connecting to Db2. This is usually 50000. \n",
    "  Userid     The userid to use when connecting (usually DB2INST1) \n",
    "  Password   No password is provided so you have to enter a value\n",
    "  SSL        Include this keyword to indicate you are connecting via SSL (usually port 50001)\n",
    "\"\"\"\n",
    "    \n",
    "    helpConnect = helpConnect.format(**locals())\n",
    "    \n",
    "    if (_environment['jupyter'] == True):\n",
    "        pdisplay(pHTML(helpConnect))\n",
    "    else:\n",
    "        print(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",
    "- Secure socket\n",
    "\n",
    "The routine is called without any parameters:\n",
    "\n",
    "```\n",
    "connected_prompt()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prompt for Connection information\n",
    "\n",
    "def connected_prompt():\n",
    "    \n",
    "    global _settings\n",
    "    \n",
    "    _database = ''\n",
    "    _hostname = ''\n",
    "    _port = ''\n",
    "    _uid = ''\n",
    "    _pwd = ''\n",
    "    _ssl = ''\n",
    "    \n",
    "    print(\"Enter the database connection details (Any empty value will cancel the connection)\")\n",
    "    _database = input(\"Enter the database name: \");\n",
    "    if (_database.strip() == \"\"): return False\n",
    "    _hostname = input(\"Enter the HOST IP address or symbolic name: \");\n",
    "    if (_hostname.strip() == \"\"): return False \n",
    "    _port = input(\"Enter the PORT number: \");\n",
    "    if (_port.strip() == \"\"): return False    \n",
    "    _ssl = input(\"Is this a secure (SSL) port (y or n)\");\n",
    "    if (_ssl.strip() == \"\"): return False\n",
    "    if (_ssl == \"n\"):\n",
    "        _ssl = \"\"\n",
    "    else:\n",
    "        _ssl = \"Security=SSL;\" \n",
    "    _uid = input(\"Enter Userid on the DB2 system: \").upper();\n",
    "    if (_uid.strip() == \"\"): return False\n",
    "    _pwd = getpass.getpass(\"Password [password]: \");\n",
    "    if (_pwd.strip() == \"\"): return False\n",
    "        \n",
    "    _settings[\"database\"] = _database.strip()\n",
    "    _settings[\"hostname\"] = _hostname.strip()\n",
    "    _settings[\"port\"] = _port.strip()\n",
    "    _settings[\"uid\"] = _uid.strip()\n",
    "    _settings[\"pwd\"] = _pwd.strip()\n",
    "    _settings[\"ssl\"] = _ssl.strip()\n",
    "    _settings[\"maxrows\"] = 10\n",
    "    _settings[\"maxgrid\"] = 5\n",
    "    _settings[\"runtime\"] = 1\n",
    "    \n",
    "    return True\n",
    "    \n",
    "# Split port and IP addresses\n",
    "\n",
    "def split_string(in_port,splitter=\":\"):\n",
    " \n",
    "    # Split input into an IP address and Port number\n",
    "    \n",
    "    global _settings\n",
    "\n",
    "    checkports = in_port.split(splitter)\n",
    "    ip = checkports[0]\n",
    "    if (len(checkports) > 1):\n",
    "        port = checkports[1]\n",
    "    else:\n",
    "        port = None\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 CLOSE\n",
    "CONNECT CREDENTIALS <variable>\n",
    "CONNECT TO database USER userid USING password HOST hostname PORT portnumber <SSL>\n",
    "```\n",
    "\n",
    "If you have credentials available from Db2 on Cloud, place the contents of the credentials into a variable and then use the `CONNECT CREDENTIALS <var>` syntax to connect to the database.\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": {},
   "outputs": [],
   "source": [
    "# Parse the CONNECT statement and execute if possible \n",
    "\n",
    "def parseConnect(inSQL,local_ns):\n",
    "    \n",
    "    global _settings, _connected\n",
    "\n",
    "    _connected = False\n",
    "    \n",
    "    cParms = inSQL.split()\n",
    "    cnt = 0\n",
    "    \n",
    "    _settings[\"ssl\"] = \"\"\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() == \"SSL\":\n",
    "            _settings[\"ssl\"] = \"Security=SSL;\"  \n",
    "            cnt = cnt + 1\n",
    "        elif cParms[cnt].upper() == 'CREDENTIALS':\n",
    "            if cnt+1 < len(cParms):\n",
    "                credentials = cParms[cnt+1]\n",
    "                tempid = eval(credentials,local_ns)\n",
    "                if (isinstance(tempid,dict) == False): \n",
    "                    errormsg(\"The CREDENTIALS variable (\" + credentials + \") does not contain a valid Python dictionary (JSON object)\")\n",
    "                    return\n",
    "                if (tempid == None):\n",
    "                    fname = credentials + \".pickle\"\n",
    "                    try:\n",
    "                        with open(fname,'rb') as f: \n",
    "                            _id = pickle.load(f) \n",
    "                    except:\n",
    "                        errormsg(\"Unable to find credential variable or file.\")\n",
    "                        return\n",
    "                else:\n",
    "                    _id = tempid\n",
    "                    \n",
    "                try:\n",
    "                    _settings[\"database\"] = _id[\"db\"]\n",
    "                    _settings[\"hostname\"] = _id[\"hostname\"]\n",
    "                    _settings[\"port\"] = _id[\"port\"]\n",
    "                    _settings[\"uid\"] = _id[\"username\"]\n",
    "                    _settings[\"pwd\"] = _id[\"password\"]\n",
    "                    try:\n",
    "                        fname = credentials + \".pickle\"\n",
    "                        with open(fname,'wb') as f:\n",
    "                            pickle.dump(_id,f)\n",
    "            \n",
    "                    except:\n",
    "                        errormsg(\"Failed trying to write Db2 Credentials.\")\n",
    "                        return\n",
    "                except:\n",
    "                    errormsg(\"Credentials file is missing information. db/hostname/port/username/password required.\")\n",
    "                    return\n",
    "                     \n",
    "            else:\n",
    "                errormsg(\"No Credentials name supplied\")\n",
    "                return\n",
    "            \n",
    "            cnt = cnt + 1\n",
    "              \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_string(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() == 'PROMPT':\n",
    "            if (connected_prompt() == False): \n",
    "                print(\"Connection canceled.\")\n",
    "                return \n",
    "            else:\n",
    "                cnt = cnt + 1\n",
    "        elif cParms[cnt].upper() in ('CLOSE','RESET') :\n",
    "            try:\n",
    "                result = ibm_db.close(_hdbc)\n",
    "                _hdbi.close()\n",
    "            except:\n",
    "                pass\n",
    "            success(\"Connection closed.\")          \n",
    "            if cParms[cnt].upper() == 'RESET': \n",
    "                _settings[\"database\"] = ''\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": {},
   "outputs": [],
   "source": [
    "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",
    "            return False\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};{5}\").format(_settings[\"database\"], \n",
    "                                 _settings[\"hostname\"], \n",
    "                                 _settings[\"port\"], \n",
    "                                 _settings[\"uid\"], \n",
    "                                 _settings[\"pwd\"],\n",
    "                                 _settings[\"ssl\"])\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",
    "        db2_error(False,True) # errormsg(str(err))\n",
    "        _connected = False\n",
    "        _settings[\"database\"] = ''\n",
    "        return False\n",
    "    \n",
    "    try:\n",
    "        _hdbi = ibm_db_dbi.Connection(_hdbc)\n",
    "    except Exception as err:\n",
    "        db2_error(False,True) # errormsg(str(err))\n",
    "        _connected = False\n",
    "        _settings[\"database\"] = ''\n",
    "        return False  \n",
    "    \n",
    "    _connected = True\n",
    "    \n",
    "    # Save the values for future use\n",
    "    \n",
    "    save_settings()\n",
    "    \n",
    "    success(\"Connection successful.\")\n",
    "    return True\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",
    "     \"maxgrid\"  : 5,\n",
    "     \"runtime\"  : 1,\n",
    "     \"display\"  : \"TEXT\",\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": {},
   "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",
    "        # Reset runtime to 1 since it would be unexpected to keep the same value between connections         \n",
    "        _settings[\"runtime\"] = 1\n",
    "        _settings[\"maxgrid\"] = 5\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,connect=False)\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().\n",
    "\n",
    "If the connect flag is True, the routine will get the SQLSTATE and SQLCODE from the connection error message rather than a statement error message."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def db2_error(quiet,connect=False):\n",
    "    \n",
    "    global sqlerror, sqlcode, sqlstate, _environment\n",
    "    \n",
    "    \n",
    "    try:\n",
    "        if (connect == False):\n",
    "            errmsg = ibm_db.stmt_errormsg().replace('\\r',' ')\n",
    "            errmsg = errmsg[errmsg.rfind(\"]\")+1:].strip()\n",
    "        else:\n",
    "            errmsg = ibm_db.conn_errormsg().replace('\\r',' ')\n",
    "            errmsg = errmsg[errmsg.rfind(\"]\")+1:].strip()\n",
    "            \n",
    "        sqlerror = errmsg\n",
    " \n",
    "        msg_start = errmsg.find(\"SQLSTATE=\")\n",
    "        if (msg_start != -1):\n",
    "            msg_end = errmsg.find(\" \",msg_start)\n",
    "            if (msg_end == -1):\n",
    "                msg_end = len(errmsg)\n",
    "            sqlstate = errmsg[msg_start+9:msg_end]\n",
    "        else:\n",
    "            sqlstate = \"0\"\n",
    "    \n",
    "        msg_start = errmsg.find(\"SQLCODE=\")\n",
    "        if (msg_start != -1):\n",
    "            msg_end = errmsg.find(\" \",msg_start)\n",
    "            if (msg_end == -1):\n",
    "                msg_end = len(errmsg)\n",
    "            sqlcode = errmsg[msg_start+8:msg_end]\n",
    "            try:\n",
    "                sqlcode = int(sqlcode)\n",
    "            except:\n",
    "                pass\n",
    "        else:        \n",
    "            sqlcode = 0\n",
    "            \n",
    "    except:\n",
    "        errmsg = \"Unknown error.\"\n",
    "        sqlcode = -99999\n",
    "        sqlstate = \"-99999\"\n",
    "        sqlerror = errmsg\n",
    "        return\n",
    "        \n",
    "    \n",
    "    msg_start = errmsg.find(\"SQLSTATE=\")\n",
    "    if (msg_start != -1):\n",
    "        msg_end = errmsg.find(\" \",msg_start)\n",
    "        if (msg_end == -1):\n",
    "            msg_end = len(errmsg)\n",
    "        sqlstate = errmsg[msg_start+9:msg_end]\n",
    "    else:\n",
    "        sqlstate = \"0\"\n",
    "        \n",
    "    \n",
    "    msg_start = errmsg.find(\"SQLCODE=\")\n",
    "    if (msg_start != -1):\n",
    "        msg_end = errmsg.find(\" \",msg_start)\n",
    "        if (msg_end == -1):\n",
    "            msg_end = len(errmsg)\n",
    "        sqlcode = errmsg[msg_start+8:msg_end]\n",
    "        try:\n",
    "            sqlcode = int(sqlcode)\n",
    "        except:\n",
    "            pass\n",
    "    else:\n",
    "        sqlcode = 0\n",
    "    \n",
    "    if quiet == True: return\n",
    "    \n",
    "    if (errmsg == \"\"): return\n",
    "\n",
    "    html = '<p><p style=\"border:2px; border-style:solid; border-color:#FF0000; background-color:#ffe6e6; padding: 1em;\">'\n",
    "    \n",
    "    if (_environment[\"jupyter\"] == True):\n",
    "        pdisplay(pHTML(html+errmsg+\"</p>\"))\n",
    "    else:\n",
    "        print(errmsg)\n",
    "    \n",
    "# Print out an error message\n",
    "\n",
    "def errormsg(message):\n",
    "    \n",
    "    global _environment\n",
    "    \n",
    "    if (message != \"\"):\n",
    "        html = '<p><p style=\"border:2px; border-style:solid; border-color:#FF0000; background-color:#ffe6e6; padding: 1em;\">'\n",
    "        if (_environment[\"jupyter\"] == True):\n",
    "            pdisplay(pHTML(html + message + \"</p>\"))     \n",
    "        else:\n",
    "            print(message)\n",
    "    \n",
    "def success(message):\n",
    "    \n",
    "    if (message != \"\"):\n",
    "        print(message)\n",
    "    return   \n",
    "\n",
    "def debug(message,error=False):\n",
    "    \n",
    "    global _environment\n",
    "    \n",
    "    if (_environment[\"jupyter\"] == True):\n",
    "        spacer = \"<br>\" + \"&nbsp;\"\n",
    "    else:\n",
    "        spacer = \"\\n \"\n",
    "    \n",
    "    if (message != \"\"):\n",
    "\n",
    "        lines = message.split('\\n')\n",
    "        msg = \"\"\n",
    "        indent = 0\n",
    "        for line in lines:\n",
    "            delta = line.count(\"(\") - line.count(\")\")\n",
    "            if (msg == \"\"):\n",
    "                msg = line\n",
    "                indent = indent + delta\n",
    "            else:\n",
    "                if (delta < 0): indent = indent + delta\n",
    "                msg = msg + spacer * (indent*2) + line\n",
    "                if (delta > 0): indent = indent + delta    \n",
    "\n",
    "            if (indent < 0): indent = 0\n",
    "        if (error == True):        \n",
    "            html = '<p><pre style=\"font-family: monospace; border:2px; border-style:solid; border-color:#FF0000; background-color:#ffe6e6; padding: 1em;\">'                  \n",
    "        else:\n",
    "            html = '<p><pre style=\"font-family: monospace; border:2px; border-style:solid; border-color:#008000; background-color:#e6ffe6; padding: 1em;\">'\n",
    "        \n",
    "        if (_environment[\"jupyter\"] == True):\n",
    "            pdisplay(pHTML(html + msg + \"</pre></p>\"))\n",
    "        else:\n",
    "            print(msg)\n",
    "        \n",
    "    return "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Macro Processor\n",
    "A macro is used to generate SQL to be executed by overriding or creating a new keyword. For instance, the base `%sql` command does not understand the `LIST TABLES` command which is usually used in conjunction with the `CLP` processor. Rather than specifically code this in the base `db2.ipynb` file, we can create a macro that can execute this code for us.\n",
    "\n",
    "There are three routines that deal with macros. \n",
    "\n",
    "- checkMacro is used to find the macro calls in a string. All macros are sent to parseMacro for checking.\n",
    "- runMacro will evaluate the macro and return the string to the parse\n",
    "- subvars is used to track the variables used as part of a macro call.\n",
    "- setMacro is used to catalog a macro"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Set Macro\n",
    "This code will catalog a macro call."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def setMacro(inSQL,parms):\n",
    "      \n",
    "    global _macros\n",
    "    \n",
    "    names = parms.split()\n",
    "    if (len(names) < 2):\n",
    "        errormsg(\"No command name supplied.\")\n",
    "        return None\n",
    "    \n",
    "    macroName = names[1].upper()\n",
    "    _macros[macroName] = inSQL\n",
    "\n",
    "    return"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check Macro\n",
    "This code will check to see if there is a macro command in the SQL. It will take the SQL that is supplied and strip out three values: the first and second keywords, and the remainder of the parameters.\n",
    "\n",
    "For instance, consider the following statement:\n",
    "```\n",
    "CREATE DATABASE GEORGE options....\n",
    "```\n",
    "The name of the macro that we want to run is called `CREATE`. We know that there is a SQL command called `CREATE` but this code will call the macro first to see if needs to run any special code. For instance, `CREATE DATABASE` is not part of the `db2.ipynb` syntax, but we can add it in by using a macro.\n",
    "\n",
    "The check macro logic will strip out the subcommand (`DATABASE`) and place the remainder of the string after `DATABASE` in options."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def checkMacro(in_sql):\n",
    "       \n",
    "    global _macros\n",
    "    \n",
    "    if (len(in_sql) == 0): return(in_sql)          # Nothing to do \n",
    "    \n",
    "    tokens = parseArgs(in_sql,None)                # Take the string and reduce into tokens\n",
    "    \n",
    "    macro_name = tokens[0].upper()                 # Uppercase the name of the token\n",
    " \n",
    "    if (macro_name not in _macros): \n",
    "        return(in_sql) # No macro by this name so just return the string\n",
    "\n",
    "    result = runMacro(_macros[macro_name],in_sql,tokens)  # Execute the macro using the tokens we found\n",
    "\n",
    "    return(result)                                 # Runmacro will either return the original SQL or the new one"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Split Assignment\n",
    "This routine will return the name of a variable and it's value when the format is x=y. If y is enclosed in quotes, the quotes are removed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def splitassign(arg):\n",
    "    \n",
    "    var_name = \"null\"\n",
    "    var_value = \"null\"\n",
    "    \n",
    "    arg = arg.strip()\n",
    "    eq = arg.find(\"=\")\n",
    "    if (eq != -1):\n",
    "        var_name = arg[:eq].strip()\n",
    "        temp_value = arg[eq+1:].strip()\n",
    "        if (temp_value != \"\"):\n",
    "            ch = temp_value[0]\n",
    "            if (ch in [\"'\",'\"']):\n",
    "                if (temp_value[-1:] == ch):\n",
    "                    var_value = temp_value[1:-1]\n",
    "                else:\n",
    "                    var_value = temp_value\n",
    "            else:\n",
    "                var_value = temp_value\n",
    "    else:\n",
    "        var_value = arg\n",
    "\n",
    "    return var_name, var_value"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Parse Args \n",
    "The commands that are used in the macros need to be parsed into their separate tokens. The tokens are separated by blanks and strings that enclosed in quotes are kept together."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def parseArgs(argin,_vars):\n",
    "\n",
    "    quoteChar = \"\"\n",
    "    inQuote = False\n",
    "    inArg = True\n",
    "    args = []\n",
    "    arg = ''\n",
    "    \n",
    "    for ch in argin.lstrip():\n",
    "        if (inQuote == True):\n",
    "            if (ch == quoteChar):\n",
    "                inQuote = False   \n",
    "                arg = arg + ch #z\n",
    "            else:\n",
    "                arg = arg + ch\n",
    "        elif (ch == \"\\\"\" or ch == \"\\'\"): # Do we have a quote\n",
    "            quoteChar = ch\n",
    "            arg = arg + ch #z\n",
    "            inQuote = True\n",
    "        elif (ch == \" \"):\n",
    "            if (arg != \"\"):\n",
    "                arg = subvars(arg,_vars)\n",
    "                args.append(arg)\n",
    "            else:\n",
    "                args.append(\"null\")\n",
    "            arg = \"\"\n",
    "        else:\n",
    "            arg = arg + ch\n",
    "                \n",
    "    if (arg != \"\"):\n",
    "        arg = subvars(arg,_vars)\n",
    "        args.append(arg)   \n",
    "               \n",
    "    return(args)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Run Macro\n",
    "This code will execute the body of the macro and return the results for that macro call."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def runMacro(script,in_sql,tokens):\n",
    "    \n",
    "    result = \"\"\n",
    "    runIT = True \n",
    "    code = script.split(\"\\n\")\n",
    "    level = 0\n",
    "    runlevel = [True,False,False,False,False,False,False,False,False,False]\n",
    "    ifcount = 0\n",
    "    _vars = {}\n",
    "    \n",
    "    for i in range(0,len(tokens)):\n",
    "        vstr = str(i)\n",
    "        _vars[vstr] = tokens[i]\n",
    "        \n",
    "    if (len(tokens) == 0):\n",
    "        _vars[\"argc\"] = \"0\"\n",
    "    else:\n",
    "        _vars[\"argc\"] = str(len(tokens)-1)\n",
    "          \n",
    "    for line in code:\n",
    "        line = line.strip()\n",
    "        if (line == \"\" or line == \"\\n\"): continue\n",
    "        if (line[0] == \"#\"): continue    # A comment line starts with a # in the first position of the line\n",
    "        args = parseArgs(line,_vars)     # Get all of the arguments\n",
    "        if (args[0] == \"if\"):\n",
    "            ifcount = ifcount + 1\n",
    "            if (runlevel[level] == False): # You can't execute this statement\n",
    "                continue\n",
    "            level = level + 1    \n",
    "            if (len(args) < 4):\n",
    "                print(\"Macro: Incorrect number of arguments for the if clause.\")\n",
    "                return insql\n",
    "            arg1 = args[1]\n",
    "            arg2 = args[3]\n",
    "            if (len(arg2) > 2):\n",
    "                ch1 = arg2[0]\n",
    "                ch2 = arg2[-1:]\n",
    "                if (ch1 in ['\"',\"'\"] and ch1 == ch2):\n",
    "                    arg2 = arg2[1:-1].strip()\n",
    "               \n",
    "            op   = args[2]\n",
    "            if (op in [\"=\",\"==\"]):\n",
    "                if (arg1 == arg2):\n",
    "                    runlevel[level] = True\n",
    "                else:\n",
    "                    runlevel[level] = False                \n",
    "            elif (op in [\"<=\",\"=<\"]):\n",
    "                if (arg1 <= arg2):\n",
    "                    runlevel[level] = True\n",
    "                else:\n",
    "                    runlevel[level] = False                \n",
    "            elif (op in [\">=\",\"=>\"]):                    \n",
    "                if (arg1 >= arg2):\n",
    "                    runlevel[level] = True\n",
    "                else:\n",
    "                    runlevel[level] = False                                       \n",
    "            elif (op in [\"<>\",\"!=\"]):                    \n",
    "                if (arg1 != arg2):\n",
    "                    runlevel[level] = True\n",
    "                else:\n",
    "                    runlevel[level] = False  \n",
    "            elif (op in [\"<\"]):\n",
    "                if (arg1 < arg2):\n",
    "                    runlevel[level] = True\n",
    "                else:\n",
    "                    runlevel[level] = False                \n",
    "            elif (op in [\">\"]):\n",
    "                if (arg1 > arg2):\n",
    "                    runlevel[level] = True\n",
    "                else:\n",
    "                    runlevel[level] = False                \n",
    "            else:\n",
    "                print(\"Macro: Unknown comparison operator in the if statement:\" + op)\n",
    "\n",
    "                continue\n",
    "\n",
    "        elif (args[0] in [\"exit\",\"echo\"] and runlevel[level] == True):\n",
    "            msg = \"\"\n",
    "            for msgline in args[1:]:\n",
    "                if (msg == \"\"):\n",
    "                    msg = subvars(msgline,_vars)\n",
    "                else:\n",
    "                    msg = msg + \" \" + subvars(msgline,_vars)\n",
    "            if (msg != \"\"): \n",
    "                if (args[0] == \"echo\"):\n",
    "                    debug(msg,error=False)\n",
    "                else:\n",
    "                    debug(msg,error=True)\n",
    "            if (args[0] == \"exit\"): return ''\n",
    "       \n",
    "        elif (args[0] == \"pass\" and runlevel[level] == True):\n",
    "            pass\n",
    "\n",
    "        elif (args[0] == \"var\" and runlevel[level] == True):\n",
    "            value = \"\"\n",
    "            for val in args[2:]:\n",
    "                if (value == \"\"):\n",
    "                    value = subvars(val,_vars)\n",
    "                else:\n",
    "                    value = value + \" \" + subvars(val,_vars)\n",
    "            value.strip()\n",
    "            _vars[args[1]] = value \n",
    "\n",
    "        elif (args[0] == 'else'):\n",
    "\n",
    "            if (ifcount == level):\n",
    "                runlevel[level] = not runlevel[level]\n",
    "                \n",
    "        elif (args[0] == 'return' and runlevel[level] == True):\n",
    "            return(result)\n",
    "\n",
    "        elif (args[0] == \"endif\"):\n",
    "            ifcount = ifcount - 1\n",
    "            if (ifcount < level):\n",
    "                level = level - 1\n",
    "                if (level < 0):\n",
    "                    print(\"Macro: Unmatched if/endif pairs.\")\n",
    "                    return ''\n",
    "                \n",
    "        else:\n",
    "            if (runlevel[level] == True):\n",
    "                if (result == \"\"):\n",
    "                    result = subvars(line,_vars)\n",
    "                else:\n",
    "                    result = result + \"\\n\" + subvars(line,_vars)\n",
    "                    \n",
    "    return(result)       "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Substitute Vars\n",
    "This routine is used by the runMacro program to track variables that are used within Macros. These are kept separate from the rest of the code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def subvars(script,_vars):\n",
    "    \n",
    "    if (_vars == None): return script\n",
    "    \n",
    "    remainder = script\n",
    "    result = \"\"\n",
    "    done = False\n",
    "    \n",
    "    while done == False:\n",
    "        bv = remainder.find(\"{\")\n",
    "        if (bv == -1):\n",
    "            done = True\n",
    "            continue\n",
    "        ev = remainder.find(\"}\")\n",
    "        if (ev == -1):\n",
    "            done = True\n",
    "            continue\n",
    "        result = result + remainder[:bv]\n",
    "        vvar = remainder[bv+1:ev]\n",
    "        remainder = remainder[ev+1:]\n",
    "        \n",
    "        upper = False\n",
    "        allvars = False\n",
    "        if (vvar[0] == \"^\"):\n",
    "            upper = True\n",
    "            vvar = vvar[1:]\n",
    "        elif (vvar[0] == \"*\"):\n",
    "            vvar = vvar[1:]\n",
    "            allvars = True\n",
    "        else:\n",
    "            pass\n",
    "        \n",
    "        if (vvar in _vars):\n",
    "            if (upper == True):\n",
    "                items = _vars[vvar].upper()\n",
    "            elif (allvars == True):\n",
    "                try:\n",
    "                    iVar = int(vvar)\n",
    "                except:\n",
    "                    return(script)\n",
    "                items = \"\"\n",
    "                sVar = str(iVar)\n",
    "                while sVar in _vars:\n",
    "                    if (items == \"\"):\n",
    "                        items = _vars[sVar]\n",
    "                    else:\n",
    "                        items = items + \" \" + _vars[sVar]\n",
    "                    iVar = iVar + 1\n",
    "                    sVar = str(iVar)\n",
    "            else:\n",
    "                items = _vars[vvar]\n",
    "        else:\n",
    "            if (allvars == True):\n",
    "                items = \"\"\n",
    "            else:\n",
    "                items = \"null\"                \n",
    "                 \n",
    "        result = result + items\n",
    "                \n",
    "    if (remainder != \"\"):\n",
    "        result = result + remainder\n",
    "        \n",
    "    return(result)"
   ]
  },
  {
   "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": {},
   "outputs": [],
   "source": [
    "def sqlTimer(hdbc, runtime, inSQL):\n",
    "    \n",
    "    count = 0\n",
    "    t_end = time.time() + runtime\n",
    "    \n",
    "    while time.time() < t_end:\n",
    "        \n",
    "        try:\n",
    "            stmt = ibm_db.exec_immediate(hdbc,inSQL) \n",
    "            if (stmt == False):\n",
    "                db2_error(flag([\"-q\",\"-quiet\"]))\n",
    "                return(-1)\n",
    "            ibm_db.free_result(stmt)\n",
    "            \n",
    "        except Exception as err:\n",
    "            db2_error(False)\n",
    "            return(-1)\n",
    "        \n",
    "        count = count + 1\n",
    "                    \n",
    "    return(count)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Split Args\n",
    "This routine takes as an argument a string and then splits the arguments according to the following logic:\n",
    "* If the string starts with a `(` character, it will check the last character in the string and see if it is a `)` and then remove those characters\n",
    "* Every parameter is separated by a comma `,` and commas within quotes are ignored\n",
    "* Each parameter returned will have three values returned - one for the value itself, an indicator which will be either True if it was quoted, or False if not, and True or False if it is numeric.\n",
    "\n",
    "Example:\n",
    "```\n",
    "   \"abcdef\",abcdef,456,\"856\"\n",
    "```\n",
    "\n",
    "Three values would be returned:\n",
    "```\n",
    "[abcdef,True,False],[abcdef,False,False],[456,False,True],[856,True,False]\n",
    "```\n",
    "\n",
    "Any quoted string will be False for numeric. The way that the parameters are handled are up to the calling program. However, in the case of Db2, the quoted strings must be in single quotes so any quoted parameter using the double quotes `\"` must be wrapped with single quotes. There is always a possibility that a string contains single quotes (i.e. O'Connor) so any substituted text should use `''` so that Db2 can properly interpret the string. This routine does not adjust the strings with quotes, and depends on the variable subtitution routine to do that. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def splitargs(arguments):\n",
    "    \n",
    "    import types\n",
    "    \n",
    "    # String the string and remove the ( and ) characters if they at the beginning and end of the string\n",
    "    \n",
    "    results = []\n",
    "    \n",
    "    step1 = arguments.strip()\n",
    "    if (len(step1) == 0): return(results)       # Not much to do here - no args found\n",
    "    \n",
    "    if (step1[0] == '('):\n",
    "        if (step1[-1:] == ')'):\n",
    "            step2 = step1[1:-1]\n",
    "            step2 = step2.strip()\n",
    "        else:\n",
    "            step2 = step1\n",
    "    else:\n",
    "        step2 = step1\n",
    "            \n",
    "    # Now we have a string without brackets. Start scanning for commas\n",
    "            \n",
    "    quoteCH = \"\"\n",
    "    pos = 0\n",
    "    arg = \"\"\n",
    "    args = []\n",
    "            \n",
    "    while pos < len(step2):\n",
    "        ch = step2[pos]\n",
    "        if (quoteCH == \"\"):                     # Are we in a quote?\n",
    "            if (ch in ('\"',\"'\")):               # Check to see if we are starting a quote\n",
    "                quoteCH = ch\n",
    "                arg = arg + ch\n",
    "                pos += 1\n",
    "            elif (ch == \",\"):                   # Are we at the end of a parameter?\n",
    "                arg = arg.strip()\n",
    "                args.append(arg)\n",
    "                arg = \"\"\n",
    "                inarg = False \n",
    "                pos += 1\n",
    "            else:                               # Continue collecting the string\n",
    "                arg = arg + ch\n",
    "                pos += 1\n",
    "        else:\n",
    "            if (ch == quoteCH):                 # Are we at the end of a quote?\n",
    "                arg = arg + ch                  # Add the quote to the string\n",
    "                pos += 1                        # Increment past the quote\n",
    "                quoteCH = \"\"                    # Stop quote checking (maybe!)\n",
    "            else:\n",
    "                pos += 1\n",
    "                arg = arg + ch\n",
    "\n",
    "    if (quoteCH != \"\"):                         # So we didn't end our string\n",
    "        arg = arg.strip()\n",
    "        args.append(arg)\n",
    "    elif (arg != \"\"):                           # Something left over as an argument\n",
    "        arg = arg.strip()\n",
    "        args.append(arg)\n",
    "    else:\n",
    "        pass\n",
    "    \n",
    "    results = []\n",
    "    \n",
    "    for arg in args:\n",
    "        result = []\n",
    "        if (len(arg) > 0):\n",
    "            if (arg[0] in ('\"',\"'\")):\n",
    "                value = arg[1:-1]\n",
    "                isString = True\n",
    "                isNumber = False\n",
    "            else:\n",
    "                isString = False \n",
    "                isNumber = False \n",
    "                try:\n",
    "                    value = eval(arg)\n",
    "                    if (type(value) == int):\n",
    "                        isNumber = True\n",
    "                    elif (isinstance(value,float) == True):\n",
    "                        isNumber = True\n",
    "                    else:\n",
    "                        value = arg\n",
    "                except:\n",
    "                    value = arg\n",
    "\n",
    "        else:\n",
    "            value = \"\"\n",
    "            isString = False\n",
    "            isNumber = False\n",
    "            \n",
    "        result = [value,isString,isNumber]\n",
    "        results.append(result)\n",
    "        \n",
    "    return results"
   ]
  },
  {
   "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": {},
   "outputs": [],
   "source": [
    "def sqlParser(sqlin,local_ns):\n",
    "       \n",
    "    sql_cmd = \"\"\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, 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, encoded_sql    \n",
    "    \n",
    "    inVar = False \n",
    "    inQuote = \"\" \n",
    "    varName = \"\"\n",
    "    encoded_sql = \"\"\n",
    "    \n",
    "    STRING = 0\n",
    "    NUMBER = 1\n",
    "    LIST = 2\n",
    "    RAW = 3\n",
    "    PANDAS = 5\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",
    "                if (varName == \"\"):\n",
    "                    encode_sql = encoded_sql + \":\"\n",
    "                elif (varName[0] in ('[',']')):\n",
    "                    encoded_sql = encoded_sql + \":\" + varName\n",
    "                else:\n",
    "                    if (ch == '.'): # If the variable name is stopped by a period, assume no quotes are used\n",
    "                        flag_quotes = False\n",
    "                    else:\n",
    "                        flag_quotes = True\n",
    "                    varValue, varType = getContents(varName,flag_quotes,local_ns)\n",
    "                    if (varType != PANDAS and varValue == None):                 \n",
    "                        encoded_sql = encoded_sql + \":\" + varName\n",
    "                    else:\n",
    "                        if (varType == STRING):\n",
    "                            encoded_sql = encoded_sql + varValue\n",
    "                        elif (varType == NUMBER):\n",
    "                            encoded_sql = encoded_sql + str(varValue)\n",
    "                        elif (varType == RAW):\n",
    "                            encoded_sql = encoded_sql + varValue\n",
    "                        elif (varType == PANDAS):\n",
    "                            insertsql = \"\"\n",
    "                            coltypes = varValue.dtypes\n",
    "                            rows, cols = varValue.shape\n",
    "                            for row in range(0,rows):\n",
    "                                insertrow = \"\"\n",
    "                                for col in range(0, cols):\n",
    "                                    value = varValue.iloc[row][col]\n",
    "                                    if (coltypes[col] == \"object\"):\n",
    "                                        value = str(value)\n",
    "                                        value = addquotes(value,True)\n",
    "                                    if (insertrow == \"\"):\n",
    "                                        insertrow = f\"{value}\"\n",
    "                                    else:\n",
    "                                        insertrow = f\"{insertrow},{value}\"\n",
    "                                if (insertsql == \"\"):\n",
    "                                    insertsql = f\"({insertrow})\"\n",
    "                                else:\n",
    "                                    insertsql = f\"{insertsql},({insertrow})\"  \n",
    "                            encoded_sql = endcoded_sql + insertsql\n",
    "                        elif (varType == LIST):\n",
    "                            start = True\n",
    "                            for v in varValue:\n",
    "                                if (start == False):\n",
    "                                    encoded_sql = encoded_sql + \",\"\n",
    "                                if (isinstance(v,int) == True):         # Integer value \n",
    "                                    encoded_sql = encoded_sql + str(v)\n",
    "                                elif (isinstance(v,float) == True):\n",
    "                                    encoded_sql = encoded_sql + str(v)\n",
    "                                else:\n",
    "                                    flag_quotes = True\n",
    "                                    try:\n",
    "                                        if (v.find('0x') == 0):               # Just guessing this is a hex value at beginning\n",
    "                                            encoded_sql = encoded_sql + v\n",
    "                                        else:\n",
    "                                            encoded_sql = encoded_sql + addquotes(v,flag_quotes)      # String\n",
    "                                    except:\n",
    "                                        encoded_sql = encoded_sql + addquotes(str(v),flag_quotes)                                   \n",
    "                                start = False\n",
    "\n",
    "                encoded_sql = encoded_sql + ch\n",
    "                varName = \"\"\n",
    "                inVar = False  \n",
    "        elif (inQuote != \"\"):\n",
    "            encoded_sql = encoded_sql + ch\n",
    "            if (ch == inQuote): inQuote = \"\"\n",
    "        elif (ch in (\"'\",'\"')):\n",
    "            encoded_sql = encoded_sql + ch\n",
    "            inQuote = ch\n",
    "        elif (ch == \":\"): # This might be a variable\n",
    "            varName = \"\"\n",
    "            inVar = True\n",
    "        else:\n",
    "            encoded_sql = encoded_sql + ch\n",
    "    \n",
    "    if (inVar == True):\n",
    "        varValue, varType = getContents(varName,True,local_ns) # We assume the end of a line is quoted\n",
    "        if (varType != PANDAS and varValue == None):                 \n",
    "            encoded_sql = encoded_sql + \":\" + varName  \n",
    "        else:\n",
    "            if (varType == STRING):\n",
    "                encoded_sql = encoded_sql + varValue\n",
    "            elif (varType == NUMBER):\n",
    "                encoded_sql = encoded_sql + str(varValue)\n",
    "            elif (varType == PANDAS):\n",
    "                insertsql = \"\"\n",
    "                coltypes = varValue.dtypes\n",
    "                rows, cols = varValue.shape\n",
    "                for row in range(0,rows):\n",
    "                    insertrow = \"\"\n",
    "                    for col in range(0, cols):\n",
    "                        value = varValue.iloc[row][col]\n",
    "                        if (coltypes[col] == \"object\"):\n",
    "                            value = str(value)\n",
    "                            value = addquotes(value,True)\n",
    "                        if (insertrow == \"\"):\n",
    "                            insertrow = f\"{value}\"\n",
    "                        else:\n",
    "                            insertrow = f\"{insertrow},{value}\"\n",
    "                    if (insertsql == \"\"):\n",
    "                        insertsql = f\"({insertrow})\"\n",
    "                    else:\n",
    "                        insertsql = f\"{insertsql},({insertrow})\"  \n",
    "                encoded_sql = encoded_sql + insertsql                \n",
    "            elif (varType == LIST):\n",
    "                flag_quotes = True\n",
    "                start = True\n",
    "                for v in varValue:\n",
    "                    if (start == False):\n",
    "                        encoded_sql = encoded_sql + \",\"\n",
    "                    if (isinstance(v,int) == True):         # Integer value \n",
    "                        encoded_sql = encoded_sql + str(v)\n",
    "                    elif (isinstance(v,float) == True):\n",
    "                        encoded_sql = encoded_sql + str(v)\n",
    "                    else:\n",
    "                        try:\n",
    "                            if (v.find('0x') == 0):               # Just guessing this is a hex value\n",
    "                                encoded_sql = encoded_sql + v\n",
    "                            else:\n",
    "                                encoded_sql = encoded_sql + addquotes(v,flag_quotes)              # String\n",
    "                        except:\n",
    "                            encoded_sql = encoded_sql + addquotes(str(v),flag_quotes)                                 \n",
    "                    start = False\n",
    "\n",
    "    return sql_cmd, encoded_sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Variable Contents Function\n",
    "The calling format of this routine is:\n",
    "\n",
    "```\n",
    "value = getContents(varName,quote,name_space)\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.\n",
    "\n",
    "The other thing that this function does is replace single quotes with two quotes. The reason for doing this is that Db2 will convert two single quotes into one quote when dealing with strings. This avoids problems when dealing with text that contains multiple quotes within the string. Note that this substitution is done only for single quote characters since the double quote character is used by Db2 for naming columns that are case sensitive or contain special characters.\n",
    "\n",
    "If the quote value is True, the field will have quotes around it. The name_space is the variables currently that are registered in Python."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getContents(varName,flag_quotes,local_ns):\n",
    "    \n",
    "    #\n",
    "    # Get the contents of the variable name that is passed to the routine. Only simple\n",
    "    # variables are checked, i.e. arrays and lists are not parsed\n",
    "    #\n",
    "    \n",
    "    STRING = 0\n",
    "    NUMBER = 1\n",
    "    LIST = 2\n",
    "    RAW = 3\n",
    "    DICT = 4\n",
    "    PANDAS = 5\n",
    "    \n",
    "    try:\n",
    "        value = eval(varName,None,local_ns) # globals()[varName] # eval(varName)\n",
    "    except:\n",
    "        return(None,STRING)\n",
    "    \n",
    "    if (isinstance(value,dict) == True):          # Check to see if this is JSON dictionary\n",
    "        return(addquotes(value,flag_quotes),STRING)\n",
    "\n",
    "    elif(isinstance(value,list) == True):         # List - tricky \n",
    "        return(value,LIST)\n",
    "    \n",
    "    elif (isinstance(value,pandas.DataFrame) == True): # Pandas dataframe\n",
    "        return(value,PANDAS)\n",
    "\n",
    "    elif (isinstance(value,int) == True):         # Integer value \n",
    "        return(value,NUMBER)\n",
    "\n",
    "    elif (isinstance(value,float) == True):       # Float value\n",
    "        return(value,NUMBER)\n",
    "\n",
    "    else:\n",
    "        try:\n",
    "            # The pattern needs to be in the first position (0 in Python terms)\n",
    "            if (value.find('0x') == 0):               # Just guessing this is a hex value\n",
    "                return(value,RAW)\n",
    "            else:\n",
    "                return(addquotes(value,flag_quotes),STRING)                     # String\n",
    "        except:\n",
    "            return(addquotes(str(value),flag_quotes),RAW)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add Quotes\n",
    "Quotes are a challenge when dealing with dictionaries and Db2. Db2 wants strings delimited with single quotes, while Dictionaries use double quotes. That wouldn't be a problems except imbedded single quotes within these dictionaries will cause things to fail. This routine attempts to double-quote the single quotes within the dicitonary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def addquotes(inString,flag_quotes):\n",
    "    \n",
    "    if (isinstance(inString,dict) == True):          # Check to see if this is JSON dictionary\n",
    "        serialized = json.dumps(inString) \n",
    "    else:\n",
    "        serialized = inString\n",
    "\n",
    "    # Replace single quotes with '' (two quotes) and wrap everything in single quotes\n",
    "    if (flag_quotes == False):\n",
    "        return(serialized)\n",
    "    else:\n",
    "        return(\"'\"+serialized.replace(\"'\",\"''\")+\"'\")    # Convert single quotes to two single quotes"
   ]
  },
  {
   "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": {},
   "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": {},
   "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": {},
   "outputs": [],
   "source": [
    "def plotData(hdbi, sql):\n",
    "    \n",
    "    try:\n",
    "        df = pandas.read_sql(sql,hdbi)\n",
    "          \n",
    "    except Exception as err:\n",
    "        db2_error(False)\n",
    "        return\n",
    "                \n",
    "        \n",
    "    if df.empty:\n",
    "        errormsg(\"No results returned\")\n",
    "        return\n",
    "    \n",
    "    col_count = len(df.columns)\n",
    "\n",
    "    if flag([\"-pb\",\"-bar\"]):                                    # Plot 1 = bar chart\n",
    "    \n",
    "        if (col_count in (1,2,3)):\n",
    "            \n",
    "            if (col_count == 1):\n",
    " \n",
    "                df.index = df.index + 1\n",
    "                _ = df.plot(kind='bar');\n",
    "                _ = plt.plot();\n",
    "                \n",
    "            elif (col_count == 2):\n",
    " \n",
    "                xlabel = df.columns.values[0]\n",
    "                ylabel = df.columns.values[1]\n",
    "                df.plot(kind='bar',x=xlabel,y=ylabel);\n",
    "                _ = plt.plot();\n",
    "                \n",
    "            else:\n",
    " \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",
    "        else:\n",
    "            errormsg(\"Can't determine what columns to plot\")\n",
    "            return\n",
    "                    \n",
    "    elif flag([\"-pp\",\"-pie\"]):                                  # Plot 2 = pie chart\n",
    "        \n",
    "        if (col_count in (1,2)):  \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",
    "            errormsg(\"Can't determine what columns to plot\")\n",
    "            return\n",
    "                    \n",
    "    elif flag([\"-pl\",\"-line\"]):                                  # Plot 3 = line chart\n",
    "            \n",
    "        if (col_count in (1,2,3)): \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",
    "            errormsg(\"Can't determine what columns to plot\")\n",
    "            return\n",
    "    else:\n",
    "        return"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Find a Procedure\n",
    "This routine will check to see if a procedure exists with the SCHEMA/NAME (or just NAME if no schema is supplied) and returns the number of answer sets returned. Possible values are 0, 1 (or greater) or None. If None is returned then we can't find the procedure anywhere."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def findProc(procname):\n",
    "    \n",
    "    global _hdbc, _hdbi, _connected, _runtime\n",
    "    \n",
    "    # Split the procedure name into schema.procname if appropriate\n",
    "    upper_procname = procname.upper()\n",
    "    schema, proc = split_string(upper_procname,\".\") # Expect schema.procname\n",
    "    if (proc == None):\n",
    "        proc = schema\n",
    "\n",
    "    # Call ibm_db.procedures to see if the procedure does exist\n",
    "    schema = \"%\"\n",
    "\n",
    "    try:\n",
    "        stmt = ibm_db.procedures(_hdbc, None, schema, proc) \n",
    "        if (stmt == False):                         # Error executing the code\n",
    "            errormsg(\"Procedure \" + procname + \" not found in the system catalog.\")\n",
    "            return None\n",
    "\n",
    "        result = ibm_db.fetch_tuple(stmt)\n",
    "        resultsets = result[5]\n",
    "        if (resultsets >= 1): resultsets = 1\n",
    "        return resultsets\n",
    "            \n",
    "    except Exception as err:\n",
    "        errormsg(\"Procedure \" + procname + \" not found in the system catalog.\")\n",
    "        return None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Parse Call Arguments\n",
    "This code will parse a SQL call #name(parm1,...) and return the name and the parameters in the call."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def parseCallArgs(macro):\n",
    "    \n",
    "    quoteChar = \"\"\n",
    "    inQuote = False\n",
    "    inParm = False\n",
    "    ignore = False\n",
    "    name = \"\"\n",
    "    parms = []\n",
    "    parm = ''\n",
    "    \n",
    "    sqlin = macro.replace(\"\\n\",\"\")\n",
    "    sqlin.lstrip()\n",
    "    \n",
    "    for ch in sqlin:\n",
    "        if (inParm == False):\n",
    "            # We hit a blank in the name, so ignore everything after the procedure name until a ( is found\n",
    "            if (ch == \" \"): \n",
    "                ignore == True\n",
    "            elif (ch ==  \"(\"): # Now we have parameters to send to the stored procedure\n",
    "                inParm = True\n",
    "            else:\n",
    "                if (ignore == False): name = name + ch # The name of the procedure (and no blanks)\n",
    "        else:\n",
    "            if (inQuote == True):\n",
    "                if (ch == quoteChar):\n",
    "                    inQuote = False  \n",
    "                else:\n",
    "                    parm = parm + ch\n",
    "            elif (ch in (\"\\\"\",\"\\'\",\"[\")): # Do we have a quote\n",
    "                if (ch == \"[\"):\n",
    "                    quoteChar = \"]\"\n",
    "                else:\n",
    "                    quoteChar = ch\n",
    "                inQuote = True\n",
    "            elif (ch == \")\"):\n",
    "                if (parm != \"\"):\n",
    "                    parms.append(parm)\n",
    "                parm = \"\"\n",
    "                break\n",
    "            elif (ch == \",\"):\n",
    "                if (parm != \"\"):\n",
    "                    parms.append(parm)                  \n",
    "                else:\n",
    "                    parms.append(\"null\")\n",
    "                    \n",
    "                parm = \"\"\n",
    "\n",
    "            else:\n",
    "                parm = parm + ch\n",
    "                \n",
    "    if (inParm == True):\n",
    "        if (parm != \"\"):\n",
    "            parms.append(parm_value)    \n",
    "            \n",
    "    return(name,parms)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get Columns\n",
    "Given a statement handle, determine what the column names are or the data types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getColumns(stmt):\n",
    "    \n",
    "    columns = []\n",
    "    types = []\n",
    "    colcount = 0\n",
    "    try:\n",
    "        colname = ibm_db.field_name(stmt,colcount)\n",
    "        coltype = ibm_db.field_type(stmt,colcount)\n",
    "        while (colname != False):\n",
    "            columns.append(colname)\n",
    "            types.append(coltype)\n",
    "            colcount += 1\n",
    "            colname = ibm_db.field_name(stmt,colcount)\n",
    "            coltype = ibm_db.field_type(stmt,colcount)            \n",
    "        return columns,types   \n",
    "                \n",
    "    except Exception as err:\n",
    "        db2_error(False)\n",
    "        return None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Call a Procedure\n",
    "The CALL statement is used for execution of a stored procedure. The format of the CALL statement is:\n",
    "```\n",
    "CALL PROC_NAME(x,y,z,...)\n",
    "```\n",
    "Procedures allow for the return of answer sets (cursors) as well as changing the contents of the parameters being passed to the procedure. In this implementation, the CALL function is limited to returning one answer set (or nothing). If you want to use more complex stored procedures then you will have to use the native python libraries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def parseCall(hdbc, inSQL, local_ns):\n",
    "    \n",
    "    global _hdbc, _hdbi, _connected, _runtime, _environment\n",
    "    \n",
    "    # Check to see if we are connected first\n",
    "    if (_connected == False):                                      # Check if you are connected \n",
    "        db2_doConnect()\n",
    "        if _connected == False: return None\n",
    "    \n",
    "    remainder = inSQL.strip()\n",
    "    procName, procArgs = parseCallArgs(remainder[5:]) # Assume that CALL ... is the format\n",
    "    \n",
    "    resultsets = findProc(procName)\n",
    "    if (resultsets == None): return None\n",
    "    \n",
    "    argvalues = []\n",
    " \n",
    "    if (len(procArgs) > 0): # We have arguments to consider\n",
    "        for arg in procArgs:\n",
    "            varname = arg\n",
    "            if (len(varname) > 0):\n",
    "                if (varname[0] == \":\"):\n",
    "                    checkvar = varname[1:]\n",
    "                    varvalue = getContents(checkvar,True,local_ns)\n",
    "                    if (varvalue == None):\n",
    "                        errormsg(\"Variable \" + checkvar + \" is not defined.\")\n",
    "                        return None\n",
    "                    argvalues.append(varvalue)\n",
    "                else:\n",
    "                    if (varname.upper() == \"NULL\"):\n",
    "                        argvalues.append(None)\n",
    "                    else:\n",
    "                        argvalues.append(varname)\n",
    "            else:\n",
    "                argvalues.append(None)\n",
    "\n",
    "    \n",
    "    try:\n",
    "\n",
    "        if (len(procArgs) > 0):\n",
    "            argtuple = tuple(argvalues)\n",
    "            result = ibm_db.callproc(_hdbc,procName,argtuple)\n",
    "            stmt = result[0]\n",
    "        else:\n",
    "            result = ibm_db.callproc(_hdbc,procName)\n",
    "            stmt = result\n",
    "        \n",
    "        if (resultsets != 0 and stmt != None): \n",
    "\n",
    "            columns, types = getColumns(stmt)\n",
    "            if (columns == None): return None\n",
    "            \n",
    "            rows = []\n",
    "            rowlist = ibm_db.fetch_tuple(stmt)\n",
    "            while ( rowlist ) :\n",
    "                row = []\n",
    "                colcount = 0\n",
    "                for col in rowlist:\n",
    "                    try:\n",
    "                        if (types[colcount] in [\"int\",\"bigint\"]):\n",
    "                            row.append(int(col))\n",
    "                        elif (types[colcount] in [\"decimal\",\"real\"]):\n",
    "                            row.append(float(col))\n",
    "                        elif (types[colcount] in [\"date\",\"time\",\"timestamp\"]):\n",
    "                            row.append(str(col))\n",
    "                        else:\n",
    "                            row.append(col)\n",
    "                    except:\n",
    "                        row.append(col)\n",
    "                    colcount += 1\n",
    "                rows.append(row)\n",
    "                rowlist = ibm_db.fetch_tuple(stmt)\n",
    "            \n",
    "            if flag([\"-r\",\"-array\"]):\n",
    "                rows.insert(0,columns)\n",
    "                if len(procArgs) > 0:\n",
    "                    allresults = []\n",
    "                    allresults.append(rows)\n",
    "                    for x in result[1:]:\n",
    "                        allresults.append(x)\n",
    "                    return allresults # rows,returned_results\n",
    "                else:\n",
    "                    return rows\n",
    "            else:\n",
    "                df = pandas.DataFrame.from_records(rows,columns=columns)\n",
    "                if flag(\"-grid\") or _settings['display'] == 'GRID':\n",
    "                    if (_environment['qgrid'] == False):\n",
    "                        with pandas.option_context('display.max_rows', None, 'display.max_columns', None):  \n",
    "                            pdisplay(df)\n",
    "                    else:\n",
    "                        try:\n",
    "                            pdisplay(qgrid.show_grid(df))\n",
    "                        except:\n",
    "                            errormsg(\"Grid cannot be used to display data with duplicate column names. Use option -a or %sql OPTION DISPLAY PANDAS instead.\")\n",
    "                            \n",
    "                    return                             \n",
    "                else:\n",
    "                    if flag([\"-a\",\"-all\"]) or _settings[\"maxrows\"] == -1 : # All of the rows\n",
    "                        with pandas.option_context('display.max_rows', None, 'display.max_columns', None): \n",
    "                            pdisplay(df)\n",
    "                    else:\n",
    "                        return df\n",
    "            \n",
    "        else:\n",
    "            if len(procArgs) > 0:\n",
    "                allresults = []\n",
    "                for x in result[1:]:\n",
    "                    allresults.append(x)\n",
    "                return allresults # rows,returned_results\n",
    "            else:\n",
    "                return None\n",
    "            \n",
    "    except Exception as err:\n",
    "        db2_error(False)\n",
    "        return None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Parse Prepare/Execute\n",
    "The PREPARE statement is used for repeated execution of a SQL statement. The PREPARE statement has the format:\n",
    "```\n",
    "stmt = PREPARE SELECT EMPNO FROM EMPLOYEE WHERE WORKDEPT=? AND SALARY<?\n",
    "```\n",
    "The SQL statement that you want executed is placed after the PREPARE statement with the location of variables marked with ? (parameter) markers. The variable stmt contains the prepared statement that need to be passed to the EXECUTE statement. The EXECUTE statement has the format:\n",
    "```\n",
    "EXECUTE :x USING z, y, s \n",
    "```\n",
    "The first variable (:x) is the name of the variable that you assigned the results of the prepare statement. The values after the USING clause are substituted into the prepare statement where the ? markers are found. \n",
    "\n",
    "If the values in USING clause are variable names (z, y, s), a **link** is created to these variables as part of the execute statement. If you use the variable subsitution form of variable name (:z, :y, :s), the **contents** of the variable are placed into the USING clause. Normally this would not make much of a difference except when you are dealing with binary strings or JSON strings where the quote characters may cause some problems when subsituted into the statement. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def parsePExec(hdbc, inSQL):\n",
    "     \n",
    "    import ibm_db    \n",
    "    global _stmt, _stmtID, _stmtSQL, sqlcode\n",
    "    \n",
    "    cParms = inSQL.split()\n",
    "    parmCount = len(cParms)\n",
    "    if (parmCount == 0): return(None)                          # Nothing to do but this shouldn't happen\n",
    "    \n",
    "    keyword = cParms[0].upper()                                  # Upper case the keyword\n",
    "    \n",
    "    if (keyword == \"PREPARE\"):                                   # Prepare the following SQL\n",
    "        uSQL = inSQL.upper()\n",
    "        found = uSQL.find(\"PREPARE\")\n",
    "        sql = inSQL[found+7:].strip()\n",
    "\n",
    "        try:\n",
    "            pattern = \"\\?\\*[0-9]+\"\n",
    "            findparm = re.search(pattern,sql)\n",
    "            while findparm != None:\n",
    "                found = findparm.group(0)\n",
    "                count = int(found[2:])\n",
    "                markers = ('?,' * count)[:-1]\n",
    "                sql = sql.replace(found,markers)\n",
    "                findparm = re.search(pattern,sql)\n",
    "            \n",
    "            stmt = ibm_db.prepare(hdbc,sql) # Check error code here\n",
    "            if (stmt == False): \n",
    "                db2_error(False)\n",
    "                return(False)\n",
    "            \n",
    "            stmttext = str(stmt).strip()\n",
    "            stmtID = stmttext[33:48].strip()\n",
    "            \n",
    "            if (stmtID in _stmtID) == False:\n",
    "                _stmt.append(stmt)              # Prepare and return STMT to caller\n",
    "                _stmtID.append(stmtID)\n",
    "            else:\n",
    "                stmtIX = _stmtID.index(stmtID)\n",
    "                _stmt[stmtiX] = stmt\n",
    "                 \n",
    "            return(stmtID)\n",
    "        \n",
    "        except Exception as err:\n",
    "            print(err)\n",
    "            db2_error(False)\n",
    "            return(False)\n",
    "\n",
    "    if (keyword == \"EXECUTE\"):                                  # Execute the prepare statement\n",
    "        if (parmCount < 2): return(False)                    # No stmtID available\n",
    "        \n",
    "        stmtID = cParms[1].strip()\n",
    "        if (stmtID in _stmtID) == False:\n",
    "            errormsg(\"Prepared statement not found or invalid.\")\n",
    "            return(False)\n",
    "\n",
    "        stmtIX = _stmtID.index(stmtID)\n",
    "        stmt = _stmt[stmtIX]\n",
    "\n",
    "        try:        \n",
    "\n",
    "            if (parmCount == 2):                           # Only the statement handle available\n",
    "                result = ibm_db.execute(stmt)               # Run it\n",
    "            elif (parmCount == 3):                          # Not quite enough arguments\n",
    "                errormsg(\"Missing or invalid USING clause on EXECUTE statement.\")\n",
    "                sqlcode = -99999\n",
    "                return(False)\n",
    "            else:\n",
    "                using = cParms[2].upper()\n",
    "                if (using != \"USING\"):                     # Bad syntax again\n",
    "                    errormsg(\"Missing USING clause on EXECUTE statement.\")\n",
    "                    sqlcode = -99999\n",
    "                    return(False)\n",
    "                \n",
    "                uSQL = inSQL.upper()\n",
    "                found = uSQL.find(\"USING\")\n",
    "                parmString = inSQL[found+5:].strip()\n",
    "                parmset = splitargs(parmString)\n",
    " \n",
    "                if (len(parmset) == 0):\n",
    "                    errormsg(\"Missing parameters after the USING clause.\")\n",
    "                    sqlcode = -99999\n",
    "                    return(False)\n",
    "                    \n",
    "                parms = []\n",
    "\n",
    "                parm_count = 0\n",
    "                \n",
    "                CONSTANT = 0\n",
    "                VARIABLE = 1\n",
    "                const = [0]\n",
    "                const_cnt = 0\n",
    "                \n",
    "                for v in parmset:\n",
    "                    \n",
    "                    parm_count = parm_count + 1\n",
    "                    \n",
    "                    if (v[1] == True or v[2] == True): # v[1] true if string, v[2] true if num\n",
    "                        \n",
    "                        parm_type = CONSTANT                        \n",
    "                        const_cnt = const_cnt + 1\n",
    "                        if (v[2] == True):\n",
    "                            if (isinstance(v[0],int) == True):         # Integer value \n",
    "                                sql_type = ibm_db.SQL_INTEGER\n",
    "                            elif (isinstance(v[0],float) == True):       # Float value\n",
    "                                sql_type = ibm_db.SQL_DOUBLE\n",
    "                            else:\n",
    "                                sql_type = ibm_db.SQL_INTEGER\n",
    "                        else:\n",
    "                            sql_type = ibm_db.SQL_CHAR\n",
    "                        \n",
    "                        const.append(v[0])\n",
    "\n",
    "                        \n",
    "                    else:\n",
    "                    \n",
    "                        parm_type = VARIABLE\n",
    "                    \n",
    "                        # See if the variable has a type associated with it varname@type\n",
    "                    \n",
    "                        varset = v[0].split(\"@\")\n",
    "                        parm_name = varset[0]\n",
    "                        \n",
    "                        parm_datatype = \"char\"\n",
    "\n",
    "                        # Does the variable exist?\n",
    "                        if (parm_name not in globals()):\n",
    "                            errormsg(\"SQL Execute parameter \" + parm_name + \" not found\")\n",
    "                            sqlcode = -99999\n",
    "                            return(false)                        \n",
    "        \n",
    "                        if (len(varset) > 1):                # Type provided\n",
    "                            parm_datatype = varset[1]\n",
    "\n",
    "                        if (parm_datatype == \"dec\" or parm_datatype == \"decimal\"):\n",
    "                            sql_type = ibm_db.SQL_DOUBLE\n",
    "                        elif (parm_datatype == \"bin\" or parm_datatype == \"binary\"):\n",
    "                            sql_type = ibm_db.SQL_BINARY\n",
    "                        elif (parm_datatype == \"int\" or parm_datatype == \"integer\"):\n",
    "                            sql_type = ibm_db.SQL_INTEGER\n",
    "                        else:\n",
    "                            sql_type = ibm_db.SQL_CHAR\n",
    "                    \n",
    "                    try:\n",
    "                        if (parm_type == VARIABLE):\n",
    "                            result = ibm_db.bind_param(stmt, parm_count, globals()[parm_name], ibm_db.SQL_PARAM_INPUT, sql_type)\n",
    "                        else:\n",
    "                            result = ibm_db.bind_param(stmt, parm_count, const[const_cnt], ibm_db.SQL_PARAM_INPUT, sql_type)\n",
    "                            \n",
    "                    except:\n",
    "                        result = False\n",
    "                        \n",
    "                    if (result == False):\n",
    "                        errormsg(\"SQL Bind on variable \" + parm_name + \" failed.\")\n",
    "                        sqlcode = -99999\n",
    "                        return(false) \n",
    "                    \n",
    "                result = ibm_db.execute(stmt) # ,tuple(parms))\n",
    "                \n",
    "            if (result == False): \n",
    "                errormsg(\"SQL Execute failed.\")      \n",
    "                return(False)\n",
    "            \n",
    "            if (ibm_db.num_fields(stmt) == 0): return(True) # Command successfully completed\n",
    "                          \n",
    "            return(fetchResults(stmt))\n",
    "                        \n",
    "        except Exception as err:\n",
    "            db2_error(False)\n",
    "            return(False)\n",
    "        \n",
    "        return(False)\n",
    "  \n",
    "    return(False)     "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fetch Result Set\n",
    "This code will take the stmt handle and then produce a result set of rows as either an array (`-r`,`-array`) or as an array of json records (`-json`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fetchResults(stmt):\n",
    "     \n",
    "    global sqlcode\n",
    "    \n",
    "    rows = []\n",
    "    columns, types = getColumns(stmt)\n",
    "    \n",
    "    # By default we assume that the data will be an array\n",
    "    is_array = True\n",
    "    \n",
    "    # Check what type of data we want returned - array or json\n",
    "    if (flag([\"-r\",\"-array\"]) == False):\n",
    "        # See if we want it in JSON format, if not it remains as an array\n",
    "        if (flag(\"-json\") == True):\n",
    "            is_array = False\n",
    "    \n",
    "    # Set column names to lowercase for JSON records\n",
    "    if (is_array == False):\n",
    "        columns = [col.lower() for col in columns] # Convert to lowercase for each of access\n",
    "    \n",
    "    # First row of an array has the column names in it\n",
    "    if (is_array == True):\n",
    "        rows.append(columns)\n",
    "        \n",
    "    result = ibm_db.fetch_tuple(stmt)\n",
    "    rowcount = 0\n",
    "    while (result):\n",
    "        \n",
    "        rowcount += 1\n",
    "        \n",
    "        if (is_array == True):\n",
    "            row = []\n",
    "        else:\n",
    "            row = {}\n",
    "            \n",
    "        colcount = 0\n",
    "        for col in result:\n",
    "            try:\n",
    "                if (types[colcount] in [\"int\",\"bigint\"]):\n",
    "                    if (is_array == True):\n",
    "                        row.append(int(col))\n",
    "                    else:\n",
    "                        row[columns[colcount]] = int(col)\n",
    "                elif (types[colcount] in [\"decimal\",\"real\"]):\n",
    "                    if (is_array == True):\n",
    "                        row.append(float(col))\n",
    "                    else:\n",
    "                        row[columns[colcount]] = float(col)\n",
    "                elif (types[colcount] in [\"date\",\"time\",\"timestamp\"]):\n",
    "                    if (is_array == True):\n",
    "                        row.append(str(col))\n",
    "                    else:\n",
    "                        row[columns[colcount]] = str(col)\n",
    "                else:\n",
    "                    if (is_array == True):\n",
    "                        row.append(col)\n",
    "                    else:\n",
    "                        row[columns[colcount]] = col\n",
    "                        \n",
    "            except:\n",
    "                if (is_array == True):\n",
    "                    row.append(col)\n",
    "                else:\n",
    "                    row[columns[colcount]] = col\n",
    "                    \n",
    "            colcount += 1\n",
    "        \n",
    "        rows.append(row)\n",
    "        result = ibm_db.fetch_tuple(stmt)\n",
    "        \n",
    "    if (rowcount == 0): \n",
    "        sqlcode = 100        \n",
    "    else:\n",
    "        sqlcode = 0\n",
    "        \n",
    "    return rows\n",
    "            "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Parse Commit\n",
    "There are three possible COMMIT verbs that can bs used:\n",
    "- COMMIT [WORK] - Commit the work in progress - The WORK keyword is not checked for\n",
    "- ROLLBACK - Roll back the unit of work\n",
    "- AUTOCOMMIT ON/OFF - Are statements committed on or off?\n",
    "\n",
    "The statement is passed to this routine and then checked."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def parseCommit(sql):\n",
    "    \n",
    "    global _hdbc, _hdbi, _connected, _runtime, _stmt, _stmtID, _stmtSQL\n",
    "\n",
    "    if (_connected == False): return                        # Nothing to do if we are not connected\n",
    "    \n",
    "    cParms = sql.split()\n",
    "    if (len(cParms) == 0): return                           # Nothing to do but this shouldn't happen\n",
    "    \n",
    "    keyword = cParms[0].upper()                             # Upper case the keyword\n",
    "    \n",
    "    if (keyword == \"COMMIT\"):                               # Commit the work that was done\n",
    "        try:\n",
    "            result = ibm_db.commit (_hdbc)                  # Commit the connection\n",
    "            if (len(cParms) > 1):\n",
    "                keyword = cParms[1].upper()\n",
    "                if (keyword == \"HOLD\"):\n",
    "                    return\n",
    "            \n",
    "            del _stmt[:]\n",
    "            del _stmtID[:]\n",
    "\n",
    "        except Exception as err:\n",
    "            db2_error(False)\n",
    "        \n",
    "        return\n",
    "        \n",
    "    if (keyword == \"ROLLBACK\"):                             # Rollback the work that was done\n",
    "        try:\n",
    "            result = ibm_db.rollback(_hdbc)                  # Rollback the connection\n",
    "            del _stmt[:]\n",
    "            del _stmtID[:]            \n",
    "\n",
    "        except Exception as err:\n",
    "            db2_error(False)\n",
    "        \n",
    "        return\n",
    "    \n",
    "    if (keyword == \"AUTOCOMMIT\"):                           # Is autocommit on or off\n",
    "        if (len(cParms) > 1): \n",
    "            op = cParms[1].upper()                          # Need ON or OFF value\n",
    "        else:\n",
    "            return\n",
    "        \n",
    "        try:\n",
    "            if (op == \"OFF\"):\n",
    "                ibm_db.autocommit(_hdbc, False)\n",
    "            elif (op == \"ON\"):\n",
    "                ibm_db.autocommit (_hdbc, True)\n",
    "            return    \n",
    "        \n",
    "        except Exception as err:\n",
    "            db2_error(False)\n",
    "            return \n",
    "        \n",
    "    return"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Set Flags\n",
    "This code will take the input SQL block and update the global flag list. The global flag list is just a list of options that are set at the beginning of a code block. The absence of a flag means it is false. If it exists it is true."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def setFlags(inSQL):\n",
    "    \n",
    "    global _flags\n",
    "    \n",
    "    _flags = [] # Delete all of the current flag settings\n",
    "    \n",
    "    pos = 0\n",
    "    end = len(inSQL)-1\n",
    "    inFlag = False\n",
    "    ignore = False\n",
    "    outSQL = \"\"\n",
    "    flag = \"\"\n",
    "    \n",
    "    while (pos <= end):\n",
    "        ch = inSQL[pos]\n",
    "        if (ignore == True):   \n",
    "            outSQL = outSQL + ch\n",
    "        else:\n",
    "            if (inFlag == True):\n",
    "                if (ch != \" \"):\n",
    "                    flag = flag + ch\n",
    "                else:\n",
    "                    _flags.append(flag)\n",
    "                    inFlag = False\n",
    "            else:\n",
    "                if (ch == \"-\"):\n",
    "                    flag = \"-\"\n",
    "                    inFlag = True\n",
    "                elif (ch == ' '):\n",
    "                    outSQL = outSQL + ch\n",
    "                else:\n",
    "                    outSQL = outSQL + ch\n",
    "                    ignore = True\n",
    "        pos += 1\n",
    "        \n",
    "    if (inFlag == True):\n",
    "        _flags.append(flag)\n",
    "        \n",
    "    return outSQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check to see if flag Exists\n",
    "This function determines whether or not a flag exists in the global flag array. Absence of a value means it is false. The parameter can be a single value, or an array of values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def flag(inflag):\n",
    "    \n",
    "    global _flags\n",
    "\n",
    "    if isinstance(inflag,list):\n",
    "        for x in inflag:\n",
    "            if (x in _flags):\n",
    "                return True\n",
    "        return False\n",
    "    else:\n",
    "        if (inflag in _flags):\n",
    "            return True\n",
    "        else:\n",
    "            return False"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Generate a list of SQL lines based on a delimiter\n",
    "Note that this function will make sure that quotes are properly maintained so that delimiters inside of quoted strings do not cause errors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def splitSQL(inputString, delimiter):\n",
    "     \n",
    "    pos = 0\n",
    "    arg = \"\"\n",
    "    results = []\n",
    "    quoteCH = \"\"\n",
    "    \n",
    "    inSQL = inputString.strip()\n",
    "    if (len(inSQL) == 0): return(results)       # Not much to do here - no args found\n",
    "            \n",
    "    while pos < len(inSQL):\n",
    "        ch = inSQL[pos]\n",
    "        pos += 1\n",
    "        if (ch in ('\"',\"'\")):                   # Is this a quote characters?\n",
    "            arg = arg + ch                      # Keep appending the characters to the current arg\n",
    "            if (ch == quoteCH):                 # Is this quote character we are in\n",
    "                quoteCH = \"\"\n",
    "            elif (quoteCH == \"\"):               # Create the quote\n",
    "                quoteCH = ch\n",
    "            else:\n",
    "                None\n",
    "        elif (quoteCH != \"\"):                   # Still in a quote\n",
    "            arg = arg + ch\n",
    "        elif (ch == delimiter):                 # Is there a delimiter?\n",
    "            results.append(arg)\n",
    "            arg = \"\"\n",
    "        else:\n",
    "            arg = arg + ch\n",
    "            \n",
    "    if (arg != \"\"):\n",
    "        results.append(arg)\n",
    "        \n",
    "    return(results)"
   ]
  },
  {
   "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": {},
   "outputs": [],
   "source": [
    "@magics_class\n",
    "class DB2(Magics):\n",
    "   \n",
    "    @needs_local_scope    \n",
    "    @line_cell_magic\n",
    "    def sql(self, line, cell=None, local_ns=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, _environment\n",
    "        global _hdbc, _hdbi, _connected, _runtime, sqlstate, sqlerror, sqlcode, sqlelapsed\n",
    "             \n",
    "        # If you use %sql (line) we just run the SQL. If you use %%SQL the entire cell is run.\n",
    "        \n",
    "        flag_cell = False\n",
    "        flag_output = False\n",
    "        sqlstate = \"0\"\n",
    "        sqlerror = \"\"\n",
    "        sqlcode = 0\n",
    "        sqlelapsed = 0\n",
    "        \n",
    "        start_time = time.time()\n",
    "        end_time = time.time()\n",
    "              \n",
    "        # Macros gets expanded before anything is done\n",
    "                \n",
    "        SQL1 = setFlags(line.strip())  \n",
    "        SQL1 = checkMacro(SQL1)                                   # Update the SQL if any macros are in there\n",
    "        SQL2 = cell    \n",
    "        \n",
    "        if flag(\"-sampledata\"):                                   # Check if you only want sample data loaded\n",
    "            if (_connected == False):\n",
    "                if (db2_doConnect() == False):\n",
    "                    errormsg('A CONNECT statement must be issued before issuing SQL statements.')\n",
    "                    return                              \n",
    "                \n",
    "            db2_create_sample(flag([\"-q\",\"-quiet\"]))\n",
    "            return  \n",
    "        \n",
    "        if SQL1 == \"?\" or flag([\"-h\",\"-help\"]):                   # Are you asking for help\n",
    "            sqlhelp()\n",
    "            return\n",
    "        \n",
    "        if len(SQL1) == 0 and SQL2 == None: return                # Nothing to do here\n",
    "                \n",
    "        # Check for help\n",
    "\n",
    "        if SQL1.upper() == \"? CONNECT\":                           # Are you asking for help on CONNECT\n",
    "            connected_help()\n",
    "            return        \n",
    "        \n",
    "        sqlType,remainder = sqlParser(SQL1,local_ns)              # What type of command do you have?\n",
    "                \n",
    "        if (sqlType == \"CONNECT\"):                                # A connect request \n",
    "            parseConnect(SQL1,local_ns)\n",
    "            return\n",
    "        elif (sqlType == \"DEFINE\"):                               # Create a macro from the body\n",
    "            result = setMacro(SQL2,remainder)\n",
    "            return\n",
    "        elif (sqlType == \"OPTION\"):\n",
    "            setOptions(SQL1)\n",
    "            return \n",
    "        elif (sqlType == 'COMMIT' or sqlType == 'ROLLBACK' or sqlType == 'AUTOCOMMIT'):\n",
    "            parseCommit(remainder)\n",
    "            return\n",
    "        elif (sqlType == \"PREPARE\"):\n",
    "            pstmt = parsePExec(_hdbc, remainder)\n",
    "            return(pstmt)\n",
    "        elif (sqlType == \"EXECUTE\"):\n",
    "            result = parsePExec(_hdbc, remainder)\n",
    "            return(result)    \n",
    "        elif (sqlType == \"CALL\"):\n",
    "            result = parseCall(_hdbc, remainder, local_ns)\n",
    "            return(result)\n",
    "        else:\n",
    "            pass        \n",
    " \n",
    "        sql = SQL1\n",
    "    \n",
    "        if (sql == \"\"): sql = SQL2\n",
    "        \n",
    "        if (sql == \"\"): return                                   # Nothing to do here\n",
    "    \n",
    "        if (_connected == False):\n",
    "            if (db2_doConnect() == False):\n",
    "                errormsg('A CONNECT statement must be issued before issuing SQL statements.')\n",
    "                return      \n",
    "        \n",
    "        if _settings[\"maxrows\"] == -1:                                 # Set the return result size\n",
    "            pandas.reset_option('display.max_rows')\n",
    "        else:\n",
    "            pandas.options.display.max_rows = _settings[\"maxrows\"]\n",
    "      \n",
    "        runSQL = re.sub('.*?--.*$',\"\",sql,flags=re.M)\n",
    "        remainder = runSQL.replace(\"\\n\",\" \") \n",
    "        if flag([\"-d\",\"-delim\"]):\n",
    "            sqlLines = splitSQL(remainder,\"@\")\n",
    "        else:\n",
    "            sqlLines = splitSQL(remainder,\";\")\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",
    "            sqlin = checkMacro(sqlin)                                 # Update based on any macros\n",
    "\n",
    "            sqlType, sql = sqlParser(sqlin,local_ns)                           # Parse the SQL  \n",
    "            if (sql.strip() == \"\"): continue\n",
    "            if flag([\"-e\",\"-echo\"]): debug(sql,False)\n",
    "                \n",
    "            if flag(\"-t\"):\n",
    "                cnt = sqlTimer(_hdbc, _settings[\"runtime\"], sql)            # Given the sql and parameters, clock the time\n",
    "                if (cnt >= 0): print(\"Total iterations in %s second(s): %s\" % (_settings[\"runtime\"],cnt))                \n",
    "                return(cnt)\n",
    "            \n",
    "            elif flag([\"-pb\",\"-bar\",\"-pp\",\"-pie\",\"-pl\",\"-line\"]):                       # We are plotting some results \n",
    "                \n",
    "                plotData(_hdbi, sql)                            # Plot the data and return\n",
    "                return\n",
    " \n",
    "            else:\n",
    "        \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",
    "                        result = ibm_db.execute(stmt)                 # Run it                            \n",
    "                        if (result == False):                         # Error executing the code\n",
    "                            db2_error(flag([\"-q\",\"-quiet\"])) \n",
    "                            continue\n",
    "                            \n",
    "                        rowcount = ibm_db.num_rows(stmt)    \n",
    "                    \n",
    "                        if (rowcount == 0 and flag([\"-q\",\"-quiet\"]) == False):\n",
    "                            errormsg(\"No rows found.\")     \n",
    "                            \n",
    "                        continue                                      # Continue running\n",
    "                    \n",
    "                    elif flag([\"-r\",\"-array\",\"-j\",\"-json\"]):                     # raw, json, format json\n",
    "                        row_count = 0\n",
    "                        resultSet = []\n",
    "                        try:\n",
    "                            result = ibm_db.execute(stmt)             # Run it\n",
    "                            if (result == False):                         # Error executing the code\n",
    "                                db2_error(flag([\"-q\",\"-quiet\"]))  \n",
    "                                return\n",
    "                                \n",
    "                            if flag(\"-j\"):                          # JSON single output\n",
    "                                row_count = 0\n",
    "                                json_results = []\n",
    "                                while( ibm_db.fetch_row(stmt) ):\n",
    "                                    row_count = row_count + 1\n",
    "                                    jsonVal = ibm_db.result(stmt,0)\n",
    "                                    jsonDict = json.loads(jsonVal)\n",
    "                                    json_results.append(jsonDict)\n",
    "                                    flag_output = True                                    \n",
    "                                \n",
    "                                if (row_count == 0): sqlcode = 100\n",
    "                                return(json_results)\n",
    "                            \n",
    "                            else:\n",
    "                                return(fetchResults(stmt))\n",
    "                                  \n",
    "                        except Exception as err:\n",
    "                            db2_error(flag([\"-q\",\"-quiet\"]))\n",
    "                            return\n",
    "                            \n",
    "                    else:\n",
    "                        \n",
    "                        try:\n",
    "                            df = pandas.read_sql(sql,_hdbi)\n",
    "          \n",
    "                        except Exception as err:\n",
    "                            db2_error(False)\n",
    "                            return\n",
    "                    \n",
    "                        if (len(df) == 0):\n",
    "                            sqlcode = 100\n",
    "                            if (flag([\"-q\",\"-quiet\"]) == False): \n",
    "                                errormsg(\"No rows found\")\n",
    "                            continue                    \n",
    "                    \n",
    "                        flag_output = True\n",
    "                        if flag(\"-grid\") or _settings['display'] == 'GRID':   # Check to see if we can display the results\n",
    "                            if (_environment['qgrid'] == False):\n",
    "                                with pandas.option_context('display.max_rows', None, 'display.max_columns', None):  \n",
    "                                    print(df.to_string())\n",
    "                            else:\n",
    "                                try:\n",
    "                                    pdisplay(qgrid.show_grid(df))\n",
    "                                except:\n",
    "                                    errormsg(\"Grid cannot be used to display data with duplicate column names. Use option -a or %sql OPTION DISPLAY PANDAS instead.\")\n",
    "                                    return \n",
    "                        else:\n",
    "                            if flag([\"-a\",\"-all\"]) or _settings[\"maxrows\"] == -1 : # All of the rows\n",
    "                                pandas.options.display.max_rows = None\n",
    "                                pandas.options.display.max_columns = None\n",
    "                                return df # print(df.to_string())\n",
    "                            else:\n",
    "                                pandas.options.display.max_rows = _settings[\"maxrows\"]\n",
    "                                pandas.options.display.max_columns = None\n",
    "                                return df # pdisplay(df) # print(df.to_string())\n",
    " \n",
    "                except:\n",
    "                    db2_error(flag([\"-q\",\"-quiet\"]))\n",
    "                    continue # return\n",
    "                \n",
    "        end_time = time.time()\n",
    "        sqlelapsed = end_time - start_time\n",
    "        if (flag_output == False and flag([\"-q\",\"-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",
    "   \n",
    "success(\"Db2 Extensions Loaded.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pre-defined Macros\n",
    "These macros are used to simulate the LIST TABLES and DESCRIBE commands that are available from within the Db2 command line."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql define LIST\n",
    "#\n",
    "# The LIST macro is used to list all of the tables in the current schema or for all schemas\n",
    "#\n",
    "var syntax Syntax: LIST TABLES [FOR ALL | FOR SCHEMA name]\n",
    "# \n",
    "# Only LIST TABLES is supported by this macro\n",
    "#\n",
    "if {^1} <> 'TABLES'\n",
    "    exit {syntax}\n",
    "endif\n",
    "\n",
    "#\n",
    "# This SQL is a temporary table that contains the description of the different table types\n",
    "#\n",
    "WITH TYPES(TYPE,DESCRIPTION) AS (\n",
    "  VALUES\n",
    "    ('A','Alias'),\n",
    "    ('G','Created temporary table'),\n",
    "    ('H','Hierarchy table'),\n",
    "    ('L','Detached table'),\n",
    "    ('N','Nickname'),\n",
    "    ('S','Materialized query table'),\n",
    "    ('T','Table'),\n",
    "    ('U','Typed table'),\n",
    "    ('V','View'),\n",
    "    ('W','Typed view')\n",
    ")\n",
    "SELECT TABNAME, TABSCHEMA, T.DESCRIPTION FROM SYSCAT.TABLES S, TYPES T\n",
    "       WHERE T.TYPE = S.TYPE \n",
    "\n",
    "#\n",
    "# Case 1: No arguments - LIST TABLES\n",
    "#\n",
    "if {argc} == 1\n",
    "   AND OWNER = CURRENT USER\n",
    "   ORDER BY TABNAME, TABSCHEMA\n",
    "   return\n",
    "endif \n",
    "\n",
    "#\n",
    "# Case 2: Need 3 arguments - LIST TABLES FOR ALL\n",
    "#\n",
    "if {argc} == 3\n",
    "    if {^2}&{^3} == 'FOR&ALL'\n",
    "        ORDER BY TABNAME, TABSCHEMA\n",
    "        return\n",
    "    endif\n",
    "    exit {syntax}\n",
    "endif\n",
    "\n",
    "#\n",
    "# Case 3: Need FOR SCHEMA something here\n",
    "#\n",
    "if {argc} == 4\n",
    "    if {^2}&{^3} == 'FOR&SCHEMA'\n",
    "        AND TABSCHEMA = '{^4}'\n",
    "        ORDER BY TABNAME, TABSCHEMA\n",
    "        return\n",
    "    else\n",
    "        exit {syntax}\n",
    "    endif\n",
    "endif\n",
    "\n",
    "#\n",
    "# Nothing matched - Error\n",
    "#\n",
    "exit {syntax}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql define describe\n",
    "#\n",
    "# The DESCRIBE command can either use the syntax DESCRIBE TABLE <name> or DESCRIBE TABLE SELECT ...\n",
    "#\n",
    "var syntax Syntax: DESCRIBE [TABLE name | SELECT statement] \n",
    "#\n",
    "# Check to see what count of variables is... Must be at least 2 items DESCRIBE TABLE x or SELECT x\n",
    "#\n",
    "if {argc} < 2\n",
    "   exit {syntax}\n",
    "endif\n",
    "\n",
    "CALL ADMIN_CMD('{*0}');"
   ]
  },
  {
   "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. In addition, we skip this code if you are running in a shell environment rather than a Jupyter notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#%%html\n",
    "#<style>\n",
    "#  table {margin-left: 0 !important; text-align: left;}\n",
    "#</style>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Credits: IBM 202, 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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}