Db2 Using Prepared Statements.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Db2 Jupyter: Using Prepared Statements"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Normal the `%sql` magic command is used to execute SQL commands immediately to get a result. If this statement needs to be executed multiple times with different variables, the process is inefficient since the SQL statement must be recompiled every time.\n",
    "\n",
    "The use of the `PREPARE` and `EXECUTE` command allow the user to optimize the statement once, and then re-execute the statement using different parameters.\n",
    "\n",
    "In addition, the commit scope can be modified so that not every statement gets committed immediately. By managing the commit scope, overhead in the database engine can be avoided. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run db2.ipynb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Autocommit and Commit Scope\n",
    "By default, any SQL statements executed with the `%sql` magic command are immediately commited. This means that the log file has the transaction details and the results are committed to disk. In other words, you can't change your mind after the statement finishes execution.\n",
    "\n",
    "This behavior is often referred to as `AUTOCOMMIT` and adds a level of overhead to statement execution because at the end of every statement the results must be \"hardened\". On the other hand, autocommit means you don't have to worry about explicitly committing work or causing potential locking issues because you are holding up resources. When a record is updated, no other user will be able to view it (unless using \"dirty read\") until you commit. Holding the resource in a lock means that other workloads may come to a halt while they wait for you to commit your work. \n",
    "\n",
    "Here is a classic example of wanting a commit scope that is based on a series of statements:\n",
    "```\n",
    "withdrawal = 100\n",
    "%sql update checking set balance = balance - withdrawal\n",
    "%sql update savings set balance = balance + withdrawal\n",
    "```\n",
    "If autocommit is `ON`, you could have a problem with the transaction if the system failed after the first update statement. You would have taken money out of the checking account, but have not updated the savings account. To make sure that this transaction is run successfully:\n",
    "```\n",
    "%sql autocommit off\n",
    "withdrawal = 100\n",
    "%sql update checking set balance = balance - withdrawal\n",
    "%sql update savings set balance = balance + withdrawal\n",
    "%sql commit work\n",
    "```\n",
    "If the transaction fails before the `COMMIT WORK`, all changes to the database will be rolled back to its original state, thus protecting the integrity of the two tables."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### AUTOCOMMIT\n",
    "Autocommit can be turned on or off using the following syntax:\n",
    "```\n",
    "%sql AUTOCOMMIT ON | OFF\n",
    "```\n",
    "If you turn `AUTOCOMMIT OFF` then you need to make sure that you COMMIT work at the end of your code. If you don't there is possible you lose your work if the connection is lost to Db2. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### COMMIT, ROLLBACK\n",
    "To `COMMIT` all changes to the database you must use the following syntax:\n",
    "```\n",
    "%sql COMMIT [WORK | HOLD]\n",
    "```\n",
    "The command `COMMIT` or `COMMIT WORK` are identical and will commit all work to the database. Issuing a `COMMIT` command also closes all open cursors or statements that are open. If you had created a prepared statement (see section below) then the compiled statement will be no longer valid. By issuing a `COMMIT` you are releasing all of the resources and locks that your application may be holding.\n",
    "\n",
    "`COMMIT HOLD` will allow you to commit your work to disk, but keeps all of the resources open for further execution. This is useful for situations where you are inserting or updating 1000's of records and do not want to tie up log space waiting for a commit to occur. The following pseudocode gives you an example how this would be used:\n",
    "```\n",
    "%sql autocommit off\n",
    "for i = 1 to 1000\n",
    "    %sql insert into x values i\n",
    "    if (i / 100 == 0) \n",
    "       print i \"Records inserted\"\n",
    "       %sql commit work\n",
    "    end if \n",
    "end for\n",
    "%sql commit work\n",
    "%sql autocommit on\n",
    "```\n",
    "You should always remember to turn `AUTOCOMMIT ON` at the end of any code block or you will have to issue `COMMIT` at the end of any SQL command to commit it to the database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## PREPARE and EXECUTE\n",
    "The `PREPARE` and `EXECUTE` commands are useful in situations where you want to repeat an SQL statement multiple times while just changing the parameter values. There isn't any benefit from using these statements for simple tasks that may only run occassionally. The benefit of `PREPARE/EXECUTE` is more evident when dealing with a large number of transactions that are the same.\n",
    "\n",
    "The `PREPARE` statement can be used against many types of SQL, but in this implementation, only the following SQL statements are supported:\n",
    "* SELECT\n",
    "* INSERT\n",
    "* UPDATE\n",
    "* DELETE\n",
    "* MERGE\n",
    "\n",
    "To prepare a statement, you must use the following syntax:\n",
    "```Python\n",
    "stmt = %sql PREPARE sql ....\n",
    "```\n",
    "\n",
    "The `PREPARE` statement always returns a statement handle. You must assign the results of the `PREPARE` statement to a variable since it will be required when you `EXECUTE` the statement.\n",
    "\n",
    "The SQL statement must have any variables replaced with a question mark `?`. For instance, if you wanted to insert a single value into a table you would use the following syntax:\n",
    "```Python\n",
    "stmt = %sql PREPARE insert into x values (?)\n",
    "```\n",
    "\n",
    "One important note with parameter markers. If you require the parameter to have a specific data type (say INTEGER) then you may want to place a `CAST` statement around it to force the proper conversion. Usually strings, integers, decimals, etc... convert fine when using this syntax, but occasionally you may run across a data type issue. For the previous example we could modify it to:\n",
    "```Python\n",
    "stmt = %sql PREPARE insert into x values (CAST(? AS INTEGER))\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once you have prepared a statement, you can execute it using the following syntax:\n",
    "```Python\n",
    "%sql EXECUTE :stmt USING :v1,:v2,:v3,....\n",
    "```\n",
    "\n",
    "You must provide the variable names with a colon `:` in front of them and separate each one with a comma. This allows the SQL parser to differentiate between a host variable and a column or SQL keyword. You can also use constants as part of the `EXECUTE` statement:\n",
    "```Python\n",
    "%sql EXECUTE :stmt USING 3,'asdsa',24.5\n",
    "```\n",
    "\n",
    "Using variables are more convenient when dealing with strings that may contain single and double quotes. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using Arrays and Multiple Parameters\n",
    "When using the `PREPARE` statement, it can become cumbersome when dealing with many parameter markers. For instance, in order to insert 10 columns into a table the code would look similar to this:\n",
    "```\n",
    "stmt = %sql PREPARE INSERT INTO X VALUES (?,?,?,?,?,?,?,?,?,?)\n",
    "```\n",
    "The `%sql` command allows you to use the shortform `?*#` where `#` is an integer representing the number of columns you want in the list. The above statement could be written as:\n",
    "```\n",
    "stmt = %sql PREPARE INSERT INTO X VALUES (?*10)\n",
    "```\n",
    "The syntax can also be used to create groups of parameter markers:\n",
    "```\n",
    "stmt = %sql PREPARE INSERT INTO X VALUES (?*3,?*7)\n",
    "```\n",
    "While this may seem a strange way of providing parameters, this becomes more useful when we use the `EXECUTE` command.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `EXECUTE` command can use Python arrays (lists) as input arguments. For the previous example with 10 parameters you could issue the following command:\n",
    "```\n",
    "%sql EXECUTE :stmt USING :v1,:v2,:v3,:v4,:v5,:v6,:v7,:v8,:v9,:v10\n",
    "```\n",
    "If you placed all of these values into an array, you could also do the following:\n",
    "```\n",
    "%sql EXECUTE :stmt USING :v[0],:v[1],:v[2],:v[3],:v[4],:v[5],:v[6],:v[7],:v[8],:v[9]\n",
    "```\n",
    "That isn't much simpler but shows that you could use items within an array (one dimensional only). The easiest syntax is:\n",
    "```\n",
    "%sql EXECUTE :stmt USING :v\n",
    "```\n",
    "The only requirement is that the array `v` has exactly the number of values required to satisfy the parameter list required by the prepared statement. \n",
    "\n",
    "When you split the argument list into groups, you can use multiple arrays to contain the data. Given the following prepare statement:\n",
    "```\n",
    "stmt = %sql PREPARE INSERT INTO X VALUES (?*3,?*7)\n",
    "```\n",
    "You could execute the statement using two arrays:\n",
    "```\n",
    "%sql EXECUTE :stmt USING :name, :details\n",
    "```\n",
    "This would work as long as the total number of parameters supplied by the `name` array and `details` array is equal to 10."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Performance Comparisons\n",
    "The following examples will show the use of `AUTOCOMMIT` and `PREPARE/EXECUTE` when running SQL statements.\n",
    "\n",
    "This first SQL statement will load the EMPLOYEE and DEPARTMENT tables (if they don't already exist) and then return an array of all of the employees in the company using a SELECT statement."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql -sampledata\n",
    "employees = %sql -r select * from employee"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `employees` variable contains all of the employee data as a Python array. The next statement will retrieve the contents of the first row only (Remember that row 0 contains the name of the columns)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(employees[1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now will create another `EMPLOYEE` table that is an exact duplicate of what we already have."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql -q\n",
    "DROP TABLE EMPLOYEE2;\n",
    "CREATE TABLE EMPLOYEE2 AS (SELECT * FROM EMPLOYEE) DEFINITION ONLY;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loop with INSERT Statements\n",
    "One could always use SQL to insert into this table, but we will use a loop to execute insert statements. The loop will be timed so we can get a sense of the cost of running this code. In order to make the loop run a longer the insert block is run 50 times."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql -q DELETE FROM EMPLOYEE2\n",
    "print(\"Starting Insert\")\n",
    "start_time = time.time()\n",
    "i = 0\n",
    "for k in range(0,50):\n",
    "    for record in employees[1:]:\n",
    "        i += 1\n",
    "        empno,firstnme,midinit,lastname,workdept,phoneno,hiredate,job,edlevel,sex,birthdate,salary,bonus,comm = record\n",
    "        %sql -q INSERT INTO EMPLOYEE2 VALUES ( \\\n",
    "            :empno,:firstnme,:midinit, \\\n",
    "            :lastname,:workdept,:phoneno, \\\n",
    "            :hiredate,:job,:edlevel, \\\n",
    "            :sex,:birthdate,:salary, \\\n",
    "            :bonus,:comm) \n",
    "end_time = time.time()\n",
    "print('Total load time for {:d} records is {:.2f} seconds'.format(i,end_time-start_time))\n",
    "time_insert = end_time-start_time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loop with PREPARE Statement\n",
    "An alternative method would be to use a prepared statement that allows us to compile the statement once in Db2 and then reuse the statement in Db2's memory. This method uses the individual column values as input into the `EXECUTE` statement."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql -q DELETE FROM EMPLOYEE2\n",
    "print(\"Starting Insert\")\n",
    "start_time = time.time()\n",
    "i = 0\n",
    "prep = %sql prepare INSERT INTO EMPLOYEE2 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)\n",
    "for k in range(0,50):\n",
    "    for record in employees[1:]:\n",
    "        i += 1\n",
    "        empno,firstnme,midinit,lastname,workdept,phoneno,hiredate,job,edlevel,sex,birthdate,salary,bonus,comm = record\n",
    "        %sql execute :prep using :empno,:firstnme,:midinit,:lastname,:workdept,:phoneno,:hiredate,:job,:edlevel,:sex,:birthdate,:salary,:bonus,:comm\n",
    "end_time = time.time()\n",
    "print('Total load time for {:d} records is {:.2f} seconds'.format(i,end_time-start_time))\n",
    "time_prepare = end_time-start_time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loop with PREPARE Statement and Arrays\n",
    "You will notice that it is a bit tedious to write out all of the columns that are required as part of an `INSERT` statement. A simpler option is to use a Python list or array to and assign it directly in the `EXECUTE` statement. So rather than:\n",
    "```\n",
    "%sql execute :prep using :empno, :firstnme, ...\n",
    "```\n",
    "We can just use the array variable generated as part of the for loop:\n",
    "```\n",
    "%sql execute :prep using :record\n",
    "```\n",
    "The following SQL demonstrates this approach."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql -q DELETE FROM EMPLOYEE2\n",
    "print(\"Starting Insert\")\n",
    "start_time = time.time()\n",
    "i = 0\n",
    "prep = %sql prepare INSERT INTO EMPLOYEE2 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)\n",
    "for k in range(0,50):\n",
    "    for record in employees[1:]:\n",
    "        i += 1\n",
    "        %sql execute :prep using :record\n",
    "\n",
    "end_time = time.time()\n",
    "print('Total load time for {:d} records is {:.2f} seconds'.format(i,end_time-start_time))\n",
    "time_array = end_time-start_time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loop with PREPARE Statement, Arrays and AUTOCOMMIT OFF\n",
    "Finally, we can turn `AUTOCOMMIT` off and then commit the work at the end of the block to improve the total time required to insert the data. Note the use of the parameter shortform `?*14` in the code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql -q DELETE FROM EMPLOYEE2\n",
    "%sql autocommit off\n",
    "print(\"Starting Insert\")\n",
    "start_time = time.time()\n",
    "i = 0\n",
    "prep = %sql prepare INSERT INTO EMPLOYEE2 VALUES (?*14)\n",
    "for k in range(0,50):\n",
    "    for record in employees[1:]:\n",
    "        i += 1\n",
    "        %sql execute :prep using :record\n",
    "%sql commit work                                           \n",
    "end_time = time.time()\n",
    "print('Total load time for {:d} records is {:.2f} seconds'.format(i,end_time-start_time))\n",
    "%sql autocommit on   \n",
    "time_commit = end_time-start_time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### Performance Comparison\n",
    "You may have noticed that the performance of the last method is substantially faster than the other examples. The primary reason for this is the `COMMIT` only occuring at the end of the code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql -pb\n",
    "WITH RESULT(RUN,ELAPSED) AS (\n",
    "  VALUES\n",
    "    ('INSERT',CAST(:time_insert AS DEC(5,2))),\n",
    "    ('PREPARE',CAST(:time_prepare AS DEC(5,2))),\n",
    "    ('ARRAY ',CAST(:time_array AS DEC(5,2))),\n",
    "    ('COMMIT ',CAST(:time_commit AS DEC(5,2)))   \n",
    ")\n",
    "SELECT RUN, ELAPSED FROM RESULT\n",
    "  ORDER BY ELAPSED DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Credits: IBM 2018, George Baklarz [baklarz@ca.ibm.com]"
   ]
  }
 ],
 "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.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}