SQL for Dates - Db2 vs. Informix.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook should be launched from a session of jupyter notebook that was launched from a DB2 command window\n",
    "To do this, run an administrator DB2 command window as an administrator and type 'jupyter notebook'\n",
    "\n",
    "Set up the enviornment:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys,os,os.path\n",
    "os.environ['IBM_DB_HOME']='C:\\Program Files\\IBM\\SQLLIB'\n",
    "!pip install ipython-sql\n",
    "!pip install ibm_db \n",
    "!pip3 install ifxpy\n",
    "!pip3 install JayDeBeApi\n",
    "!pip install ibm_db_sa"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Restart the Kernel if this is your first time installing the above. The next steps will fail unless you do this."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Import the modules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import ibm_db\n",
    "import IfxPy\n",
    "import pandas as pd\n",
    "import sqlalchemy\n",
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Source the file with connection information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define filename for passwords\n",
    "filename = 'ifx_and_db2_variables.py'\n",
    "# source the file\n",
    "%run $filename"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Informix Connection"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Connect to the database. Change the values to match your environment. For connection to a local host, use 'localhost' for the host name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "ConStr = \"SERVER=\"+ifxserver+\";DATABASE=\"+ifx_DB+\";HOST=\"+ifx_Host+\";SERVICE=\"+ifx_Port+\";UID=\"+ifx_User+\";PWD=\"+ifx_PW+\";DB_LOCALE=en_US.utf8;\"\n",
    "\n",
    "try:\n",
    "    conn = IfxPy.connect( ConStr, \"\", \"\")\n",
    "except Exception as e:\n",
    "    print ('ERROR: Connect failed')\n",
    "    print ( e )\n",
    "    quit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Db2 Connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: ecrooks@sample'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql db2+ibm_db://$db2_User:$db2_PW@$db2_Host:$db2_Port/$db2_DB"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Example #1\n",
    "Find rows from two days in the past to 7 days in the future from a given, static date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Db2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql ex_1_db2 << select *\n",
    "from sales\n",
    "where ts_sales_date between date('2006-03-31') - 2 days and date('2006-03-31') + 7 days\n",
    "order by ts_sales_date\n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ex_1_db2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Informix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Select records\n",
    "sql = \"\"\"\n",
    "select *\n",
    "from sales\n",
    "and ts_sales_date::DATETIME year to day between DATE('03/31/2006') - 2 UNITS DAY and DATE('03/31/2006') + 7 UNITS DAY\n",
    "order by ts_sales_date\n",
    "\"\"\"\n",
    "\n",
    "stmt = IfxPy.exec_immediate(conn, sql)\n",
    "display(stmt)\n",
    "assoc = IfxPy.fetch_assoc(stmt)\n",
    "\n",
    "ls=[]\n",
    "while assoc != False:\n",
    "    ls.append(assoc)\n",
    "    assoc = IfxPy.fetch_assoc(stmt)\n",
    "\n",
    "\n",
    "\n",
    "ifx_ex_1=pd.DataFrame(ls)\n",
    "display(ifx_ex_1.head())\n",
    "display(ifx_ex_1.tail())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Example #2 \n",
    "Find what the date was 36 hours ago"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Db2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * db2+ibm_db://ecrooks:***@localhost:50000/sample\n",
      "Done.\n",
      "Returning data to local variable ex_2_db2\n"
     ]
    }
   ],
   "source": [
    "%%sql ex_2_db2 << select date(current timestamp - 36 hours) \n",
    "from sysibm.sysdummy1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2019-03-13</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.date(2019, 3, 13),)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ex_2_db2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Informix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<IfxPy.IFXStatement at 0x21576d8c500>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-03-13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             \n",
       "0  2019-03-13"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Select records\n",
    "sql = \"\"\"\n",
    "select (current - 36 units hour)::DATETIME YEAR to DAY from sysmaster:sysdual \n",
    "\"\"\"\n",
    "\n",
    "stmt = IfxPy.exec_immediate(conn, sql)\n",
    "display(stmt)\n",
    "assoc = IfxPy.fetch_assoc(stmt)\n",
    "\n",
    "ls=[]\n",
    "while assoc != False:\n",
    "    ls.append(assoc)\n",
    "    assoc = IfxPy.fetch_assoc(stmt)\n",
    "\n",
    "\n",
    "\n",
    "ifx_ex_2=pd.DataFrame(ls)\n",
    "display(ifx_ex_2)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Example #3\n",
    "Find the day of the week one month ago"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Db2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * db2+ibm_db://ecrooks:***@localhost:50000/sample\n",
      "Done.\n",
      "Returning data to local variable ex_3_db2\n"
     ]
    }
   ],
   "source": [
    "%%sql ex_3_db2 << select dayname(current timestamp - 1 month) weekday \n",
    "from sysibm.sysdummy1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>weekday</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Thursday</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Thursday',)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ex_3_db2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Informix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<IfxPy.IFXStatement at 0x21577026570>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Thursday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     weekday\n",
       "0  Thursday "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Select records\n",
    "sql = \"\"\"\n",
    "select case WEEKDAY(current - 1 units month) \n",
    "  when 0 then 'Sunday'\n",
    "  when 1 then 'Monday'\n",
    "  when 2 then 'Tuesday'\n",
    "  when 3 then 'Wednesday'\n",
    "  when 4 then 'Thursday'\n",
    "  when 5 then 'Friday'\n",
    "  when 6 then 'Saturday' end as weekday\n",
    "from sysmaster:sysdual \n",
    "\"\"\"\n",
    "\n",
    "stmt = IfxPy.exec_immediate(conn, sql)\n",
    "display(stmt)\n",
    "assoc = IfxPy.fetch_assoc(stmt)\n",
    "\n",
    "ls=[]\n",
    "while assoc != False:\n",
    "    ls.append(assoc)\n",
    "    assoc = IfxPy.fetch_assoc(stmt)\n",
    "\n",
    "\n",
    "\n",
    "ifx_ex_3=pd.DataFrame(ls)\n",
    "display(ifx_ex_3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Example #4\n",
    "Find the exact time 450 seconds from now"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Db2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * db2+ibm_db://ecrooks:***@localhost:50000/sample\n",
      "Done.\n",
      "Returning data to local variable ex_4_db2\n"
     ]
    }
   ],
   "source": [
    "%%sql ex_4_db2 << select current timestamp + 450 seconds time \n",
    "from sysibm.sysdummy1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>TIME</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2019-03-14 19:14:26.794000</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2019, 3, 14, 19, 14, 26, 794000),)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ex_4_db2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Informix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<IfxPy.IFXStatement at 0x215770267a0>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-03-14 20:59:25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     \n",
       "0 2019-03-14 20:59:25"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Select records\n",
    "sql = \"\"\"\n",
    "select current + 450 units second from sysmaster:sysdual \n",
    "\"\"\"\n",
    "\n",
    "stmt = IfxPy.exec_immediate(conn, sql)\n",
    "display(stmt)\n",
    "assoc = IfxPy.fetch_assoc(stmt)\n",
    "\n",
    "ls=[]\n",
    "while assoc != False:\n",
    "    ls.append(assoc)\n",
    "    assoc = IfxPy.fetch_assoc(stmt)\n",
    "\n",
    "\n",
    "\n",
    "ifx_ex_4=pd.DataFrame(ls)\n",
    "display(ifx_ex_4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done\n"
     ]
    }
   ],
   "source": [
    "# Free up memory used by informix result and then stmt too\n",
    "IfxPy.free_result(stmt)\n",
    "IfxPy.free_stmt (stmt)\n",
    "\n",
    "IfxPy.close(conn)\n",
    "\n",
    "print (\"Done\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Format for password file:\n",
    "```python\n",
    "ifx_Host='example.example.com'\n",
    "ifx_DB='sample'\n",
    "ifx_Port='50001'\n",
    "ifx_User='username'\n",
    "ifx_PW='pw'\n",
    "ifxserver='reporting_tcp'\n",
    "\n",
    "db2_Host='example.example.com'\n",
    "db2_DB='sample'\n",
    "db2_Port='50000'\n",
    "db2_User='user'\n",
    "db2_PW='pw'\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": true,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}