Db2DatabaseHealthcheck-Anonymized.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Db2 Database Health Check\n",
    "This Notebook is designed to gather the informaiton that can easily be gathered via database connection to perform a health check on a Db2 database. This notebook must be run separately for each Db2 database, even though it also includes some instance-level checks. While some checks are automated, most of them require human judgement - this notebook mostly just provides the data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Disclaimer\n",
    "Run at your own risk. Understand what each cell is doing before executing it. This is not a perfect document or perfect code. Depending on your system, some parts may or may not work. There are no guarantees or support. Interpretation of a skilled Db2 DBA is required. While some sections may provide automatic checks, manual checks are still required for every section. This is designed as an occasional health check for a database and is not designed for high-performance, high-frequency checks. Current known flaws:\n",
    "1. The tablespace section is a bit jumbled and can use some work and clarification\n",
    "1. Data pruning is currently in a separate notebook and needs to be integrated here\n",
    "1. SQL analysis only has one KPI checked. Need to add additional KPIs. SQL analysis and index impact analysis will likely remain in a separate notebook.\n",
    "1. Multiple instance analysis should be in a separate notebook\n",
    "1. No analysis is done for DPF or PureScale\n",
    "1. HADR analysis is pretty basic\n",
    "1. No analysis is done for Replication\n",
    "1. There are still system level things that must be checked outside of this notebook. Need to consider how to integrate them.\n",
    "1. Need to figure out if I could get this notebook to form the skeleton of a health check document automatically\n",
    "1. Charts and graphs are generally not included here. Engaging XTIVIA for a health check will probably get you full access to the updated document with charts and graphs included.\n",
    "\n",
    "This is a living document that is constantly being updated. Backwards compatability is not guaranteed. Updates here are likely only a couple of times a year, near conferences."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Instructions for running\n",
    "1. Install Jupyter Notebook using Anaconda (https://www.anaconda.com/distribution/)\n",
    "    - Anaconda can be installed on your laptop or a vm on your laptop - anywhere you can connect to the databases in question. This works well if you are working with it alone, or have to connect to different vpns to connect to different databases\n",
    "    - Anaconda can be installed on a central VM or server that can connect to the databases you wish to work with. This works well if you are working with a team of DBAs and only need to work with databases on that one network. Anaconda works just fine on Ubuntu if you are looking for a free option\n",
    "    - Anaconda can be installed directly on the database server. This is generally my last choice, as I would rather not run an http server on my database server. I also rarely only care about one database server.\n",
    "1. Copy this notebook to the computer you've installed Jupyter Notebook on. I'll refer to this as your Jupyter Notebook server. \n",
    "1. Create a separate file to store enviornment variables. I've called mine ember_variables.py, and I run in in a cell below. This allows you to easily share the notebook without also sharing your ids and passwords and other sensative information. This also makes using git or other source control easy so you can keep notebooks updated across multiple locations. The format for this file is laid out below.\n",
    "1. I strongly recommend using a table of contents via the nbextensions module to configure a table of contents to navigate this document. The options for this should appear on the bottom of the edit menu after you have installed the libraries in the first code cell.\n",
    "1. Cells up through the database connection one should be run one by one to immediately detect and deal with errors. After the database connection cell, all further cells can be run using \"Run All Below\" from the Cell menu.\n",
    "\n",
    "### Format for variables file\n",
    "The ember_variables.py file has a format like this:\n",
    "```python\n",
    "NA1_User='yourid'\n",
    "NA1_PW='yourpw'\n",
    "\n",
    "NA1_Host='server1.example.com'\n",
    "NA1_insts = ('db2inst1', 'db2inst2', 'db2inst3', 'db2inst4')\n",
    "NA1_ports = {'db2inst1': 50001, 'db2inst2': 50002, 'db2inst3':50003, 'db2inst4':50004}\n",
    "NA1_dbs = {'db2inst1': ['SAMPLE1'], 'db2inst2': ['SAMPLE2'], 'db2inst3':['SAMPLE3'], 'db2inst4':['SAMPLE4','SAMPLE5']}\n",
    "```\n",
    "Feel free to structure things differently and if you have any good ideas in this area, please share them."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Set up the enviornment\n",
    "### Install Libraries\n",
    "Run the following cell if it is the first time using this notebook on a specific jupyter notebook server. If anything is installed, restart the kernel using the 'Kernel' menu at the top of this notebook"
   ]
  },
  {
   "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",
    "\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",
    "spec = importlib.util.find_spec(\"jupyter_contrib_nbextensions\")\n",
    "if spec is None:\n",
    "    print(\"Installing prerequisites.\")\n",
    "    !pip install jupyter_contrib_nbextensions\n",
    "    !pip install jupyter_nbextensions_configurator\n",
    "else:\n",
    "    print(\"jupyter_contrib_nbextensions is already installed.\")\n"
   ]
  },
  {
   "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 and load the SQL magic\n",
    "Required each time the kernel for this notebook is started or restarted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "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 matplotlib.dates as mdates\n",
    "from datetime import datetime\n",
    "import pandas as pd\n",
    "from IPython.display import display, HTML, Markdown\n",
    "import nbextensions\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Set Basic Variables and Connect to Database\n",
    "Connect to the database. Change the values in your variables file to match the environment you're connecting to. The format for this file is provided above."
   ]
  },
  {
   "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": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This is the database connection Cell\n",
    "user=NA1_User\n",
    "host=NA1_Host\n",
    "inst='b2cnapr'\n",
    "\n",
    "password=NA1_PW\n",
    "db=NA1_dbs[inst][0]\n",
    "port=NA1_ports[inst]\n",
    "\n",
    "%sql db2+ibm_db://$user:$password@$host:$port/$db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After you have a successful connection, all further cells in this notebook can be executed using the \"Run All Below\" option on the Cell Menu"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Configure SQL Magic in a few nice ways\n",
    "%config SqlMagic.style = 'MSWORD_FRIENDLY'\n",
    "pd.set_option('max_rows', 4096)\n",
    "pd.set_option('max_columns', 4096)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Functions that may be used in multiple other cells\n",
    "def highlight_equals(s,threshold,column):\n",
    "    is_max = pd.Series(data=False, index=s.index)\n",
    "    is_max[column] = s.loc[column] == threshold\n",
    "    print(type(is_max))\n",
    "    return ['background-color: yellow' if is_max.any() else '' for v in is_max]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Overall System Information<a class=\"anchor\" id=\"system-info\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql system_info << SELECT OS_NAME \n",
    "    , HOST_NAME \n",
    "    , OS_FULL_VERSION \n",
    "    , OS_KERNEL_VERSION \n",
    "    , OS_ARCH_TYPE \n",
    "    , CPU_TOTAL \n",
    "    , CPU_ONLINE \n",
    "    , CPU_CONFIGURED \n",
    "    , CPU_SPEED \n",
    "    , CPU_HMT_DEGREE \n",
    "    , CPU_CORES_PER_SOCKET \n",
    "    , MEMORY_TOTAL \n",
    "    , MEMORY_FREE \n",
    "    , VIRTUAL_MEM_TOTAL \n",
    "    , VIRTUAL_MEM_RESERVED \n",
    "    , VIRTUAL_MEM_FREE \n",
    "    , CPU_LOAD_SHORT \n",
    "    , CPU_LOAD_MEDIUM \n",
    "    , CPU_LOAD_LONG \n",
    "    , CPU_USAGE_TOTAL \n",
    "    , CPU_USER \n",
    "    , CPU_IDLE \n",
    "    , CPU_IOWAIT \n",
    "    , CPU_SYSTEM \n",
    "    , SWAP_PAGE_SIZE \n",
    "    , SWAP_PAGES_IN \n",
    "    , SWAP_PAGES_OUT \n",
    "FROM TABLE(SYSPROC.ENV_GET_SYSTEM_RESOURCES()) AS T \n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print out System information\n",
    "sys_info_df=system_info.DataFrame()\n",
    "sys_info_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Automatic Checks for Server Info\n",
    "display(Markdown(\"### CHECK: Paging space is either 50% of physical memory or a maximum of 16 GB\"))\n",
    "virt_mem=int(sys_info_df.loc[0]['virtual_mem_total']) - int(sys_info_df.loc[0]['memory_total'])\n",
    "display(Markdown(\"Physical Memory:\"+str(sys_info_df.loc[0]['memory_total'])))\n",
    "display(Markdown(\"Virtual Memory:\"+str(virt_mem)))\n",
    "if int(sys_info_df.loc[0]['memory_total']/2) < 16384 :\n",
    "    thresh=int(sys_info_df.loc[0]['memory_total']/2)\n",
    "else :\n",
    "    thresh=16384\n",
    "if int( virt_mem ) < thresh :\n",
    "    display(Markdown(\"**Fail**: Virtual memory of **\"+str(sys_info_df.loc[0]['virtual_mem_total']-sys_info_df.loc[0]['memory_total'])+\"** is less than **\"+str(thresh)+\"**. It should be greater than or equal.\"))\n",
    "else:\n",
    "    print(\"Virtual Memory size of \"+str(sys_info_df.loc[0]['virtual_mem_total'])+\" is acceptable given the real memory size of \"+str(sys_info_df.loc[0]['memory_total'])+\".\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Db2 Version and Fix Pack"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql level_info << SELECT INST_NAME \n",
    "    , IS_INST_PARTITIONABLE \n",
    "    , NUM_DBPARTITIONS \n",
    "    , INST_PTR_SIZE \n",
    "    , RELEASE_NUM \n",
    "    , SERVICE_LEVEL \n",
    "    , BLD_LEVEL \n",
    "    , PTF \n",
    "    , FIXPACK_NUM \n",
    "    , NUM_MEMBERS \n",
    "FROM SYSIBMADM.ENV_INST_INFO \n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print out Db2 Level information\n",
    "level_info_df=level_info.DataFrame()\n",
    "level_info_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Db2 Licensed Product(s)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql license_info << SELECT INSTALLED_PROD \n",
    "    , INSTALLED_PROD_FULLNAME \n",
    "    , LICENSE_INSTALLED \n",
    "    , PROD_RELEASE \n",
    "    , LICENSE_TYPE \n",
    "from SYSIBMADM.ENV_PROD_INFO \n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print out License information, and highlight items that are actually licensed\n",
    "lic_info_df=license_info.DataFrame()\n",
    "lic_info_df.style.apply(highlight_equals,threshold='Y',column=['license_installed'], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Db2 Configuration\n",
    "### Db2 Registry"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql registry_settings << SELECT REG_VAR_NAME \n",
    "    , REG_VAR_VALUE \n",
    "    , IS_AGGREGATE \n",
    "    , AGGREGATE_NAME \n",
    "    , LEVEL \n",
    "from SYSIBMADM.REG_VARIABLES \n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Print out Db2 Registry Settings\n",
    "reg_df=registry_settings.DataFrame()\n",
    "reg_df=reg_df.set_index('reg_var_name')\n",
    "reg_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DBM CFG"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql dbm_settings << SELECT NAME \n",
    "    , VALUE \n",
    "    , VALUE_FLAGS \n",
    "    , DEFERRED_VALUE \n",
    "    , DEFERRED_VALUE_FLAGS \n",
    "from SYSIBMADM.DBMCFG \n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Print out all DBM cfg settings\n",
    "dbm_df=dbm_settings.DataFrame()\n",
    "dbm_df=dbm_df.set_index('name')\n",
    "pd.set_option('display.max_rows', 4096)\n",
    "display(dbm_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Automatic Checks for DBM CFG"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Automatic Checks for DBM CFG\n",
    "# Check for deferred settings not in effect\n",
    "for index, row in dbm_df.iterrows():\n",
    "    if row['VALUE'] != row['deferred_value']:\n",
    "        display(Markdown(\"**WARNING** Deferred value not in effect! \"+index.upper()+\" value of **\"+str(row['VALUE'])+\"** and deferred value of **\"+str(row['deferred_value'])+\"** are different!\"))\n",
    "# Verify diag level is 3\n",
    "if int(dbm_df.loc['diaglevel']['VALUE']) == 3:\n",
    "    print(\"DIAGLEVEL is 3\")\n",
    "else:\n",
    "    display(Markdown(\"**WARNING** DIAGLEVEL is \"+str(dbm_df.loc['diaglevel']['VALUE'])+\", not 3\"))\n",
    "# Verify notify level is 3\n",
    "if int(dbm_df.loc['notifylevel']['VALUE']) == 3:\n",
    "    print(\"NOTIFYLEVEL is 3\")\n",
    "else:\n",
    "    display(Markdown(\"**WARNING** NOTIFYLEVEL is \"+str(dbm_df.loc['notifylevel']['VALUE'])+\", not 3\"))\n",
    "# Verify AUTHENTICATION is not CLIENT\n",
    "if dbm_df.loc['authentication']['VALUE'] == 'CLIENT':\n",
    "    display(Markdown(\"**WARNING** AUTHENTICATION is \"+str(dbm_df.loc['authentication']['VALUE'])))\n",
    "else:\n",
    "    print(\"AUTHENTICATION is \"+dbm_df.loc['authentication']['VALUE'])\n",
    "# Check to see if SVCENAME is a number or a string, and if it's set to a common default\n",
    "try:\n",
    "    svcename=int(dbm_df.loc['svcename']['VALUE'])\n",
    "except:\n",
    "    print (\"SVCENAME is \"+str(dbm_df.loc['svcename']['VALUE'])+\".\")\n",
    "else:\n",
    "    display(Markdown(\"**WARNING** SVCENAME is \"+str(dbm_df.loc['svcename']['VALUE'])+\". This is a number, and it is better to set SVCENAME to a service name that is defined in /etc/services.\"))   \n",
    "    if (svcename >= 50000 and svcename < 50011) or (svcename >= 60000 and svcename < 60011)  :\n",
    "        display(Markdown(\"**WARNING** SVCENAME is \"+str(dbm_df.loc['svcename']['VALUE'])+\". This number is an often-used default.\"))\n",
    "# Verify SYSMON_GROUP is set to something\n",
    "if dbm_df.loc['sysmon_group']['VALUE'] is None:\n",
    "    display(Markdown(\"**WARNING** SYSMON_GROUP is not set. Recommend setting it to something\"))\n",
    "else:\n",
    "    print (\"SYSMON_GROUP is \"+str(dbm_df.loc['sysmon_group']['VALUE'])+\".\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### DBM CFG Values to Pay Special Attention to in Manual Verification"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print DBM Cfg settings to pay special attention to, which are not included in the automatic warnings\n",
    "dft_mon_count = 0\n",
    "sys_group_count = 0\n",
    "# Report values of INTRA_PARALLEL and INSTANCE_MEMORY\n",
    "display(Markdown(\"INTRA_PARALLEL value of \"+str(dbm_df.loc['intra_parallel']['VALUE'])))\n",
    "display(Markdown(\"INSTANCE_MEMORY value of \"+str(dbm_df.loc['instance_memory']['VALUE'])+\" 4K pages, automatic setting of \"+str(dbm_df.loc['instance_memory']['value_flags'])))\n",
    "# Report values of DFT_MON parameters\n",
    "for index, row in dbm_df.iterrows():\n",
    "    # Report values of DFT_MON parameters\n",
    "    if index.startswith('dft_mon') :\n",
    "        if dft_mon_count == 0:\n",
    "            display(Markdown(\"DFT_MON settings:\"))\n",
    "            dft_mon_count += 1\n",
    "        if len(index.upper()) < 16: \n",
    "            print(index.upper()+\"\t\t=\"+str(row['VALUE']))\n",
    "        else: \n",
    "            print(index.upper()+\"\t=\"+str(row['VALUE']))\n",
    "    # Report values of system groups\n",
    "    elif index.endswith('group') :\n",
    "        if sys_group_count == 0 :\n",
    "            display(Markdown(\"System groups:\"))\n",
    "            sys_group_count += 1\n",
    "        print(index.upper()+\"\t\t=\"+str(row['VALUE']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DB CFG"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql db_settings << SELECT NAME \n",
    "        , VALUE \n",
    "        , VALUE_FLAGS \n",
    "        , DEFERRED_VALUE \n",
    "        , DEFERRED_VALUE_FLAGS \n",
    "    from SYSIBMADM.DBCFG \n",
    "    with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "display(Markdown('### Database Configuration Information for '+db+' on '+inst))\n",
    "db_df=db_settings.DataFrame()\n",
    "db_df=db_df.set_index('name')\n",
    "pd.set_option('display.max_rows', 4096)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Automatic Checks for this Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Automatic Checks for this Database\n",
    "# LOCKTIMEOUT is set to something and is less than 120\n",
    "if int(db_df.loc['locktimeout']['VALUE']) == -1 :\n",
    "    display(Markdown(\"**WARNING** LOCKTIMEOUT is -1.\"))\n",
    "elif int(db_df.loc['locktimeout']['VALUE']) > 180  :\n",
    "    display(Markdown(\"**WARNING** LOCKTIMEOUT is \"+str(db_df.loc['locktimeout']['VALUE'])+\".\"))\n",
    "else :\n",
    "    display(Markdown(\"LOCKTIMEOUT is \"+str(db_df.loc['locktimeout']['VALUE'])+\".\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# IF HADR, then BLOCKNONLOGGED\n",
    "## NOT WORKING when HADR is OFF\n",
    "if (db_df.loc['hadr_local_host']['VALUE'] is not None and db_df.loc['hadr_remote_host']['VALUE'] is not None and db_df.loc['hadr_local_svc']['VALUE'] is not None and db_df.loc['hadr_remote_svc']['VALUE'] is not None) or (db_df.loc['hadr_target_list']['VALUE'] is not None) :\n",
    "    if db_df.loc['blocknonlogged']['VALUE'].upper() == 'NO':\n",
    "        display(Markdown(\"**WARNING** HADR appears to be configured, but BLOCKNONLOGGED is set to NO.\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TRACKMOD ON\n",
    "if db_df.loc['trackmod']['VALUE'].upper() == 'OFF' or db_df.loc['trackmod']['VALUE'].upper() == 'NO' or db_df.loc['trackmod']['VALUE'].upper() == 'FALSE' :\n",
    "    display(Markdown(\"**WARNING** TRACKMOD is set to NO.\"))\n",
    "else:\n",
    "    display(Markdown(\"TRACKMOD is set to \"+db_df.loc['trackmod']['VALUE'].upper()+\".\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DFT QUERYOPT is 5\n",
    "if int(db_df.loc['dft_queryopt']['VALUE']) != 5 :\n",
    "    display(Markdown(\"**WARNING** dft_queryopt has been changed from the default of 5. It is set to \"+db_df.loc['dft_queryopt']['VALUE']))\n",
    "else :\n",
    "    display(Markdown(\"DFT_QUERYOPT is 5\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DFT_DEGREE (is 1)\n",
    "if int(db_df.loc['dft_degree']['VALUE']) != 1 :\n",
    "    display(Markdown(\"**WARNING** DFT_DEGREE has been changed from the default of 1. It is set to \"+db_df.loc['dft_degree']['VALUE']))\n",
    "else :\n",
    "    display(Markdown(\"DFT_DEGREE is 1\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Verify infinite logging is not used\n",
    "if int(db_df.loc['logsecond']['VALUE']) == -1 :\n",
    "    display(Markdown(\"**WARNING** infinite logging is in use. LOGSECOND is set to \"+db_df.loc['logsecond']['VALUE']))\n",
    "else :\n",
    "    display(Markdown(\"LOGSECOND is \"+db_df.loc['logsecond']['VALUE']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Manual Checks for this database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Manual Checks for this Database\n",
    "# STMT_CONC\n",
    "display(Markdown(\"STMT_CONC is set to \"+db_df.loc['stmt_conc']['VALUE']))\n",
    "# CUR_COMMIT\n",
    "display(Markdown(\"CUR_COMMIT is set to \"+db_df.loc['cur_commit']['VALUE']))\n",
    "# Encrypted?\n",
    "display(Markdown(\"ENCRYPTED_DATABASE is set to \"+db_df.loc['encrypted_database']['VALUE']))\n",
    "# SELF_TUNING_MEM and all STMM areas\n",
    "display(Markdown(\"#### Settings related to STMM :\"))\n",
    "display(Markdown(\"SELF_TUNING_MEM is set to \"+db_df.loc['self_tuning_mem']['VALUE']))\n",
    "print(\"\tINSTANCE_MEMORY is set to \"+dbm_df.loc['instance_memory']['VALUE']+\" - \"+dbm_df.loc['instance_memory']['value_flags'])\n",
    "print(\"\tDATABASE_MEMORY is set to \"+db_df.loc['database_memory']['VALUE']+\" - \"+db_df.loc['database_memory']['value_flags'])\n",
    "print(\"\tSHEAPTHRES_SHR is set to \"+db_df.loc['sheapthres_shr']['VALUE']+\" - \"+db_df.loc['sheapthres_shr']['value_flags'])\n",
    "print(\"\tSHEAPTHRES is set to \"+dbm_df.loc['sheapthres']['VALUE']+\" - \"+dbm_df.loc['sheapthres']['value_flags'])\n",
    "print(\"\tSORTHEAP is set to \"+db_df.loc['sortheap']['VALUE']+\" - \"+db_df.loc['sortheap']['value_flags'])\n",
    "print(\"\tPCKCACHESZ is set to \"+db_df.loc['pckcachesz']['VALUE']+\" - \"+db_df.loc['pckcachesz']['value_flags'])\n",
    "bp_sizes=%sql SELECT BP_NAME \\\n",
    "    , pagesize \\\n",
    "    , BP_CUR_BUFFSZ * pagesize /1024 /1024 as cur_bp_size_mb \\\n",
    "    , case when AUTOMATIC = 1 then 'AUTOMATIC' else 'STATIC' end as AUTOMATIC \\\n",
    "from table(mon_get_bufferpool(null,-2)) mgbp \\\n",
    "    join syscat.bufferpools bp on bp.bpname=mgbp.bp_name \\\n",
    "where BP_NAME not like 'IBMSYSTEM%' \\\n",
    "with ur \n",
    "bp_df=bp_sizes.DataFrame()\n",
    "bp_df=bp_df.set_index('bp_name')\n",
    "print(bp_df)\n",
    "\n",
    "# Automatic maintenance \n",
    "display(Markdown(\"#### Automatic Maintenance:\"))\n",
    "display(Markdown(\"AUTO_MAINT is set to \"+str(db_df.loc['auto_maint']['VALUE'])))\n",
    "display(Markdown(\"\tAUTO_DB_BACKUP is set to \"+str(db_df.loc['auto_db_backup']['VALUE'])))\n",
    "display(Markdown(\"\tAUTO_TBL_MAINT is set to \"+str(db_df.loc['auto_tbl_maint']['VALUE'])))\n",
    "display(Markdown(\"\t\tAUTO_RUNSTATS is set to \"+str(db_df.loc['auto_runstats']['VALUE'])))\n",
    "display(Markdown(\"\t\t\tAUTO_STMT_STATS is set to \"+str(db_df.loc['auto_stmt_stats']['VALUE'])))\n",
    "display(Markdown(\"\t\t\tAUTO_STATS_VIEWS is set to \"+str(db_df.loc['auto_stats_views']['VALUE'])))\n",
    "display(Markdown(\"\t\t\tAUTO_SAMPLING is set to \"+str(db_df.loc['auto_sampling']['VALUE'])))\n",
    "display(Markdown(\"\t\tAUTO_REORG is set to \"+str(db_df.loc['auto_reorg']['VALUE'])))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print out the entire db cfg\n",
    "display(db_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Memory"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Current Memory Layout"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql inst_memory << select memory_set_type || ' ' \n",
    "    || db_name as memory_set \n",
    "    , sum(memory_set_used)/1024 as used_mb \n",
    "from table(mon_get_memory_set(NULL,NULL,-2)) \n",
    "group by memory_set_type, db_name\n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Show balance of memory at the instance level\n",
    "display(Markdown(\"### Current Memory Layout for \"+inst))\n",
    "display(inst_memory)\n",
    "df=inst_memory.DataFrame()\n",
    "df[['used_mb']]=df[['used_mb']].astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql db_memory << select memory_set_type|| ' ' \n",
    "    || memory_pool_type as memory_pool \n",
    "    , sum(memory_pool_used)/1024 as used_mb \n",
    "from table(mon_get_memory_pool(NULL,:db,-2)) \n",
    "where db_name=upper(:db) \n",
    "group by memory_set_type, memory_pool_type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Show balance of memory by database\n",
    "display(Markdown(\"### Current Memory Layout for \"+db+\" on \"+inst))\n",
    "display(db_memory)\n",
    "db_mem_df=db_memory.DataFrame()\n",
    "db_mem_df[['used_mb']]=db_mem_df[['used_mb']].astype(float)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Layout with Maximum Sort\n",
    "Sort memory is only allocated as needed. This diagram uses the maximum possible sort allocation to understand the balance when sorts are occurring."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add memory layout with maximum sort\n",
    "db_mem_sort_df=db_mem_df.copy()\n",
    "db_mem_sort_df.columns\n",
    "db_mem_sort_df=db_mem_sort_df.set_index('memory_pool')\n",
    "display(db_mem_sort_df.loc['DATABASE SHARED_SORT']['used_mb'])\n",
    "db_mem_sort_df.loc['DATABASE SHARED_SORT']['used_mb']=float(db_df.loc['sheapthres_shr']['VALUE']) * 4 / 1024\n",
    "display(db_mem_sort_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Memory Breakdown by Buffer Pool"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql bp_sizes << SELECT BP_NAME \n",
    "    , pagesize \n",
    "    , BP_CUR_BUFFSZ * pagesize /1024 /1024 as cur_bp_size_mb \n",
    "    , case when AUTOMATIC = 1 then 'AUTOMATIC' else 'STATIC' end as AUTOMATIC \n",
    "from table(mon_get_bufferpool(null,-2)) mgbp \n",
    "    join syscat.bufferpools bp on bp.bpname=mgbp.bp_name \n",
    "where BP_NAME not like 'IBMSYSTEM%' \n",
    "with ur "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql bp_grp_sizes << SELECT BP_NAME \n",
    "    , BP_CUR_BUFFSZ * pagesize /1024 /1024 as cur_bp_size_mb \n",
    "from table(mon_get_bufferpool(null,-2)) mgbp \n",
    "    join syscat.bufferpools bp on bp.bpname=mgbp.bp_name \n",
    "where BP_NAME not like 'IBMSYSTEM%' \n",
    "and BP_CUR_BUFFSZ * pagesize /1024 /1024 > 1000 \n",
    "union \n",
    "SELECT case pagesize when 4096 then '4K_MISC' \n",
    "        when 8192 then '8K_MISC' \n",
    "        when 16384 then '16K_MISC' \n",
    "        when 32768 then '32K_MISC' \n",
    "        else 'OTHER' end as BP_NAME \n",
    "    , (sum(BP_CUR_BUFFSZ) * pagesize) /1024 /1024 as cur_bp_size_mb \n",
    "from table(mon_get_bufferpool(null,-2)) mgbp \n",
    "    join syscat.bufferpools bp on bp.bpname=mgbp.bp_name \n",
    "where BP_NAME not like 'IBMSYSTEM%' \n",
    "and BP_CUR_BUFFSZ * pagesize /1024 /1024 <= 1000 \n",
    "group by pagesize \n",
    "with ur "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Memory Breakdown by Buffer Pool\n",
    "\n",
    "display(Markdown(\"#### Buffer Pool Layout for \"+db+\" on \"+inst))\n",
    "%sql db2+ibm_db://$user:$password@$host:$port/$db\n",
    "bp_df=bp_sizes.DataFrame()\n",
    "#bp_df=bp_df.set_index('bp_name')\n",
    "\n",
    "bp_df[['cur_bp_size_mb']]=bp_df[['cur_bp_size_mb']].astype(float)\n",
    "\n",
    "display(Markdown(\"#### Buffer Pools by size\"))\n",
    "\n",
    "display(bp_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group buffer pools for better display\n",
    "display(Markdown(\"#### Grouping Small Buffer Pools for Better Display\"))\n",
    "\n",
    "bp_grp_size_df=bp_grp_sizes.DataFrame()\n",
    "bp_grp_size_df=bp_grp_size_df.set_index('bp_name')\n",
    "\n",
    "display(bp_grp_size_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Security"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('Security Information for ',db,' on ',inst)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### IDs with SECADM"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql secadm_ids << select grantee \n",
    "from syscat.dbauth \n",
    "where securityadmauth='Y' \n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print IDs with SECADM\n",
    "display(secadm_ids)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### IDs with DBADM"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql dbadm_ids << select grantee \n",
    "from syscat.dbauth \n",
    "where dbadmauth='Y' \n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Print ids with DBADM\n",
    "display(dbadm_ids)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### IDs with DATAACCESS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql dataaccess_ids << select grantee \n",
    "from syscat.dbauth \n",
    "where dataaccessauth='Y' \n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Print IDs with DATAACCESS\n",
    "display(dataaccess_ids)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Privileges held by PUBLIC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql public_perms << select privilege \n",
    "            , OBJECTTYPE \n",
    "            , OBJECTSCHEMA \n",
    "            , OBJECTNAME \n",
    "        from sysibmadm.privileges \n",
    "        where authid='PUBLIC' \n",
    "            and objectschema not like 'SYS%'\n",
    "            and objectschema not like 'NULLID%'\n",
    "            and objectschema not like 'DB2CAEM%'\n",
    "            and objectschema != 'SQLJ'\n",
    "            and objectname != 'DB2CLI'\n",
    "        order by objecttype, objectschema, objectname \n",
    "        with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql public_connect << select COALESCE(connectauth,'N') as connectauth\n",
    "        , grantee \n",
    "        from syscat.dbauth \n",
    "        where grantee='PUBLIC' \n",
    "        union \n",
    "        select 'N', 'ZZZ' as grantee from sysibm.sysdummy1 \n",
    "        order by grantee \n",
    "        with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Check to see if PUBLIC has connect, and generate a warning\n",
    "#Also print all PUBLIC privileges\n",
    "pub_df=public_connect.DataFrame()\n",
    "if 'Y' in public_connect[0][0] :\n",
    "    display(Markdown(\"**WARNING** PUBLIC has CONNECT authority on the database\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "List of public permissions minus those on the system catalog and packages granted with the \"grant public\" keywords on bind for db2cli and db2ubind"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "display(public_perms)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Database Objects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql list_evms << select evmonname \n",
    "    ,case when event_mon_state(evmonname) = 0 then 'INACTIVE' \n",
    "        when event_mon_state(evmonname) = 1 then 'ACTIVE' end as status \n",
    "    , target_type \n",
    "    , target \n",
    "    , autostart \n",
    "    , versionnumber \n",
    "    from syscat.eventmonitors \n",
    "    with ur"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Event Monitors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Database Objects\n",
    "## Event Monitors\n",
    "display(list_evms)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Explain Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql expln_schemas << SELECT tabschema \n",
    "    ,count(*) count_tables\n",
    "    FROM syscat.tables \n",
    "    where tabname like 'EXPLAIN%' \n",
    "        or tabname like 'ADVISE%' \n",
    "        GROUP BY tabschema \n",
    "    with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Database Objects\n",
    "## Explain Tables\n",
    "expl_schema_last = {}\n",
    "expl_schema_valid = {}\n",
    "expln_schema_df=expln_schemas.DataFrame()\n",
    "expln_schema_df.set_index('tabschema', inplace=True)\n",
    "display(expln_schema_df)\n",
    "for index, row in expln_schema_df.iterrows() :\n",
    "    display(Markdown(\"#### Verifying Explain Schema for \"+index))\n",
    "    verify_expln=%sql call SYSPROC.SYSINSTALLOBJECTS('EXPLAIN','V', NULL, '{index}')\n",
    "    if verify_expln is None:\n",
    "        display(Markdown(\"**Error found**\"))\n",
    "        display(verify_expln)\n",
    "        these_expln_tables = %sql SELECT tabname, card \\\n",
    "            FROM syscat.tables \\\n",
    "            WHERE tabschema=:index \\\n",
    "                and (tabname like 'EXPLAIN%' or tabname like 'ADVISE%') with ur\n",
    "        display(these_expln_tables)\n",
    "        expln_schema_df.at[index, 'Valid'] = \"No\"\n",
    "    else :\n",
    "        expln_schema_df.at[index, 'Valid'] = \"Yes\"\n",
    "        last_explain=%sql select max(explain_time) max_expln FROM {index}.EXPLAIN_INSTANCE with ur\n",
    "        expln_schema_df.at[index, 'last_explain_time'] = last_explain[0]['max_expln']\n",
    "display(expln_schema_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql list_bps << select \n",
    "        substr(bphr.bp_name,1,18) as bp_name \n",
    "        , bp_cur_buffsz \n",
    "        , pagesize \n",
    "        , ((pagesize*bp_cur_buffsz)/1024)/1024 as sz_mb \n",
    "        , total_logical_reads \n",
    "        , total_physical_reads \n",
    "        , data_hit_ratio_percent \n",
    "        , (select listagg(tbspace,chr(10)) within group (order by create_time) from syscat.tablespaces ts, syscat.bufferpools b where ts.bufferpoolid = b.bufferpoolid and b.bpname=mgbp.bp_name) as tablespaces \n",
    "from sysibmadm.bp_hitratio bphr join table(mon_get_bufferpool(NULL,-2)) mgbp \n",
    "        on mgbp.bp_name=bphr.bp_name \n",
    "    join syscat.bufferpools sbp on sbp.bpname=mgbp.bp_name \n",
    "with ur"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Buffer Pools\n",
    "#### List of Buffer Pools"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Database Objects\n",
    "## Buffer Pools\n",
    "display(list_bps)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Buffer Pool Hit Ratios by Tablespace"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql ts_bp_hitratios << select tbsp_name \n",
    "    , decimal(((float(pool_data_lbp_pages_found) + float(pool_index_lbp_pages_found) + float(pool_xda_lbp_pages_found) + float(pool_col_lbp_pages_found) - float(pool_async_data_lbp_pages_found) - float(pool_async_index_lbp_pages_found) - float(pool_async_xda_lbp_pages_found) - float(pool_async_col_lbp_pages_found)) / (float(pool_data_l_reads) + float(pool_index_l_reads) + float(pool_xda_l_reads) + float(pool_col_l_reads) + float(pool_temp_data_l_reads) + float(pool_temp_xda_l_reads) + float(pool_temp_index_l_reads) + float(pool_temp_col_l_reads))) * 100,5,2) \n",
    "    from table(mon_get_tablespace('',-2)) as t \n",
    "    order by tbsp_cur_pool_id \n",
    "    with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "display(ts_bp_hitratios)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Tables/Table Spaces by Buffer Pool"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql bp_tables << select b.bpname \n",
    "    , b.bufferpoolid \n",
    "    , (select count(*) from syscat.tablespaces ts where b.bufferpoolid=ts.bufferpoolid)as ts_count \n",
    "    , (select count(*) \n",
    "       from syscat.tables t join syscat.tablespaces ts \n",
    "           on t.tbspace=ts.tbspace or t.index_tbspace=ts.tbspace or t.long_tbspace=ts.tbspace \n",
    "       where ts.bufferpoolid=b.bufferpoolid) as tab_count \n",
    "    from syscat.bufferpools b \n",
    "with ur"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### All Buffer Pools"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bp_tab_df=bp_tables.DataFrame()\n",
    "display(bp_tab_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Unused Bufferpools"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "filter_bp_no_tab=(bp_tab_df['ts_count'] == 0) | (bp_tab_df['tab_count'] == 0)\n",
    "display(bp_tab_df[filter_bp_no_tab])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Comparing Buffer Pools"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql bp_compare << with bp_data as( select \n",
    "    mgbp.bp_name \n",
    "    , ((pagesize*bp_cur_buffsz)/1024)/1024 as sz_mb \n",
    "    , total_logical_reads \n",
    "    , data_hit_ratio_percent \n",
    "    , (select sum((tbsp_used_pages*pagesize)/1024/1024) as tabledata_mb from table(mon_get_tablespace(null,-2)) as mgt join syscat.bufferpools b on b.bufferpoolid=mgt.tbsp_cur_pool_id where mgbp.bp_name=b.bpname ) as tabledata_mb \n",
    "from sysibmadm.bp_hitratio bphr join table(mon_get_bufferpool(NULL,-2)) mgbp \n",
    "    on mgbp.bp_name=bphr.bp_name \n",
    "join syscat.bufferpools sbp on sbp.bpname=mgbp.bp_name) \n",
    ", bp_sum as (select \n",
    "    sum(sz_mb) as sum_size_mb \n",
    "    , sum(total_logical_reads) as sum_reads \n",
    "    , sum(tabledata_mb) as sum_data_mb \n",
    "from bp_data) \n",
    "select d.bp_name \n",
    "    , case when sum_size_mb > 0 then decimal(float(sz_mb)/float(sum_size_mb)*100,5,2) else 0.0 end as bp_size \n",
    "    , case when sum_reads >0 then decimal(float(total_logical_reads)/float(sum_reads)*100,5,2) else 0.0 end as bp_reads \n",
    "    , case when sum_data_mb >0 then decimal(float( tabledata_mb)/float(sum_data_mb)*100,5,2) else 0.0 end as data_served \n",
    "    , data_hit_ratio_percent \n",
    "from bp_data as d, bp_sum as s \n",
    "with ur"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Table Spaces"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Database Size by Pages in Table Spaces"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculated and display database data size based only on allocated pages in table spaces\n",
    "db_size_query=%sql select sum(tbsp_total_pages*tbsp_page_size/1024/1024/1024) as data_size_gb from table(mon_get_tablespace('',-2))\n",
    "display(db_size_query)   "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Count of Table Spaces"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Database Objects\n",
    "## Table Spaces\n",
    "# Tablespace Count\n",
    "tbsp_count=%sql select count(*) as num_tbsps from syscat.tablespaces\n",
    "display(tbsp_count)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Table Space Details"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql list_tbsps << select  tbsp_name, \n",
    "    tbsp_type, \n",
    "    tbsp_content_type as type, \n",
    "    (select count(*) from syscat.tables st where st.tbspace=t.tbsp_name) as tabcount, \n",
    "    tbsp_using_auto_storage as auto_sto, \n",
    "    tbsp_auto_resize_enabled as auto_resize, \n",
    "    tbsp_page_size as page_size, \n",
    "    tbsp_used_pages as used_pages, \n",
    "    tbsp_total_pages as total_pages, \n",
    "    tbsp_total_pages*tbsp_page_size/1024/1024/1024 as ts_gb, \n",
    "    case \n",
    "            when tbsp_type = 'SMS' then 'EXCLUDE' \n",
    "            when tbsp_using_auto_storage = 1 then 'EXCLUDE' \n",
    "            when tbsp_auto_resize_enabled = 1 then 'EXCLUDE' \n",
    "            else 'INCLUDE' \n",
    "    end as Space_check, \n",
    "    case \n",
    "            when \n",
    "                tbsp_max_size > 0 \n",
    "                and tbsp_max_size < 65536 \n",
    "            then to_char(tbsp_max_size) \n",
    "            when \n",
    "                tbsp_type = 'DMS' \n",
    "                and tbsp_content_type = 'ANY' \n",
    "                and tbsp_page_size = 4096 \n",
    "            then '64' \n",
    "            when \n",
    "                tbsp_type = 'DMS' \n",
    "                and tbsp_content_type = 'ANY' \n",
    "                and tbsp_page_size = 8192 \n",
    "            then '128' \n",
    "            when \n",
    "                tbsp_type = 'DMS' \n",
    "                and tbsp_content_type = 'ANY' \n",
    "                and tbsp_page_size = 16384 \n",
    "            then '256' \n",
    "            when \n",
    "                tbsp_type = 'DMS' \n",
    "                and tbsp_content_type = 'ANY' \n",
    "                and tbsp_page_size = 32768 \n",
    "            then '512' \n",
    "            when \n",
    "                tbsp_type = 'DMS' \n",
    "                and tbsp_content_type in ('SYSTEMP','USRTEMP','LARGE') \n",
    "                and tbsp_page_size = 4096 \n",
    "            then '8192' \n",
    "            when \n",
    "                tbsp_type = 'DMS' \n",
    "                and tbsp_content_type in ('SYSTEMP','USRTEMP','LARGE') \n",
    "                and tbsp_page_size = 8192 \n",
    "            then '16384' \n",
    "            when\n",
    "                tbsp_type = 'DMS' \n",
    "                and tbsp_content_type in ('SYSTEMP','USRTEMP','LARGE') \n",
    "                and tbsp_page_size = 16384 \n",
    "            then '32768' \n",
    "            when \n",
    "                tbsp_type = 'DMS' \n",
    "                and tbsp_content_type in ('SYSTEMP','USRTEMP','LARGE') \n",
    "                and tbsp_page_size = 32768 \n",
    "            then '65536' \n",
    "            else 'EXCLUDE' \n",
    "    end as maxsize_thresh \n",
    "from table(mon_get_tablespace('',-2)) as t \n",
    "order by tbsp_name \n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# clean the tablespace data a bit\n",
    "ts_df=list_tbsps.DataFrame()\n",
    "ts_df=ts_df.set_index('tbsp_name')\n",
    "ts_df[['ts_gb']]=ts_df[['ts_gb']].astype(float)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Potentially Wasted Space in Table Spaces"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql tbsps_space_waste << select  tbsp_name, \n",
    "    tbsp_auto_resize_enabled as auto_resize, \n",
    "    tbsp_page_size as page_size, \n",
    "    tbsp_total_pages*tbsp_page_size/1024/1024 as ts_mb, \n",
    "    100*decimal(float(tbsp_used_pages)/float(tbsp_total_pages),5,2) as pct_used, \n",
    "    tbsp_extent_size, \n",
    "    tbsp_total_pages - case when tbsp_used_pages > (tbsp_extent_size * 5) then tbsp_used_pages else tbsp_extent_size * 5 end as tbsp_freeable_pgs, \n",
    "    (tbsp_total_pages - case when tbsp_used_pages > (tbsp_extent_size * 5) then tbsp_used_pages else tbsp_extent_size * 5 end) *tbsp_page_size/1024/1024 as freeable_mb, \n",
    "    case when tbsp_used_pages < tbsp_extent_size * 5 then ((tbsp_extent_size * 5) - tbsp_used_pages) * tbsp_page_size/1024/1024 else 0 end as free_mb_sm_ext , \n",
    "    RECLAIMABLE_SPACE_ENABLED as reclaimable \n",
    "from table(mon_get_tablespace('',-2)) as t \n",
    "where tbsp_type = 'DMS' \n",
    "order by tbsp_name \n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use data collected on tablespaces to do a number of checks and chart tablespace size\n",
    "## Need to split this cell out into multiple cells\n",
    "tssw_df=tbsps_space_waste.DataFrame()\n",
    "tssw_df=tssw_df.set_index('tbsp_name')\n",
    "\n",
    "# Examine space data for problems\n",
    "display(Markdown(\"##### Easily Reclaimable Space in MB\"))\n",
    "display(tssw_df['freeable_mb'].sum())\n",
    "reducable_ts=tssw_df['freeable_mb'] > 100\n",
    "display(Markdown(\"###### Table Spaces With More Than 100 MB to Reclaim\"))\n",
    "with pd.option_context('display.max_rows', 999):\n",
    "    display(tssw_df[reducable_ts])\n",
    "display(Markdown(\"##### Reclaimable Space with Smaller Extent in MB\"))\n",
    "display(tssw_df['free_mb_sm_ext'].sum())   \n",
    "columns=[ts_df.index.name] + list(tssw_df)\n",
    "norec_tbsps=pd.DataFrame(columns=columns)\n",
    "norec_tbsps=norec_tbsps.set_index('tbsp_name')\n",
    "for index, row in tssw_df.iterrows() :\n",
    "    # Non-reclaimable\n",
    "    if row['reclaimable'] != 1 :\n",
    "        norec_tbsps=norec_tbsps.append(tssw_df.loc[[index]])\n",
    "display(Markdown(\"##### Table Spaces that are Not Reclaimable\"))\n",
    "display(norec_tbsps)\n",
    "\n",
    "# Examine tablespaces for problems\n",
    "columns=[ts_df.index.name] + list(ts_df)\n",
    "full_tbsps=pd.DataFrame(columns=columns)\n",
    "full_tbsps=full_tbsps.set_index('tbsp_name')\n",
    "noast_tbsps=pd.DataFrame(columns=columns)\n",
    "noast_tbsps=noast_tbsps.set_index('tbsp_name')\n",
    "static_tbsps=pd.DataFrame(columns=columns)\n",
    "static_tbsps=static_tbsps.set_index('tbsp_name')\n",
    "for index, row in ts_df.iterrows() :\n",
    "    # Nearly full tablespaces\n",
    "    max_pct=0\n",
    "    space_pct=0\n",
    "    if row['maxsize_thresh'] != 'EXCLUDE' :\n",
    "        max_thresh=float(row['maxsize_thresh'])\n",
    "        max_pct=row['ts_gb'] / max_thresh\n",
    "    if row['space_check'] != 'EXCLUDE' :\n",
    "        space_thresh=float(row['space_check'])\n",
    "        space_pct=row['ts_gb'] / space_thresh\n",
    "    if max_pct > 0.8 or space_pct > 0.8 :\n",
    "        full_tbsps=full_tbsps.append(ts_df.loc[[index]])\n",
    "    # Tablespaces not using AST\n",
    "    if row['auto_sto'] != 1 :\n",
    "        noast_tbsps=noast_tbsps.append(ts_df.loc[[index]])\n",
    "    # Tablespace not Using Auto Resize\n",
    "    if row['auto_resize'] != 1 and row['tbsp_type'] != 'SMS' :\n",
    "        static_tbsps=static_tbsps.append(ts_df.loc[[index]])\n",
    "display(Markdown(\"##### Table Spaces that are Nearly Full\"))\n",
    "display(full_tbsps)\n",
    "display(Markdown(\"##### Table Spaces that are Not Using AST\"))\n",
    "display(noast_tbsps)\n",
    "display(Markdown(\"##### Table Spaces that are Not Using Automatic Resize\"))\n",
    "display(static_tbsps)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Grouping tablespaces for better display"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql large_tbsps << select  tbsp_name, \n",
    "    tbsp_total_pages*tbsp_page_size/1024/1024/1024 as ts_gb \n",
    "    from table(mon_get_tablespace('',-2)) as t \n",
    "    where (tbsp_total_pages*tbsp_page_size/1024/1024/1024) > 40 \n",
    "    union \n",
    "    select substr(tbsp_name,1,4) || '_' as tbsp_name, \n",
    "    sum (tbsp_total_pages*tbsp_page_size/1024/1024/1024) as ts_gb \n",
    "    from table(mon_get_tablespace('',-2)) as t \n",
    "    where (tbsp_total_pages*tbsp_page_size/1024/1024/1024) <= 40 \n",
    "    group by substr(tbsp_name,1,4) \n",
    "    order by tbsp_name \n",
    "    with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "large_ts_df=large_tbsps.DataFrame()\n",
    "large_ts_df=large_ts_df.set_index('tbsp_name')\n",
    "\n",
    "display(ts_df)\n",
    "display(tssw_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Database Size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "    # Total Database size\n",
    "    # This cell needs work - often does not work\n",
    "    #conn=ibm_db.connect(\"DATABASE=\"+db+\";HOSTNAME=\"+host+\";PORT=\"+port+\";PROTOCOL=TCPIP;UID=\"+user+\";PWD=\"+password+\";\", \"\", \"\")\n",
    "    size_ts='TIMESTAMP(\\'2019-02-19-00.00.00\\')'\n",
    "    db_size=-1\n",
    "    db_capacity=-1\n",
    "    size_refresh=-1\n",
    "    #db_size_query=ibm_db.callproc(conn,'get_dbsize_info', (size_ts,db_size,db_capacity,-1))\n",
    "    db_size_query=%sql call get_dbsize_info(:size_ts, :db_size, :db_capacity, -1)\n",
    "    display(db_size_query)   "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Indexes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Database Objects\n",
    "## Indexes\n",
    "display(Markdown(\"### Indexes in \"+db+\" on \"+inst))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Indexes with a cardinality of one"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql card1_indexes << with indcols as ( select indschema \n",
    "            , indname \n",
    "            , listagg(case when colorder = 'A' then '+' when colorder = 'D' then '-' else '>' end || colname,chr(10)) within group (order by colseq) as colnames \n",
    "        from syscat.indexcoluse group by indschema, indname)\n",
    "select  i.lastused, \n",
    "    t.tabschema, \n",
    "    t.tabname, \n",
    "    i.indname, \n",
    "    ic.colnames, \n",
    "    fullkeycard, \n",
    "    card, \n",
    "    volatile \n",
    "from    syscat.indexes i join syscat.tables t \n",
    "    on i.tabname=t.tabname and i.tabschema=t.tabschema \n",
    "    join indcols ic on i.indname=ic.indname and i.indschema=ic.indschema\n",
    "where   fullkeycard=1 \n",
    "    and indextype not in ('BLOK', 'DIM') \n",
    "    and t.tabschema not like 'SYS%' \n",
    "    and uniquerule='D' \n",
    "    and not exists (select 1 \n",
    "            from syscat.references r join syscat.keycoluse k \n",
    "                    on r.tabschema=k.tabschema and r.tabname=k.tabname \n",
    "            where t.tabschema=r.tabschema \n",
    "                    and r.tabname = t.tabname \n",
    "                    and k.colname in (      select colname \n",
    "                                    from syscat.indexcoluse as ic \n",
    "                                    where ic.indschema=i.indschema \n",
    "                                    and ic.indname=i.indname)) \n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Format data nicely, and then display\n",
    "card1_ind_df=card1_indexes.DataFrame()\n",
    "# Add Thosands comma to numbers\n",
    "card1_ind_df['card'] = card1_ind_df.apply(lambda x: \"{:,}\".format(x['card']), axis=1)\n",
    "# Display each column of an index on a separate line (the line breaks from SQL don't translate right to the data frame)\n",
    "display(HTML(card1_ind_df.to_html(index=False).replace(\"\\\\n\",\"<br>\")))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Indexes not used in the last 30 days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql unused_indexes << with indcols as ( select indschema \n",
    "            , indname \n",
    "            , listagg(case when colorder = 'A' then '+' when colorder = 'D' then '-' else '>' end || colname,chr(10)) within group (order by colseq) as colnames \n",
    "        from syscat.indexcoluse group by indschema, indname) select  i.lastused, \n",
    "    t.tabschema, \n",
    "    t.tabname, \n",
    "    i.indname, \n",
    "    ic.colnames, \n",
    "    bigint(fullkeycard)as fullkeycard, \n",
    "    bigint(card) as table_card, \n",
    "    mi.index_scans, \n",
    "    mi.index_only_scans, \n",
    "    volatile \n",
    "from    syscat.indexes i join syscat.tables t \n",
    "    on i.tabname=t.tabname and i.tabschema=t.tabschema \n",
    "    join table(mon_get_index('','',-2)) as mi on i.iid=mi.iid and i.tabschema=mi.tabschema and i.tabname = mi.tabname \n",
    "    join indcols ic on i.indschema=ic.indschema and i.indname=ic.indname \n",
    "where \n",
    "    indextype not in ('BLOK', 'DIM') \n",
    "    and t.tabschema not like 'SYS%' \n",
    "    and uniquerule='D' \n",
    "    and i.lastused < current date - 30 days \n",
    "    and card > 0 \n",
    "    and not exists (select 1 \n",
    "            from syscat.references r join syscat.keycoluse k \n",
    "                    on r.tabschema=k.tabschema and r.tabname=k.tabname \n",
    "            where t.tabschema=r.tabschema \n",
    "                    and r.tabname = t.tabname \n",
    "                    and k.colname in (      select colname \n",
    "                                    from syscat.indexcoluse as ic \n",
    "                                    where ic.indschema=i.indschema \n",
    "                                    and ic.indname=i.indname)) \n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Format data nicely, and display\n",
    "unused_ind_df=unused_indexes.DataFrame()\n",
    "# Add Thousands comma to numbers\n",
    "unused_ind_df['fullkeycard'] = unused_ind_df.apply(lambda x: \"{:,}\".format(x['fullkeycard']), axis=1)\n",
    "unused_ind_df['table_card'] = unused_ind_df.apply(lambda x: \"{:,}\".format(x['table_card']), axis=1)\n",
    "# Display each column of an index on a separate line (the line breaks from SQL don't translate right to the data frame)\n",
    "display(HTML(unused_ind_df.to_html(index=False).replace(\"\\\\n\",\"<br>\")))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Tables With Largest Number of Indexes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql many_indexes << select \n",
    "    substr(t.tabschema,1,15) as tabschema, \n",
    "    substr(t.tabname,1,30) as tabname, \n",
    "    lastused, \n",
    "    date(stats_time) as stats_date, \n",
    "    card ,\n",
    "    (select count(*) from syscat.indexes i where t.tabschema=i.tabschema and t.tabname=i.tabname) as ind_count\n",
    "from    syscat.tables t \n",
    "where \n",
    "    t.tabschema not like 'SYS%' \n",
    "    and t.tabname not like 'ADVISE%' \n",
    "    and t.tabname not like 'EXPLAIN%' \n",
    "    and type = 'T'  \n",
    "order by ind_count desc, tabschema, tabname \n",
    "fetch first 10 rows only\n",
    "with ur "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Format data nicely and display\n",
    "many_ind_df=many_indexes.DataFrame()\n",
    "# Add thousands comma to numbers\n",
    "many_ind_df['card'] = many_ind_df.apply(lambda x: \"{:,}\".format(x['card']), axis=1)\n",
    "display(HTML(many_ind_df.to_html(index=False)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Tables Without any Indexes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql no_indexes << select \n",
    "    substr(t.tabschema,1,15) as tabschema, \n",
    "    substr(t.tabname,1,30) as tabname, \n",
    "    lastused, \n",
    "    date(stats_time) as stats_date, \n",
    "    card \n",
    "from    syscat.tables t \n",
    "where \n",
    "    t.tabschema not like 'SYS%' \n",
    "    and t.tabname not like 'ADVISE%' \n",
    "    and t.tabname not like 'EXPLAIN%' \n",
    "    and type = 'T' \n",
    "    and t.tabname not like 'TI_%' \n",
    "    and not exists (select 1 from syscat.indexes i where t.tabschema=i.tabschema and t.tabname=i.tabname) \n",
    "order by card desc, tabschema, tabname \n",
    "with ur "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Format data nicely and display\n",
    "no_ind_df=no_indexes.DataFrame()\n",
    "# Add thousands comma to numbers\n",
    "no_ind_df['card'] = no_ind_df.apply(lambda x: \"{:,}\".format(x['card']), axis=1)\n",
    "display(HTML(no_ind_df.to_html(index=False)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Most Used Indexes in the Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql mostused_indexes << with indcols as ( select indschema \n",
    "            , indname\n",
    "            , listagg(case when colorder = 'A' then '+' when colorder = 'D' then '-' else '>' end || colname,chr(10)) within group (order by colseq) as colnames \n",
    "        from syscat.indexcoluse group by indschema, indname) select  i.lastused, \n",
    "    t.tabschema as tabschema, \n",
    "    t.tabname as tabname, \n",
    "    i.indname as indname, \n",
    "    ic.colnames, \n",
    "    bigint(fullkeycard)as fullkeycard, \n",
    "    bigint(card) as table_card, \n",
    "    mi.index_scans, \n",
    "    mi.index_only_scans, \n",
    "    mi.page_allocations, \n",
    "    volatile \n",
    "from    syscat.indexes i join syscat.tables t \n",
    "    on i.tabname=t.tabname and i.tabschema=t.tabschema \n",
    "    join table(mon_get_index('','',-2)) as mi on i.iid=mi.iid and i.tabschema=mi.tabschema and i.tabname = mi.tabname \n",
    "    join indcols ic on i.indschema=ic.indschema and i.indname=ic.indname \n",
    "where \n",
    "    indextype not in ('BLOK', 'DIM') \n",
    "    and t.tabschema not like 'SYS%' \n",
    "    and uniquerule='D' \n",
    "    and not exists (select 1 \n",
    "            from syscat.references r join syscat.keycoluse k \n",
    "                    on r.tabschema=k.tabschema and r.tabname=k.tabname \n",
    "            where t.tabschema=r.tabschema \n",
    "                    and r.tabname = t.tabname \n",
    "                    and k.colname in (      select colname \n",
    "                                    from syscat.indexcoluse as ic \n",
    "                                    where ic.indschema=i.indschema \n",
    "                                    and ic.indname=i.indname)) \n",
    "order by mi.index_scans desc \n",
    "fetch first 20 rows only \n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Format data nicely and display\n",
    "busy_ind_df=mostused_indexes.DataFrame()\n",
    "# Add thousands comma to numbers\n",
    "busy_ind_df['fullkeycard'] = busy_ind_df.apply(lambda x: \"{:,}\".format(x['fullkeycard']), axis=1)\n",
    "busy_ind_df['table_card'] = busy_ind_df.apply(lambda x: \"{:,}\".format(x['table_card']), axis=1)\n",
    "busy_ind_df['index_scans'] = busy_ind_df.apply(lambda x: \"{:,}\".format(x['index_scans']), axis=1)\n",
    "busy_ind_df['index_only_scans'] = busy_ind_df.apply(lambda x: \"{:,}\".format(x['index_only_scans']), axis=1)\n",
    "busy_ind_df['page_allocations'] = busy_ind_df.apply(lambda x: \"{:,}\".format(x['page_allocations']), axis=1)\n",
    "# Display each column of an index on a separate line (the line breaks from SQL don't translate right to the data frame)\n",
    "display(HTML(unused_ind_df.to_html(index=False).replace(\"\\\\n\",\"<br>\")))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Database Objects\n",
    "## Tables\n",
    "#All databases on this instance\n",
    "display(Markdown(\"### Tables in \"+db+\" on \"+inst))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Tables Not Used in 30 days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql unused_tables << select  t.lastused, \n",
    "    date(stats_time) as stats_time, \n",
    "    date(create_time) as create_time, \n",
    "    substr(t.tabschema,1,10) as tabschema, \n",
    "    substr(t.tabname,1,25) as tabname, \n",
    "    bigint(card) as table_card, \n",
    "    mt.table_scans, \n",
    "    mt.rows_read, \n",
    "    mt.rows_inserted + mt.rows_updated + mt.rows_deleted as rows_altered, \n",
    "    t.volatile \n",
    "from    syscat.tables t \n",
    "    join table(mon_get_table('','',-2)) as mt on t.tabschema=mt.tabschema and t.tabname = mt.tabname \n",
    "where \n",
    "    t.tabschema not like 'SYS%' \n",
    "    and t.tabname not like '%EXPLAIN%' \n",
    "    and t.tabname not like '%ADVISE%' \n",
    "    and t.lastused < current date - 30 days \n",
    "    and type = 'T' \n",
    "order by t.lastused, t.card desc, t.tabschema, t.tabname \n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print the unused tables\n",
    "display(unused_tables)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Most used tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql mostused_tables << select  t.lastused, \n",
    "    substr(t.tabschema,1,10) as tabschema, \n",
    "    substr(t.tabname,1,25) as tabname, \n",
    "    bigint(card) as table_card, \n",
    "    mt.table_scans, \n",
    "    mt.rows_read, \n",
    "    case when card >0 then mt.rows_read/card else 0 end as avg_reads_per_row, \n",
    "    mt.rows_inserted + mt.rows_updated + mt.rows_deleted as rows_altered, \n",
    "    t.volatile \n",
    "from    syscat.tables t \n",
    "    join table(mon_get_table('','',-2)) as mt on t.tabschema=mt.tabschema and t.tabname = mt.tabname\n",
    "where \n",
    "    t.tabschema not like 'SYS%' \n",
    "    and t.tabname not like '%EXPLAIN%' \n",
    "    and t.tabname not like '%ADVISE%' \n",
    "order by 7 desc, 8 desc, t.tabschema, t.tabname \n",
    "fetch first 20 rows only \n",
    "with ur "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Format the data nicely and display\n",
    "busy_tab_df=mostused_tables.DataFrame()\n",
    "# Add thousands comma to numbers\n",
    "busy_tab_df['table_card'] = busy_tab_df.apply(lambda x: \"{:,}\".format(x['table_card']), axis=1)\n",
    "busy_tab_df['table_scans'] = busy_tab_df.apply(lambda x: \"{:,}\".format(x['table_scans']), axis=1)\n",
    "busy_tab_df['rows_read'] = busy_tab_df.apply(lambda x: \"{:,}\".format(x['rows_read']), axis=1)\n",
    "busy_tab_df['avg_reads_per_row'] = busy_tab_df.apply(lambda x: \"{:,}\".format(x['avg_reads_per_row']), axis=1)\n",
    "busy_tab_df['rows_altered'] = busy_tab_df.apply(lambda x: \"{:,}\".format(x['rows_altered']), axis=1)\n",
    "display(HTML(busy_tab_df.to_html(index=False)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Tables with the highest number of rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql highcard_tables << select  t.lastused, \n",
    "    substr(t.tabschema,1,10) as tabschema, \n",
    "    substr(t.tabname,1,25) as tabname, \n",
    "    bigint(card) as table_card, \n",
    "    mt.table_scans, \n",
    "    mt.rows_read, \n",
    "    case when card >0 then mt.rows_read/card else 0 end as avg_reads_per_row, \n",
    "    mt.rows_inserted + mt.rows_updated + mt.rows_deleted as rows_altered, \n",
    "    t.volatile \n",
    "from    syscat.tables t \n",
    "    join table(mon_get_table('','',-2)) as mt on t.tabschema=mt.tabschema and t.tabname = mt.tabname \n",
    "where \n",
    "    t.tabschema not like 'SYS%' \n",
    "    and t.tabname not like '%EXPLAIN%' \n",
    "    and t.tabname not like '%ADVISE%' \n",
    "order by table_card desc, t.tabschema, t.tabname \n",
    "fetch first 20 rows only \n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Format data nicely and display\n",
    "busy_tab_df=mostused_tables.DataFrame()\n",
    "# Add thousands comma to numbers\n",
    "busy_tab_df['table_card'] = busy_tab_df.apply(lambda x: \"{:,}\".format(x['table_card']), axis=1)\n",
    "busy_tab_df['table_scans'] = busy_tab_df.apply(lambda x: \"{:,}\".format(x['table_scans']), axis=1)\n",
    "busy_tab_df['rows_read'] = busy_tab_df.apply(lambda x: \"{:,}\".format(x['rows_read']), axis=1)\n",
    "busy_tab_df['avg_reads_per_row'] = busy_tab_df.apply(lambda x: \"{:,}\".format(x['avg_reads_per_row']), axis=1)\n",
    "busy_tab_df['rows_altered'] = busy_tab_df.apply(lambda x: \"{:,}\".format(x['rows_altered']), axis=1)\n",
    "display(HTML(busy_tab_df.to_html(index=False)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Tables using the extended row size feature"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql ext_row_tables << select \n",
    "    mgt.tabschema, \n",
    "    mgt.tabname \n",
    "from \n",
    "    table(mon_get_table(NULL,NULL,NULL)) mgt \n",
    "where \n",
    "    mgt.lob_object_l_pages > 0 \n",
    "    and not exists (select 1 \n",
    "                  from syscat.columns c \n",
    "                  where c.tabschema = mgt.tabschema \n",
    "          and c.tabname = mgt.tabname \n",
    "          and c.typename in ('CLOB','BLOB')) \n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "display(ext_row_tables)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Tables with LOBs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql lob_tables << select \n",
    "    c.tabschema, \n",
    "    c.tabname, \n",
    "    c.colname,\n",
    "    c.typename, \n",
    "    c.length,\n",
    "    t.tbspace,\n",
    "    case ts.fs_caching\n",
    "        when 0 then 'No'\n",
    "        when 1 then 'Yes'\n",
    "        when 2 then 'Depends on OS and FS Type'\n",
    "        end as fs_caching,\n",
    "    t.card as table_card,\n",
    "    mgt.rows_read,\n",
    "    c.logged,\n",
    "    pctinlined\n",
    "from \n",
    "    syscat.columns c \n",
    "    join syscat.tables t on c.tabschema = t.tabschema and c.tabname = t.tabname\n",
    "    left outer join table(mon_get_tablespace(NULL,-2)) ts on coalesce(t.long_tbspace, t.tbspace) = ts.tbsp_name\n",
    "    left outer join table(mon_get_table(NULL,NULL,-2)) as mgt on c.tabschema=mgt.tabschema and c.tabname=mgt.tabname\n",
    "where \n",
    "    c.typename in ('CLOB','BLOB') \n",
    "    and c.tabschema not like 'SYS%'\n",
    "    and t.type = 'T'\n",
    "order by mgt.rows_read desc, table_card asc\n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Format data nicely and display\n",
    "lob_tab_df=lob_tables.DataFrame()\n",
    "# Only do this if there is data. If there is no data, the formatting will throw an error\n",
    "if int(lob_tab_df.shape[0]) != 0 :\n",
    "    # Add thousands comma to numbers\n",
    "    lob_tab_df['table_card'] = lob_tab_df.apply(lambda x: \"{:,}\".format(x['table_card']), axis=1)\n",
    "    lob_tab_df['length'] = lob_tab_df.apply(lambda x: \"{:,}\".format(x['length']), axis=1)\n",
    "    lob_tab_df['rows_read'] = lob_tab_df.apply(lambda x: \"{:,}\".format(x['rows_read']), axis=1)\n",
    "    display(HTML(lob_tab_df.to_html(index=False)))\n",
    "else :\n",
    "    print(\"No tables with LOBs found.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Tables with XML"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql xml_tables << select \n",
    "    c.tabschema, \n",
    "    c.tabname, \n",
    "    c.colname,\n",
    "    c.typename, \n",
    "    c.length,\n",
    "    t.tbspace,\n",
    "    case ts.fs_caching\n",
    "        when 0 then 'No'\n",
    "        when 1 then 'Yes'\n",
    "        when 2 then 'Depends on OS and FS Type'\n",
    "        end as fs_caching,\n",
    "    t.card as table_card,\n",
    "    mgt.rows_read,\n",
    "    c.logged,\n",
    "    pctinlined\n",
    "from \n",
    "    syscat.columns c \n",
    "    join syscat.tables t on c.tabschema = t.tabschema and c.tabname = t.tabname\n",
    "    left outer join table(mon_get_tablespace(NULL,-2)) ts on coalesce(t.long_tbspace, t.tbspace) = ts.tbsp_name\n",
    "    left outer join table(mon_get_table(NULL,NULL,-2)) as mgt on c.tabschema=mgt.tabschema and c.tabname=mgt.tabname\n",
    "where \n",
    "    c.typename in ('XML') \n",
    "    and c.tabschema not like 'SYS%'\n",
    "    and t.type = 'T'\n",
    "order by mgt.rows_read desc, table_card asc\n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Format data nicely and display\n",
    "xml_tab_df=xml_tables.DataFrame()\n",
    "# Only do the formatting if data exists\n",
    "if int(xml_tab_df.shape[0]) != 0 :\n",
    "    # Add thousands comma to numbers\n",
    "    xml_tab_df['table_card'] = xml_tab_df.apply(lambda x: \"{:,}\".format(x['table_card']), axis=1)\n",
    "    xml_tab_df['length'] = xml_tab_df.apply(lambda x: \"{:,}\".format(x['length']), axis=1)\n",
    "    xml_tab_df['rows_read'] = xml_tab_df.apply(lambda x: \"{:,}\".format(x['rows_read']), axis=1)\n",
    "    display(HTML(xml_tab_df.to_html(index=False)))\n",
    "else :\n",
    "    print(\"No XML tables found.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Tables with constraint issues"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql const_tables << select \n",
    "    t.tabschema, \n",
    "    t.tabname, \n",
    "    t.const_checked,\n",
    "    t.card as table_card,\n",
    "    mgt.rows_read, \n",
    "    (select count(*) from syscat.tabconst tc where t.tabschema=tc.tabschema and t.tabname=tc.tabname and enforced='N') as unenforced_const\n",
    "from \n",
    "    syscat.tables t \n",
    "    left outer join table(mon_get_table(NULL,NULL,-2)) as mgt on t.tabschema=mgt.tabschema and t.tabname=mgt.tabname\n",
    "where \n",
    "    t.const_checked like '%N%' or \n",
    "    t.const_checked like '%F%' or \n",
    "    t.const_checked like '%U%' or \n",
    "    t.const_checked like '%W%' \n",
    "order by mgt.rows_read desc, t.card desc\n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Format data nicely and display\n",
    "const_tab_df=const_tables.DataFrame()\n",
    "# Only do the formatting if data exists\n",
    "if int(const_tab_df.shape[0]) != 0 :\n",
    "    # Add thousands comma to numbers\n",
    "    const_tab_df['table_card'] =const_tab_df.apply(lambda x: \"{:,}\".format(x['table_card']), axis=1)\n",
    "    const_tab_df['rows_read'] = const_tab_df.apply(lambda x: \"{:,}\".format(x['rows_read']), axis=1)\n",
    "    display(HTML(const_tab_df.to_html(index=False)))\n",
    "else :\n",
    "    print(\"No tables with constraint issues found.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Statistical views"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql stats_views << select \n",
    "    t.tabschema\n",
    "    , t.tabname\n",
    "    , date(t.stats_time) as stats_date\n",
    "    , t.card as table_card\n",
    "    , coalesce(mgt.rows_read,0) as rows_read\n",
    "from \n",
    "    syscat.tables t \n",
    "    left outer join table(mon_get_table(NULL,NULL,-2)) as mgt on t.tabschema=mgt.tabschema and t.tabname=mgt.tabname\n",
    "where \n",
    "    substr(t.property,13,1) = 'Y' \n",
    "order by 5 desc, 4 desc\n",
    "with ur\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Format data nicely and display\n",
    "stats_vw_df=stats_views.DataFrame()\n",
    "# Only format if data exists\n",
    "if int(stats_vw_df.shape[0]) != 0 :\n",
    "    # add thousands comma to numbers\n",
    "    stats_vw_df['table_card'] =stats_vw_df.apply(lambda x: \"{:,}\".format(x['table_card']), axis=1)\n",
    "    stats_vw_df['rows_read'] = stats_vw_df.apply(lambda x: \"{:,}\".format(x['rows_read']), axis=1)\n",
    "    display(HTML(stats_vw_df.to_html(index=False)))\n",
    "else :\n",
    "    print(\"No statistical views found.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Column-Organized Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql col_tables << select \n",
    "    t.tabschema, \n",
    "    t.tabname \n",
    "from \n",
    "    syscat.tables t \n",
    "where \n",
    "    substr(t.property,20,1) = 'Y' \n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "display(col_tables)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Tables Using Compression"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql comp_tables << select \n",
    "    case compression when 'B' then 'ROW and VALUE' when 'N' then 'No Compression' when 'R' then 'ROW' when '' then 'NA' else compression end as compression, \n",
    "    case rowcompmode when 'A' then 'ADAPTIVE' when 'S' then 'STATIC' else rowcompmode end as comp_mode, \n",
    "    count(*) as tables \n",
    "from \n",
    "    syscat.tables t \n",
    "where type='T'\n",
    "group by compression, rowcompmode \n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "display(comp_tables)\n",
    "# Future check to add: how is compression doing - problem tables or columns for compression"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Clustered Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql clus_tables << select \n",
    "    case clustered when 'T' then 'INSERT TIME' when 'Y' then 'DIMENSION' else clustered end as clustered, \n",
    "    count(*) as tables \n",
    "from \n",
    "    syscat.tables t \n",
    "group by clustered \n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "display(clus_tables)\n",
    "# Future area to add - space wasted due to MDC or other clustering problems"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transaction Logs\n",
    "#### Histogram of Transaction Log Archives"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql archlog_histogram << WITH gen_ts (ts) AS ( \n",
    "        VALUES current timestamp - 7 days \n",
    "        UNION ALL \n",
    "        SELECT ts + 1 hour \n",
    "        FROM gen_ts \n",
    "        WHERE ts <= current timestamp), \n",
    "    format_ts (yyyymmddhh) AS ( \n",
    "        SELECT bigint(ts)/10000 \n",
    "        FROM gen_ts), \n",
    "    log_archives (yyyymmddhh, archive_count) AS ( \n",
    "        SELECT substr(start_time, 1, 10) as YYYYMMDDhh, count(*) \n",
    "        FROM sysibmadm.db_history \n",
    "        WHERE operation = 'X' \n",
    "        GROUP BY substr(start_time, 1, 10) ) \n",
    "    SELECT \n",
    "        translate('ABCD-EF-GH IJh', cast(f.yyyymmddhh as char(12)), 'ABCDEFGHIJ') as hour \n",
    "        ,coalesce(a.archive_count,0) AS logs_archived \n",
    "    FROM \n",
    "       format_ts f \n",
    "            LEFT OUTER JOIN log_archives a \n",
    "            ON f.yyyymmddhh = a.yyyymmddhh \n",
    "    ORDER BY hour \n",
    "    with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "display(archlog_histogram)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Log Buffer Sizing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql logbuf_sizing << select log_reads \n",
    "    , log_writes \n",
    "    , decimal(float(log_reads)/float(log_writes),10,5) log_read_write_ratio \n",
    "from table (mon_get_transaction_log(-2)) \n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# log pages read vs. log pages written\n",
    "display(logbuf_sizing)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Database Maintenance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Database Maintenance\n",
    "## Backups\n",
    "display(Markdown(\"### Database Maintenance for \"+db+\" on \"+inst))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### List of all Backups in History in the Last 14 days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql backup_list << select date(timestamp(start_time)) as start_date \n",
    "    , time(timestamp(start_time)) as start_time \n",
    "    , start_time as start_timestamp \n",
    "    , dayname(start_time) as day\n",
    "    , timestampdiff ( 4, varchar(timestamp(end_time) - timestamp(start_time)) ) as duration \n",
    "    , case operationtype \n",
    "        when 'D' then 'Delta Offline' \n",
    "        when 'E' then 'Delta Online' \n",
    "        when 'F' then 'Offline' \n",
    "        when 'I' then 'Incremental Offline' \n",
    "        when 'N' then 'Online' \n",
    "        when 'O' then 'Incremental Online' \n",
    "     else operationtype \n",
    "     end || ' ' || case \n",
    "            when objecttype = 'D' then 'DB' \n",
    "            when objecttype = 'P' then 'TS'\n",
    "            else objecttype \n",
    "        end as Type \n",
    "    , devicetype \n",
    "    , sqlcode \n",
    "from sysibmadm.db_history \n",
    "where operation='B' \n",
    "    and start_time > current timestamp - 14 days\n",
    "order by start_date, start_time \n",
    "with ur "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generate a timeline based on data to more easily see the strategy\n",
    "backup_df=backup_list.DataFrame()\n",
    "display(HTML(backup_df.to_html(index=False)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Runstats\n",
    "#### Last runstats date for non-system tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql last_stats << select date(stats_time) as stats_date\n",
    "    , volatile\n",
    "    , count(*) as num_tables \n",
    "from syscat.tables \n",
    "where type='T' \n",
    "    and tabschema not like 'SYS%' \n",
    "group by date(stats_time), volatile\n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "display(last_stats)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Last runstats date for system tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql last_stats << select month(stats_time) as stats_month \n",
    "    , year(stats_time) as stats_year \n",
    "    , count(*) as num_tables \n",
    "from syscat.tables \n",
    "where type='T' \n",
    "    and tabschema like 'SYS%' \n",
    "group by month(stats_time), year(stats_time) \n",
    "order by stats_year desc, stats_month desc \n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "display(last_stats)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reorgs\n",
    "#### Recent Reorgs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql reorg_list << select date(timestamp(start_time)) as start_date \n",
    "    , time(timestamp(start_time)) as start_time \n",
    "    , tabschema \n",
    "    , tabname \n",
    "    , case operationtype \n",
    "        when 'F' then 'Offline' \n",
    "        when 'N' then 'Online' \n",
    "     else operationtype \n",
    "     end as Type \n",
    "    , sqlcode \n",
    "from sysibmadm.db_history \n",
    "where operation='G' \n",
    "order by start_date, start_time \n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "display(reorg_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Tables Needing Reorg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generate reorgchk information\n",
    "%sql call reorgchk_tb_stats('T', 'ALL')\n",
    "%sql call reorgchk_ix_stats('T', 'ALL')\n",
    "#ignore errors that say \"ibm_db_dbi::ProgrammingError: The last call to execute did not produce any result set.\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql tab_reorg_list << select table_schema\n",
    "    , table_name\n",
    "    , npages\n",
    "    , card\n",
    "    , reorg\n",
    "FROM session.tb_stats\n",
    "WHERE reorg like '%*%'\n",
    "    and card > 10\n",
    "    and fpages > 10\n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print data on tables needing reorg\n",
    "tab_reorg_needed_df=tab_reorg_list.DataFrame()\n",
    "if int(tab_reorg_needed_df.shape[0]) != 0 :\n",
    "    # Add thousands comma for numbers\n",
    "    tab_reorg_needed_df['card'] =tab_reorg_needed_df.apply(lambda x: \"{:,}\".format(x['card']), axis=1)\n",
    "    tab_reorg_needed_df['npages'] =tab_reorg_needed_df.apply(lambda x: \"{:,}\".format(x['npages']), axis=1)\n",
    "    display(HTML(tab_reorg_needed_df.to_html(index=False)))\n",
    "else :\n",
    "    print(\"No tables needing reorg found.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql ind_reorg_list << select table_schema\n",
    "    , table_name\n",
    "    , index_schema\n",
    "    , index_name\n",
    "    , nleaf\n",
    "    , indcard\n",
    "    , reorg\n",
    "FROM session.ix_stats\n",
    "WHERE (reorg like '%*%' and reorg not like '*----')\n",
    "    and indcard > 10\n",
    "    and nleaf > 10\n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Display tables needing index reorg\n",
    "#display(ind_reorg_list)\n",
    "ind_reorg_needed_df=ind_reorg_list.DataFrame()\n",
    "if int(ind_reorg_needed_df.shape[0]) != 0 :\n",
    "    # Add thousands comma for numbers\n",
    "    ind_reorg_needed_df['indcard'] =ind_reorg_needed_df.apply(lambda x: \"{:,}\".format(x['indcard']), axis=1)\n",
    "    ind_reorg_needed_df['nleaf'] =ind_reorg_needed_df.apply(lambda x: \"{:,}\".format(x['nleaf']), axis=1)\n",
    "    display(HTML(ind_reorg_needed_df.to_html(index=False)))\n",
    "else :\n",
    "    print(\"No indexes needing reorg found.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Pruning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Data Pruning\n",
    "# In seperate Notebook"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SQL Analysis\n",
    "Only indicators that SQL analysis is needed are calculated here. Separate notebooks are used for identifying and analyzing problem SQL.\n",
    "### Index Read Efficiency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql ixref << select rows_read/rows_returned \n",
    "from table(mon_get_database(-2)) \n",
    "with ur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if ixref[0][0] <= 10 :\n",
    "    print(\"Index read efficiency is ideal for an OLTP database at \"+ str(ixref[0][0]))\n",
    "elif ixref[0][0] > 10 & ixref[0][0] <= 100 :\n",
    "    print(\"Index read efficiency is not great but not horrible for an OLTP database at \"+ str(ixref[0][0]))\n",
    "elif ixref[0][0] > 100 & ixref[0][0] <= 1000 :\n",
    "    print(\"Index read efficiency is bad for an OLTP database at \"+ str(ixref[0][0]))\n",
    "elif ixref[0][0] > 1000 :\n",
    "    print(\"Index read efficiency is horrendous for an OLTP database at \"+ str(ixref[0][0]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Memory Picture for Whole Server\n",
    "This section is designed to be used with a multi-instance server and does not apply to all environments. At least on Linux, Db2 often over-allocates memory for multiple instances"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Total Memory on server"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "server_memory=%sql select decimal(value/1024,10,2) as mem_tot_gb \\\n",
    "    from sysibmadm.env_sys_resources \\\n",
    "    where name='MEMORY_TOTAL'\n",
    "server_memory"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Portion of Server Memory this Instance Represents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "instance_memory=%sql select decimal((value*4)/1024/1024,20,2) as instance_memory_gb \\\n",
    "  from sysibmadm.dbmcfg \\\n",
    "  where name='instance_memory' \\\n",
    "  with ur\n",
    "instance_memory"
   ]
  }
 ],
 "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
}