db2-2024-06-05.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DB2 Jupyter Notebook Extensions\n",
    "\n",
    "Version: 2024-06-05\n",
    "\n",
    "This code is imported as a Jupyter notebook extension in any notebooks you create with DB2 code in it. Place the following line of code in any notebook that you want to use these commands with:\n",
    "<pre>\n",
    "&#37;run db2.ipynb\n",
    "</pre>\n",
    "\n",
    "### Install Db2 Extensions\n",
    "\n",
    "To load the Db2 magic commands into your notebook, run the following command in your Jupyter notebook:\n",
    "```\n",
    "!wget https://raw.githubusercontent.com/IBM/db2-jupyter/master/db2.ipynb -O db2.ipynb\n",
    "```\n",
    "\n",
    "### 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 ibm_db --user\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Db2 Extensions Loaded.\n"
     ]
    }
   ],
   "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 2024: George Baklarz\n",
    "# Version 2024-06-05\n",
    "#\n",
    "\n",
    "from __future__ import print_function\n",
    "import multiprocessing\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 getpass\n",
    "import pickle\n",
    "import time\n",
    "import re\n",
    "import warnings\n",
    "import matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "import sys\n",
    "import copy\n",
    "\n",
    "_db2magic_version = \"2024-06-05\"\n",
    "\n",
    "#\n",
    "#   Empty settings creates a structure that we will use for saving control information\n",
    "#   on the database. Needs to be at the front of the code to be found in a Python \n",
    "#   cell.\n",
    "#\n",
    "\n",
    "def emptySettings():\n",
    "\n",
    "    settings = {\n",
    "     \"maxrows\"     : 10,\n",
    "     \"maxgrid\"     : 250,\n",
    "     \"display\"     : \"PANDAS\",\n",
    "     \"threads\"     : 0,\n",
    "     \"database\"    : \"\",\n",
    "     \"hostname\"    : \"localhost\",\n",
    "     \"port\"        : \"50000\",\n",
    "     \"protocol\"    : \"TCPIP\",    \n",
    "     \"uid\"         : \"DB2INST1\",\n",
    "     \"pwd\"         : \"password\",\n",
    "     \"ssl\"         : \"\",\n",
    "     \"dsn\"         : \"\",\n",
    "     \"sslfile\"     : \"\",\n",
    "     \"apikey\"      : \"\",\n",
    "     \"accesstoken\" : \"\",\n",
    "    }\n",
    "\n",
    "    return settings\n",
    "\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "_settings = copy.deepcopy(emptySettings())\n",
    "\n",
    "_environment = {\n",
    "     \"jupyter\"  : True,\n",
    "     \"grid\"     : True\n",
    "}\n",
    "\n",
    "# Db2 and Pandas data types\n",
    "\n",
    "\n",
    "_db2types = [\"unknown\",\n",
    "             \"string\",\n",
    "             \"smallint\",\n",
    "             \"int\",\n",
    "             \"bigint\",          \n",
    "             \"real\",\n",
    "             \"float\",\n",
    "             \"decfloat16\",\n",
    "             \"decfloat34\",\n",
    "             \"decimal\",\n",
    "             \"boolean\",\n",
    "             \"clob\",\n",
    "             \"blob\",\n",
    "             \"xml\",\n",
    "             \"date\",\n",
    "             \"time\",\n",
    "             \"timestamp\"]\n",
    "\n",
    "_pdtypes =  [\"object\",\n",
    "             \"string\",\n",
    "             \"Int16\",\n",
    "             \"Int32\",\n",
    "             \"Int64\",\n",
    "             \"float32\",\n",
    "             \"float64\",\n",
    "             \"float64\",\n",
    "             \"float64\",\n",
    "             \"float64\",\n",
    "             \"boolean\", \n",
    "             \"string\", \n",
    "             \"object\",\n",
    "             \"string\",\n",
    "             \"string\",\n",
    "             \"string\",\n",
    "             \"datetime64[ns]\"] \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",
    "    from ipydatagrid import DataGrid\n",
    "except:\n",
    "    _environment['grid'] = False\n",
    "\n",
    "if (_environment['grid'] == False):\n",
    "    print(\"Warning: pydatagrid is unavailable for displaying results in scrollable windows.\")\n",
    "    print(\"         Install pydatagrid if you want to enable scrolling of result sets.\")\n",
    "\n",
    "# Check if we are running in iPython or Jupyter\n",
    "\n",
    "_environment['jupyter'] = True\n",
    "\n",
    "try:\n",
    "    if (get_ipython().config == {}): \n",
    "        _environment['jupyter'] = False\n",
    "        _environment['grid'] = False\n",
    "    else:\n",
    "        _environment['jupyter'] = True\n",
    "except:\n",
    "    _environment['jupyter'] = False\n",
    "    _environment['grid'] = False\n",
    "    \n",
    "# Check if pandas supports data types in the data frame - Introduced in 1.3 of pandas\n",
    "\n",
    "_pandas_dtype = False\n",
    "try:\n",
    "    _vrm = pandas.__version__.split(\".\")\n",
    "    _version  = 0\n",
    "    _release  = 0\n",
    "    _modlevel = 0\n",
    "    if (len(_vrm) >= 1):\n",
    "        _version  = int(_vrm[0])\n",
    "    if (len(_vrm) >= 2):\n",
    "        _release  = int(_vrm[1])\n",
    "    if (len(_vrm) >= 3):\n",
    "        _modlevel = int(_vrm[2])\n",
    "    if ((_version >= 1 and _release >= 3) or _version > 1):\n",
    "        _pandas_dtype = True\n",
    "    else:\n",
    "        _pandas_dtype = False\n",
    "except:\n",
    "    _pandas_dtype = False\n",
    "\n",
    "if (_pandas_dtype == False):\n",
    "    print(\"Warning: PANDAS level does not support Db2 typing which will can increase memory usage.\")\n",
    "    print(\"         Install PANDAS version 1.3+ for more efficient dataframe creation.\")\n",
    "    \n",
    "# Check if we have parallism available\n",
    "\n",
    "if (sys.platform == \"darwin\"):\t\t\t\t\t# For the Mac we need multiprocess\n",
    "    try:\n",
    "        import multiprocess as mp\t\t\t\t# Check to see if it exists\n",
    "        from multiprocess.sharedctypes import Value, Array\n",
    "        _parallel = True\n",
    "    except:\n",
    "        print(\"Warning: On OSX you need to pip install the multiprocess package if you want\")\n",
    "        print(\"         parallelism to work.\")\n",
    "        _parallel = False\n",
    "else:\n",
    "    try:\n",
    "        import multiprocessing as mp\n",
    "        from multiprocessing.sharedctypes import Value, Array\n",
    "        _parallel = True\n",
    "    except:\n",
    "        _parallel = False\n",
    "    \n",
    "if (_parallel == False):\n",
    "    print(\"Warning: Parallelism is unavailable and THREADS option will be ignored.\")\n",
    "    print(\"         Install MULTIPROCESSING/MULTIPROCESS(OSX) if you want allow\")\n",
    "    print(\"         multiple SQL threads to run in parallel.\")  \n",
    "    _settings[\"threads\"] = 0\n",
    "\n",
    "#\n",
    "# Set Options for the Db2 Magic Commands\n",
    "#\n",
    "\n",
    "def setOptions(inSQL):\n",
    "\n",
    "    global _settings\n",
    "\n",
    "    cParms = inSQL.split()\n",
    "    cnt = 0\n",
    "    \n",
    "    if (len(cParms) == 1):\n",
    "        print(\"(MAXROWS) Maximum number of rows displayed: \" + str(_settings.get(\"maxrows\",10)))\n",
    "        print(\"(MAXGRID) Maximum grid display size: \" + str(_settings.get(\"maxgrid\",100)))\n",
    "        print(\"(DISPLAY) Use PANDAS or GRID display format for output: \" + _settings.get(\"display\",\"PANDAS\"))  \n",
    "        print(\"(THREADS) Maximum number of threads to use when running SQL: \" + str(_settings.get(\"threads\",0)))     \n",
    "        return\n",
    "\n",
    "    while cnt < len(cParms):\n",
    "        \n",
    "        if cParms[cnt][0] == \"?\":\n",
    "            print(\"%sql OPTION MAXROWS n MAXGRID n DISPLAY n THREADS n\")\n",
    "            print(\"LIST      - List the current option settings\")\n",
    "            print(\"MAXROWS n - The maximum number of rows displayed when returning results\")\n",
    "            print(\"MAXGRID n - Maximum size of a scrollable GRID window\")\n",
    "            print(\"THREADS n - Maximum number of parallel threads to use when running SQL\")\n",
    "            return\n",
    "        \n",
    "        if cParms[cnt].upper() == 'MAXROWS':\n",
    "            \n",
    "            if cnt+1 < len(cParms):\n",
    "                try:\n",
    "                    _settings[\"maxrows\"] = int(cParms[cnt+1])\n",
    "                    if (_settings[\"maxrows\"] > 100 or _settings[\"maxrows\"] <= 0):\n",
    "                        _settings[\"maxrows\"] = 100\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 <= 100):                      # Minimum window size is 100px\n",
    "                        maxgrid = 100\n",
    "                    _settings[\"maxgrid\"] =  int(maxgrid)\n",
    "                    \n",
    "                except Exception as err:\n",
    "                    errormsg(\"Invalid MAXGRID value provided.\")\n",
    "                    pass\n",
    "                cnt = cnt + 1\n",
    "            else:\n",
    "                errormsg(\"No maximum table size (pixels) specified for the MAXGRID 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",
    "            \n",
    "        elif cParms[cnt].upper() == 'THREADS':\n",
    "            if cnt+1 < len(cParms):\n",
    "                try:\n",
    "                    threads = int(cParms[cnt+1])\n",
    "                    if (threads < 0):\n",
    "                        threads = 0\n",
    "                    elif (threads > 12):\n",
    "                        threads = 12\n",
    "                    else:\n",
    "                        pass\n",
    "                    _settings[\"threads\"] = threads\n",
    "                except Exception as err:\n",
    "                    errormsg(\"Invalid THREADS value provided.\")\n",
    "                    pass\n",
    "                cnt = cnt + 1\n",
    "            else:\n",
    "                errormsg(\"No thread count specified for the THREADS option.\")\n",
    "                return\n",
    "            \n",
    "        elif (cParms[cnt].upper() == 'LIST'):\n",
    "            print(\"(MAXROWS) Maximum number of rows displayed: \" + str(_settings.get(\"maxrows\",10)))\n",
    "            print(\"(MAXGRID) Maximum grid display size in pixels: \" + str(_settings.get(\"maxgrid\",100)))\n",
    "            print(\"(DISPLAY) Use PANDAS or GRID display format for output: \" + _settings.get(\"display\",\"PANDAS\"))\n",
    "            print(\"(THREADS) Maximum number of threads to use when running SQL: \" + str(_settings.get(\"threads\",0)))\n",
    "            return\n",
    "        else:\n",
    "            cnt = cnt + 1\n",
    "            \n",
    "    save_settings(_settings)\n",
    "\n",
    "    print(\"(MAXROWS) Maximum number of rows displayed: \" + str(_settings.get(\"maxrows\",10)))\n",
    "    print(\"(MAXGRID) Maximum grid display size: \" + str(_settings.get(\"maxgrid\",100)))\n",
    "    print(\"(DISPLAY) Use PANDAS or GRID display format for output: \" + _settings.get(\"display\",\"PANDAS\"))\n",
    "    print(\"(THREADS) Maximum number of threads to use when running SQL: \" + str(_settings.get(\"threads\",0)))\n",
    "    \n",
    "    return\t\n",
    "\n",
    "#\n",
    "# Display help (link to documentation)\n",
    "#\n",
    "\n",
    "def sqlhelp():\n",
    "    \n",
    "    print(\"Db2 Magic Documentation: https://ibm.github.io/db2-jupyter/\")\n",
    "    return\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",
    "    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\n",
    "\n",
    "# Parse the CONNECT statement and execute if possible \n",
    "\n",
    "def parseConnect(inSQL,local_ns):\n",
    "\n",
    "    import shlex\n",
    "    \n",
    "    global _settings, _connected\n",
    "\n",
    "    _connected = False\n",
    "    \n",
    "    cParms = shlex.split(inSQL) # inSQL.split()\n",
    "    cnt = 0\n",
    "\n",
    "    connection = copy.deepcopy(emptySettings())\n",
    "\n",
    "    # CONNECT {no arguments}\n",
    "\n",
    "    if (len(cParms) == 1):\n",
    "        connection = copy.deepcopy(_settings)\n",
    "        _ = db2_doConnect(connection)\n",
    "        return\n",
    "    \n",
    "    # Check CONNECT arguments\n",
    "  \n",
    "    while cnt < len(cParms):\n",
    "\n",
    "        if cParms[cnt].upper() == 'TO':\n",
    "            if cnt+1 < len(cParms):\n",
    "                connection[\"database\"] = cParms[cnt+1].upper()\n",
    "                cnt = cnt + 1\n",
    "            else:\n",
    "                errormsg(\"No database specified in the CONNECT statement\")\n",
    "                return\n",
    "            \n",
    "        elif cParms[cnt].upper() == \"SSL\":\n",
    "            connection[\"ssl\"] = \"SSL\"\n",
    "\n",
    "        elif cParms[cnt].upper() == \"SSLFILE\":\n",
    "            if cnt+1 < len(cParms):\n",
    "                cert = cParms[cnt+1]\n",
    "                connection[\"sslfile\"] = cert\n",
    "                connection[\"ssl\"] = \"SSL\"\n",
    "                cnt = cnt + 1\n",
    "            else:\n",
    "                errormsg(\"No filename provided for the SSLFILE option\")\n",
    "                return\n",
    "            \n",
    "        elif cParms[cnt].upper() == 'CREDENTIALS':\n",
    "            if cnt+1 < len(cParms):\n",
    "                credentials = cParms[cnt+1]\n",
    "                if (credentials in local_ns):\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",
    "                else:\n",
    "                    tempid = None\n",
    "                    \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",
    "                    connection[\"database\"]      = _id.get(\"database\",\"\")\n",
    "                    connection[\"hostname\"]      = _id.get(\"hostname\",\"\")\n",
    "                    connection[\"port\"]          = _id.get(\"port\",\"50000\")\n",
    "                    connection[\"uid\"]           = _id.get(\"uid\",\"\")\n",
    "                    connection[\"pwd\"]           = _id.get(\"pwd\",\"\")\n",
    "                    connection[\"ssl\"]           = _id.get(\"ssl\",\"\")\n",
    "                    connection[\"sslfile\"]       = _id.get(\"sslfile\",\"\")\n",
    "                    connection[\"apikey\"]        = _id.get(\"apikey\",\"\")\n",
    "                    connection[\"accesstoken\"]   = _id.get(\"accesstoken\",\"\")\n",
    "                    connection[\"dsn\"]           = _id.get(\"dsn\",\"\")\n",
    "                    connection[\"protocol\"]      = _id.get(\"protocol\",\"TCPIP\")\n",
    "                    connection[\"maxgrid\"]       = _id.get(\"maxgrid\",250)\n",
    "                    connection[\"display\"]       = _id.get(\"display\",\"PANDAS\")\n",
    "                    connection[\"threads\"]       = _id.get(\"threads\",0)\n",
    "                    connection[\"maxrows\"]       = _id.get(\"maxrows\",250)\n",
    "        \n",
    "                    try:\n",
    "                        fname = credentials + \".pickle\"\n",
    "                        with open(fname,'wb') as f:\n",
    "                            pickle.dump(connection,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",
    "                connection[\"uid\"] = cParms[cnt+1]\n",
    "                cnt = cnt + 1\n",
    "            else:\n",
    "                errormsg(\"No userid specified in the CONNECT statement\")\n",
    "                return\n",
    "            \n",
    "        elif cParms[cnt].upper() == 'USING':\n",
    "            if cnt+1 < len(cParms):\n",
    "                connection[\"pwd\"] = cParms[cnt+1]   \n",
    "                if (connection.get(\"pwd\",\"?\") == '?'):\n",
    "                    connection[\"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",
    "            \n",
    "        elif cParms[cnt].upper() == 'HOST':\n",
    "            if cnt+1 < len(cParms):\n",
    "                hostport = cParms[cnt+1]\n",
    "                ip, port = split_string(hostport)\n",
    "                if (port == None): connection[\"port\"] = \"50000\"\n",
    "                connection[\"hostname\"] = ip\n",
    "                cnt = cnt + 1\n",
    "            else:\n",
    "                errormsg(\"No hostname specified in the CONNECT statement\")\n",
    "                return\n",
    "            \n",
    "        elif cParms[cnt].upper() == 'PORT':                           \n",
    "            if cnt+1 < len(cParms):\n",
    "                connection[\"port\"] = cParms[cnt+1]\n",
    "                cnt = cnt + 1\n",
    "            else:\n",
    "                errormsg(\"No port specified in the CONNECT statement\")\n",
    "                return\n",
    "            \n",
    "        elif cParms[cnt].upper() == 'DSN':                           \n",
    "            if cnt+1 < len(cParms):\n",
    "                connection[\"dsn\"] = cParms[cnt+1]\n",
    "                cnt = cnt + 1\n",
    "            else:\n",
    "                errormsg(\"No DSN string provided in the CONNECT statement\")\n",
    "                return\t\n",
    "            \n",
    "        elif cParms[cnt].upper() == 'APIKEY':                           \n",
    "            if cnt+1 < len(cParms):\n",
    "                connection[\"apikey\"] = cParms[cnt+1]\n",
    "                cnt = cnt + 1\n",
    "            else:\n",
    "                errormsg(\"No APIKEY string provided in the CONNECT statement\")\n",
    "                return\t            \n",
    "            \n",
    "        elif cParms[cnt].upper() == 'ACCESSTOKEN':                           \n",
    "            if cnt+1 < len(cParms):\n",
    "                connection[\"accesstoken\"] = cParms[cnt+1]\n",
    "                cnt = cnt + 1\n",
    "            else:\n",
    "                errormsg(\"No ACCESSTOKEN provided in the CONNECT statement\")\n",
    "                return\n",
    "            \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 = copy.deepcopy(emptySettings())\n",
    "                save_settings(_settings)\n",
    "            return\n",
    "        else:\n",
    "            pass\n",
    "        \n",
    "        cnt = cnt + 1\n",
    "                     \n",
    "    _ = db2_doConnect(connection)\n",
    "\n",
    "# Build the DSN string based on the connection information\n",
    "\n",
    "def buildDSN(connection,reconnect=False):\n",
    "    \n",
    "    database    = connection.get(\"database\",None)\n",
    "    hostname    = connection.get(\"hostname\",None)\n",
    "    port        = connection.get(\"port\",\"50000\")\n",
    "    uid         = connection.get(\"uid\",None)\n",
    "    pwd         = connection.get(\"pwd\",None)\n",
    "    ssl         = connection.get(\"ssl\",None)\n",
    "    sslfile     = connection.get(\"sslfile\",None)\n",
    "    apikey      = connection.get(\"apikey\",None)\n",
    "    accesstoken = connection.get(\"accesstoken\",None)\n",
    "    user_dsn    = connection.get(\"dsn\",None)\n",
    "\n",
    "    dsn = None\n",
    "\n",
    "    while True:\n",
    "\n",
    "        # CONNECT TO <database> HOST <host> PORT <port> APIKEY <apikey>\n",
    "\n",
    "        if (apikey not in [\"\",None]):       # APIKEY does not require a userid/password   \n",
    "\n",
    "            if (hostname in [None,\"\"] or port in [None,\"\"] or database in [None,\"\"]):\n",
    "                if (reconnect == False):\n",
    "                    errormsg(\"Connect using an APIKEY requires HOST, PORT, and DATABASE name\")\n",
    "                return None       \n",
    "\n",
    "            if (uid != None):\n",
    "                \n",
    "                dsn = (\"DRIVER={IBM DB2 ODBC DRIVER};\"\n",
    "                f\"DATABASE={database};\"\n",
    "                f\"HOSTNAME={hostname};\"\n",
    "                f\"PORT={port};\"\n",
    "                f\"PROTOCOL=TCPIP;ConnectTimeout=15;loginTimeout=15;LONGDATACOMPAT=1;\"\n",
    "                f\"UID={uid};\"\n",
    "                f\"AUTHENTICATION=GSSplugin;SECURITY=SSL;\"\n",
    "                f\"APIKEY={apikey};\")\n",
    "                \n",
    "            else:\n",
    "\n",
    "                dsn = (\"DRIVER={IBM DB2 ODBC DRIVER};\"\n",
    "                f\"DATABASE={database};\"\n",
    "                f\"HOSTNAME={hostname};\"\n",
    "                f\"PORT={port};\"\n",
    "                f\"PROTOCOL=TCPIP;ConnectTimeout=15;loginTimeout-15;LONGDATACOMPAT=1;\"\n",
    "                f\"AUTHENTICATION=GSSplugin;SECURITY=SSL;\"\n",
    "                f\"APIKEY={apikey};\")\n",
    "\n",
    "            break\n",
    "\n",
    "        # CONNECT TO <database> HOST <host> PORT <port> ACCESSTOKEN <accesstoken>\n",
    "\n",
    "        if (accesstoken not in [\"\",None]): # ACCESSTOKEN does not require a userid/password\n",
    "\n",
    "            if (hostname in [None,\"\"] or port in [None,\"\"] or database in [None,\"\"]):\n",
    "                if (reconnect == False):\n",
    "                    errormsg(\"Connect using an ACCESSTOKEN requires HOST, PORT, and DATABASE name\")\n",
    "                return None               \n",
    "\n",
    "            dsn = (\"DRIVER={IBM DB2 ODBC DRIVER};\"\n",
    "            f\"DATABASE={database};\"\n",
    "            f\"HOSTNAME={hostname};\"\n",
    "            f\"PORT={port};\"\n",
    "            f\"PROTOCOL=TCPIP;ConnectTimeout=15;loginTimeout=15;LONGDATACOMPAT=1;\"\n",
    "            f\"AUTHENTICATION=GSSplugin;SECURITY=SSL;\"\n",
    "            f\"ACCESSTOKEN={accesstoken};\")\n",
    "\n",
    "            break\n",
    "\n",
    "        # CONNECT DSN <dsn>\n",
    "\n",
    "        if (user_dsn not in [\"\",None]): # Absolutely no checking is done on your DSN Setting\n",
    "\n",
    "            dsn = user_dsn\n",
    "            break\n",
    "\n",
    "        # CONNECT TO <database> HOST <host> PORT <port> <SSL> SSLFILE <sslfile>\n",
    "\n",
    "        if (hostname in [None,\"\"] or port in [None,\"\"] or database in [None,\"\"]):\n",
    "            if (reconnect == False):\n",
    "                errormsg(\"Connect requires a HOST, PORT, and DATABASE name\")\n",
    "            return None            \n",
    "        \n",
    "        if (uid in [\"\",None] or pwd in [\"\",None]): \n",
    "            if (reconnect == False):\n",
    "                errormsg(\"Connect requires a userid and password\")\n",
    "            return None\n",
    "\n",
    "        if (ssl not in [\"\",None] or sslfile not in [\"\",None]): # See if you have specified SSL connection\n",
    "\n",
    "            if (sslfile not in [\"\",None]):\n",
    "\n",
    "                dsn = (\"DRIVER={IBM DB2 ODBC DRIVER};\"\n",
    "                f\"DATABASE={database};\"\n",
    "                f\"HOSTNAME={hostname};\"\n",
    "                f\"PORT={port};\"\n",
    "                f\"UID={uid};\"\n",
    "                f\"PWD={pwd};\"\n",
    "                f\"PROTOCOL=TCPIP;ConnectTimeout=15;loginTimeout=15;LONGDATACOMPAT=1;\"\n",
    "                f\"SECURITY=SSL;SSLServerCertificate={sslfile};\")         \n",
    "\n",
    "            else:\n",
    "                        \n",
    "                dsn = (\"DRIVER={IBM DB2 ODBC DRIVER};\"\n",
    "                f\"DATABASE={database};\"\n",
    "                f\"HOSTNAME={hostname};\"\n",
    "                f\"PORT={port};\"\n",
    "                f\"UID={uid};\"\n",
    "                f\"PWD={pwd};\"\n",
    "                f\"PROTOCOL=TCPIP;ConnectTimeout=15;loginTimeout=15;LONGDATACOMPAT=1;\"\n",
    "                f\"SECURITY=SSL;\")   \n",
    "\n",
    "        else:\n",
    "                dsn = (\"DRIVER={IBM DB2 ODBC DRIVER};\"\n",
    "                f\"DATABASE={database};\"\n",
    "                f\"HOSTNAME={hostname};\"\n",
    "                f\"PORT={port};\"\n",
    "                f\"UID={uid};\"\n",
    "                f\"PWD={pwd};\"\n",
    "                f\"PROTOCOL=TCPIP;ConnectTimeout=15;loginTimeout=15;LONGDATACOMPAT=1;\")\n",
    "\n",
    "        break\n",
    "        \n",
    "    return dsn\n",
    "\n",
    "def db2_doConnect(connection):\n",
    "    \n",
    "    global _hdbc, _hdbi, _connected\n",
    "    global _settings  \n",
    "\n",
    "    reconnect = False\n",
    "\n",
    "    if (connection == None):\n",
    "        reconnect = True\n",
    "        connection = copy.deepcopy(_settings)\n",
    "\n",
    "    database    = connection.get(\"database\",None)\n",
    "    hostname    = connection.get(\"hostname\",None)  \n",
    "\n",
    "    dsn = buildDSN(connection,reconnect=reconnect)\n",
    "\n",
    "    if flag([\"-e\",\"-echo\"]) and dsn not in [None, \"\"]:\n",
    "        print(dsn)\n",
    "        return False\n",
    "\n",
    "    if (dsn in [None,\"\"]):\n",
    "        if (reconnect == False):\n",
    "            errormsg(\"Empty connection string\")\n",
    "        return False\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",
    "        print(repr(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",
    "        print(repr(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(connection)\n",
    "    \n",
    "    success(f\"Connection successful. {database} @ {hostname} \")\n",
    "    return True\n",
    "    \n",
    "\n",
    "def load_settings():\n",
    "\n",
    "    # This routine will load the settings from the previous session if they exist\n",
    "    \n",
    "    global _settings\n",
    "    \n",
    "    fname = \"db2connect.pickle\"\n",
    "\n",
    "    try:\n",
    "        with open(fname,'rb') as f: \n",
    "            _settings = pickle.load(f) \n",
    "        \n",
    "    except: \n",
    "        pass\n",
    "    \n",
    "    return\n",
    "\n",
    "def save_settings(connection):\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(connection,f)\n",
    "        _settings = copy.deepcopy(connection)\n",
    "            \n",
    "    except:\n",
    "        errormsg(\"Failed trying to write Db2 Configuration Information.\")\n",
    " \n",
    "    return  \n",
    "\n",
    "def db2_error(quiet,connect=False):\n",
    "    \n",
    "    global sqlerror, sqlcode, sqlstate, _environment\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 not in (None,\"\")):\n",
    "        print(message)\n",
    "    return   \n",
    "\n",
    "def debug(message,error=False):\n",
    "    \n",
    "    global _environment\n",
    "    \n",
    "    if (message in (None,\"\")):\n",
    "        return\n",
    "    \n",
    "    if (_environment[\"jupyter\"] == True):\n",
    "        spacer = \"<br>\" + \"&nbsp;\"\n",
    "    else:\n",
    "        spacer = \"\\n \"\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 \n",
    "\n",
    "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 # inSQL.replace(\"\\t\",\" \")\n",
    "\n",
    "    return\n",
    "\n",
    "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\n",
    "\n",
    "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\n",
    "\n",
    "def parseArgs(argin,_vars):\n",
    "\n",
    "    quoteChar = \"\"\n",
    "    blockChar = \"\"\n",
    "    inQuote = False\n",
    "    inBlock = False\n",
    "    inArg = True\n",
    "    args = []\n",
    "    arg = ''\n",
    "\n",
    "    for ch in argin.lstrip():\n",
    "        if (inBlock == True):\n",
    "            if (ch == \")\"):\n",
    "                inBlock = False\n",
    "                arg = arg + ch\n",
    "            else:\n",
    "                arg = arg + ch\n",
    "        elif (inQuote == True):\n",
    "            if (ch == quoteChar):\n",
    "                inQuote = False   \n",
    "                arg = arg + ch #z\n",
    "            else:\n",
    "                arg = arg + ch\n",
    "        elif (ch == \"(\"): # Do we have a block\n",
    "            arg = arg + ch\n",
    "            inBlock = True\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)\n",
    "\n",
    "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",
    "    flags = \"\"\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 in_sql\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] == \"flags\" and runlevel[level] == True):\n",
    "            if (len(args) > 1):\n",
    "                for i in range(1,len(args)):\n",
    "                    flags = flags + \" \" + args[i]\n",
    "                flags = flags.strip()\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(f\"{flags} {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(f\"{flags} {result}\")      \n",
    "\n",
    "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].strip()\n",
    "        remainder = remainder[ev+1:]\n",
    "        \n",
    "        modifier = \"\"\n",
    "        \n",
    "        if (len(vvar) == 0):\n",
    "            errormsg(f\"No variable name supplied in the braces {{}}.\")\n",
    "            return script\n",
    "        \n",
    "        upper = False\n",
    "        allvars = False\n",
    "        concat = \" \"\n",
    "        \n",
    "        if (len(vvar) > 1):\n",
    "            modifier = vvar[0]\n",
    "            if (modifier == \"^\"):\n",
    "                upper = True\n",
    "                vvar = vvar[1:]\n",
    "            elif (modifier == \"*\"):\n",
    "                vvar = vvar[1:]\n",
    "                allvars = True\n",
    "                concat = \" \"\n",
    "            elif (vvar[0] == \",\"):\n",
    "                vvar = vvar[1:]\n",
    "                allvars = True\n",
    "                concat = \",\"\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 + concat + _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)\n",
    "\n",
    "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\n",
    "\n",
    "def createDF(hdbc,hdbi,sqlin,local_ns):\n",
    "    \n",
    "    import datetime\n",
    "    import ibm_db    \n",
    "    import shlex\n",
    "    \n",
    "    global sqlcode, _settings, _parallel\n",
    "    \n",
    "    NoDF  = False\n",
    "    YesDF = True\n",
    "    \n",
    "    if (hdbc == None or hdbi == None):\n",
    "        errormsg(\"You need to connect to a database before issuing this command.\")\n",
    "        return NoDF, None\n",
    "    \n",
    "    # Strip apart the command into tokens based on spaces\n",
    "\n",
    "    tokens = shlex.split(sqlin)\n",
    "\n",
    "    token_count = len(tokens)\n",
    "    \n",
    "    if (token_count < 5): # Not enough parameters\n",
    "        errormsg(\"Insufficient arguments for USING command.\")\n",
    "        return NoDF, None\n",
    "        \n",
    "    keyword_command = tokens[0].upper()\n",
    "    dfName          = tokens[1]\n",
    "    keyword_create  = tokens[2].upper()\n",
    "    keyword_table   = tokens[3].upper()\n",
    "    table           = tokens[4]           \n",
    "    \n",
    "    if (dfName not in local_ns):\n",
    "        errormsg(f'The variable \"{dfName}\" does not exist in the local variable list.')\n",
    "        return NoDF, None    \n",
    "\n",
    "    try:\n",
    "        dfValue = eval(dfName,None,local_ns) # globals()[varName] # eval(varName)\n",
    "    except:\n",
    "        errormsg(f'The variable \"{dfName}\" does not contain a value.')\n",
    "        return NoDF, None     \n",
    "   \n",
    "    if (keyword_create in (\"SELECT\",\"WITH\")):\n",
    "        \n",
    "        if (_parallel == False):\n",
    "            errormsg(\"Parallelism is not availble on this system.\")\n",
    "            return NoDF, None\n",
    " \n",
    "        thread_count = _settings.get(\"threads\",0)\n",
    "        if (thread_count in (0,1)):\n",
    "            errormsg(\"The THREADS option is currently set to 0 or 1 which disables parallelism.\")\n",
    "            return NoDF, None      \n",
    "        \n",
    "        ok, df = dfSQL(hdbc,hdbi,sqlin,dfName,dfValue,thread_count)\n",
    "        \n",
    "        if (ok == False):\n",
    "            return NoDF, None\n",
    "        else:\n",
    "            return YesDF, df\n",
    "                \n",
    "    if (isinstance(dfValue,pandas.DataFrame) == False): # Not a Pandas dataframe\n",
    "        errormsg(f'The variable \"{dfName}\" is not a Pandas dataframe.')\n",
    "        return NoDF, None\n",
    "    \n",
    "    if (keyword_create not in (\"CREATE\",\"REPLACE\",\"APPEND\",\"DECLARE\") or keyword_table != \"TABLE\"):\n",
    "        errormsg(\"Incorrect syntax: %sql using <df> [create | replace | append | declare] table <name> [options]\")\n",
    "        return NoDF, None\n",
    "        \n",
    "    flag_withdata = False\n",
    "    flag_asis     = False\n",
    "    flag_export   = False\n",
    "    flag_ddl      = False\n",
    "    clob_type     = \"CLOB\"\n",
    "    blob_type     = \"BLOB\"\n",
    "    char_type     = \"VARCHAR\"\n",
    "    limit         = -1\n",
    "    column_list   = []\n",
    "    column_index  = []\n",
    "    padding       = 1.0\n",
    "        \n",
    "    token_idx = 5\n",
    "    while (token_idx < token_count):\n",
    "        option_key = tokens[token_idx].upper()\n",
    "        if (token_idx + 1 >= token_count):\n",
    "            errormsg(f\"Insufficient arguments for the {option_key} option.\")\n",
    "            return NoDF, None\n",
    "        option_val = tokens[token_idx+1].upper()\n",
    "        if (option_key == \"WITH\" and option_val == \"DATA\"):\n",
    "            flag_withdata = True\n",
    "            token_idx += 2\n",
    "        elif (option_key == \"NAMES\" and option_val == \"ASIS\"):\n",
    "            flag_asis = True\n",
    "            token_idx += 2\n",
    "        elif (option_key == \"DDL\"):\n",
    "            if (option_val == \"EXPORT\"):\n",
    "                flag_export = True\n",
    "            elif (option_val == \"ONLY\"):\n",
    "                flag_ddl = True\n",
    "            else:\n",
    "                errormsg(\"DDL option requires EXPORT or ONLY keyword\")\n",
    "                return NoDF, None\n",
    "            token_idx += 2  \n",
    "        elif (option_key in [\"NCHAR\",\"PADDING\"]):\n",
    "            try:\n",
    "                padding = float(option_val)\n",
    "            except:\n",
    "                errormsg(\"The NCHAR/PADDING value must be a valid decimal value greater than 1.0\")\n",
    "                return NoDF, None\n",
    "            if (padding <= 1.0 or padding > 5.0):\n",
    "                padding = 1.0 \n",
    "            token_idx += 2\n",
    "        elif (option_key == \"LIMIT\"):\n",
    "            if (option_val.isnumeric() == False):\n",
    "                errormsg(\"The LIMIT must be a valid number from -1 (unlimited) to the maximun number of rows to insert\")\n",
    "                return NoDF, None\n",
    "            limit = int(option_val)\n",
    "            token_idx += 2\n",
    "        elif (option_key == \"COLUMNS\"):\n",
    "            column_list == []\n",
    "            for i in range (token_idx + 1, token_count):\n",
    "                column_list.append(tokens[i].upper())\n",
    "            token_idx = token_count\n",
    "        else:\n",
    "            errormsg(\"Invalid option. [WITH DATA] [DDL (ONLY | EXPORT)] [NAMES ASIS] [PADDING x] [LIMIT count] [COLUMNS list]\")\n",
    "            return NoDF, None   \n",
    "\n",
    "    if (keyword_create == \"REPLACE\" and (flag_ddl == False and flag_export == False)):\n",
    "        sql = f\"DROP TABLE {table}\"\n",
    "        ok = execSQL(hdbc,sql,quiet=True)   \n",
    "\n",
    "    sql = [] \n",
    "    columns = dict(dfValue.dtypes)\n",
    "    if (keyword_create == \"DECLARE\"):\n",
    "        sql.append(f'CREATE GLOBAL TEMPORARY TABLE {table} (')\n",
    "    else:\n",
    "        sql.append(f'CREATE TABLE {table} (')\n",
    "    datatypes = []\n",
    "    sql_types = []\n",
    "    column_names = []\n",
    "    comma = \"\"\n",
    "\n",
    "    idx = -1\n",
    "\n",
    "    temp_column_list = []\n",
    "    for column in columns:\n",
    "        temp_column_list.append(column.upper())\n",
    "\n",
    "    for column_requested in column_list:\n",
    "        if (column_requested not in temp_column_list):\n",
    "            errormsg(f'Column \"{column_requested}\" was not found in the dataframe.')\n",
    "            return NoDF, None             \n",
    "\n",
    "    for column in columns:\n",
    "\n",
    "        if (len(column_list) != 0):\n",
    "            if (column.upper() not in column_list):\n",
    "                idx += 1\n",
    "                continue\n",
    "            column_found = True\n",
    "\n",
    "        idx += 1\n",
    "        datatype = str(columns[column])\n",
    "        datatype = datatype.upper()\n",
    "        column_index.append(idx)\n",
    " \n",
    "        if (datatype == \"OBJECT\"):\n",
    "            maxlength = dfValue[column].apply(str).apply(lambda x: len(x.encode('utf-8'))).max() \n",
    "            if (maxlength <= 0): maxlength = 1\n",
    "            adjusted = int(float(maxlength) * padding) \n",
    "            if (adjusted < 32000):\n",
    "                type = f\"{char_type}({adjusted})\" #f\"VARCHAR({adjusted})\"\n",
    "                sql_type = ibm_db.SQL_VARCHAR\n",
    "            else:\n",
    "                type = f\"{clob_type}({adjusted})\" # \"CLOB({adjusted})\"\n",
    "                if (\"DBCLOB\" in clob_type):\n",
    "                    sql_type = ibm_db.SQL_DBCLOB    \n",
    "                else:            \n",
    "                    sql_type = ibm_db.SQL_CLOB # lob_sqltype\n",
    "        elif (datatype in [\"INT64\",\"INT32\",\"INT16\",\"INT8\"]):\n",
    "            count = dfValue[column].count()\n",
    "            if (count == 0):\n",
    "                maxlength = dfValue[column].apply(str).apply(lambda x: len(x.encode('utf-8'))).max() \n",
    "                if (maxlength <= 0): maxlength = 1\n",
    "                adjusted = int(float(maxlength) * padding) \n",
    "                type = f\"{char_type}({adjusted})\" \n",
    "                sql_type = ibm_db.SQL_VARCHAR\n",
    "            else:\n",
    "                if (datatype == \"INT64\"):\n",
    "                    sql_type = ibm_db.SQL_BIGINT\n",
    "                    type = \"BIGINT\"\n",
    "                elif (datatype == \"INT32\"):\n",
    "                    sql_type = ibm_db.SQL_INTEGER\n",
    "                    type = \"INT\"\n",
    "                elif (datatype == \"INT16\"):\n",
    "                    sql_type = ibm_db.SQL_SMALLINT\n",
    "                    type = \"SMALLINT\"                     \n",
    "                else:\n",
    "                    sql_type = ibm_db.SQL_SMALLINT\n",
    "                    type = \"SMALLINT\"\n",
    "        elif (datatype in [\"FLOAT64\",\"FLOAT32\",\"FLOAT16\"]):\n",
    "            count = dfValue[column].count()\n",
    "            if (count == 0):\n",
    "                maxlength = dfValue[column].apply(str).apply(lambda x: len(x.encode('utf-8'))).max() \n",
    "                if (maxlength <= 0): maxlength = 1\n",
    "                adjusted = int(float(maxlength) * padding) \n",
    "                type = f\"{char_type}({adjusted})\" \n",
    "                sql_type = ibm_db.SQL_VARCHAR\n",
    "            else:\n",
    "                if (datatype == \"FLOAT64\"):\n",
    "                    sql_type = ibm_db.SQL_FLOAT\n",
    "                    type = \"FLOAT\"\n",
    "                elif (datatype == \"FLOAT32\"):\n",
    "                    sql_type = ibm_db.SQL_REAL\n",
    "                    type = \"REAL\"\n",
    "                else:\n",
    "                    sql_type = ibm_db.SQL_REAL\n",
    "                    type = \"REAL\"\n",
    "        elif (\"DATETIME64\" in datatype):\n",
    "            type = \"TIMESTAMP\"\n",
    "            sql_type = ibm_db.SQL_VARCHAR\n",
    "        elif (datatype == \"BOOL\"):\n",
    "            type = \"BINARY\"\n",
    "            sql_type = ibm_db.SQL_BINARY\n",
    "            maxlength = dfValue[column].apply(str).apply(len).max() \n",
    "            if (maxlength <= 0): maxlength = 1\n",
    "            adjusted = int(float(maxlength) * padding) \n",
    "            if (adjusted < 32000):\n",
    "                type = f\"{char_type}({adjusted})\" # f\"VARCHAR({adjusted})\"\n",
    "                sql_type = ibm_db.SQL_VARCHAR\n",
    "            else:\n",
    "                type = f\"{blob_type}({adjusted})\" # \"CLOB({adjusted})\"\n",
    "                sql_type = ibm_db.SQL_BLOB # lob_sqltype\n",
    "        else:\n",
    "            maxlength = dfValue[column].apply(str).apply(lambda x: len(x.encode('utf-8'))).max() \n",
    "            if (maxlength <= 0): maxlength = 1\n",
    "            adjusted = int(float(maxlength) * padding) \n",
    "            if (adjusted < 32000):\n",
    "                type = f\"{char_type}({adjusted})\" # \"VARCHAR({adjusted})\"\n",
    "                sql_type = ibm_db.SQL_VARCHAR\n",
    "            else:\n",
    "                type = f\"{clob_type}({adjusted})\" # \"CLOB({adjusted})\"\n",
    "                sql_type = ibm_db.SQL_CLOB # lob_sqltype\n",
    "\n",
    "        datatypes.append(type)   \n",
    "        sql_types.append(sql_type) \n",
    "\n",
    "        if (flag_asis == False):\n",
    "            if (isinstance(column,str) == False):\n",
    "                column = str(column)\n",
    "            identifier = \"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_\"\n",
    "            column_name = column.strip().upper()\n",
    "            new_name = \"\"\n",
    "            for ch in column_name:\n",
    "                if (ch not in identifier):\n",
    "                    new_name = new_name + \"_\"\n",
    "                else:\n",
    "                    new_name = new_name + ch\n",
    "                    \n",
    "            new_name = new_name.lstrip('_').rstrip('_')\n",
    "            \n",
    "            if (new_name == \"\" or new_name[0] not in \"ABCDEFGHIJKLMNOPQRSTUVWXYZ\"):\n",
    "                new_name = f'\"{column}\"'\n",
    "        else:\n",
    "            new_name = f'\"{column}\"'\n",
    "\n",
    "        column_names.append(new_name)\n",
    "        \n",
    "        sql.append(f\"    {new_name} {type}\")\n",
    "    sql.append(\")\")\n",
    "\n",
    "    sqlcmd = \"\"\n",
    "    for i in range(0,len(sql)):\n",
    "        if (i > 0 and i < len(sql)-2):\n",
    "            comma = \",\"\n",
    "        else:\n",
    "            comma = \"\"\n",
    "        if (i == 0):\n",
    "            sqlcmd = \"{}{}\".format(sql[i],comma)\n",
    "        else:\n",
    "            sqlcmd = \"{}\\n{}{}\".format(sqlcmd,sql[i],comma)\n",
    "\n",
    "    if (keyword_create == \"DECLARE\"):\n",
    "        sqlcmd = \"{}\\n{}\".format(sqlcmd,\"ON COMMIT PRESERVE ROWS NOT LOGGED\")\n",
    "\n",
    "    if (flag_export == True or flag_ddl == True):\n",
    "        if (flag_ddl == True):\n",
    "            print(sqlcmd)\n",
    "            return NoDF, None\n",
    "        else:\n",
    "            return NoDF, sqlcmd\n",
    "        \n",
    "    if (keyword_create != \"APPEND\"):\n",
    "        print(sqlcmd)\n",
    "        ok = execSQL(hdbc,sqlcmd,quiet=False)\n",
    "        if (ok == False):\n",
    "            return NoDF, None\n",
    "\n",
    "    if (flag_withdata == True or keyword_create == \"APPEND\"):\n",
    "        \n",
    "        autocommit = ibm_db.autocommit(hdbc)\n",
    "        ibm_db.autocommit(hdbc,False)\n",
    "\n",
    "        row_count = 0\n",
    "        insert_sql = \"\"\n",
    "        rows, cols = dfValue.shape\n",
    "\n",
    "        markers = \"\"\n",
    "        column_limit = len(sql_types)\n",
    "\n",
    "        # column_list = \"\"\n",
    "        for i in range(0, column_limit):\n",
    "            if (markers == \"\"):\n",
    "                markers = \"?\"\n",
    "            else:\n",
    "                markers = f\"{markers},?\"\n",
    "\n",
    "        insert_sql = f\"INSERT INTO {table} VALUES ({markers})\"\n",
    "\n",
    "        try:\n",
    "            stmt = ibm_db.prepare(hdbc,insert_sql) # Check error code here\n",
    "            if (stmt in [False,None]): \n",
    "                db2_error(False)\n",
    "                return NoDF, None\n",
    "        except:\n",
    "            db2_error(False)\n",
    "            return NoDF, None\n",
    "\n",
    "        row_count = 0\n",
    "\n",
    "        parms = [None] * column_limit\n",
    "\n",
    "        convert_table = []\n",
    "\n",
    "        for col in range(0, column_limit):\n",
    "\n",
    "            coltype = sql_types[col]\n",
    "            \n",
    "            if (coltype in [ibm_db.SQL_BIGINT, ibm_db.SQL_SMALLINT, ibm_db.SQL_INTEGER]):\t\t\t# Big Integer -> Python int\n",
    "                conversion = \"int\"\n",
    "            elif (coltype in [ibm_db.SQL_DECFLOAT,ibm_db.SQL_FLOAT,ibm_db.SQL_REAL,ibm_db.SQL_DOUBLE]):\t\t# Decfloat -> convert to character\n",
    "                conversion = \"float\"\n",
    "            elif (coltype == ibm_db.SQL_VARCHAR):\t\t# Varchar -> convert to character\n",
    "                conversion = \"str\"\n",
    "            elif (coltype == ibm_db.SQL_CLOB):\t\t\t# CLOB -> convert to character\n",
    "                conversion = \"str\"\n",
    "            elif (coltype == ibm_db.SQL_BINARY):\t\t# Binary -> Integer\n",
    "                conversion = \"int\"\n",
    "            else:\n",
    "                conversion = \"str\"\n",
    "            convert_table.append(conversion)\n",
    "\n",
    "            try:\n",
    "                result = ibm_db.bind_param(stmt, col+1, parms[col], ibm_db.SQL_PARAM_INPUT, sql_types[col]) \n",
    "            except:\n",
    "                db2_error(False)\n",
    "                return NoDF, None\n",
    "        \n",
    "            if (result in [False,None]):\n",
    "                errormsg(\"SQL Prepare failed.\")      \n",
    "                return NoDF, None\n",
    "\n",
    "        for row in range(0,rows):\n",
    "            \n",
    "            for col in range(0, column_limit):\n",
    "\n",
    "                colno = column_index[col]\n",
    "                checkValue = dfValue.iat[row,colno]\n",
    "\n",
    "                conversion = convert_table[col]\n",
    "                try:\n",
    "                    if (conversion == None):\t\t\t# Nothing to do\n",
    "                        converted = checkValue\n",
    "                    elif (conversion == \"int\"):\t\t\t# Set it to integer\n",
    "                        try:\n",
    "                            converted: int = int(checkValue) \n",
    "                        except ValueError:\n",
    "                            converted: int = None\n",
    "                    elif (conversion == \"float\"):\t\t# Set to float\n",
    "                        try:\n",
    "                            converted: float = float(checkValue)\n",
    "                        except ValueError:\n",
    "                            converted: float = None\n",
    "                    elif (conversion == \"str\"):\t\t\t# Set to string\n",
    "                        try:\n",
    "                            converted: str = str(checkValue)\n",
    "                        except ValueError:\n",
    "                            converted: str = None\n",
    "                    else:\n",
    "                        converted = checkValue\t\t\t# Hope for the best\n",
    "                except:\n",
    "                    converted = checkValue\n",
    "\n",
    "                parms[col] = converted\n",
    "\n",
    "            try:\n",
    "                result = ibm_db.execute(stmt,(*parms,)) \n",
    "            except Exception as e:\n",
    "                print(\"SQL Execution error due to possible datatype mismatch or column too small\")\n",
    "                print(repr(e))\n",
    "                db2_error(True)\n",
    "                ibm_db.free_result(stmt)\n",
    "                return NoDF, None\n",
    "                \n",
    "            if (result == False): \n",
    "                errormsg(\"SQL Execute failed.\")      \n",
    "                return NoDF, None\n",
    "\n",
    "            row_count += 1\n",
    "            if (row_count % 1000 == 0 or row_count == limit):\n",
    "                ibm_db.commit(hdbc)\n",
    "                print(f\"\\r{row_count} of {rows} rows inserted.\",end=\"\")\n",
    "                \n",
    "            if (row_count == limit):\n",
    "                break\n",
    "\n",
    "        print(f\"\\r{row_count} of {rows} rows inserted.\",end=\"\")\n",
    "                    \n",
    "        ibm_db.commit(hdbc)\n",
    "        ibm_db.autocommit(hdbc,autocommit)\n",
    "\n",
    "        print(\"\\nInsert completed.\")\n",
    "                      \n",
    "    return NoDF, None\n",
    "\n",
    "\n",
    "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",
    "                    encoded_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",
    "                                    else:\n",
    "                                        strvalue = str(value)\n",
    "                                        if (\"NAN\" in strvalue.upper()):\n",
    "                                            value = \"NULL\"    \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",
    "                            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 == RAW):\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",
    "                        else:\n",
    "                            strvalue = str(value)\n",
    "                            if (\"NAN\" in strvalue.upper()):\n",
    "                                value = \"NULL\"    \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\n",
    "\n",
    "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\n",
    "\n",
    "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 or isinstance(value,tuple) == 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)\n",
    "\n",
    "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\n",
    "    \n",
    "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\n",
    "\n",
    "def findProc(procname):\n",
    "    \n",
    "    global _hdbc, _hdbi, _connected\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\n",
    "\n",
    "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)    \n",
    "                       \n",
    "    return(name,parms)\n",
    "\n",
    "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",
    "        precision = ibm_db.field_precision(stmt,colcount)\n",
    "        while (colname != False):\n",
    "            if (coltype == \"real\"):\n",
    "                if (precision == 7):\n",
    "                    coltype = \"real\"\n",
    "                elif (precision == 15):\n",
    "                    coltype = \"float\"\n",
    "                elif (precision == 16):\n",
    "                    coltype = \"decfloat16\"\n",
    "                elif (precision == 34):\n",
    "                    coltype = \"decfloat34\"\n",
    "                else:\n",
    "                    coltype = \"real\"\n",
    "            elif (coltype == \"int\"):\n",
    "                if (precision == 1):\n",
    "                    coltype = \"boolean\"\n",
    "                elif (precision == 5):\n",
    "                    coltype = \"smallint\"\n",
    "                elif (precision == 10):\n",
    "                    coltype = \"int\"\n",
    "                else:\n",
    "                    coltype = \"int\"\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",
    "            precision = ibm_db.field_precision(stmt,colcount)     \n",
    "            \n",
    "        return columns,types   \n",
    "                \n",
    "    except Exception as err:\n",
    "        db2_error(False)\n",
    "        return None\n",
    "\n",
    "def parseCall(hdbc, inSQL, local_ns):\n",
    "    \n",
    "    global _hdbc, _hdbi, _connected, _environment\n",
    "    \n",
    "    # Check to see if we are connected first\n",
    "    if (_connected == False):                                      # Check if you are connected \n",
    "        db2_doConnect(None)\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.get('display',\"PANDAS\") == 'GRID':\n",
    "                    if (_environment['grid'] == False):\n",
    "                        with pandas.option_context('display.max_rows', None, 'display.max_columns', None):  \n",
    "                            pdisplay(df)\n",
    "                    else:\n",
    "                        try:\n",
    "                            _datagrid = DataGrid(df,layout={\"height\": f'{_settings[\"maxgrid\"]}px'},auto_fit_columns=True,editable=False)\n",
    "                            pdisplay(_datagrid)\n",
    "                        except Exception as err:\n",
    "                            print(repr(err))\n",
    "                            errormsg(\"Grid control failed to diplay data. Use option -a or %sql OPTION DISPLAY PANDAS instead.\")\n",
    "                            \n",
    "                    return                             \n",
    "                else:\n",
    "                    if flag([\"-a\",\"-all\"]) or _settings.get(\"maxrows\",10) == -1 : # All of the rows\n",
    "                        with pandas.option_context('display.max_rows', 100, '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\n",
    "\n",
    "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",
    "                parm_count = 0\n",
    "                parms = []\n",
    "                parms.append(None)\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",
    "                    parms.append(None)\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",
    "                        if (parm_name[0] == \":\"):\n",
    "                            parm_name = parm_name[1:]\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",
    "                        parms[parm_count] = globals()[parm_name]\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",
    "                        elif (parm_datatype == \"file\"):\n",
    "                            sql_type = ibm_db.SQL_CHAR\n",
    "                        else:\n",
    "                            sql_type = ibm_db.SQL_CHAR\n",
    "                            parms[parm_count] = addquotes(parms[parm_count],False)\n",
    "                    \n",
    "                    try:\n",
    "                        if (parm_type == VARIABLE):\n",
    "                            if (parm_datatype == \"file\"):\n",
    "                                result = ibm_db.bind_param(stmt, parm_count, parms[parm_count], ibm_db.PARAM_FILE,ibm_db.SQL_CLOB) \t\t\t\t\t\t\t\t\n",
    "                            else:\n",
    "                                result = ibm_db.bind_param(stmt, parm_count, parms[parm_count], ibm_db.SQL_PARAM_INPUT, sql_type)                           \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 Exception as e:\n",
    "                        print(repr(e))\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)     \n",
    "\n",
    "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",
    "            \n",
    "\n",
    "def parseCommit(sql):\n",
    "    \n",
    "    global _hdbc, _hdbi, _connected, _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\n",
    "\n",
    "def setFlags(inSQL,reset=False):\n",
    "\n",
    "    global _flags\n",
    "\n",
    "    if (reset == True):\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\n",
    "\n",
    "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\n",
    "\n",
    "def execSQL(hdbc,sql,quiet=True):\n",
    "\n",
    "    success = True\n",
    "    try:                                                  # See if we have an answer set\n",
    "        stmt = ibm_db.prepare(hdbc,sql)\n",
    "        result = ibm_db.execute(stmt)                 # Run it                            \n",
    "        if (result == False):                         # Error executing the code\n",
    "            db2_error(quiet)\n",
    "            success = False\n",
    "    except:\n",
    "        db2_error(quiet) \n",
    "        success = False\n",
    "\n",
    "    return success\t            \n",
    "\n",
    "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)\n",
    "\n",
    "def process_slice(connection, dfName, dfValue, pd_dtypes, sql, q, s):\n",
    "    \n",
    "    import numpy as np    \n",
    "    import pandas as pd\n",
    "\n",
    "    if (q.empty() == False): return None\n",
    "\n",
    "    if (isinstance(dfValue,list) == True or isinstance(dfValue,tuple) == True):\n",
    "        encoded_sql = \"\"\n",
    "        start = True\n",
    "        for v in dfValue:\n",
    "            if (start == False):\n",
    "                encoded_sql = encoded_sql + \",\"\n",
    "            if (isinstance(v,str) == True):\n",
    "                encoded_sql = encoded_sql + addquotes(v,True)\n",
    "            else:\n",
    "                encoded_sql = encoded_sql + str(v)\n",
    "            start = False\n",
    "\n",
    "        dfValue = encoded_sql\n",
    "    elif (isinstance(dfValue,str) == True):\n",
    "        dfValue = addquotes(dfValue,True)\n",
    "    else:\n",
    "        dfValue = str(dfValue)\n",
    "\n",
    "    if (q.empty() == False): return None\n",
    "\n",
    "    dsn = buildDSN(connection)\n",
    "\n",
    "    if (dsn in [None,\"\"]):\n",
    "        return None\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",
    "        try:\n",
    "            errmsg = ibm_db.conn_errormsg().replace('\\r',' ')\n",
    "            errmsg = errmsg[errmsg.rfind(\"]\")+1:].strip()\n",
    "        except:\n",
    "            errmsg = \"Error attempting to retrieve error message\"\n",
    "        q.put(errmsg)  \n",
    "        return None\n",
    "    \n",
    "    try:\n",
    "        hdbi = ibm_db_dbi.Connection(hdbc)\n",
    "    except Exception as err:\n",
    "        errmsg = \"Connection error when connecting through DBI adapter.\"\n",
    "        q.put(errmsg)\n",
    "        return None\n",
    "\n",
    "    if (q.empty() == False): return None\n",
    "    \n",
    "    # if (isinstance(dfValue,str) == True):\n",
    "    # \tdfValue = addquotes(dfValue,True)\n",
    "    # else:\n",
    "    # \tdfValue = str(dfValue)\n",
    "        \n",
    "    protoSQL = sql.replace(f\":{dfName}\",dfValue)\n",
    "\n",
    "    s.put(protoSQL)\n",
    "\n",
    "    if (q.empty() == False): return None\n",
    "\n",
    "    try:\n",
    "        if (pd_dtypes != None):\n",
    "            df = pd.read_sql_query(protoSQL,hdbi,dtype=pd_dtypes)  \n",
    "        else:\n",
    "            df = pd.read_sql_query(protoSQL,hdbi)    \n",
    "    except:\n",
    "        try:\n",
    "            errmsg = ibm_db.stmt_errormsg().replace('\\r',' ')\n",
    "            errmsg = errmsg[errmsg.rfind(\"]\")+1:].strip()\t\t\n",
    "            ibm_db.close(hdbc)\n",
    "        except:\n",
    "            errmsg = \"Error attempting to retrieve statement error message.\"\n",
    "        q.put(errmsg)\n",
    "        return None\n",
    "\n",
    "    if (q.empty() == False): return None\n",
    "\n",
    "    try:\n",
    "        ibm_db.close(hdbc)\n",
    "    except:\n",
    "        pass\n",
    "              \n",
    "    return df\n",
    "\n",
    "def dfSQL(hdbc,hdbi,sqlin,dfName,dfValue,thread_count):\n",
    "    \n",
    "    import shlex\n",
    "\n",
    "    NoDF  = False\n",
    "    YesDF = True\n",
    "\n",
    "    sqlin = \" \".join(shlex.split(sqlin)) \n",
    "\n",
    "    if (hdbc == None or hdbi == None or sqlin in (None, \"\")):\n",
    "        return NoDF,None\n",
    "    \n",
    "    uSQLin = sqlin.upper()\n",
    "      \n",
    "    select_location = uSQLin.find(\"SELECT\")\n",
    "    with_location   = uSQLin.find(\"WITH\")\n",
    "\n",
    "    if (select_location == -1):\n",
    "        errormsg(\"SQL statement does not contain a SELECT statement.\")\n",
    "        return NoDF, None          \n",
    "      \n",
    "    if (with_location != -1 and (with_location < select_location)):\n",
    "        keyword_location = with_location\n",
    "    else:\n",
    "        keyword_location = select_location\n",
    "        \n",
    "    sql = sqlin[keyword_location:]\n",
    "\n",
    "    keyword_location = sql.find(f\":{dfName}\")\n",
    "       \n",
    "    if (keyword_location == -1):\n",
    "        errormsg(f'The parallelism value \"{dfName}\" was not found in the SQL statement.')\n",
    "        return NoDF, None\n",
    "    \n",
    "    if (isinstance(dfValue,list) == False):\n",
    "        errormsg(f'The variable \"{dfName}\" is not an array or a list of values.')\n",
    "        return NoDF, None\n",
    "\n",
    "    #\tCreate a prototype statement to make sure the SQL will run\n",
    "    \n",
    "    protoValue = dfValue[0]\n",
    "\n",
    "    if (isinstance(protoValue,list) == True or isinstance(protoValue,tuple) == True):\n",
    "        if (len(protoValue) == 0):\n",
    "            errormsg(f'The variable \"{dfName}\" contains array values that are empty.')\n",
    "            return NoDF, None\n",
    "        protoValue = protoValue[0]\n",
    "\n",
    "    if (isinstance(protoValue,str) == True):\n",
    "        protoValue = addquotes(protoValue,True)\n",
    "    else:\n",
    "        protoValue = str(protoValue)\n",
    "           \n",
    "    protoSQL = sql.replace(f\":{dfName}\",protoValue)\n",
    "    \n",
    "    try:\n",
    "        stmt = ibm_db.prepare(hdbc,protoSQL)\n",
    "\n",
    "        if (ibm_db.num_fields(stmt) == 0):                \n",
    "            errormsg(\"The SQL statement does not return an answer set.\")\n",
    "            return NoDF, None\n",
    "  \n",
    "    except Exception as err:\n",
    "        db2_error(False)\n",
    "        return NoDF, None\n",
    "\n",
    "    #\tDetermine the datatypes for a Pandas dataframe if it is supported\n",
    "\n",
    "    pd_dtypes = None\n",
    "\n",
    "    if (_pandas_dtype == True):\n",
    "        pd_dtypes = None\n",
    "        columns, types = getColumns(stmt)\n",
    "        pd_dtypes={}\n",
    "        for idx, col in enumerate(columns):\n",
    "            try:\n",
    "                _dindex = _db2types.index(types[idx])\n",
    "            except:\n",
    "                _dindex = 0\n",
    "\n",
    "            pd_dtypes[col] = _pdtypes[_dindex]\n",
    "\n",
    "        if len(pd_dtypes.keys()) == 0:\n",
    "            pd_dtypes = None\n",
    "    \n",
    "    pool \t = mp.Pool(processes=thread_count)\n",
    "    m \t\t = mp.Manager()\t\t\t# multiprocessing.Manager()\n",
    "    q\t\t = m.Queue()\n",
    "    tracesql = m.Queue()\n",
    "    \n",
    "    try:\n",
    "        results = [pool.apply_async(process_slice, args=(_settings,dfName,x,pd_dtypes,sql,q,tracesql,)) for x in dfValue]\n",
    "    except Exception as err:\n",
    "        print(repr(err))\n",
    "        return NoDF, None        \n",
    "\n",
    "    output=[]\n",
    "\n",
    "    badresults = False\n",
    "    \n",
    "    for p in results:\n",
    "        try:\n",
    "            df = p.get()\n",
    "            if (isinstance(df,pandas.DataFrame) == True):\n",
    "                output.append(df)\n",
    "            else:\n",
    "                badresults = True\n",
    "        except Exception as err:\n",
    "            print(repr(err))\n",
    "            badresults = True \n",
    "\n",
    "    if flag([\"-e\",\"-echo\"]): \n",
    "        while (tracesql.empty() == False):\n",
    "            debug(tracesql.get(),False)\n",
    "\n",
    "    if (badresults == True):\n",
    "        if (q.empty() == False):\n",
    "            errormsg(q.get())\n",
    "        return NoDF, None      \n",
    "\n",
    "    finaldf = pandas.concat(output)\n",
    "    finaldf.reset_index(drop=True, inplace=True)\n",
    "    \n",
    "    if (len(finaldf) == 0):\n",
    "        sqlcode = 100\n",
    "        errormsg(\"No rows found\")\n",
    "        return NoDF, None    \n",
    "\n",
    "    return YesDF, finaldf\n",
    "\n",
    "def stripComments(sql):\n",
    "\n",
    "    import re\n",
    "\n",
    "    if (sql in [\"\",None]):\n",
    "        return(sql)\n",
    "\n",
    "    sql = re.sub(r\"(.*)--.*\\n\",r\"\\1 \",sql)\n",
    "    sql = sql.strip()\n",
    "    return(sql)    \n",
    "\n",
    "@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, 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",
    "        start_time = time.time()       \n",
    "        \n",
    "        # Macros gets expanded before anything is done\n",
    "\n",
    "        SQL1 = stripComments(line)\n",
    "        \n",
    "        SQL1 = line.replace(\"\\n\",\" \").strip()\n",
    "        SQL1 = setFlags(SQL1,reset=True)  \n",
    "        SQL1 = checkMacro(SQL1)                                   # Update the SQL if any macros are in there\n",
    "        SQL1 = setFlags(SQL1)\n",
    "        SQL2 = stripComments(cell)    \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",
    "        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 == \"USING\"):                                # You want to use a dataframe to create a table?\n",
    "            pdReturn, df = createDF(_hdbc,_hdbi, SQL1,local_ns)\n",
    "            if (pdReturn == True):\n",
    "                if flag(\"-grid\") or _settings.get('display',\"PANDAS\") == 'GRID':   # Check to see if we can display the results\n",
    "                    if (_environment['grid'] == False):\n",
    "                        with pandas.option_context('display.max_rows', 100, 'display.max_columns', None):  \n",
    "                            print(df.to_string())\n",
    "                    else:\n",
    "                        try:\n",
    "                            _datagrid = DataGrid(df,layout={\"height\": f'{_settings[\"maxgrid\"]}px'},auto_fit_columns=True,editable=False)\n",
    "                            pdisplay(_datagrid)\n",
    "                        except Exception as err:\n",
    "                            print(repr(err))\n",
    "                            errormsg(\"Grid control failed to diplay data. Use option -a or %sql OPTION DISPLAY PANDAS instead.\")\n",
    "                    return \n",
    "                else:\n",
    "                    if flag([\"-a\",\"-all\"]) or _settings.get(\"maxrows\",10) == -1 : # All of the rows\n",
    "                        pandas.options.display.max_rows = 100\n",
    "                        pandas.options.display.max_columns = None\n",
    "                        return df # print(df.to_string())\n",
    "                    else:\n",
    "                        pandas.options.display.max_rows = _settings.get(\"maxrows\",10)\n",
    "                        pandas.options.display.max_columns = None\n",
    "                        return df # pdisplay(df) # print(df.to_string())\n",
    "            else:\n",
    "                if (df != None):\n",
    "                    return df\n",
    "                else:\n",
    "                    return\n",
    "        elif (sqlType == \"DEFINE\"):                               # Create a macro from the body\n",
    "            result = setMacro(SQL2,remainder)\n",
    "            return\n",
    "        elif (sqlType in (\"OPTION\",\"OPTIONS\")):\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(None) == False):\n",
    "                errormsg('A CONNECT statement must be issued before running SQL statements.')\n",
    "                return      \n",
    "        \n",
    "        if _settings.get(\"maxrows\",10) == -1:                                 # Set the return result size\n",
    "            pandas.reset_option('display.max_rows')\n",
    "        else:\n",
    "            pandas.options.display.max_rows = _settings.get(\"maxrows\",10)\n",
    "      \n",
    "        runSQL = re.sub('.*?--.*$',\"\",sql,flags=re.M)\n",
    "        remainder = runSQL.replace(\"\\n\",\" \") \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",
    "\n",
    "            if flag([\"-e\",\"-echo\"]): \n",
    "                debug(sql,False)\n",
    "                \n",
    "            if flag([\"-pb\",\"-bar\",\"-pp\",\"-pie\",\"-pl\",\"-line\"]): # We are plotting some results              \n",
    "                plotData(_hdbi, sql)                            # Plot the data and return\n",
    "                return                \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",
    "                    start_time = time.time()\n",
    "                    result = ibm_db.execute(stmt)                 # Run it                            \n",
    "                    sqlelapsed = time.time() - start_time\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",
    "                        start_time = time.time()                 \n",
    "                        result = ibm_db.execute(stmt)             # Run it\n",
    "                        sqlelapsed = time.time() - start_time                            \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",
    "                    # New for pandas 1.3. We can coerce the PD datatypes to mimic those of Db2\n",
    "                    \n",
    "                    pd_dtypes = None\n",
    "                    \n",
    "                    if (_pandas_dtype == True):\n",
    "                        pd_dtypes = None\n",
    "                        columns, types = getColumns(stmt)\n",
    "                        pd_dtypes={}\n",
    "                        for idx, col in enumerate(columns):\n",
    "                            try:\n",
    "                                _dindex = _db2types.index(types[idx])\n",
    "                            except:\n",
    "                                _dindex = 0\n",
    "\n",
    "                            pd_dtypes[col] = _pdtypes[_dindex]\n",
    "\n",
    "                        if len(pd_dtypes.keys()) == 0:\n",
    "                            pd_dtypes = None\n",
    "                    try:\n",
    "                        \n",
    "                        start_time = time.time()    \n",
    "                        if (_pandas_dtype == True):\n",
    "                            df = pandas.read_sql_query(sql,_hdbi,dtype=pd_dtypes)  \n",
    "                        else:\n",
    "                            df = pandas.read_sql_query(sql,_hdbi)                        \n",
    "                        sqlelapsed = time.time() - start_time                                \n",
    "                            \n",
    "                    except Exception as err:\n",
    "                        print(repr(err))\n",
    "                        sqlelapsed = 0\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.get('display',\"PANDAS\") == 'GRID':   # Check to see if we can display the results\n",
    "                        if (_environment['grid'] == False):\n",
    "                            with pandas.option_context('display.max_rows', None, 'display.max_columns', None):  \n",
    "                                print(df.to_string())\n",
    "                        else:\n",
    "                            try:\n",
    "                                _datagrid = DataGrid(df,layout={\"height\": f'{_settings[\"maxgrid\"]}px'},auto_fit_columns=True,editable=False)\n",
    "                                pdisplay(_datagrid)\n",
    "                            except Exception as err:\n",
    "                                print(repr(err))\n",
    "                                errormsg(\"Grid control failed to diplay data. Use option -a or %sql OPTION DISPLAY PANDAS instead.\")\n",
    "                                return \n",
    "                    else:\n",
    "                        if flag([\"-a\",\"-all\"]) or _settings.get(\"maxrows\",10) == -1 : # All of the rows\n",
    "                            pandas.options.display.max_rows = 100\n",
    "                            pandas.options.display.max_columns = None\n",
    "                            return df # print(df.to_string())\n",
    "                        else:\n",
    "                            pandas.options.display.max_rows = _settings.get(\"maxrows\",10)\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",
    "        sqlelapsed = time.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",
    "macro_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",
    "flags -a\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}\n",
    "'''\n",
    "DB2.sql(None, \"define LIST\", cell=macro_list, local_ns=locals())\n",
    "\n",
    "macro_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",
    "flags -a\n",
    "if {argc} < 2\n",
    "   exit {syntax}\n",
    "endif\n",
    "\n",
    "CALL ADMIN_CMD('{*0}');\n",
    "'''\n",
    "\n",
    "DB2.sql(None,\"define describe\", cell=macro_describe, local_ns=locals())\n",
    "\n",
    "create_sample = \"\"\"\n",
    "flags -d\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",
    "\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",
    "DB2.sql(None,\"define sampledata\", cell=create_sample, local_ns=locals())\n",
    "\n",
    "create_set = '''\n",
    "#\n",
    "# Convert a SET statement into an OPTION statement\n",
    "#\n",
    "\n",
    "# Display settings\n",
    "if {^1} == 'DISPLAY'\n",
    "    if {^2} == \"PANDAS\" \n",
    "        OPTION DISPLAY PANDAS\n",
    "        return\n",
    "    else\n",
    "        if {^2} == \"GRID\"\n",
    "            OPTION DISPLAY GRID\n",
    "            return\n",
    "        endif\n",
    "    endif\n",
    "endif\n",
    "\n",
    "# Multithreading\n",
    "if {^1} == 'THREADS'\n",
    "    OPTION THREADS {2}\n",
    "    return\n",
    "endif\n",
    "        \n",
    "# Maximum number of rows displayed\n",
    "if {^1} == 'MAXROWS'\n",
    "    OPTION MAXROWS {2}\n",
    "    return\n",
    "endif\n",
    "\n",
    "# Maximum number of grid rows displayed\n",
    "if {^1} == 'MAXGRID'\n",
    "    OPTION MAXGRID {2}\n",
    "    return\n",
    "endif\n",
    "        \n",
    "{*0}\n",
    "return\n",
    "'''\n",
    "\n",
    "DB2.sql(None,\"define set\", cell=create_set, local_ns=locals())\n",
    "   \n",
    "success(f\"Db2 Extensions Loaded. Version: {_db2magic_version}\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Credits: IBM 2024, George Baklarz [baklarz@ca.ibm.com]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.8.10"
  },
  "vscode": {
   "interpreter": {
    "hash": "916dbcbb3f70747c44a77c7bcd40155683ae19c65e1c03b4aa3499c5328201f1"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}