Db2DataManagementConsoleTechnicalPreview.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# The Db2 Data Management Console Technical Preview \n",
    "\n",
    "The Jupyter Notebook in the project contains examples of how to use the Open APIs and composable interface that is available in the Db2 Data Management Console Technical Preview. Everything in the User Interface is also available through an open and fully documented RESTful Services API. You can find the full documentation in the Help included with the Db2 Data Management Console.\n",
    "\n",
    "The technical preview also lets you embed elements of the full user interface into an IFrame by constructing the appropriate URL. The sample notebooks include examples of how to do this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import the class libraries \n",
    "import requests\n",
    "import ssl\n",
    "import json\n",
    "from pprint import pprint\n",
    "from requests import Response\n",
    "import pandas as pd\n",
    "import time\n",
    "from requests.packages.urllib3.exceptions import InsecureRequestWarning\n",
    "requests.packages.urllib3.disable_warnings(InsecureRequestWarning)\n",
    "from IPython.display import IFrame\n",
    "from IPython.display import display, HTML\n",
    "from pandas.io.json import json_normalize"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Run the Db2 Class library\n",
    "# Used to construct and reuse an Autentication Key\n",
    "# Used to construct RESTAPI URLs and JSON payloads\n",
    "class Db2():\n",
    "    \n",
    "    def __init__(self, url, verify = False, proxies=None, ):\n",
    "        self.url = url\n",
    "        self.proxies = proxies\n",
    "        self.verify = verify\n",
    "\n",
    "    def authenticate(self, userid, password, profile=\"\"):\n",
    "        credentials = {'userid':userid, 'password':password}\n",
    "        r = requests.post(self.url+'/auth/tokens', verify=self.verify, json=credentials, proxies=self.proxies)\n",
    "        if (r.status_code == 200):\n",
    "            bearerToken = r.json()['token']\n",
    "            if profile == \"\":\n",
    "                self.headers = {'Authorization': 'Bearer'+ ' '+bearerToken}\n",
    "            else:\n",
    "                self.headers = {'Authorization': 'Bearer'+ ' '+bearerToken, 'X-DB-Profile': profile}\n",
    "        else:\n",
    "            print ('Unable to authenticate, no bearer token obtained')\n",
    "        \n",
    "    def printResponse(self, r, code):\n",
    "        if (r.status_code == code):\n",
    "            pprint(r.json())\n",
    "        else:\n",
    "            print (r.status_code)\n",
    "            print (r.content)\n",
    "    \n",
    "    def getRequest(self, api, json=None):\n",
    "        return requests.get(self.url+api, verify = self.verify, headers=self.headers, proxies = self.proxies, json=json)\n",
    "\n",
    "    def postRequest(self, api, json=None):\n",
    "        return requests.post(self.url+api, verify = self.verify, headers=self.headers, proxies = self.proxies, json=json) \n",
    "        \n",
    "    def getStatusCode(self, response):\n",
    "        return (response.status_code)\n",
    "\n",
    "    def getJSON(self, response):\n",
    "        return (response.json())\n",
    "    \n",
    "    def getSchemas(self):\n",
    "        return self.getRequest('/schemas')\n",
    "    \n",
    "    def runSQL(self, script, limit=10, separator=';', stopOnError=False):\n",
    "        sqlJob = {'commands': script, 'limit':limit, 'separator':separator, 'stop_on_error':str(stopOnError)}\n",
    "        return self.postRequest('/sql_jobs',sqlJob)\n",
    "        \n",
    "    def getSQLJobResult(self, jobid):\n",
    "        return self.getRequest('/sql_jobs/'+jobid)\n",
    "    \n",
    "    def getCurrentApplicationsConnections(self, includeSystem='true'):\n",
    "        return self.getRequest('/metrics/applications/connections/current/list?&include_sys='+str(includeSystem))\n",
    "    \n",
    "    def getInflightCount(self, startTime, endTime):\n",
    "        return self.getRequest('/metrics/statements/inflight_executions/current/list?start='+str(startTime)+'&end='+str(endTime));\n",
    "       \n",
    "    def getInflightCurrentList(self, includeSystem='true'):\n",
    "        return self.getRequest('/metrics/statements/inflight_executions/current/list?'+'&include_sys='+str(includeSystem));\n",
    "    \n",
    "    def getIndividualStatementExecution(self, startTime, endTime, limit=100, includeSystem='false'):\n",
    "        return self.getRequest('/metrics/statements/evmon_activity?start='+str(startTime)+'&end='+str(endTime)+'&include_sys='+str(includeSystem)+'&offset=0&limit='+str(limit))\n",
    "\n",
    "    def getFiles(self, path):\n",
    "        return self.getRequest('/home'+path)\n",
    "    \n",
    "    def getUsers(self):\n",
    "        return self.getRequest('/users')\n",
    "    \n",
    "    def getTablesMetrics(self, startTime, endTime, includeSystem='false'):\n",
    "        return self.getRequest('/metrics/tables?start='+str(startTime)+'&end='+str(endTime)+'&include_sys='+str(includeSystem));\n",
    "\n",
    "    def getAverageResponseTime(self, startTime, endTime):\n",
    "        return self.getRequest('/metrics/average_response_time?start='+str(startTime)+'&end='+str(endTime));    \n",
    "    \n",
    "    def getUnitsOfWork(self, startTime, endTime):\n",
    "        return self.getRequest('/applications/uow?start='+str(startTime)+'&end='+str(endTime));    \n",
    "    \n",
    "    def getSchemaSize(self, startTime, endTime, tabSchema):\n",
    "        return self.getRequest('/metrics/storage/schemas/'+tabSchema+'/timeseries?start='+str(startTime)+'&end='+str(endTime));\n",
    "  \n",
    "    def getSearchViewList(self, searchtext, show_systems=\"false\"):\n",
    "        return self.getRequest('/admin/schemas/obj_type/view?search_name='+searchtext+'&show_systems='+str(show_systems)+'&rows_return=200');\n",
    "    \n",
    "    def getSearchTableList(self, searchtext):\n",
    "        return self.getRequest('/admin/schemas/obj_type/table?search_name='+searchtext+'&show_systems=true&rows_return=100');\n",
    "              \n",
    "    def getRowsRead(self, startTime, endTime):\n",
    "        return self.getRequest('/metrics/rows_read?start='+str(startTime)+'&end='+str(endTime));\n",
    "\n",
    "    def getResponseTime(self, startTime, endTime):\n",
    "        return self.getRequest('/metrics/response_time?start='+str(startTime)+'&end='+str(endTime));\n",
    "\n",
    "    def getStatementsCount(self, startTime, endTime):\n",
    "        return self.getRequest('/metrics/statements_count?start='+str(startTime)+'&end='+str(endTime));\n",
    "\n",
    "    def getPackageCacheStatement(self, startTime, endTime, show_systems='true'):\n",
    "        return self.getRequest('/metrics/statements/package_cache?start='+str(startTime)+'&end='+str(endTime)+'&show_systems='+str(show_systems))\n",
    "    \n",
    "    def postSearchObjects(self, obj_type, search_text, rows_return=100, show_systems='false', is_ascend='true'):     \n",
    "        json = {\"search_name\":search_text,\"rows_return\":rows_return,\"show_systems\":show_systems,\"obj_type\":obj_type,\"filters_match\":\"ALL\",\"filters\":[]}       \n",
    "        return self.postRequest('/admin/'+str(obj_type)+'s',json);\n",
    "                \n",
    "    def putFile(self, filename, path):\n",
    "        with open(filename, 'rb') as f:\n",
    "            r = requests.post(self.url+'/home_content/path', files={filename: f}, verify = self.verify, headers=self.headers, proxies = self.proxies)\n",
    "            \n",
    "    def getTablesInSchema(self, schema):\n",
    "        return self.getRequest('/schemas/'+str(schema)+'/tables');\n",
    "    \n",
    "    def getTableStorageBySchema(self):\n",
    "        return self.getRequest('/metrics/storage/schemas?end=0&include_sys=true&limit=1000&offset=0&start=0')\n",
    "    \n",
    "    def getCurrentPackageCacheList(self, show_systems=\"false\"):\n",
    "        return self.getRequest('/metrics/statements/package_cache/current/list?include_sys='+str(show_systems))\n",
    "    \n",
    "    def getProfile(self,profile):\n",
    "        return self.getRequest('/dbprofiles/'+profile)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Setup data frame set calculation functions\n",
    "def epochtotimeseries(epoch):\n",
    "    return time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(epoch/1000))\n",
    "def KBtoGB(kb):\n",
    "    return kb/1024/1024"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Setup time series calculation values\n",
    "import time\n",
    "from datetime import date\n",
    "endTime = int(time.time())\n",
    "endTime = endTime*1000\n",
    "startTime = endTime-(600*1000)\n",
    "oneWeek = 604800000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:100% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Increase the width of the notebook working spaces\n",
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Enterprise Console Local\n",
    "profile = \"Sample\"\n",
    "profileURL = \"?profile=\"+profile\n",
    "database = 'http://127.0.0.1:11090'\n",
    "databaseAPI = Db2(database+'/dbapi/v4')\n",
    "databaseAPI.authenticate(\"admin\", \"password\", profile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'name': 'Sample', 'HADR_Role': 'STANDARD', 'disableDataCollection': 'false', 'databaseVersion': '11.1.2', 'databaseName': 'SAMPLE', 'timeZone': '0', 'isHADR': 'false', 'DB2Instance': 'db2inst1', 'db2license': 'DEC', 'isInstPureScale': 'false', 'databaseVersion_VRMF': '11.1.2.2', 'sslConnection': 'false', 'userProfileRole': 'OWNER', 'timeZoneDiff': '-14400000', 'host': 'localhost', '_PROFILE_INIT_': 'true', 'dataServerType': 'DB2LUW', 'port': '50000', 'URL': 'jdbc:db2://localhost:50000/SAMPLE:retrieveMessagesFromServerOnGetMessage=true;', 'edition': 'DEC', 'isInstPartitionable': 'false', 'dataServerExternalType': 'DB2LUW', 'CF_NODE': '', 'capabilities': '[\"trial\"]', 'OSType': 'Linux'}\n"
     ]
    }
   ],
   "source": [
    "# List Monitoring Profile\n",
    "r = databaseAPI.getProfile(profile)\n",
    "json = databaseAPI.getJSON(r)\n",
    "print(json)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "definertype, name\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DB2INST1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DB2OE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DB2OSC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DSJOBMGR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DSSCHED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>DSSHSV1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>DSWEBSECURITY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>HEALTHMETRICS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>IBMIOCM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>IBMOTS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            name\n",
       "0       DB2INST1\n",
       "1          DB2OE\n",
       "2         DB2OSC\n",
       "3       DSJOBMGR\n",
       "4        DSSCHED\n",
       "5        DSSHSV1\n",
       "6  DSWEBSECURITY\n",
       "7  HEALTHMETRICS\n",
       "8        IBMIOCM\n",
       "9         IBMOTS"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# List all schema\n",
    "r = databaseAPI.getSchemas()\n",
    "if (databaseAPI.getStatusCode(r)==200):\n",
    "    json = databaseAPI.getJSON(r)\n",
    "    df = pd.DataFrame(json_normalize(json['resources']))\n",
    "    print(', '.join(list(df)))\n",
    "    display(df[['name']].head(10))\n",
    "else:\n",
    "    print(databaseAPI.getStatusCode(r))   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "IFrame(database+'/console/?mode=compact#monitor/summary'+profileURL, width=1400, height=1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Columns\n",
      "interval, rows_read_per_min, timestamp\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>timestamp</th>\n",
       "      <th>rows_read_per_min</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>2019-05-29 14:46:54</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>2019-05-29 14:51:54</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>2019-05-29 14:56:56</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>2019-05-29 15:01:55</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>2019-05-29 15:06:54</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>2019-05-29 15:11:55</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>2019-05-29 15:16:55</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>2019-05-29 15:21:55</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>2019-05-29 15:26:55</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>2019-05-29 15:31:57</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>2019-05-29 15:36:57</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>2019-05-29 15:41:57</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>2019-05-29 15:46:57</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>2019-05-29 15:51:57</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>2019-05-29 15:56:57</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>2019-05-29 16:01:56</td>\n",
       "      <td>215.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>2019-05-29 16:06:57</td>\n",
       "      <td>527.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>2019-05-29 16:11:58</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>2019-05-29 16:16:57</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165</th>\n",
       "      <td>2019-05-29 16:21:56</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               timestamp  rows_read_per_min\n",
       "146  2019-05-29 14:46:54                0.0\n",
       "147  2019-05-29 14:51:54                0.0\n",
       "148  2019-05-29 14:56:56                0.0\n",
       "149  2019-05-29 15:01:55                0.0\n",
       "150  2019-05-29 15:06:54                0.0\n",
       "151  2019-05-29 15:11:55                0.0\n",
       "152  2019-05-29 15:16:55                0.0\n",
       "153  2019-05-29 15:21:55                0.0\n",
       "154  2019-05-29 15:26:55                0.0\n",
       "155  2019-05-29 15:31:57                0.0\n",
       "156  2019-05-29 15:36:57                0.0\n",
       "157  2019-05-29 15:41:57                0.0\n",
       "158  2019-05-29 15:46:57                0.0\n",
       "159  2019-05-29 15:51:57                0.0\n",
       "160  2019-05-29 15:56:57                0.0\n",
       "161  2019-05-29 16:01:56              215.0\n",
       "162  2019-05-29 16:06:57              527.0\n",
       "163  2019-05-29 16:11:58                0.0\n",
       "164  2019-05-29 16:16:57                0.0\n",
       "165  2019-05-29 16:21:56               29.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Retrieve the number of rows read over the last week\n",
    "# List the last 10 data points\n",
    "# Graph the history\n",
    "lastWeek = endTime - oneWeek\n",
    "r = databaseAPI.getRowsRead(lastWeek, endTime)\n",
    "if (databaseAPI.getStatusCode(r)==200):\n",
    "    json = databaseAPI.getJSON(r)\n",
    "    if json['count'] > 0:\n",
    "        df = pd.DataFrame(json_normalize(json['timeseries']))\n",
    "        print('Columns')\n",
    "        print(', '.join(list(df)))\n",
    "        df['timestamp'] = df['timestamp'].apply(epochtotimeseries)\n",
    "        display(df[['timestamp','rows_read_per_min']].tail(20))\n",
    "        df.plot(x='timestamp',y='rows_read_per_min') \n",
    "    else: \n",
    "        print('No data returned')\n",
    "else:\n",
    "    print(databaseAPI.getStatusCode(r))\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "IFrame(database+'/console/?mode=compact#monitor/overview'+profileURL, width=1600, height=1200)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "IFrame(database+'/console/?mode=compact#sql/explain/e015be3b-2849-4ed4-ab9a-478c1cea38bc', width=1600, height=700)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "IFrame(database+'/console/?mode=compact#monitor/inflight_executions'+profileURL, width=1400, height=360)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Columns\n",
      "activity_id, activity_state, adm_bypassed, application_handle, application_name, client_ipaddr, effective_query_degree, entry_time, estimated_runtime_ms, exec_time_ms, idle_time_ms, max_estimated_shrheap_per_part_percent, max_shrheap_allocated_per_part_percent, max_shrheap_peak_per_part_percent, query_cost_estimate_timerons, queue_time_ms, rows_read, session_auth_id, stmt_text, uow_id\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>application_name</th>\n",
       "      <th>stmt_text</th>\n",
       "      <th>exec_time_ms</th>\n",
       "      <th>estimated_runtime_ms</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>UC_MYMON</td>\n",
       "      <td>/* IBM_DSSNAP */ WITH unicode_fix(x) AS(\\n\\t\\t...</td>\n",
       "      <td>0</td>\n",
       "      <td>5296740</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  application_name                                          stmt_text  \\\n",
       "0         UC_MYMON  /* IBM_DSSNAP */ WITH unicode_fix(x) AS(\\n\\t\\t...   \n",
       "\n",
       "   exec_time_ms  estimated_runtime_ms  \n",
       "0             0               5296740  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Retreive the current statements running now\n",
    "# Display the top 10 by execution time\n",
    "r = databaseAPI.getInflightCurrentList()\n",
    "if (databaseAPI.getStatusCode(r)==200):\n",
    "    json = databaseAPI.getJSON(r)\n",
    "    if json['count'] > 0:\n",
    "        df = pd.DataFrame(json_normalize(json['resources']))\n",
    "        print('Columns')\n",
    "        print(', '.join(list(df)))\n",
    "        df = df.sort_values(by='exec_time_ms', ascending=False)\n",
    "        display(df[['application_name','stmt_text','exec_time_ms','estimated_runtime_ms']].head(10))\n",
    "    else: \n",
    "        print('No data returned')\n",
    "else:\n",
    "    code = databaseAPI.getStatusCode(r)\n",
    "    databaseAPI.printResponse(r, code)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "IFrame(database+'/console/?mode=compact#monitor/individual'+profileURL, width=1400, height=480)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Columns\n",
      "activity_id, activity_type, address, agents_top, appl_id, appl_name, coord_partition_num, direct_reads, direct_writes, executable_id, fed_rows_deleted, fed_rows_inserted, fed_rows_read, fed_rows_updated, fed_wait_time, fed_waits_total, lock_wait_time, member, partial_record, prep_time, query_cost_estimate, rows_read, rows_read_per_returned, rows_returned, service_subclass_name, service_superclass_name, session_auth_id, sql_hash_id, sql_text, sqlcode, sqlwarn, stmt_exec_time, time_completed, time_started, total_act_wait_time, total_cpu_time, uow_id, wlm_queue_time_total, workloadname\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sql_text</th>\n",
       "      <th>total_cpu_time</th>\n",
       "      <th>wlm_queue_time_total</th>\n",
       "      <th>stmt_exec_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>select * from syscat.tables</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>select * from syscat.tables</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>select * from syscat.tables</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>select * from syscat.tables</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>select * from syscat.tables</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>select * from syscat.tables</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>select * from syscat.tables</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>select * from syscat.indexes</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>select * from syscat.indexes</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>select * from syscat.indexes</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        sql_text  total_cpu_time  wlm_queue_time_total  \\\n",
       "50   select * from syscat.tables              15                     0   \n",
       "20   select * from syscat.tables              14                     0   \n",
       "14   select * from syscat.tables              14                     0   \n",
       "22   select * from syscat.tables              13                     0   \n",
       "24   select * from syscat.tables              13                     0   \n",
       "16   select * from syscat.tables              13                     0   \n",
       "48   select * from syscat.tables              12                     0   \n",
       "23  select * from syscat.indexes               4                     0   \n",
       "19  select * from syscat.indexes               3                     0   \n",
       "15  select * from syscat.indexes               3                     0   \n",
       "\n",
       "    stmt_exec_time  \n",
       "50              31  \n",
       "20              26  \n",
       "14              21  \n",
       "22              18  \n",
       "24              20  \n",
       "16              37  \n",
       "48              19  \n",
       "23               5  \n",
       "19               6  \n",
       "15               5  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Retrieve the statements that ran over the last week.\n",
    "# Retrieve the first 10000 statements\n",
    "# Show the top ten by Total CPU TIME\n",
    "startTimeMinusWeek = endTime - oneWeek\n",
    "r = databaseAPI.getIndividualStatementExecution(startTimeMinusWeek, endTime, 10000)\n",
    "if (databaseAPI.getStatusCode(r)==200):\n",
    "    json = databaseAPI.getJSON(r)\n",
    "    if json['count'] > 0:     \n",
    "        df = pd.DataFrame(json_normalize(json['resources']))\n",
    "        print('Columns')\n",
    "        print(', '.join(list(df)))\n",
    "        df = df.sort_values(by='total_cpu_time', ascending=False)\n",
    "        display(df[['sql_text','total_cpu_time','wlm_queue_time_total','stmt_exec_time']].head(10))\n",
    "    else: \n",
    "        print('No data returned')  \n",
    "else:\n",
    "    code = databaseAPI.getStatusCode(r)\n",
    "    databaseAPI.printResponse(r, code)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "IFrame(database+'/console/?mode=compact#monitor/package_cache'+profileURL, width=1400, height=480)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Columns\n",
      "_per_min, availabePreviousTimestamp, calFieldsMap, col_vector_consumer_overflows, collected, coord_stmt_exec_time_ms, dbcollectTimestamp, dbconn_int, deltaDone, estimated_runtime_ms, estimated_sort_shrheap_top, ext_table_read_volume_kb, ext_table_recv_volume_kb, ext_table_send_volume_kb, ext_table_write_volume_kb, fed_rows_deleted, fed_rows_inserted, fed_rows_read, fed_rows_updated, fed_wait_time_ms, fed_waits_total, groupKeyOnMembers, hash_grpby_overflows, hash_join_overflows, interval, key, level_tag, lock_escals, lock_wait_time_ms, lock_waits, logical_reads, num_exec_with_metrics, olap_func_overflows, physical_reads, planid, pool_data_l_reads, pool_index_l_reads, pool_read_time_ms, pool_write_time_ms, pool_writes, post_threshold_col_vector_consumers, post_threshold_hash_grpbys, post_threshold_hash_joins, post_threshold_olap_funcs, post_threshold_sorts, prefetch_wait_time_ms, rows_modified, rows_read, rows_returned, schemaName, semantic_env_id, sort_overflows, sort_shrheap_top, sql_hash_id, startbatchcollected, stmt_exec_time_ms, stmt_text, stmt_type_id, stmtid, temp_reads, timestamp, total_act_wait_time_ms, total_cpu_time_ms\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>stmt_text</th>\n",
       "      <th>stmt_exec_time_ms</th>\n",
       "      <th>stmtid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>/* IBM_DSSNAP */ with unicode_fix(x) as (selec...</td>\n",
       "      <td>194</td>\n",
       "      <td>-2784068627434905255</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>/* IBM_DSSNAP */ set event monitor RTMON_EVMON...</td>\n",
       "      <td>9</td>\n",
       "      <td>7880021567290041286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>/* IBM_DSSNAP */ select sum(fs_total_size)/(CA...</td>\n",
       "      <td>5</td>\n",
       "      <td>2688556594859344219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>/* IBM_DSSNAP */ delete from (select CAST (? A...</td>\n",
       "      <td>3</td>\n",
       "      <td>-1293921616590792542</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>/* IBM_DSSNAP */ delete from (select CAST (? A...</td>\n",
       "      <td>3</td>\n",
       "      <td>507896686448705249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>/* IBM_DSSNAP */ WITH unicode_fix(x) AS ( SELE...</td>\n",
       "      <td>2</td>\n",
       "      <td>-2755369640262110092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/* IBM_DSSNAP */ select event_mon_state(evmonn...</td>\n",
       "      <td>0</td>\n",
       "      <td>9142607139278373844</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>/* IBM_DSSNAP */ select count(CAST (? AS INT))...</td>\n",
       "      <td>0</td>\n",
       "      <td>4151764324223964662</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>call SYSIBM.SQLCAMESSAGECCSID(?,?,?,?,?,?,?,?,...</td>\n",
       "      <td>0</td>\n",
       "      <td>8110287721056492873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>/* IBM_DSSNAP */ WITH UNICODE_FIX(X) AS ( SELE...</td>\n",
       "      <td>0</td>\n",
       "      <td>-3421969322199409333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            stmt_text  stmt_exec_time_ms  \\\n",
       "2   /* IBM_DSSNAP */ with unicode_fix(x) as (selec...                194   \n",
       "12  /* IBM_DSSNAP */ set event monitor RTMON_EVMON...                  9   \n",
       "13  /* IBM_DSSNAP */ select sum(fs_total_size)/(CA...                  5   \n",
       "4   /* IBM_DSSNAP */ delete from (select CAST (? A...                  3   \n",
       "9   /* IBM_DSSNAP */ delete from (select CAST (? A...                  3   \n",
       "6   /* IBM_DSSNAP */ WITH unicode_fix(x) AS ( SELE...                  2   \n",
       "0   /* IBM_DSSNAP */ select event_mon_state(evmonn...                  0   \n",
       "1   /* IBM_DSSNAP */ select count(CAST (? AS INT))...                  0   \n",
       "3   call SYSIBM.SQLCAMESSAGECCSID(?,?,?,?,?,?,?,?,...                  0   \n",
       "5   /* IBM_DSSNAP */ WITH UNICODE_FIX(X) AS ( SELE...                  0   \n",
       "\n",
       "                  stmtid  \n",
       "2   -2784068627434905255  \n",
       "12   7880021567290041286  \n",
       "13   2688556594859344219  \n",
       "4   -1293921616590792542  \n",
       "9     507896686448705249  \n",
       "6   -2755369640262110092  \n",
       "0    9142607139278373844  \n",
       "1    4151764324223964662  \n",
       "3    8110287721056492873  \n",
       "5   -3421969322199409333  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Retrieve the current package cache list \n",
    "# Show the first ten as sorted by the statement execution time\n",
    "r = databaseAPI.getCurrentPackageCacheList(\"true\")\n",
    "if (databaseAPI.getStatusCode(r)==200):\n",
    "    json = databaseAPI.getJSON(r)\n",
    "    if json['count'] > 0:  \n",
    "        df = pd.DataFrame(json_normalize(json['resources']))\n",
    "        print('Columns')\n",
    "        print(', '.join(list(df)))\n",
    "        df = df.sort_values(by='stmt_exec_time_ms', ascending=False)\n",
    "        display(df[['stmt_text','stmt_exec_time_ms','stmtid']].head(10))\n",
    "    else: \n",
    "        print('No data returned')  \n",
    "else:\n",
    "    print(databaseAPI.getStatusCode(r))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "IFrame(database+'/console/?mode=compact#monitor/locked_objects_with_waiting_connections'+profileURL, width=1400, height=480)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "IFrame(database+'/console/?mode=compact#monitor/connections'+profileURL, width=1400, height=480)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "act_aborted_total_per_min, act_completed_total_per_min, application_handle, application_name, client_ipaddr, client_userid, connection_start_time, ddl_sql_stmts_per_min, deadlocks_per_min, dynamic_sql_stmts_per_min, estimated_sort_shrheap_top_pages, failed_sql_stmts_per_min, idle_time_ms, max_estimated_shrheap_per_part_percent, max_shrheap_allocated_per_part_percent, max_shrheap_peak_per_part_percent, memory_pool_used_kb, other_stmts_per_min, rejected_rate, select_sql_stmts_per_min, service_superclass_name, session_auth_id, sort_shrheap_allocated_pages, sort_shrheap_top_pages, static_sql_stmts_per_min, thresh_violations_per_min, total_cpu_time_ms_per_min, uid_sql_stmts_per_min, uow_log_space_used_kb, workload_name, workload_occurrence_state\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>application_name</th>\n",
       "      <th>application_handle</th>\n",
       "      <th>connection_start_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>UC_MYMON</td>\n",
       "      <td>8143</td>\n",
       "      <td>2019-05-29 16:26:36</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  application_name  application_handle connection_start_time\n",
       "0         UC_MYMON                8143   2019-05-29 16:26:36"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Display the 10 most recently started Database Connections\n",
    "r = databaseAPI.getCurrentApplicationsConnections()\n",
    "if (databaseAPI.getStatusCode(r)==200):\n",
    "    json = databaseAPI.getJSON(r)\n",
    "    if json['count'] > 0: \n",
    "        df = pd.DataFrame(json_normalize(json['resources']))\n",
    "        print(', '.join(list(df)))\n",
    "        df = df.sort_values(by='connection_start_time', ascending=False)\n",
    "        df['connection_start_time'] = df['connection_start_time'].apply(epochtotimeseries)\n",
    "        display(df[['application_name','application_handle','connection_start_time']].head(10))\n",
    "    else: \n",
    "        print('No data returned')  \n",
    "else:\n",
    "    print(databaseAPI.getStatusCode(r))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "IFrame(database+'/console/?mode=compact#monitor/wlm_workload_summary', width=1400, height=480)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "IFrame(database+'/console/?mode=compact#monitor/storage', width=1400, height=480)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'timeseries': [], 'count': 0}\n",
      "No data returned\n"
     ]
    }
   ],
   "source": [
    "# Retrieve the storage size for the Historical Data Repostory stored in IBMCONSOLE schema.\n",
    "# If this doesn't run against the repository database you will get a zero result set\n",
    "# List the last 10 data points\n",
    "# Graph the history of the storage usage\n",
    "tabSchema = 'IBMCONSOLE'\n",
    "OneWeekStartTime = endTime - oneWeek\n",
    "r = databaseAPI.getSchemaSize(0, endTime, tabSchema)\n",
    "if (databaseAPI.getStatusCode(r)==200):\n",
    "    json = databaseAPI.getJSON(r)\n",
    "    print(json)\n",
    "    if json['count'] > 0: \n",
    "        df = pd.DataFrame(json_normalize(json['timeseries']))\n",
    "        print('Columns')\n",
    "        print(', '.join(list(df)))\n",
    "        df['total_physical_size_gb'] = df['total_physical_size_kb'].apply(KBtoGB)\n",
    "        df['timestamp'] = df['timestamp'].apply(epochtotimeseries)\n",
    "        display(df[['timestamp','total_physical_size_gb']].tail(10))\n",
    "        df.plot(x='timestamp',y='total_physical_size_gb')\n",
    "    else: \n",
    "        print('No data returned') \n",
    "else:\n",
    "    print(databaseAPI.getStatusCode(r))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "column_organized_data_logical_size_kb, column_organized_data_physical_size_kb, data_logical_size_kb, data_physical_size_kb, estimated_reclaim_size_kb, index_logical_size_kb, index_physical_size_kb, interval, lob_logical_size_kb, lob_physical_size_kb, long_logical_size_kb, long_physical_size_kb, tabschema, timestamp, total_logical_size_kb, total_physical_size_kb, xml_logical_size_kb, xml_physical_size_kb\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tabschema</th>\n",
       "      <th>space_mb</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>IBMIOCM</td>\n",
       "      <td>90.5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>39.1875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>IBM_RTMON_DATA</td>\n",
       "      <td>28.5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>DB2OSC</td>\n",
       "      <td>25.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>IBM_RTMON</td>\n",
       "      <td>22.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>DB2INST1</td>\n",
       "      <td>19.5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>SYSTOOLS</td>\n",
       "      <td>19.2500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>HEALTHMETRICS</td>\n",
       "      <td>14.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>IBMOTS</td>\n",
       "      <td>14.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>IBMPDQ</td>\n",
       "      <td>11.0000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         tabschema  space_mb\n",
       "17         IBMIOCM   90.5000\n",
       "0           SYSIBM   39.1875\n",
       "19  IBM_RTMON_DATA   28.5000\n",
       "12          DB2OSC   25.0000\n",
       "14       IBM_RTMON   22.0000\n",
       "15        DB2INST1   19.5000\n",
       "13        SYSTOOLS   19.2500\n",
       "18   HEALTHMETRICS   14.0000\n",
       "16          IBMOTS   14.0000\n",
       "6           IBMPDQ   11.0000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# List storage used by schema\n",
    "# Display the top ten schemas\n",
    "r = databaseAPI.getTableStorageBySchema()\n",
    "if (databaseAPI.getStatusCode(r)==200):\n",
    "    json = databaseAPI.getJSON(r)        \n",
    "    if json['count'] > 0: \n",
    "        df = pd.DataFrame(json_normalize(json['resources']))\n",
    "        print(', '.join(list(df)))\n",
    "        df['space_mb'] = df['data_physical_size_kb'].apply(lambda x: x / 1024)\n",
    "        df = df.sort_values(by='data_physical_size_kb', ascending=False)    \n",
    "        display(df[['tabschema','space_mb']].head(10))\n",
    "    else: \n",
    "        print('No data returned') \n",
    "else:\n",
    "    print(\"RC: \"+str(databaseAPI.getStatusCode(r)))    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# List any open alerts using part of the Console Interface\n",
    "IFrame(database+'/console/?mode=compact#monitor/notification'+profileURL, width=1400, height=480)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Explore tables in your database using part of the Console Interface\n",
    "IFrame(database+'/console/?mode=compact#explore/table'+profileURL, width=1400, height=480)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Columns:\n",
      "alter_time, create_time, optimize_query, owner, owner_type, read_only, sql, stats_time, valid, view_check, view_name, view_schema\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>view_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TABLE_CONSTRAINTS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TABLES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TABLES_S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EVENTTABLES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TABLES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>TABLESPACES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>TABLES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>SQLTABLEPRIVILEGES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>SQLTABLETYPES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>SQLTABLES</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            view_name\n",
       "0   TABLE_CONSTRAINTS\n",
       "1              TABLES\n",
       "2            TABLES_S\n",
       "3         EVENTTABLES\n",
       "4              TABLES\n",
       "5         TABLESPACES\n",
       "6              TABLES\n",
       "7  SQLTABLEPRIVILEGES\n",
       "8       SQLTABLETYPES\n",
       "9           SQLTABLES"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Search for tables across all schemas that match simple search critera \n",
    "# Display the first 100\n",
    "# Switch between searching tables or views\n",
    "object = 'view'\n",
    "#object = 'table'\n",
    "r = databaseAPI.postSearchObjects(object,\"TABLE\",10,'true','false')\n",
    "if (databaseAPI.getStatusCode(r)==200):\n",
    "    json = databaseAPI.getJSON(r)\n",
    "    df = pd.DataFrame(json_normalize(json))\n",
    "    print('Columns:')\n",
    "    print(', '.join(list(df)))\n",
    "    display(df[[object+'_name']].head(100))\n",
    "else:\n",
    "    print(\"RC: \"+str(databaseAPI.getStatusCode(r)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name, schema\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>schema</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>SYSATTRIBUTES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>SYSAUDITEXCEPTIONS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>SYSAUDITPOLICIES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>SYSAUDITUSE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>SYSBUFFERPOOLNODES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>SYSBUFFERPOOLS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>SYSCHECKS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>SYSCODEPROPERTIES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>SYSCOLAUTH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>SYSCOLCHECKS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     schema                name\n",
       "0  SYSIBM         SYSATTRIBUTES\n",
       "1  SYSIBM    SYSAUDITEXCEPTIONS\n",
       "2  SYSIBM      SYSAUDITPOLICIES\n",
       "3  SYSIBM           SYSAUDITUSE\n",
       "4  SYSIBM    SYSBUFFERPOOLNODES\n",
       "5  SYSIBM        SYSBUFFERPOOLS\n",
       "6  SYSIBM             SYSCHECKS\n",
       "7  SYSIBM     SYSCODEPROPERTIES\n",
       "8  SYSIBM            SYSCOLAUTH\n",
       "9  SYSIBM          SYSCOLCHECKS"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Find all the tables in the SYSIBM schema and display the first 10\n",
    "schema = 'SYSIBM'\n",
    "r = databaseAPI.getTablesInSchema(schema)\n",
    "if (databaseAPI.getStatusCode(r)==200):\n",
    "    json = databaseAPI.getJSON(r)\n",
    "    df = pd.DataFrame(json_normalize(json['resources']))\n",
    "    print(', '.join(list(df)))\n",
    "    display(df[['schema','name']].head(10))\n",
    "else:\n",
    "    print(databaseAPI.getStatusCode(r))   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'id': '1559161642136_2067242640', 'results': [], 'status': 'running'}\n"
     ]
    }
   ],
   "source": [
    "#Run an SQL Statement\n",
    "r = databaseAPI.runSQL(\"select * from syscat.tables\")\n",
    "if (databaseAPI.getStatusCode(r)==201):\n",
    "    jobid = databaseAPI.getJSON(r)['id']\n",
    "    r = databaseAPI.getSQLJobResult(jobid)\n",
    "    if (databaseAPI.getStatusCode(r)==200):\n",
    "        pprint(databaseAPI.getJSON(r))\n",
    "    else:\n",
    "        print(databaseAPI.getStatusCode(r))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'id': '1559161642136_2067242640', 'results': [{'exceed_size': 'yes', 'rows_count': 11, 'runtime_seconds': 0.019999999552965164, 'columns': ['TABSCHEMA', 'TABNAME', 'OWNER', 'OWNERTYPE', 'TYPE', 'STATUS', 'BASE_TABSCHEMA', 'BASE_TABNAME', 'ROWTYPESCHEMA', 'ROWTYPENAME', 'CREATE_TIME', 'ALTER_TIME', 'INVALIDATE_TIME', 'STATS_TIME', 'COLCOUNT', 'TABLEID', 'TBSPACEID', 'CARD', 'NPAGES', 'MPAGES', 'FPAGES', 'NPARTITIONS', 'NFILES', 'TABLESIZE', 'OVERFLOW', 'TBSPACE', 'INDEX_TBSPACE', 'LONG_TBSPACE', 'PARENTS', 'CHILDREN', 'SELFREFS', 'KEYCOLUMNS', 'KEYINDEXID', 'KEYUNIQUE', 'CHECKCOUNT', 'DATACAPTURE', 'CONST_CHECKED', 'PMAP_ID', 'PARTITION_MODE', 'LOG_ATTRIBUTE', 'PCTFREE', 'APPEND_MODE', 'REFRESH', 'REFRESH_TIME', 'LOCKSIZE', 'VOLATILE', 'ROW_FORMAT', 'PROPERTY', 'STATISTICS_PROFILE', 'COMPRESSION', 'ROWCOMPMODE', 'ACCESS_MODE', 'CLUSTERED', 'ACTIVE_BLOCKS', 'DROPRULE', 'MAXFREESPACESEARCH', 'AVGCOMPRESSEDROWSIZE', 'AVGROWCOMPRESSIONRATIO', 'AVGROWSIZE', 'PCTROWSCOMPRESSED', 'LOGINDEXBUILD', 'CODEPAGE', 'COLLATIONSCHEMA', 'COLLATIONNAME', 'COLLATIONSCHEMA_ORDERBY', 'COLLATIONNAME_ORDERBY', 'ENCODING_SCHEME', 'PCTPAGESSAVED', 'LAST_REGEN_TIME', 'SECPOLICYID', 'PROTECTIONGRANULARITY', 'AUDITPOLICYID', 'AUDITPOLICYNAME', 'AUDITEXCEPTIONENABLED', 'DEFINER', 'ONCOMMIT', 'LOGGED', 'ONROLLBACK', 'LASTUSED', 'CONTROL', 'TEMPORALTYPE', 'TABLEORG', 'EXTENDED_ROW_SIZE', 'PCTEXTENDEDROWS', 'REMARKS'], 'columns_type': ['string', 'string', 'string', 'string', 'string', 'string', 'string', 'string', 'string', 'string', 'time', 'time', 'time', 'time', 'number', 'number', 'number', 'number', 'number', 'number', 'number', 'number', 'number', 'number', 'number', 'string', 'string', 'string', 'number', 'number', 'number', 'number', 'number', 'number', 'number', 'string', 'string', 'number', 'string', 'string', 'number', 'string', 'string', 'time', 'string', 'string', 'string', 'string', 'string', 'string', 'string', 'string', 'string', 'number', 'string', 'number', 'number', 'number', 'number', 'number', 'string', 'number', 'string', 'string', 'string', 'string', 'string', 'number', 'time', 'number', 'string', 'number', 'string', 'string', 'string', 'string', 'string', 'string', 'date', 'string', 'string', 'string', 'string', 'number', 'string'], 'limit': 10, 'index': 0, 'rows': [['SYSIBM  ', 'SYSTABLES', 'SYSIBM  ', 'S', 'T', 'N', '', '', '', '', '2017-06-28 14:39:30.081618', '2017-06-28 14:39:30.081618', '2017-06-28 14:39:30.081618', '2019-02-11 12:56:37.894711', '83', '5', '0', '1052', '73', '0', '73', '-1', '-1', '-1', '23', 'SYSCATSPACE', '', '', '0', '0', '0', '0', '0', '0', '0', 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', '0', ' ', '0', '-1', 'N', ' ', '', 'R', ' ', 'N', '                                ', '', 'N', ' ', 'F', '', '0', '�', '999', '0', '0.0', '541', '0.0', '', '1208', 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', '0', '2017-06-28 14:39:30.081618', '0', ' ', '', '', 'N', 'SYSIBM  ', ' ', ' ', ' ', '2018-07-05', ' ', 'N', 'R', 'N', '-1.0', ''], ['SYSIBM  ', 'SYSCOLUMNS', 'SYSIBM  ', 'S', 'T', 'N', '', '', '', '', '2017-06-28 14:39:30.081618', '2017-06-28 14:39:30.081618', '2017-06-28 14:39:30.081618', '2018-01-20 18:49:30.417142', '45', '6', '0', '15272', '467', '0', '467', '-1', '-1', '-1', '68', 'SYSCATSPACE', '', '', '0', '0', '0', '0', '0', '0', '0', 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', '0', ' ', '0', '-1', 'N', ' ', '', 'R', ' ', 'N', '                                ', '', 'N', ' ', 'F', '', '0', '�', '999', '0', '0.0', '235', '0.0', '', '1208', 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', '0', '2017-06-28 14:39:30.081618', '0', ' ', '', '', 'N', 'SYSIBM  ', ' ', ' ', ' ', '2018-07-05', ' ', 'N', 'R', 'N', '-1.0', ''], ['SYSIBM  ', 'SYSINDEXES', 'SYSIBM  ', 'S', 'T', 'N', '', '', '', '', '2017-06-28 14:39:30.081618', '2017-06-28 14:39:30.081618', '2017-06-28 14:39:30.081618', '2017-10-05 02:09:16.757295', '71', '7', '0', '1163', '62', '0', '62', '-1', '-1', '-1', '8', 'SYSCATSPACE', '', '', '0', '0', '0', '0', '0', '0', '0', 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', '0', ' ', '0', '-1', 'N', ' ', '', 'R', ' ', 'N', '                                ', '', 'N', ' ', 'F', '', '0', '�', '999', '0', '0.0', '416', '0.0', '', '1208', 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', '0', '2017-06-28 14:39:30.081618', '0', ' ', '', '', 'N', 'SYSIBM  ', ' ', ' ', ' ', '2018-07-05', ' ', 'N', 'R', 'N', '-1.0', ''], ['SYSIBM  ', 'SYSVIEWS', 'SYSIBM  ', 'S', 'T', 'N', '', '', '', '', '2017-06-28 14:39:30.081618', '2017-06-28 14:39:30.081618', '2017-06-28 14:39:30.081618', '2017-06-28 18:50:56.011932', '12', '11', '0', '308', '8', '0', '8', '-1', '-1', '-1', '0', 'SYSCATSPACE', '', '', '0', '0', '0', '0', '0', '0', '0', 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', '0', ' ', '0', '-1', 'N', ' ', '', 'R', ' ', 'N', '                                ', '', 'N', ' ', 'F', '', '0', '�', '999', '0', '0.0', '188', '0.0', '', '1208', 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', '0', '2017-06-28 14:39:30.081618', '0', ' ', '', '', 'N', 'SYSIBM  ', ' ', ' ', ' ', '2017-09-06', ' ', 'N', 'R', 'N', '-1.0', ''], ['SYSIBM  ', 'SYSVIEWDEP', 'SYSIBM  ', 'S', 'T', 'N', '', '', '', '', '2017-06-28 14:39:30.081618', '2017-06-28 14:39:30.081618', '2017-06-28 14:39:30.081618', '2017-09-29 09:17:01.868603', '11', '12', '0', '789', '10', '0', '10', '-1', '-1', '-1', '0', 'SYSCATSPACE', '', '', '0', '0', '0', '0', '0', '0', '0', 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', '0', ' ', '0', '-1', 'N', ' ', '', 'R', ' ', 'N', '                                ', '', 'N', ' ', 'F', '', '0', '�', '999', '0', '0.0', '94', '0.0', '', '1208', 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', '0', '2017-06-28 14:39:30.081618', '0', ' ', '', '', 'N', 'SYSIBM  ', ' ', ' ', ' ', '2018-05-01', ' ', 'N', 'R', 'N', '-1.0', ''], ['SYSIBM  ', 'SYSPLAN', 'SYSIBM  ', 'S', 'T', 'N', '', '', '', '', '2017-06-28 14:39:30.081618', '2017-06-28 14:39:30.081618', '2017-06-28 14:39:30.081618', '2017-09-13 20:21:41.546693', '69', '13', '0', '335', '23', '0', '23', '-1', '-1', '-1', '0', 'SYSCATSPACE', '', '', '0', '0', '0', '0', '0', '0', '0', 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', '0', ' ', '0', '-1', 'N', ' ', '', 'R', ' ', 'N', '                                ', '', 'N', ' ', 'F', '', '0', '�', '999', '0', '0.0', '531', '0.0', '', '1208', 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', '0', '2017-06-28 14:39:30.081618', '0', ' ', '', '', 'N', 'SYSIBM  ', ' ', ' ', ' ', '2018-07-05', ' ', 'N', 'R', 'N', '-1.0', ''], ['SYSIBM  ', 'SYSPLANDEP', 'SYSIBM  ', 'S', 'T', 'N', '', '', '', '', '2017-06-28 14:39:30.081618', '2017-06-28 14:39:30.081618', '2017-06-28 14:39:30.081618', '2017-07-05 17:30:48.184288', '12', '14', '0', '1609', '28', '0', '28', '-1', '-1', '-1', '1', 'SYSCATSPACE', '', '', '0', '0', '0', '0', '0', '0', '0', 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', '0', ' ', '0', '-1', 'N', ' ', '', 'R', ' ', 'N', '                                ', '', 'N', ' ', 'F', '', '0', '�', '999', '0', '0.0', '105', '0.0', '', '1208', 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', '0', '2017-06-28 14:39:30.081618', '0', ' ', '', '', 'N', 'SYSIBM  ', ' ', ' ', ' ', '2018-05-01', ' ', 'N', 'R', 'N', '-1.0', ''], ['SYSIBM  ', 'SYSSECTION', 'SYSIBM  ', 'S', 'T', 'N', '', '', '', '', '2017-06-28 14:39:30.081618', '2017-06-28 14:39:30.081618', '2017-06-28 14:39:30.081618', '2017-09-20 12:08:46.56695', '7', '15', '0', '2187', '32', '0', '32', '-1', '-1', '-1', '0', 'SYSCATSPACE', '', '', '0', '0', '0', '0', '0', '0', '0', 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', '0', ' ', '0', '-1', 'N', ' ', '', 'R', ' ', 'N', '                                ', '', 'N', ' ', 'F', '', '0', '�', '999', '0', '0.0', '116', '0.0', '', '1208', 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', '0', '2017-06-28 14:39:30.081618', '0', ' ', '', '', 'N', 'SYSIBM  ', ' ', ' ', ' ', '2018-05-01', ' ', 'N', 'R', 'N', '-1.0', ''], ['SYSIBM  ', 'SYSSTMT', 'SYSIBM  ', 'S', 'T', 'N', '', '', '', '', '2017-06-28 14:39:30.081618', '2017-06-28 14:39:30.081618', '2017-06-28 14:39:30.081618', '', '7', '16', '0', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', 'SYSCATSPACE', '', '', '0', '0', '0', '0', '0', '0', '0', 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', '0', ' ', '0', '-1', 'N', ' ', '', 'R', ' ', 'N', '                                ', '', 'N', ' ', 'F', '', '-1', '�', '999', '-1', '-1.0', '-1', '-1.0', '', '1208', 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', '-1', '2017-06-28 14:39:30.081618', '0', ' ', '', '', 'N', 'SYSIBM  ', ' ', ' ', ' ', '2018-05-01', ' ', 'N', 'R', 'N', '-1.0', ''], ['SYSIBM  ', 'SYSDBAUTH', 'SYSIBM  ', 'S', 'T', 'N', '', '', '', '', '2017-06-28 14:39:30.081618', '2017-06-28 14:39:30.081618', '2017-06-28 14:39:30.081618', '2017-09-14 23:27:01.081343', '27', '17', '0', '2', '1', '0', '1', '-1', '-1', '-1', '0', 'SYSCATSPACE', '', '', '0', '0', '0', '0', '0', '0', '0', 'N', 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY', '0', ' ', '0', '-1', 'N', ' ', '', 'R', ' ', 'N', '                                ', '', 'N', ' ', 'F', '', '0', '�', '999', '0', '0.0', '67', '0.0', '', '1208', 'SYSIBM', 'IDENTITY', 'SYSIBM', 'IDENTITY', ' ', '0', '2017-06-28 14:39:30.081618', '0', ' ', '', '', 'N', 'SYSIBM  ', ' ', ' ', ' ', '2018-07-05', ' ', 'N', 'R', 'N', '-1.0', '']], 'command': 'select * from syscat.tables'}], 'status': 'completed'}\n"
     ]
    }
   ],
   "source": [
    "#If the results don't come back immediately then try again\n",
    "r = databaseAPI.getSQLJobResult(jobid)\n",
    "if (databaseAPI.getStatusCode(r)==200):\n",
    "    json = databaseAPI.getJSON(r)\n",
    "    print(json)\n",
    "else:\n",
    "    print(databaseAPI.getStatusCode(r))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  }
 ],
 "metadata": {
  "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"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}