Db2 Magic Command with Pandas Data Frame.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1>Db2 Magic Query into Pandas Data Frame</h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p><strong>Welcome!</strong> This notebook will provide examples of using Db2 Magic Commands and converting the out put to a Pandas Data Frame.  Many of our data science examples use data in a Pandas data frame, so I thogut that this example would be useful since the magic commands put the output of a query into a LIST data type.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2 id=\"dataset\">About Db2 Magic Commands</h2>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Db2 Magic commands make it easy to run SQL in cells in your Jupyter Notebook as is without any Python code.  This notebook shows how to enable this feature in Cloud Pak for Data.  Using these examples would probably allow you to use them in Watson Studio outside of CPD as well.  I only show some basic Magic Commands here.  For more information on Db2 Magic Commands and lots of example see https://github.com/IBM/db2-jupyter/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2 id=\"pandas\">Get the db2 magic commands notebook from github: <code>db2.ipynb</code></h2>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You must get and run the db2.iyndb notebook before using the magic commands.  You can't run the db2.ipynb notebook from the directory shown above.  Instead you need to reference the \"raw\" version as is done in the command below.  I got that raw URL by going into directory shown in the above \"About\" description, then clicking on the db2.ipynb link, and then clicking the \"Raw\" button near that top of the notebook itself.  Finally I copied the URL in the browser and pasted it in the wget command.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--2020-03-02 16:30:39--  https://raw.githubusercontent.com/IBM/db2-jupyter/master/db2.ipynb\n",
      "Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 199.232.32.133\n",
      "Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|199.232.32.133|:443... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 151979 (148K) [text/plain]\n",
      "Saving to: 'db2.ipynb.1'\n",
      "\n",
      "100%[======================================>] 151,979     --.-K/s   in 0.03s   \n",
      "\n",
      "2020-03-02 16:30:39 (4.52 MB/s) - 'db2.ipynb.1' saved [151979/151979]\n",
      "\n"
     ]
    }
   ],
   "source": [
    "!wget https://raw.githubusercontent.com/IBM/db2-jupyter/master/db2.ipynb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Run the db2 magic comands notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Db2 Extensions Loaded.\n"
     ]
    }
   ],
   "source": [
    "%run db2.ipynb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to Db2 Database\n",
    "Change the connect string to use your own database, user, password, host and port"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connection successful.\n"
     ]
    }
   ],
   "source": [
    "%sql CONNECT TO bludb USER user1013 USING ppppppp HOST host.or.ip PORT 50000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Run a query, putting the output into a list called db2indexes_list\n",
    "Query the syscat.indexes view in the Db2 database.  Any view or table could be queried this way.  The magic command puts the data into a list datatype.  The row count, column count, data type and the view data are displayed.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ROW COUNT 743\n",
      "COLUMN COUNT 72\n",
      "<class 'list'>\n",
      "[['INDSCHEMA', 'INDNAME', 'OWNER', 'OWNERTYPE', 'TABSCHEMA', 'TABNAME', 'COLNAMES', 'UNIQUERULE', 'MADE_UNIQUE', 'COLCOUNT', 'UNIQUE_COLCOUNT', 'INDEXTYPE', 'ENTRYTYPE', 'PCTFREE', 'IID', 'NLEAF', 'NLEVELS', 'FIRSTKEYCARD', 'FIRST2KEYCARD', 'FIRST3KEYCARD', 'FIRST4KEYCARD', 'FULLKEYCARD', 'CLUSTERRATIO', 'CLUSTERFACTOR', 'SEQUENTIAL_PAGES', 'DENSITY', 'USER_DEFINED', 'SYSTEM_REQUIRED', 'CREATE_TIME', 'STATS_TIME', 'PAGE_FETCH_PAIRS', 'MINPCTUSED', 'REVERSE_SCANS', 'INTERNAL_FORMAT', 'COMPRESSION', 'IESCHEMA', 'IENAME', 'IEARGUMENTS', 'INDEX_OBJECTID', 'NUMRIDS', 'NUMRIDS_DELETED', 'NUM_EMPTY_LEAFS', 'AVERAGE_RANDOM_FETCH_PAGES', 'AVERAGE_RANDOM_PAGES', 'AVERAGE_SEQUENCE_GAP', 'AVERAGE_SEQUENCE_FETCH_GAP', 'AVERAGE_SEQUENCE_PAGES', 'AVERAGE_SEQUENCE_FETCH_PAGES', 'TBSPACEID', 'LEVEL2PCTFREE', 'PAGESPLIT', 'AVGPARTITION_CLUSTERRATIO', 'AVGPARTITION_CLUSTERFACTOR', 'AVGPARTITION_PAGE_FETCH_PAIRS', 'PCTPAGESSAVED', 'DATAPARTITION_CLUSTERFACTOR', 'INDCARD', 'AVGLEAFKEYSIZE', 'AVGNLEAFKEYSIZE', 'OS_PTR_SIZE', 'COLLECTSTATISTCS', 'DEFINER', 'LASTUSED', 'PERIODNAME', 'PERIODPOLICY', 'MADE_WITHOUTOVERLAPS', 'ENVSTRINGUNITS', 'NULLKEYS', 'FUNC_PATH', 'VIEWSCHEMA', 'VIEWNAME', 'REMARKS'], ['SYSIBM  ', 'INDTABLES01', 'SYSIBM  ', 'S', 'SYSIBM  ', 'SYSTABLES', '+CREATOR+NAME', 'U', 'N', 2, 2, 'REG ', ' ', -1, 1, 2, 2, 17, 775, -1, -1, 775, 95, -1.0, 0, 0, 0, 0, '2019-11-14 19:11:20.907790', '2020-03-02 03:16:17.858709', '', 0, 'N', 2, 'N', None, None, None, 5, 804, 29, 0, -1.0, 2.0, 0.0, -1.0, 0.0, -1.0, 0, -1, 'S', -1, -1.0, '', 0, 1.0, 775, 25, 25, 64, ' ', 'SYSIBM  ', '2020-03-02', None, 'N', 'N', 'SYSTEM', 'Y', None, None, None, None], ['SYSIBM  ', 'INDTABLES02', 'SYSIBM  ', 'S', 'SYSIBM  ', 'SYSTABLES', '+TBSPACE', 'D', 'N', 1, -1, 'REG ', ' ', -1, 2, 1, 1, 6, -1, -1, -1, 6, 98, -1.0, 0, 0, 0, 0, '2019-11-14 19:11:20.907790', '2020-03-02 03:16:17.858709', '', 0, 'N', 2, 'N', None, None, None, 5, 852, 77, 0, -1.0, 1.0, 0.0, -1.0, 0.0, -1.0, 0, -1, 'S', -1, -1.0, '', 0, 1.0, 775, 8, 8, 64, ' ', 'SYSIBM  ', '0001-01-01', None, 'N', 'N', 'SYSTEM', 'Y', None, None, None, None]]\n"
     ]
    }
   ],
   "source": [
    "db2indexs_list = %sql -r select * from syscat.indexes \n",
    "nbr_cols = len(db2indexs_list) - 1\n",
    "print('ROW COUNT', len(db2indexs_list))\n",
    "print('COLUMN COUNT',len(db2indexs_list[0]))\n",
    "print(type(db2indexs_list))\n",
    "print(db2indexs_list[0:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Copy thge list to a data frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(743, 72)\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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "      <th>13</th>\n",
       "      <th>14</th>\n",
       "      <th>15</th>\n",
       "      <th>16</th>\n",
       "      <th>17</th>\n",
       "      <th>18</th>\n",
       "      <th>19</th>\n",
       "      <th>20</th>\n",
       "      <th>21</th>\n",
       "      <th>22</th>\n",
       "      <th>23</th>\n",
       "      <th>24</th>\n",
       "      <th>25</th>\n",
       "      <th>26</th>\n",
       "      <th>27</th>\n",
       "      <th>28</th>\n",
       "      <th>29</th>\n",
       "      <th>30</th>\n",
       "      <th>31</th>\n",
       "      <th>32</th>\n",
       "      <th>33</th>\n",
       "      <th>34</th>\n",
       "      <th>35</th>\n",
       "      <th>36</th>\n",
       "      <th>37</th>\n",
       "      <th>38</th>\n",
       "      <th>39</th>\n",
       "      <th>40</th>\n",
       "      <th>41</th>\n",
       "      <th>42</th>\n",
       "      <th>43</th>\n",
       "      <th>44</th>\n",
       "      <th>45</th>\n",
       "      <th>46</th>\n",
       "      <th>47</th>\n",
       "      <th>48</th>\n",
       "      <th>49</th>\n",
       "      <th>50</th>\n",
       "      <th>51</th>\n",
       "      <th>52</th>\n",
       "      <th>53</th>\n",
       "      <th>54</th>\n",
       "      <th>55</th>\n",
       "      <th>56</th>\n",
       "      <th>57</th>\n",
       "      <th>58</th>\n",
       "      <th>59</th>\n",
       "      <th>60</th>\n",
       "      <th>61</th>\n",
       "      <th>62</th>\n",
       "      <th>63</th>\n",
       "      <th>64</th>\n",
       "      <th>65</th>\n",
       "      <th>66</th>\n",
       "      <th>67</th>\n",
       "      <th>68</th>\n",
       "      <th>69</th>\n",
       "      <th>70</th>\n",
       "      <th>71</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>INDSCHEMA</td>\n",
       "      <td>INDNAME</td>\n",
       "      <td>OWNER</td>\n",
       "      <td>OWNERTYPE</td>\n",
       "      <td>TABSCHEMA</td>\n",
       "      <td>TABNAME</td>\n",
       "      <td>COLNAMES</td>\n",
       "      <td>UNIQUERULE</td>\n",
       "      <td>MADE_UNIQUE</td>\n",
       "      <td>COLCOUNT</td>\n",
       "      <td>UNIQUE_COLCOUNT</td>\n",
       "      <td>INDEXTYPE</td>\n",
       "      <td>ENTRYTYPE</td>\n",
       "      <td>PCTFREE</td>\n",
       "      <td>IID</td>\n",
       "      <td>NLEAF</td>\n",
       "      <td>NLEVELS</td>\n",
       "      <td>FIRSTKEYCARD</td>\n",
       "      <td>FIRST2KEYCARD</td>\n",
       "      <td>FIRST3KEYCARD</td>\n",
       "      <td>FIRST4KEYCARD</td>\n",
       "      <td>FULLKEYCARD</td>\n",
       "      <td>CLUSTERRATIO</td>\n",
       "      <td>CLUSTERFACTOR</td>\n",
       "      <td>SEQUENTIAL_PAGES</td>\n",
       "      <td>DENSITY</td>\n",
       "      <td>USER_DEFINED</td>\n",
       "      <td>SYSTEM_REQUIRED</td>\n",
       "      <td>CREATE_TIME</td>\n",
       "      <td>STATS_TIME</td>\n",
       "      <td>PAGE_FETCH_PAIRS</td>\n",
       "      <td>MINPCTUSED</td>\n",
       "      <td>REVERSE_SCANS</td>\n",
       "      <td>INTERNAL_FORMAT</td>\n",
       "      <td>COMPRESSION</td>\n",
       "      <td>IESCHEMA</td>\n",
       "      <td>IENAME</td>\n",
       "      <td>IEARGUMENTS</td>\n",
       "      <td>INDEX_OBJECTID</td>\n",
       "      <td>NUMRIDS</td>\n",
       "      <td>NUMRIDS_DELETED</td>\n",
       "      <td>NUM_EMPTY_LEAFS</td>\n",
       "      <td>AVERAGE_RANDOM_FETCH_PAGES</td>\n",
       "      <td>AVERAGE_RANDOM_PAGES</td>\n",
       "      <td>AVERAGE_SEQUENCE_GAP</td>\n",
       "      <td>AVERAGE_SEQUENCE_FETCH_GAP</td>\n",
       "      <td>AVERAGE_SEQUENCE_PAGES</td>\n",
       "      <td>AVERAGE_SEQUENCE_FETCH_PAGES</td>\n",
       "      <td>TBSPACEID</td>\n",
       "      <td>LEVEL2PCTFREE</td>\n",
       "      <td>PAGESPLIT</td>\n",
       "      <td>AVGPARTITION_CLUSTERRATIO</td>\n",
       "      <td>AVGPARTITION_CLUSTERFACTOR</td>\n",
       "      <td>AVGPARTITION_PAGE_FETCH_PAIRS</td>\n",
       "      <td>PCTPAGESSAVED</td>\n",
       "      <td>DATAPARTITION_CLUSTERFACTOR</td>\n",
       "      <td>INDCARD</td>\n",
       "      <td>AVGLEAFKEYSIZE</td>\n",
       "      <td>AVGNLEAFKEYSIZE</td>\n",
       "      <td>OS_PTR_SIZE</td>\n",
       "      <td>COLLECTSTATISTCS</td>\n",
       "      <td>DEFINER</td>\n",
       "      <td>LASTUSED</td>\n",
       "      <td>PERIODNAME</td>\n",
       "      <td>PERIODPOLICY</td>\n",
       "      <td>MADE_WITHOUTOVERLAPS</td>\n",
       "      <td>ENVSTRINGUNITS</td>\n",
       "      <td>NULLKEYS</td>\n",
       "      <td>FUNC_PATH</td>\n",
       "      <td>VIEWSCHEMA</td>\n",
       "      <td>VIEWNAME</td>\n",
       "      <td>REMARKS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>INDTABLES01</td>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>S</td>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>SYSTABLES</td>\n",
       "      <td>+CREATOR+NAME</td>\n",
       "      <td>U</td>\n",
       "      <td>N</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>REG</td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>17</td>\n",
       "      <td>775</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>775</td>\n",
       "      <td>95</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2019-11-14 19:11:20.907790</td>\n",
       "      <td>2020-03-02 03:16:17.858709</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>2</td>\n",
       "      <td>N</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>5</td>\n",
       "      <td>804</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>S</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>775</td>\n",
       "      <td>25</td>\n",
       "      <td>25</td>\n",
       "      <td>64</td>\n",
       "      <td></td>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>None</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>SYSTEM</td>\n",
       "      <td>Y</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>INDTABLES02</td>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>S</td>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>SYSTABLES</td>\n",
       "      <td>+TBSPACE</td>\n",
       "      <td>D</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>REG</td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>6</td>\n",
       "      <td>98</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2019-11-14 19:11:20.907790</td>\n",
       "      <td>2020-03-02 03:16:17.858709</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>2</td>\n",
       "      <td>N</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>5</td>\n",
       "      <td>852</td>\n",
       "      <td>77</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>S</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>775</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>64</td>\n",
       "      <td></td>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>None</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>SYSTEM</td>\n",
       "      <td>Y</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>INDTABLES03</td>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>S</td>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>SYSTABLES</td>\n",
       "      <td>+INDEX_TBSPACE</td>\n",
       "      <td>D</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>REG</td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "      <td>100</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2019-11-14 19:11:20.907790</td>\n",
       "      <td>2020-03-02 03:16:17.858709</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>2</td>\n",
       "      <td>N</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>5</td>\n",
       "      <td>852</td>\n",
       "      <td>77</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>S</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>775</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>64</td>\n",
       "      <td></td>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>None</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>SYSTEM</td>\n",
       "      <td>Y</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>INDTABLES04</td>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>S</td>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>SYSTABLES</td>\n",
       "      <td>+LONG_TBSPACE</td>\n",
       "      <td>D</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>REG</td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "      <td>100</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2019-11-14 19:11:20.907790</td>\n",
       "      <td>2020-03-02 03:16:17.858709</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>2</td>\n",
       "      <td>N</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>5</td>\n",
       "      <td>852</td>\n",
       "      <td>77</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>S</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>775</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>64</td>\n",
       "      <td></td>\n",
       "      <td>SYSIBM</td>\n",
       "      <td>0001-01-01</td>\n",
       "      <td>None</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>SYSTEM</td>\n",
       "      <td>Y</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          0            1         2          3          4          5   \\\n",
       "0  INDSCHEMA      INDNAME     OWNER  OWNERTYPE  TABSCHEMA    TABNAME   \n",
       "1   SYSIBM    INDTABLES01  SYSIBM            S   SYSIBM    SYSTABLES   \n",
       "2   SYSIBM    INDTABLES02  SYSIBM            S   SYSIBM    SYSTABLES   \n",
       "3   SYSIBM    INDTABLES03  SYSIBM            S   SYSIBM    SYSTABLES   \n",
       "4   SYSIBM    INDTABLES04  SYSIBM            S   SYSIBM    SYSTABLES   \n",
       "\n",
       "               6           7            8         9                10  \\\n",
       "0        COLNAMES  UNIQUERULE  MADE_UNIQUE  COLCOUNT  UNIQUE_COLCOUNT   \n",
       "1   +CREATOR+NAME           U            N         2                2   \n",
       "2        +TBSPACE           D            N         1               -1   \n",
       "3  +INDEX_TBSPACE           D            N         1               -1   \n",
       "4   +LONG_TBSPACE           D            N         1               -1   \n",
       "\n",
       "          11         12       13   14     15       16            17  \\\n",
       "0  INDEXTYPE  ENTRYTYPE  PCTFREE  IID  NLEAF  NLEVELS  FIRSTKEYCARD   \n",
       "1       REG                   -1    1      2        2            17   \n",
       "2       REG                   -1    2      1        1             6   \n",
       "3       REG                   -1    3      1        1             1   \n",
       "4       REG                   -1    4      1        1             1   \n",
       "\n",
       "              18             19             20           21            22  \\\n",
       "0  FIRST2KEYCARD  FIRST3KEYCARD  FIRST4KEYCARD  FULLKEYCARD  CLUSTERRATIO   \n",
       "1            775             -1             -1          775            95   \n",
       "2             -1             -1             -1            6            98   \n",
       "3             -1             -1             -1            1           100   \n",
       "4             -1             -1             -1            1           100   \n",
       "\n",
       "              23                24       25            26               27  \\\n",
       "0  CLUSTERFACTOR  SEQUENTIAL_PAGES  DENSITY  USER_DEFINED  SYSTEM_REQUIRED   \n",
       "1             -1                 0        0             0                0   \n",
       "2             -1                 0        0             0                0   \n",
       "3             -1                 0        0             0                0   \n",
       "4             -1                 0        0             0                0   \n",
       "\n",
       "                           28                          29                30  \\\n",
       "0                 CREATE_TIME                  STATS_TIME  PAGE_FETCH_PAIRS   \n",
       "1  2019-11-14 19:11:20.907790  2020-03-02 03:16:17.858709                     \n",
       "2  2019-11-14 19:11:20.907790  2020-03-02 03:16:17.858709                     \n",
       "3  2019-11-14 19:11:20.907790  2020-03-02 03:16:17.858709                     \n",
       "4  2019-11-14 19:11:20.907790  2020-03-02 03:16:17.858709                     \n",
       "\n",
       "           31             32               33           34        35      36  \\\n",
       "0  MINPCTUSED  REVERSE_SCANS  INTERNAL_FORMAT  COMPRESSION  IESCHEMA  IENAME   \n",
       "1           0              N                2            N      None    None   \n",
       "2           0              N                2            N      None    None   \n",
       "3           0              N                2            N      None    None   \n",
       "4           0              N                2            N      None    None   \n",
       "\n",
       "            37              38       39               40               41  \\\n",
       "0  IEARGUMENTS  INDEX_OBJECTID  NUMRIDS  NUMRIDS_DELETED  NUM_EMPTY_LEAFS   \n",
       "1         None               5      804               29                0   \n",
       "2         None               5      852               77                0   \n",
       "3         None               5      852               77                0   \n",
       "4         None               5      852               77                0   \n",
       "\n",
       "                           42                    43                    44  \\\n",
       "0  AVERAGE_RANDOM_FETCH_PAGES  AVERAGE_RANDOM_PAGES  AVERAGE_SEQUENCE_GAP   \n",
       "1                          -1                     2                     0   \n",
       "2                          -1                     1                     0   \n",
       "3                          -1                     1                     0   \n",
       "4                          -1                     1                     0   \n",
       "\n",
       "                           45                      46  \\\n",
       "0  AVERAGE_SEQUENCE_FETCH_GAP  AVERAGE_SEQUENCE_PAGES   \n",
       "1                          -1                       0   \n",
       "2                          -1                       0   \n",
       "3                          -1                       0   \n",
       "4                          -1                       0   \n",
       "\n",
       "                             47         48             49         50  \\\n",
       "0  AVERAGE_SEQUENCE_FETCH_PAGES  TBSPACEID  LEVEL2PCTFREE  PAGESPLIT   \n",
       "1                            -1          0             -1          S   \n",
       "2                            -1          0             -1          S   \n",
       "3                            -1          0             -1          S   \n",
       "4                            -1          0             -1          S   \n",
       "\n",
       "                          51                          52  \\\n",
       "0  AVGPARTITION_CLUSTERRATIO  AVGPARTITION_CLUSTERFACTOR   \n",
       "1                         -1                          -1   \n",
       "2                         -1                          -1   \n",
       "3                         -1                          -1   \n",
       "4                         -1                          -1   \n",
       "\n",
       "                              53             54                           55  \\\n",
       "0  AVGPARTITION_PAGE_FETCH_PAIRS  PCTPAGESSAVED  DATAPARTITION_CLUSTERFACTOR   \n",
       "1                                             0                            1   \n",
       "2                                             0                            1   \n",
       "3                                             0                            1   \n",
       "4                                             0                            1   \n",
       "\n",
       "        56              57               58           59                60  \\\n",
       "0  INDCARD  AVGLEAFKEYSIZE  AVGNLEAFKEYSIZE  OS_PTR_SIZE  COLLECTSTATISTCS   \n",
       "1      775              25               25           64                     \n",
       "2      775               8                8           64                     \n",
       "3      775               0                0           64                     \n",
       "4      775               0                0           64                     \n",
       "\n",
       "         61          62          63            64                    65  \\\n",
       "0   DEFINER    LASTUSED  PERIODNAME  PERIODPOLICY  MADE_WITHOUTOVERLAPS   \n",
       "1  SYSIBM    2020-03-02        None             N                     N   \n",
       "2  SYSIBM    0001-01-01        None             N                     N   \n",
       "3  SYSIBM    0001-01-01        None             N                     N   \n",
       "4  SYSIBM    0001-01-01        None             N                     N   \n",
       "\n",
       "               66        67         68          69        70       71  \n",
       "0  ENVSTRINGUNITS  NULLKEYS  FUNC_PATH  VIEWSCHEMA  VIEWNAME  REMARKS  \n",
       "1          SYSTEM         Y       None        None      None     None  \n",
       "2          SYSTEM         Y       None        None      None     None  \n",
       "3          SYSTEM         Y       None        None      None     None  \n",
       "4          SYSTEM         Y       None        None      None     None  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "db2indexes_df = pd.DataFrame(db2indexs_list)\n",
    "print(db2indexes_df.shape)\n",
    "db2indexes_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### You can have more complex queries if desired\n",
    "Also its always a good idea to get just the columns you need in your query and thus your data frame.  Use the back slash to continue the query on a new line."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ROW COUNT 65\n",
      "COLUMN COUNT 9\n",
      "<class 'list'>\n",
      "(65, 9)\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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>INDNAME</td>\n",
       "      <td>TABNAME</td>\n",
       "      <td>TABSCHEMA</td>\n",
       "      <td>UNIQUERULE</td>\n",
       "      <td>COLCOUNT</td>\n",
       "      <td>PCTFREE</td>\n",
       "      <td>NLEVELS</td>\n",
       "      <td>STATS_TIME</td>\n",
       "      <td>COMPRESSION</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SQL191215185042622707</td>\n",
       "      <td>CUSTOMER_DEMOGRAPHICS</td>\n",
       "      <td>DFD</td>\n",
       "      <td>D</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-12-15 18:50:46.545753</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SQL191215185117765229</td>\n",
       "      <td>CUSTOMER_CHURN</td>\n",
       "      <td>DFD</td>\n",
       "      <td>D</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-12-15 18:51:20.166780</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SQL191215185245683482</td>\n",
       "      <td>MERGED_DEMOGRAPHICS_TRADING_CUSTOMER</td>\n",
       "      <td>CPD</td>\n",
       "      <td>D</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-12-15 18:52:48.293380</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SQL191215185332076952</td>\n",
       "      <td>CUSTOMER_OFFER_ACCEPTED</td>\n",
       "      <td>CPD</td>\n",
       "      <td>D</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-12-15 18:53:35.401269</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       0                                     1          2  \\\n",
       "0                INDNAME                               TABNAME  TABSCHEMA   \n",
       "1  SQL191215185042622707                 CUSTOMER_DEMOGRAPHICS   DFD        \n",
       "2  SQL191215185117765229                        CUSTOMER_CHURN   DFD        \n",
       "3  SQL191215185245683482  MERGED_DEMOGRAPHICS_TRADING_CUSTOMER   CPD        \n",
       "4  SQL191215185332076952               CUSTOMER_OFFER_ACCEPTED   CPD        \n",
       "\n",
       "            3         4        5        6                           7  \\\n",
       "0  UNIQUERULE  COLCOUNT  PCTFREE  NLEVELS                  STATS_TIME   \n",
       "1           D         2        0        1  2019-12-15 18:50:46.545753   \n",
       "2           D         2        0        1  2019-12-15 18:51:20.166780   \n",
       "3           D         2        0        1  2019-12-15 18:52:48.293380   \n",
       "4           D         2        0        1  2019-12-15 18:53:35.401269   \n",
       "\n",
       "             8  \n",
       "0  COMPRESSION  \n",
       "1            Y  \n",
       "2            Y  \n",
       "3            Y  \n",
       "4            Y  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db2indexs_list = %sql -r select indname, tabname, tabschema, uniquerule, colcount, \\\n",
    "                                pctfree, nlevels, stats_time, compression \\\n",
    "                           from syscat.indexes \\\n",
    "                          where TABSCHEMA not like 'SYS%' \\\n",
    "                            and TABSCHEMA not like 'IBM%' \n",
    "nbr_cols = len(db2indexs_list) - 1\n",
    "print('ROW COUNT', len(db2indexs_list))\n",
    "print('COLUMN COUNT',len(db2indexs_list[0]))\n",
    "print(type(db2indexs_list))\n",
    "db2indexes_df = pd.DataFrame(db2indexs_list)\n",
    "print(db2indexes_df.shape)\n",
    "db2indexes_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " Disconnect from the database when you are done."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connection successful.\n"
     ]
    }
   ],
   "source": [
    "%sql connect reset;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}