DB2_HealthCheck_Malta.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook should be launched from a session of jupyter notebook that was launched from a DB2 command window\n",
    "To do this, run an administrator DB2 command window as an administrator and type 'jupyter notebook'\n",
    "\n",
    "Set up the enviornment:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys,os,os.path\n",
    "os.environ['IBM_DB_HOME']='C:\\Program Files\\IBM\\SQLLIB'\n",
    "!pip install ipython-sql\n",
    "!pip install \"ibm-db==2.0.8a\" \n",
    "!pip install ibm_db_sa"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Restart the Kernel if this is your first time installing the above. The next steps will fail unless you do this."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Import the modules and load the SQL magic"
   ]
  },
  {
   "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"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Connect to the database. Change the values of user, host, and password to match your environment. For connection to a local host, use 'localhost' for the host name. Also change the port number and database name in the connection string."
   ]
  },
  {
   "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": [
    "user=NA1_User\n",
    "host=NA1_Host\n",
    "inst=NA1_insts[0]\n",
    "\n",
    "password=NA1_PW\n",
    "db=NA1_dbs['sample'][0]\n",
    "port=NA1_ports['sample']\n",
    "\n",
    "%sql db2+ibm_db://$user:$password@$host:$port/$db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Overall System Information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "system_info=%sql 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\n",
    "sys_info_df=system_info.DataFrame()\n",
    "sys_info_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Db2 Version and Fix Pack"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "level_info=%sql 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\n",
    "level_info"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Db2 Licensed Product(s)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "level_info=%sql 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\n",
    "level_info"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Db2 Configuration\n",
    "### Db2 Registry"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "registry_settings=%sql SELECT REG_VAR_NAME \\\n",
    "    , REG_VAR_VALUE \\\n",
    "    , IS_AGGREGATE \\\n",
    "    , AGGREGATE_NAME \\\n",
    "    , LEVEL \\\n",
    "from SYSIBMADM.REG_VARIABLES \\\n",
    "with ur\n",
    "registry_settings"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DBM CFG"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "dbm_settings=%sql SELECT NAME \\\n",
    "    , VALUE \\\n",
    "    , VALUE_FLAGS \\\n",
    "    , DEFERRED_VALUE \\\n",
    "    , DEFERRED_VALUE_FLAGS \\\n",
    "from SYSIBMADM.DBMCFG \\\n",
    "with ur\n",
    "df=dbm_settings.DataFrame()\n",
    "dbm_df=df.set_index('name')\n",
    "pd.set_option('display.max_rows', 500)\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",
    "# (Sample, build your own)\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\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Values to Pay Special Attention to in Manual Verification"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Values to focus on for manual verification\n",
    "# (Sample, build your own)\n",
    "display(Markdown(\"INTRA_PARALLEL value of \"+str(dbm_df.loc['intra_parallel']['VALUE'])))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DB CFG"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "#del db_df\n",
    "for db in NA1_dbs[inst]:\n",
    "    print('Database Configuration Information for ',db,' on ',inst)\n",
    "    %sql db2+ibm_db://$user:$password@$host:$port/$db\n",
    "    db_settings=%sql SELECT NAME \\\n",
    "        , VALUE \\\n",
    "        , VALUE_FLAGS \\\n",
    "        , DEFERRED_VALUE \\\n",
    "        , DEFERRED_VALUE_FLAGS \\\n",
    "    from SYSIBMADM.DBCFG \\\n",
    "    with ur\n",
    "    db_df=db_settings.DataFrame()\n",
    "    db_df=db_df.set_index('name')\n",
    "    pd.set_option('display.max_rows', 500)\n",
    "    #display(db_df)\n",
    "    \n",
    "    #Database Memory Layout\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",
    "\n",
    "    display(HTML(df.to_html()))\n",
    "    \n",
    "    ## Automatic Checks for this Database\n",
    "    # (Sample, build your own)\n",
    "    # IF HADR, then BLOCKNONLOGGED\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.\"))\n",
    "\n",
    "    ## Manual Checks for this Database\n",
    "    # (Sample, build your own)\n",
    "    # STMT_CONC\n",
    "    display(Markdown(\"STMT_CONC is set to \"+db_df.loc['stmt_conc']['VALUE']))\n",
    "\n",
    "    display(db_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Security"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#All databases on this instance\n",
    "for db in NA1_dbs[inst]:\n",
    "    %sql db2+ibm_db://$user:$password@$host:$port/$db\n",
    "    secadm = {}\n",
    "    secadm.clear()\n",
    "    dbadm = {}\n",
    "    dbadm.clear()\n",
    "    dataaccess = {}\n",
    "    dataaccess.clear()\n",
    "    host=NA1_Host\n",
    "    print('Security Information for ',db,' on ',inst)\n",
    "    display(Markdown(\"### Security Information for \"+db+\" on \"+inst))\n",
    "    secadm_ids=%sql select grantee \\\n",
    "        from syscat.dbauth \\\n",
    "        where securityadmauth='Y' \\\n",
    "        with ur\n",
    "    display(Markdown(\"#### IDs with SECADM\"))\n",
    "    display(secadm_ids)\n",
    "    dbadm_ids=%sql select grantee \\\n",
    "        from syscat.dbauth \\\n",
    "        where dbadmauth='Y' \\\n",
    "        with ur\n",
    "    display(Markdown(\"#### IDs with DBADM\"))\n",
    "    display(dbadm_ids)\n",
    "    dataaccess_ids=%sql select grantee \\\n",
    "        from syscat.dbauth \\\n",
    "        where dataaccessauth='Y' \\\n",
    "        with ur\n",
    "    display(Markdown(\"#### IDs with DATAACCESS\"))\n",
    "    display(dataaccess_ids)\n",
    "    public_perms=%sql select privilege \\\n",
    "            , OBJECTTYPE \\\n",
    "            , OBJECTSCHEMA \\\n",
    "            , OBJECTNAME \\\n",
    "        from sysibmadm.privileges \\\n",
    "        where authid='PUBLIC' \\\n",
    "        order by objecttype, objectschema, objectname \\\n",
    "        with ur\n",
    "    display(Markdown(\"#### Privileges held by PUBLIC\"))\n",
    "    public_connect=%sql select connectauth \\\n",
    "        from syscat.dbauth \\\n",
    "        where grantee='PUBLIC' \\\n",
    "        with ur\n",
    "    pub_df=public_connect.DataFrame()\n",
    "    if public_connect[0][0] != 'N' :\n",
    "        display(Markdown(\"**WARNING** PUBLIC has CONNECT authority on the database\"))\n",
    "    display(public_perms)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Database Objects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Database Objects\n",
    "## Event Monitors\n",
    "#All databases on this instance\n",
    "for db in NA1_dbs[inst]:\n",
    "    %sql db2+ibm_db://$user:$password@$host:$port/$db\n",
    "    host=NA1_Host\n",
    "    display(Markdown(\"### Event Monitors in \"+db+\" on \"+inst))\n",
    "    list_evms=%sql 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\n",
    "    display(list_evms)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Database Objects\n",
    "## Explain Tables\n",
    "#All databases on this instance\n",
    "for db in NA1_dbs[inst]:\n",
    "    expl_schema_last = {}\n",
    "    expl_schema_valid = {}\n",
    "    %sql db2+ibm_db://$user:$password@$host:$port/$db\n",
    "    host=NA1_Host\n",
    "    display(Markdown(\"### Explain Tables in \"+db+\" on \"+inst))\n",
    "    expln_schemas=%sql 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\n",
    "    expln_schema_df=expln_schemas.DataFrame()\n",
    "    expln_schema_df.set_index('tabschema', inplace=True)\n",
    "    display(expln_schema_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Database Objects\n",
    "## Buffer Pools\n",
    "#All databases on this instance\n",
    "for db in NA1_dbs[inst]:\n",
    "    %sql db2+ibm_db://$user:$password@$host:$port/$db\n",
    "    host=NA1_Host\n",
    "    display(Markdown(\"### Buffer Pools in \"+db+\" on \"+inst))\n",
    "    list_bps=%sql 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\n",
    "    display(list_bps)\n",
    "    display(Markdown(\"#### Buffer Pool hit ratios by tablespace\"))\n",
    "    ts_bp_hitratios=%sql 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\n",
    "    display(ts_bp_hitratios)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Database Objects\n",
    "## Table Spaces\n",
    "#All databases on this instance\n",
    "for db in NA1_dbs[inst]:\n",
    "    %sql db2+ibm_db://$user:$password@$host:$port/$db\n",
    "    host=NA1_Host\n",
    "    display(Markdown(\"### Table Spaces in \"+db+\" on \"+inst))\n",
    "    list_tbsps=%sql 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_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\n",
    "    ts_df=list_tbsps.DataFrame()\n",
    "    ts_df=ts_df.set_index('tbsp_name')\n",
    "\n",
    "    ts_df.plot.pie(y='ts_gb')\n",
    "    plt.axis('equal')\n",
    "    plt.legend(bbox_to_anchor=(1,1))\n",
    "    plt.show()\n",
    "    display(ts_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Database Objects\n",
    "## Indexes\n",
    "#All databases on this instance\n",
    "for db in NA1_dbs[inst]:\n",
    "    %sql db2+ibm_db://$user:$password@$host:$port/$db\n",
    "    host=NA1_Host\n",
    "    display(Markdown(\"### Indexes in \"+db+\" on \"+inst))\n",
    "    display(Markdown(\"#### Indexes with a cardinality of one\"))\n",
    "    card1_indexes=%sql select  i.lastused, \\\n",
    "        t.tabschema, \\\n",
    "        t.tabname, \\\n",
    "        indname, \\\n",
    "        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",
    "    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\n",
    "    display(card1_indexes)\n",
    "    display(Markdown(\"#### Indexes not used in the last 30 days\"))\n",
    "    unused_indexes=%sql 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\n",
    "    display(unused_indexes)\n",
    "    display(Markdown(\"#### Tables Without any Indexes\"))\n",
    "    no_indexes=%sql 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 tabschema, tabname \\\n",
    "    with ur \n",
    "    display(no_indexes)\n",
    "    \n",
    "    display(Markdown(\"#### Most Used Indexes in the Database\"))\n",
    "    mostused_indexes=%sql 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\n",
    "    display(mostused_indexes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Database Objects\n",
    "## Tables\n",
    "#All databases on this instance\n",
    "for db in NA1_dbs[inst]:\n",
    "    %sql db2+ibm_db://$user:$password@$host:$port/$db\n",
    "    host=NA1_Host\n",
    "    display(Markdown(\"### Tables in \"+db+\" on \"+inst))\n",
    "    display(Markdown(\"#### Tables Not Used in 30 days\"))\n",
    "    unused_tables=%sql 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\n",
    "    display(unused_tables)\n",
    "    display(Markdown(\"#### Most used tables\"))\n",
    "    mostused_tables=%sql 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 \n",
    "    display(mostused_tables)\n",
    "    display(Markdown(\"#### Largest Tables by Number of Rows\"))\n",
    "    highcard_tables=%sql 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_times_each_row_in_table_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",
    "    order by table_card desc, t.tabschema, t.tabname \\\n",
    "    fetch first 20 rows only \\\n",
    "    with ur\n",
    "    display(highcard_tables)\n",
    "    display(Markdown(\"#### Tables making use of the extended row size feature\"))\n",
    "    ext_row_tables=%sql 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\n",
    "    display(ext_row_tables)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Database Objects\n",
    "## Transaction Logs\n",
    "#All databases on this instance\n",
    "for db in NA1_dbs[inst]:\n",
    "    %sql db2+ibm_db://$user:$password@$host:$port/$db\n",
    "    host=NA1_Host\n",
    "    display(Markdown(\"### Transaction logs in \"+db+\" on \"+inst))\n",
    "    display(Markdown(\"#### Transaction log file histogram\"))\n",
    "    archlog_histogram=%sql 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\n",
    "    display(archlog_histogram)\n",
    "    archlog_df=archlog_histogram.DataFrame()\n",
    "    # log pages read vs. log pages written\n",
    "    display(Markdown(\"#### Log Buffer Sizing\"))\n",
    "    logbuf_sizing=%sql 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\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",
    "#All databases on this instance\n",
    "for db in NA1_dbs[inst]:\n",
    "    %sql db2+ibm_db://$user:$password@$host:$port/$db\n",
    "    host=NA1_Host\n",
    "    display(Markdown(\"### Database Maintenance for \"+db+\" on \"+inst))\n",
    "    display(Markdown(\"#### List of all Backups in History\"))\n",
    "    backup_list=%sql select date(timestamp(start_time)) as start_date \\\n",
    "        , time(timestamp(start_time)) as start_time \\\n",
    "        , start_time as start_timestamp \\\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 when objecttype = 'D' then 'DB' else objecttype end as Type \\\n",
    "        , devicetype \\\n",
    "        , sqlcode \\\n",
    "    from sysibmadm.db_history \\\n",
    "    where operation='B' \\\n",
    "    order by start_date, start_time \\\n",
    "    with ur    \n",
    "    backup_df=backup_list.DataFrame()\n",
    "    display(backup_df)\n",
    "## Runstats\n",
    "    display(Markdown(\"#### Last runstats date for non-system tables\"))\n",
    "    last_stats=%sql select date(stats_time) as stats_date \\\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) \\\n",
    "    with ur\n",
    "    display(last_stats)\n",
    "    display(Markdown(\"#### Last runstats date for system tables\"))\n",
    "    last_stats=%sql 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\n",
    "    display(last_stats)\n",
    "## Reorgs\n",
    "    display(Markdown(\"#### Recent Reorgs\"))\n",
    "    reorg_list=%sql 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\n",
    "    display(reorg_list)"
   ]
  },
  {
   "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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# SQL Analysis\n",
    "# In seperate Notebook"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Memory Picture for Whole Server"
   ]
  },
  {
   "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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#All instances on one server\n",
    "inst_mem = {}\n",
    "inst_mem.clear\n",
    "\n",
    "for inst in NA1_insts:\n",
    "    port=NA1_ports[inst]\n",
    "    db=NA1_dbs[inst][0]\n",
    "    %sql db2+ibm_db://$user:$password@$host:$port/$db\n",
    "    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",
    "    inst_mem[inst]=instance_memory[0][0]\n",
    "\n",
    "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",
    "tot_db2_mem=sum(inst_mem.values())\n",
    "inst_mem['system']=server_memory[0][0]-tot_db2_mem\n",
    "labels = inst_mem.keys()\n",
    "sizes = inst_mem.values()\n",
    "plt.pie(sizes, labels=labels, autopct='%1.1f%%',  shadow=True, startangle=140)\n",
    " \n",
    "plt.axis('equal')\n",
    "plt.show()\n",
    "\n",
    "df = pd.DataFrame.from_dict(data=inst_mem, orient='index')\n",
    "df.columns = ['GB']\n",
    "#print(df.to_html())\n",
    "display(HTML(df.to_html()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#All instances on one server - level\n",
    "level = {}\n",
    "level.clear()\n",
    "host=NA1_Host\n",
    "for inst in NA1_insts:\n",
    "    port=NA1_ports[inst]\n",
    "    db=NA1_dbs[inst][0]\n",
    "    %sql db2+ibm_db://$user:$password@$host:$port/$db\n",
    "    service_level=%sql select service_level \\\n",
    "    from SYSIBMADM.ENV_INST_INFO \\\n",
    "    with ur\n",
    "    level[inst]=service_level[0][0]\n",
    "df = pd.DataFrame.from_dict(data=level, orient='index')\n",
    "df.columns = ['Service Level']\n",
    "#print(df.to_html())\n",
    "display(HTML(df.to_html()))\n"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}