IndexAnalysis-Anonymized2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set up the environment\n",
    "# This cell only needed the first time you use this notebook on a system\n",
    "import sys,os,os.path\n",
    "os.environ['IBM_DB_HOME']='/home/ubuntu'\n",
    "\n",
    "# Check to see if the libraries already have been installed\n",
    "import importlib\n",
    "\n",
    "# Check for ibm_db_sa.  If it exists, it's safe to assume that the other requirements\n",
    "# are already installed.\n",
    "spec = importlib.util.find_spec(\"ibm_db_sa\")\n",
    "if spec is None:\n",
    "    print(\"Installing prerequisites.\")\n",
    "    !pip install ipython-sql\n",
    "    !pip install \"ibm-db==2.0.8a\" \n",
    "    !pip install ibm_db_sa\n",
    "else:\n",
    "    print(\"sql magic, ibm_db and ibm_db_sa already installed.\")\n",
    "# Restart the Kernel if this is your first time installing the above. The next steps will fail unless you do this.\n",
    "spec = importlib.util.find_spec(\"sqlparse\")\n",
    "if spec is None:\n",
    "    print(\"Installing sqlparse.\")\n",
    "    !pip install sqlparse\n",
    "else:\n",
    "    print(\"sqlparse already installed.\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import the modules and load the SQL magic\n",
    "import ibm_db\n",
    "import ibm_db_sa\n",
    "import sqlalchemy\n",
    "%load_ext sql\n",
    "import matplotlib\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib import cm\n",
    "import pandas as pd\n",
    "from IPython.display import display, HTML\n",
    "import datetime\n",
    "import time\n",
    "from IPython.display import display, HTML, Markdown\n",
    "import sqlparse\n",
    "import getpass\n",
    "pd.set_option('display.max_colwidth', -1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define filename for passwords\n",
    "filename = 'ember_variables.py'\n",
    "# source the file\n",
    "%run $filename"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Report on Reocmmended Indexes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(datetime.datetime.now())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Connect to the database. Change the values of user, host, and password to match your environment. \n",
    "#For connection to a local host, use 'localhost' for the host name. \n",
    "#Also change the port number and database name.\n",
    "user=''\n",
    "host=''\n",
    "password = getpass.getpass('Enter password for '+user)\n",
    "inst=''\n",
    "db=''\n",
    "port=''\n",
    "\n",
    "%sql db2+ibm_db://$user:$password@$host:$port/$db\n",
    "            \n",
    "print(\"Database: \"+db)\n",
    "print(\"Host: \"+host)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tabschema=''\n",
    "explainschema=''\n",
    "%sql set current schema :tabschema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check to see if explain tables exist. Create them if they do not exist. Migrate them if they are invalid. Drop and recreate if that fails\n",
    "ex_tabs=%sql call sysproc.sysinstallobjects('EXPLAIN','V',NULL,'SYSTOOLS')\n",
    "if ex_tabs is None :\n",
    "    display(\"Explain tables in the SYSTOOLS schema are invalid\")\n",
    "    %sql call sysproc.sysinstallobjects('EXPLAIN','M',NULL,'SYSTOOLS')\n",
    "    %sql call sysproc.sysinstallobjects('EXPLAIN','C',NULL,'SYSTOOLS')\n",
    "    ex_tabs=%sql call sysproc.sysinstallobjects('EXPLAIN','V',NULL,'SYSTOOLS')\n",
    "    if ex_tabs is None :\n",
    "        display('Migration failed, dropping and re-creating explain tables')\n",
    "        %sql call sysproc.sysinstallobjects('EXPLAIN','D',NULL,'SYSTOOLS')\n",
    "        %sql call sysproc.sysinstallobjects('EXPLAIN','C',NULL,'SYSTOOLS')\n",
    "elif len(ex_tabs) == 0 : \n",
    "    display(\"Explain tables in the SYSTOOLS schema exist and are valid\")\n",
    "    \n",
    "%sql update {explainschema}.advise_index set use_index='N' where exists='N'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Hide code cells\n",
    "#HTML('''<script>\n",
    "#code_show=true; \n",
    "#function code_toggle() {\n",
    "# if (code_show){\n",
    "# $('div.input').hide();\n",
    "# } else {\n",
    "# $('div.input').show();\n",
    "# }\n",
    "# code_show = !code_show\n",
    "#} \n",
    "#$( document ).ready(code_toggle);\n",
    "#</script>\n",
    "#The raw code for this IPython notebook is by default hidden for easier reading.\n",
    "#To toggle on/off the raw code, click <a href=\"javascript:code_toggle()\">here</a>.''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Statement\n",
    "Define the statement this notebook focuses on. Only one statement per notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dummy_val=\"50000\"\n",
    "dummy_date=\"2018-01-12\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the box below between the first and last lines, you need to pasted in the single SQL statement this notebook focuses on. <br>\n",
    "Any date parameter markers need to be replaced with ':dummy_date'. <br>\n",
    "All other parameter markers will have dummy values filled in for you - just leave them as question marks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt=\"\"\"\\\n",
    "SELECT ATTRVAL.ATTRVAL_ID,\n",
    "       ATTRVAL.IDENTIFIER,\n",
    "       ATTRVAL.ATTR_ID,\n",
    "       ATTRVAL.FIELD1,\n",
    "       ATTRVAL.FIELD2,\n",
    "       ATTRVAL.FIELD3\n",
    "FROM ATTRVAL\n",
    "WHERE ATTRVAL.ATTR_ID IN (?,\n",
    "                          ?,\n",
    "                          ?,\n",
    "                          ?)\n",
    "  AND ATTRVAL.VALUSAGE is NOT NULL\n",
    "  AND ATTRVAL.STOREENT_ID IN (?,\n",
    "                              ?)\n",
    "  AND ATTRVAL.ATTRVAL_ID IN\n",
    "    (SELECT C1.ATTRVAL_ID\n",
    "     FROM CATENTRYATTR C1,\n",
    "          CATENTREL,\n",
    "          CATENTRY\n",
    "     WHERE C1.CATENTRY_ID = CATENTREL.CATENTRY_ID_CHILD\n",
    "       AND CATENTREL.CATENTRY_ID_CHILD = CATENTRY.CATENTRY_ID\n",
    "       AND CATENTRY.MARKFORDELETE = 0\n",
    "       AND CATENTREL.CATENTRY_ID_PARENT IN (?) ) \n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt=stmt.replace(\"?\", \":dummy_val\")\n",
    "stmt=stmt.replace(\"{tabschema}\", tabschema)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get starting time\n",
    "start_time=%sql select current timestamp from sysibm.sysdummy1\n",
    "#start_time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql set current explain mode explain\n",
    "try:\n",
    "    %sql $stmt\n",
    "except Exception as e:\n",
    "    error_string=str(e)\n",
    "    if \"CLI0115E\" not in error_string:\n",
    "        print(e)\n",
    "## errors about an invalid cursor state can be ignored - that is what we expect here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "time.sleep(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql set current explain mode recommend indexes\n",
    "try:\n",
    "    %sql $stmt\n",
    "except Exception as e: \n",
    "    error_string=str(e)\n",
    "    if \"CLI0115E\" not in error_string:\n",
    "        print(e)\n",
    "## errors about an invalid cursor state can be ignored - that is what we expect here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql set current explain mode no"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "time.sleep(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get ending time\n",
    "end_time=%sql select current timestamp from sysibm.sysdummy1\n",
    "#end_time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "time.sleep(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reord Before and After Costs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql set current schema :explainschema\n",
    "expln_start_time=start_time[0][0].strftime('%Y-%m-%d-%H.%M.%S')\n",
    "expln_end_time=end_time[0][0].strftime('%Y-%m-%d-%H.%M.%S')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql before_costs << select \n",
    "    dec(total_cost,20,4) as before_total_cost\n",
    "    , dec(io_cost,20,4) as before_io_cost\n",
    "    , dec(CPU_cost,20,4) as before_cpu_cost\n",
    "    , dec(Comm_cost,20,4) as before_comm_cost\n",
    "from Explain_Operator\n",
    "    ,(select min(explain_time) as mintime\n",
    "        from Explain_Operator \n",
    "        where operator_type = 'RETURN' \n",
    "          and explain_time between :expln_start_time and :expln_end_time) as b\n",
    "where \n",
    "    explain_time = b.mintime\n",
    "    and operator_type = 'RETURN' \n",
    "with UR ;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "display(before_costs)\n",
    "before_costs_df=before_costs.DataFrame()\n",
    "before_costs_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql after_costs << select \n",
    "    dec(total_cost,20,4) as after_total_cost\n",
    "    , dec(io_cost,20,4) as after_io_cost\n",
    "    , dec(CPU_cost,20,4) as after_cpu_cost\n",
    "    , dec(Comm_cost,20,4) as after_comm_cost\n",
    "from Explain_Operator\n",
    "    ,(select max(explain_time) as maxtime\n",
    "        from Explain_Operator \n",
    "        where operator_type = 'RETURN' \n",
    "          and explain_time between :expln_start_time and :expln_end_time) as b\n",
    "where \n",
    "    explain_time = b.maxtime\n",
    "    and operator_type = 'RETURN' \n",
    "with UR ;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "after_costs_df=after_costs.DataFrame()\n",
    "after_costs_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql rec_ind_list << with ts as ( select max(explain_time) as maxtime\n",
    "        from Explain_Operator \n",
    "        where operator_type = 'RETURN' \n",
    "          and explain_time between :expln_start_time and :expln_end_time)\n",
    "select substr(name,1,25) as indname\n",
    "    , substr(tbcreator,1,13) as tabschema\n",
    "    , substr(tbname,1,18) as tabname\n",
    "    , coalesce(si.fullkeycard, ai.fullkeycard) as fullkeycard\n",
    "    , st.card\n",
    "    , coalesce(si.uniquerule, ai.uniquerule) as uniquerule\n",
    "    , use_index\n",
    "    , exists\n",
    "    , coalesce(si.colnames, ai.colnames) as colnames \n",
    "from advise_index ai \n",
    "    left outer join syscat.indexes si on ai.tbcreator=si.tabschema and ai.tbname=si.tabname and ai.name=si.indname \n",
    "    left outer join syscat.tables st on st.tabschema=ai.tbcreator and st.tabname=ai.tbname \n",
    "    join ts on explain_time = ts.maxtime\n",
    "order by exists, use_index, uniquerule desc, indname with ur;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#rec_ind_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "rec_ind_df1=rec_ind_list.DataFrame()\n",
    "#rec_ind_df=rec_ind_df.drop_duplicates\n",
    "#rec_ind_df['card'] = rec_ind_df.apply(lambda x: \"{:,}\".format(x['card']), axis=1)\n",
    "#rec_ind_df['fullkeycard'] = rec_ind_df.apply(lambda x: \"{:,}\".format(x['fullkeycard']), axis=1)\n",
    "#rec_ind_df['colnames']=rec_ind_df['colnames'].replace('+','\\n+',regex=False)\n",
    "#display(HTML(rec_ind_df.to_html(index=False).replace(\"\\\\n\",\"<br>\")))\n",
    "rec_ind_df=rec_ind_df1.drop_duplicates(subset=['indname'])\n",
    "rec_ind_df.indname = rec_ind_df['indname'].map(lambda x: x.strip())\n",
    "rec_ind_df.card = rec_ind_df.apply(lambda x: \"{:,}\".format(x['card']), axis=1)\n",
    "rec_ind_df.fullkeycard = rec_ind_df.apply(lambda x: \"{:,}\".format(x['fullkeycard']), axis=1)\n",
    "rec_ind_df.colnames=rec_ind_df.colnames.replace(to_replace='\\+', value='\\\\n+', regex=True).replace(to_replace='-', value='\\\\n-', regex=True).str.lstrip('\\n')\n",
    "ind_list=rec_ind_df.indname.values.tolist()\n",
    "ind_list_str=str(ind_list).strip('[]')\n",
    "\n",
    "rec_ind_df=rec_ind_df.set_index('indname')\n",
    "pd.set_option('display.max_colwidth', -1)\n",
    "#display(HTML(rec_ind_df.to_html(index=True).replace(\"\\\\n\",\"<br>\")))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "this_cost_stmt=\"\"\"\\\n",
    "select \n",
    "    dec(total_cost,20,4) as this_index_total_cost\n",
    "    , dec(io_cost,20,4) as this_index_io_cost\n",
    "    , dec(CPU_cost,20,4) as this_index_cpu_cost\n",
    "    , dec(Comm_cost,20,4) as this_index_comm_cost\n",
    "from Explain_Operator\n",
    "    ,(select max(explain_time) as maxtime\n",
    "        from Explain_Operator \n",
    "        where operator_type = 'RETURN') as b\n",
    "where \n",
    "    explain_time = b.maxtime\n",
    "    and operator_type = 'RETURN' \n",
    "with UR \n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql update advise_index set use_index='N' where exists='N' and name in ({ind_list_str})\n",
    "for index, row in rec_ind_df.iterrows():\n",
    "    if row['EXISTS'] == 'N' :\n",
    "        display(index)\n",
    "        display(row)\n",
    "        %sql update advise_index set use_index='Y' where name = :index\n",
    "        %sql set current schema :tabschema\n",
    "        %sql set current explain mode evaluate indexes\n",
    "        try: \n",
    "            %sql $stmt\n",
    "        except Exception as e: \n",
    "            error_string=str(e)\n",
    "            if \"CLI0115E\" not in error_string:\n",
    "                print(e)\n",
    "        ## errors about an invalid cursor state can be ignored - that is what we expect here.\n",
    "        %sql set current explain mode no\n",
    "        %sql set current schema :explainschema\n",
    "        display(\"this cost:\")\n",
    "        this_cost=%sql $this_cost_stmt\n",
    "        this_cost_df=this_cost.DataFrame()\n",
    "        display(this_cost)\n",
    "        %sql update advise_index set use_index='N' where name = :index\n",
    "        rec_ind_df.at[index, 'Index_Add']=before_costs_df.iloc[0]['before_total_cost']-this_cost_df.iloc[0]['this_index_total_cost']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%sql update advise_index set use_index='Y' where name in ({ind_list_str})\n",
    "for index, row in rec_ind_df.iterrows():\n",
    "    if row['EXISTS'] == 'N' :\n",
    "        display(index)\n",
    "        display(row)\n",
    "        %sql update advise_index set use_index='N' where name = :index\n",
    "        %sql set current explain mode evaluate indexes\n",
    "        %sql set current schema :tabschema\n",
    "        try: \n",
    "            %sql $stmt\n",
    "        except Exception as e: \n",
    "            error_string=str(e)\n",
    "            if \"CLI0115E\" not in error_string:\n",
    "                print(e)\n",
    "        ## errors about an invalid cursor state can be ignored - that is what we expect here.\n",
    "        %sql set current explain mode no\n",
    "        %sql set current schema :explainschema\n",
    "        display(\"this cost:\")\n",
    "        this_cost=%sql $this_cost_stmt\n",
    "        this_cost_df=this_cost.DataFrame()\n",
    "        display(this_cost)\n",
    "        %sql update advise_index set use_index='Y' where name = :index\n",
    "        rec_ind_df.at[index, 'Index_Subtr']=(before_costs_df.iloc[0]['before_total_cost']-after_costs_df.iloc[0]['after_total_cost'])-(before_costs_df.iloc[0]['before_total_cost']-this_cost_df.iloc[0]['this_index_total_cost'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "display(HTML(rec_ind_df.to_html(index=True).replace(\"\\\\n\",\"<br>\")))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "display(before_costs_df)\n",
    "display(after_costs_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import ipywidgets as widgets\n",
    "inds=widgets.SelectMultiple(\n",
    "    options=rec_ind_df.query('EXISTS == \"N\"').index,\n",
    "    value=list(rec_ind_df.query('EXISTS == \"N\" and Index_Add > 10000').index),\n",
    "    #rows=10,\n",
    "    description='New Indexes',\n",
    "    disabled=False\n",
    ")\n",
    "display(inds)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the box above, select the combination of indexes you would like to use and then re-run the cell below to try different index combinations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#inds_to_try_str=list(inds.value)\n",
    "inds_to_try_str=str(list(inds.value)).strip(\"][\\\\\")\n",
    "inds_to_try_str"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#%sql update advise_index set use_index='N' where exists='N' and name in ({ind_list_str})\n",
    "%sql update advise_index set use_index='N' where exists='N'\n",
    "%sql update advise_index set use_index='Y' where name in ({inds_to_try_str})\n",
    "%sql set current explain mode evaluate indexes\n",
    "%sql set current schema :tabschema\n",
    "try: \n",
    "    %sql $stmt\n",
    "except Exception as e: \n",
    "    error_string=str(e)\n",
    "    if \"CLI0115E\" not in error_string:\n",
    "        print(e)\n",
    "## errors about an invalid cursor state can be ignored - that is what we expect here.\n",
    "%sql set current explain mode no\n",
    "%sql set current schema :explainschema\n",
    "display(\"this cost:\")\n",
    "this_cost=%sql $this_cost_stmt\n",
    "this_cost_df=this_cost.DataFrame()\n",
    "display(this_cost)\n",
    "pct_savings=100*((before_costs_df.iloc[0]['before_total_cost']-this_cost_df.iloc[0]['this_index_total_cost'])/(before_costs_df.iloc[0]['before_total_cost']-after_costs_df.iloc[0]['after_total_cost']))\n",
    "pct_imp=100*(this_cost_df.iloc[0]['this_index_total_cost']/before_costs_df.iloc[0]['before_total_cost'])\n",
    "display(\"This combination of indexes will get {:.2f} percent of what adding all indexes would\".format(pct_savings))\n",
    "display(\"This combination of indexes represents a {:.2f} percent improvement over the current indexes\".format(pct_imp))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "desc_tab_stmt = \"select \\\n",
    "                    c.colname as column_name, \\\n",
    "                    c.colno as position, \\\n",
    "                    c.typename as data_type, \\\n",
    "                    c.length, \\\n",
    "                    c.scale, \\\n",
    "                    case when  c.nulls = 'Y' then 1 else 0 end as nullable, \\\n",
    "                    default as default_value, \\\n",
    "                    case when c.identity ='Y' then 1 else 0 end as is_identity, \\\n",
    "                    case when c.generated ='' then 0 else 1 end as  is_computed, \\\n",
    "                    c.text as computed_formula \\\n",
    "                from syscat.columns c \\\n",
    "                where c.tabschema=:tabschema and c.tabname=:tabname \\\n",
    "                order by position with ur \""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "desc_ind_stmt = \"select \\\n",
    "                    i.lastused \\\n",
    "                    , substr(indname,1,20) as indname \\\n",
    "                    , colnames \\\n",
    "                    , fullkeycard  as indcard \\\n",
    "                    , card as table_card \\\n",
    "                    , float(fullkeycard)/float(card) * 100 as card_pct \\\n",
    "                    , decimal(clusterfactor,10,5) as clusterfactor \\\n",
    "                    , indextype \\\n",
    "                from syscat.indexes i join syscat.tables t on i.tabname = t.tabname and i.tabschema = t.tabschema \\\n",
    "                where t.tabschema=:tabschema and t.tabname=:tabname order by float(fullkeycard)/float(card) * 100 desc with ur\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Index details\n",
    "if inds.value:\n",
    "    index_list = inds.value\n",
    "else:\n",
    "    index_list = rec_ind_df.query('EXISTS == \"N\"').index\n",
    "\n",
    "for ind in index_list:\n",
    "    display(Markdown(\"## Index \"+str(ind)))\n",
    "    display(Markdown(\"### Index Columns\"))\n",
    "    display(HTML(rec_ind_df['colnames'][ind]))\n",
    "    ind_details=%sql select tbcreator, tbname, creation_text from {explainschema}.advise_index where name=:ind order by explain_time desc fetch first 1 rows only\n",
    "    ind_det_df=ind_details.DataFrame()\n",
    "    #display(ind_det_df)\n",
    "    display(Markdown(\"### Index Creation Syntax\"))\n",
    "    display(ind_det_df['creation_text'][0])\n",
    "    tabschema=ind_det_df['tbcreator'][0]\n",
    "    tabname=ind_det_df['tbname'][0]\n",
    "    display(Markdown(\"### Index is on table \"+ind_det_df['tbcreator'][0]+\".\"+ind_det_df['tbname'][0]))\n",
    "    display(Markdown('#### Table Structure'))\n",
    "    tab_desc=%sql $desc_tab_stmt\n",
    "    #display(tab_desc)\n",
    "    tab_desc_df=tab_desc.DataFrame()\n",
    "    tab_desc_df=tab_desc_df.set_index('POSITION')\n",
    "    display(tab_desc_df)\n",
    "    display(Markdown('#### Existing indexes on this table'))\n",
    "    ind_desc=%sql $desc_ind_stmt\n",
    "    ind_desc_df=ind_desc.DataFrame()\n",
    "    ind_desc_df.colnames=ind_desc_df.colnames.replace(to_replace='\\+', value='\\\\n+', regex=True).replace(to_replace='-', value='\\\\n-', regex=True).str.lstrip('\\n')\n",
    "    ind_desc_df=ind_desc_df.set_index('indname')\n",
    "    display(HTML(ind_desc_df.to_html(index=True).replace(\"\\\\n\",\"<br>\")))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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.7.3"
  },
  "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
}