Db2 11 Compatibility Features.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"top\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "# Db2 11 Compatibility Features"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "Moving from one database vendor to another can sometimes be difficult due to syntax differences between data types, functions, and language elements. Db2 already has a high degree of compatibility with Oracle PLSQL along with some of the Oracle data types. \n",
    "\n",
    "Db2 11 introduces some additional data type and function compatibility that will reduce some of the migration effort required when porting from other systems. There are some specific features within Db2 that are targeted at Netezza SQL and that is discussed in a separate section."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hideCode": false,
    "hideOutput": false,
    "hidePrompt": false
   },
   "outputs": [],
   "source": [
    "%run db2.ipynb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We populate the database with the EMPLOYEE and DEPARTMENT tables so that we can run the various examples."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql -sampledata"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Table of Contents\n",
    "\n",
    "* [Outer Join Operator](#outer)\n",
    "\n",
    "* [CHAR datatype size increase](#char)\n",
    "\n",
    "* [Binary Data Type](#binary)\n",
    "\n",
    "* [Boolean Data Type](#boolean)\n",
    "\n",
    "* [Synonyms for Data Types](#synonyms)\n",
    "\n",
    "* [Function Synonymns](#function)\n",
    "\n",
    "* [Netezza Compatibility](#netezza)\n",
    "\n",
    "* [Select Enhancements](#select)\n",
    "\n",
    "* [Hexadecimal Functions](#hexadecimal)\n",
    "\n",
    "* [Table Creation with Data](#create)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='outer'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Outer Join Operator\n",
    "\n",
    "Db2 allows the use of the Oracle outer-join operator when Oracle compatibility is turned on within a database. In Db2 11, the outer join operator is available by default and does not require the DBA to turn on Oracle compatibility.\n",
    "\n",
    "Db2 supports standard join syntax for LEFT and RIGHT OUTER JOINS.\n",
    "However, there is proprietary syntax used by Oracle employing a keyword: \"(+)\"\n",
    "to mark the \"null-producing\" column reference that precedes it in an\n",
    "implicit join notation. That is (+) appears in the WHERE clause and\n",
    "refers to a column of the inner table in a left outer join.\n",
    "\n",
    "For instance:\n",
    "<pre>  \n",
    "SELECT * FROM T1, T2\n",
    "WHERE T1.C1 = T2.C2 (+)\n",
    "</pre>\n",
    "Is the same as:\n",
    "<pre>\n",
    "SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.C1 = T2.C2\n",
    "</pre>\n",
    "\n",
    "In this example, we get list of departments and their employees, as\n",
    "well as the names of departments who have no employees.\n",
    "\n",
    "This example uses the standard Db2 syntax.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT DEPTNAME, LASTNAME FROM\n",
    "  DEPARTMENT D LEFT OUTER JOIN EMPLOYEE E\n",
    "  ON D.DEPTNO = E.WORKDEPT"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "This example works in the same manner as the last one, but uses\n",
    "the \"+\" sign syntax. The format is a lot simpler to remember than OUTER JOIN\n",
    "syntax, but it is not part of the SQL standard."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT DEPTNAME, LASTNAME FROM\n",
    "  DEPARTMENT D, EMPLOYEE E\n",
    "WHERE D.DEPTNO = E.WORKDEPT (+)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='char'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## CHAR Datatype Size Increase\n",
    " \n",
    "The CHAR datatype was limited to 254 characters in prior releases of Db2. In Db2 11, the limit has been increased\n",
    "to 255 characters to bring it in line with other SQL implementations.\n",
    "\n",
    "First we drop the table if it already exists."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql -q\n",
    "DROP TABLE LONGER_CHAR;\n",
    "  \n",
    "CREATE TABLE LONGER_CHAR\n",
    "  (\n",
    "  NAME CHAR(255)\n",
    "  );"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='binary'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Binary Data Types\n",
    "\n",
    "Db2 11 introduces two new binary data types: BINARY and VARBINARY. These two data types can contain any combination \n",
    "of characters or binary values and are not affected by the codepage of the server that the values are stored on.\n",
    "\n",
    "A BINARY data type is fixed and can have a maximum length of 255 bytes, while a VARBINARY column can contain up to \n",
    "32672 bytes. Each of these data types is compatible with columns created with the FOR BIT DATA keyword.\n",
    "\n",
    "The BINARY data type will reduce the amount of conversion required from other data bases. Although binary data was supported with the FOR BIT DATA clause on a character column, it required manual DDL changes when migrating a table definition.\n",
    "\n",
    "This example shows the creation of the three types of binary data types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql -q\n",
    "DROP TABLE HEXEY;\n",
    "\n",
    "CREATE TABLE HEXEY\n",
    "  (\n",
    "  AUDIO_SHORT BINARY(255),\n",
    "  AUDIO_LONG  VARBINARY(1024),\n",
    "  AUDIO_CHAR  VARCHAR(255) FOR BIT DATA\n",
    "  );"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Inserting data into a binary column can be done through the use of BINARY functions, or the use of X'xxxx' modifiers when using the VALUE clause. For fixed strings you use the X'00' format to specify a binary value and BX'00' for variable length binary strings. For instance, the following SQL will insert data into the previous table that was created."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "INSERT INTO HEXEY VALUES\n",
    "  (BINARY('Hello there'), \n",
    "   BX'2433A5D5C1', \n",
    "   VARCHAR_BIT_FORMAT(HEX('Hello there')));\n",
    "\n",
    "SELECT * FROM HEXEY;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Handling binary data with a FOR BIT DATA column was sometimes tedious, so the BINARY columns will make coding a little simpler. You can compare and assign values between any of these types of columns. The next SQL statement will update the AUDIO_CHAR column with the contents of the AUDIO_SHORT column. Then the SQL will test to make sure they are the same value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "UPDATE HEXEY \n",
    "  SET AUDIO_CHAR = AUDIO_SHORT"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We should have one record that is equal."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT COUNT(*) FROM HEXEY WHERE\n",
    "  AUDIO_SHORT = AUDIO_CHAR"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='boolean'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Boolean Data Type\n",
    "\n",
    "The boolean data type (true/false) has been available in SQLPL and PL/SQL scripts for some time. However,\n",
    "the boolean data type could not be used in a table definition. Db2 11 FP1 now allows you to use this\n",
    "data type in a table definition and use TRUE/FALSE clauses to compare values.\n",
    "\n",
    "This simple table will be used to demonstrate how BOOLEAN types can be used."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql -q\n",
    "DROP TABLE TRUEFALSE;\n",
    "\n",
    "CREATE TABLE TRUEFALSE (\n",
    "    EXAMPLE INT,\n",
    "    STATE   BOOLEAN\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The keywords for a true value are TRUE, 'true', 't', 'yes', 'y', 'on', and '1'. For false the values are\n",
    "FALSE, 'false', 'f', 'no', 'n', and '0'. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "INSERT INTO TRUEFALSE VALUES\n",
    "  (1, TRUE), \n",
    "  (2, FALSE),\n",
    "  (3, 0),\n",
    "  (4, 't'),\n",
    "  (5, 'no')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can check to see what has been inserted into the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%sql SELECT * FROM TRUEFALSE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Retrieving the data in a SELECT statement will return an integer value for display purposes.\n",
    "1 is true and 0 is false (binary 1 and 0).\n",
    "\n",
    "Comparison operators with BOOLEAN data types will use TRUE, FALSE, 1 or 0 or any of the supported binary values. You have the choice of using the equal (=) operator or the IS or IS NOT syntax as shown in the following SQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT * FROM TRUEFALSE\n",
    "  WHERE STATE = TRUE OR STATE = 1 OR STATE = 'on' OR STATE IS TRUE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='synonyms'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Synonym Data types\n",
    "\n",
    "\n",
    "Db2 has the standard data types that most developers are familiar with, like CHAR, INTEGER, and DECIMAL. There are other SQL implementations that use different names for these data types, so Db2 11 now allows these data types as syonomys for the base types.\n",
    "\n",
    "These data types are:\n",
    "\n",
    "|Type   |Db2 Equivalent\n",
    "|:----- |:-------------\n",
    "|INT2   |SMALLINT\n",
    "|INT4   |INTEGER\n",
    "|INT8   |BIGINT\n",
    "|FLOAT4 |REAL\n",
    "|FLOAT8 |FLOAT\n",
    "\n",
    "The following SQL will create a table with all of these data types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql -q\n",
    "DROP TABLE SYNONYM_EMPLOYEE;\n",
    "\n",
    "CREATE TABLE SYNONYM_EMPLOYEE\n",
    "  (\n",
    "  NAME VARCHAR(20),\n",
    "  SALARY     INT4,\n",
    "  BONUS      INT2,\n",
    "  COMMISSION INT8,\n",
    "  COMMISSION_RATE FLOAT4,\n",
    "  BONUS_RATE FLOAT8\n",
    "  );"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When you create a table with these other data types, Db2 does not use these \"types\" in the catalog. What Db2 will do is use the Db2 type instead of these synonym types. What this means is that if you describe the contents of a table, \n",
    "you will see the Db2 types displayed, not these synonym types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT(NAME), COLTYPE, LENGTH FROM SYSIBM.SYSCOLUMNS \n",
    "  WHERE TBNAME='SYNONYM_EMPLOYEE' AND TBCREATOR=CURRENT USER"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='function'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Function Name Compatibility\n",
    "\n",
    "\n",
    "Db2 has a wealth of built-in functions that are equivalent to competitive functions, but with a different name. In\n",
    "Db2 11, these alternate function names are mapped to the Db2 function so that there is no re-write of the function\n",
    "name required. This first SQL statement generates some data required for the statistical functions.\n",
    "\n",
    "\n",
    "### Generate Linear Data\n",
    "\n",
    "\n",
    "This command generates X,Y coordinate pairs in the xycoord table that are based on the\n",
    "function y = 2x + 5.  Note that the table creation uses Common Table Expressions\n",
    "and recursion to generate the data!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql -q\n",
    "DROP TABLE XYCOORDS;\n",
    "\n",
    "CREATE TABLE XYCOORDS\n",
    "  (\n",
    "  X INT,\n",
    "  Y INT\n",
    "  );\n",
    "    \n",
    "INSERT INTO XYCOORDS\n",
    "   WITH TEMP1(X) AS\n",
    "     (\n",
    "     VALUES (0)\n",
    "     UNION ALL\n",
    "     SELECT X+1 FROM TEMP1 WHERE X < 10\n",
    "     )\n",
    "   SELECT X, 2*X + 5\n",
    "     FROM TEMP1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### COVAR_POP is an alias for COVARIANCE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT 'COVAR_POP', COVAR_POP(X,Y) FROM XYCOORDS\n",
    "UNION ALL\n",
    "SELECT 'COVARIANCE', COVARIANCE(X,Y) FROM XYCOORDS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### VAR_POP is an alias for VARIANCE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT 'STDDEV_POP', STDDEV_POP(X) FROM XYCOORDS\n",
    "UNION ALL\n",
    "SELECT 'STDDEV', STDDEV(X) FROM XYCOORDS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### VAR_SAMP is an alias for VARIANCE_SAMP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT 'VAR_SAMP', VAR_SAMP(X) FROM XYCOORDS\n",
    "UNION ALL\n",
    "SELECT 'VARIANCE_SAMP', VARIANCE_SAMP(X) FROM XYCOORDS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ISNULL, NOTNULL is an alias for IS NULL, IS NOT NULL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "WITH EMP(LASTNAME, WORKDEPT) AS\n",
    "  (\n",
    "  VALUES ('George','A01'),\n",
    "         ('Fred',NULL),\n",
    "         ('Katrina','B01'),\n",
    "         ('Bob',NULL)\n",
    "  )\n",
    "SELECT * FROM EMP WHERE \n",
    "   WORKDEPT ISNULL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### LOG is an alias for LN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "VALUES ('LOG',LOG(10))\n",
    "UNION ALL\n",
    "VALUES ('LN', LN(10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### RANDOM is an alias for RAND\n",
    "\n",
    "Notice that the random number that is generated for the two calls results in a different value! This behavior is the \n",
    "not the same with timestamps, where the value is calculated once during the execution of the SQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "VALUES ('RANDOM', RANDOM())\n",
    "UNION ALL\n",
    "VALUES ('RAND', RAND())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### STRPOS is an alias for POSSTR"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "VALUES ('POSSTR',POSSTR('Hello There','There'))\n",
    "UNION ALL\n",
    "VALUES ('STRPOS',STRPOS('Hello There','There'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### STRLEFT is an alias for LEFT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "VALUES ('LEFT',LEFT('Hello There',5))\n",
    "UNION ALL\n",
    "VALUES ('STRLEFT',STRLEFT('Hello There',5))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### STRRIGHT is an alias for RIGHT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "VALUES ('RIGHT',RIGHT('Hello There',5))\n",
    "UNION ALL\n",
    "VALUES ('STRRIGHT',STRRIGHT('Hello There',5))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Additional Synonyms\n",
    "\n",
    "\n",
    "There are a couple of additional keywords that are synonyms for existing Db2 functions. The list below includes only\n",
    "those features that were introduced in Db2 11.\n",
    "\n",
    "|Keyword      | Db2 Equivalent\n",
    "|:------------| :-----------------------------\n",
    "|BPCHAR       | VARCHAR (for casting function)\n",
    "|DISTRIBUTE ON| DISTRIBUTE BY"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='netezza'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Netezza Compatibility\n",
    "\n",
    "\n",
    "Db2 provides features that enable applications that were written for a Netezza Performance Server (NPS) \n",
    "database to use a Db2 database without having to be rewritten.\n",
    "\n",
    "The SQL_COMPAT global variable is used to activate the following optional NPS compatibility features:\n",
    "\n",
    " - Double-dot notation - When operating in NPS compatibility mode, you can use double-dot notation to specify a database object.\n",
    " - TRANSLATE parameter syntax - The syntax of the TRANSLATE parameter depends on whether NPS compatibility mode is being used.\n",
    " - Operators - Which symbols are used to represent operators in expressions depends on whether NPS compatibility mode is being used.\n",
    " - Grouping by SELECT clause columns - When operating in NPS compatibility mode, you can specify the ordinal position or exposed name of a SELECT clause column when grouping the results of a query.\n",
    " - Routines written in NZPLSQL - When operating in NPS compatibility mode, the NZPLSQL language can be used in addition to the SQL PL language."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Special Characters\n",
    "\n",
    "A quick review of Db2 special characters. Before we change the behavior of Db2, we need to understand\n",
    "what some of the special characters do. The following SQL shows how some of the special characters\n",
    "work. Note that the HASH/POUND sign (#) has no meaning in Db2."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "WITH SPECIAL(OP, DESCRIPTION, EXAMPLE, RESULT) AS\n",
    "  (\n",
    "  VALUES \n",
    "     (' | ','OR        ', '2 | 3   ', 2 | 3),\n",
    "     (' & ','AND       ', '2 & 3   ', 2 & 3),\n",
    "     (' ^ ','XOR       ', '2 ^ 3   ', 2 ^ 3),\n",
    "     (' ~ ','COMPLEMENT', '~2      ', ~2),\n",
    "     (' # ','NONE      ', '        ',0)\n",
    "  )\n",
    "SELECT * FROM SPECIAL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we turn on NPS compatibility, you see a couple of special characters change behavior. Specifically the \n",
    "^ operator becomes a \"power\" operator, and the # becomes an XOR operator."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SET SQL_COMPAT = 'NPS';\n",
    "WITH SPECIAL(OP, DESCRIPTION, EXAMPLE, RESULT) AS\n",
    "  (\n",
    "  VALUES \n",
    "     (' | ','OR        ', '2 | 3   ', 2 | 3),\n",
    "     (' & ','AND       ', '2 & 3   ', 2 & 3),\n",
    "     (' ^ ','POWER     ', '2 ^ 3   ', 2 ^ 3),\n",
    "     (' ~ ','COMPLIMENT', '~2      ', ~2),\n",
    "     (' # ','XOR       ', '2 # 3   ', 2 # 3)\n",
    "  )\n",
    "SELECT * FROM SPECIAL;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## GROUP BY Ordinal Location\n",
    "\n",
    "The GROUP BY command behavior also changes in NPS mode. The following SQL statement groups results\n",
    "using the default Db2 syntax:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SET SQL_COMPAT='DB2';\n",
    "\n",
    "SELECT WORKDEPT,INT(AVG(SALARY)) \n",
    "  FROM EMPLOYEE\n",
    "GROUP BY WORKDEPT;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you try using the ordinal location (similar to an ORDER BY clause), you will\n",
    "get an error message."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT WORKDEPT, INT(AVG(SALARY))\n",
    "  FROM EMPLOYEE\n",
    "GROUP BY 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If NPS compatibility is turned on then then you use the GROUP BY clause with an ordinal location."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SET SQL_COMPAT='NPS';\n",
    "SELECT WORKDEPT, INT(AVG(SALARY))\n",
    "  FROM EMPLOYEE\n",
    "GROUP BY 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## TRANSLATE Function\n",
    "\n",
    "The translate function syntax in Db2 is: \n",
    "<pre>\n",
    "TRANSLATE(expression, to_string, from_string, padding)\n",
    "</pre>\n",
    "The TRANSLATE function returns a value in which one or more characters in a string expression might \n",
    "have been converted to other characters. The function converts all the characters in char-string-exp\n",
    "in from-string-exp to the corresponding characters in to-string-exp or, if no corresponding characters exist, \n",
    "to the pad character specified by padding.\n",
    "\n",
    "If no parameters are given to the function, the original string is converted to uppercase.\n",
    "\n",
    "In NPS mode, the translate syntax is: \n",
    "<pre>\n",
    "TRANSLATE(expression, from_string, to_string)\n",
    "</pre>\n",
    "If a character is found in the from string, and there is no corresponding character in the to string, it is removed. If it was using Db2 syntax, the padding character would be used instead.\n",
    "\n",
    "Note: If ORACLE compatibility is ON then the behavior of TRANSLATE is identical to NPS mode.\n",
    "\n",
    "This first example will uppercase the string."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql \n",
    "SET SQL_COMPAT = 'NPS';\n",
    "VALUES TRANSLATE('Hello');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this example, the letter 'o' will be replaced with an '1'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%sql VALUES TRANSLATE('Hello','o','1')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that you could replace more than one character by expanding both the \"to\" and \"from\" strings. This\n",
    "example will replace the letter \"e\" with an \"2\" as well as \"o\" with \"1\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%sql VALUES TRANSLATE('Hello','oe','12')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Translate will also remove a character if it is not in the \"to\" list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%sql VALUES TRANSLATE('Hello','oel','12')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Reset the behavior back to Db2 mode."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%sql SET SQL_COMPAT='DB2'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='select'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SELECT Enhancements\n",
    "Db2 has the ability to limit the amount of data retrieved on a SELECT statement\n",
    "through the use of the FETCH FIRST n ROWS ONLY clause. In Db2 11, the ability to offset \n",
    "the rows before fetching was added to the FETCH FIRST clause.\n",
    "\n",
    "## Simple SQL with Fetch First Clause\n",
    "\n",
    "The FETCH first clause can be used in a variety of locations in a SELECT clause. This\n",
    "first example fetches only 10 rows from the EMPLOYEE table.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT LASTNAME FROM EMPLOYEE\n",
    "  FETCH FIRST 5 ROWS ONLY"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also add ORDER BY and GROUP BY clauses in the SELECT statement. Note that\n",
    "Db2 still needs to process all of the records and do the ORDER/GROUP BY work\n",
    "before limiting the answer set. So you are not getting the first 5 rows \"sorted\". You \n",
    "are actually getting the entire answer set sorted before retrieving just 5 rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT LASTNAME FROM EMPLOYEE\n",
    "  ORDER BY LASTNAME\n",
    "  FETCH FIRST 5 ROWS ONLY"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here is an example with the GROUP BY statement. This first SQL statement gives us the total\n",
    "answer set - the count of employees by WORKDEPT."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT WORKDEPT, COUNT(*) FROM EMPLOYEE\n",
    "  GROUP BY WORKDEPT\n",
    "  ORDER BY WORKDEPT"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Adding the FETCH FIRST clause only reduces the rows returned, not the rows that\n",
    "are used to compute the GROUPing result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT WORKDEPT, COUNT(*) FROM EMPLOYEE\n",
    "  GROUP BY WORKDEPT\n",
    "  ORDER BY WORKDEPT\n",
    "  FETCH FIRST 5 ROWS ONLY"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## OFFSET Extension\n",
    "\n",
    "The FETCH FIRST n ROWS ONLY clause can also include an OFFSET keyword. The OFFSET keyword \n",
    "allows you to retrieve the answer set after skipping \"n\" number of rows. The syntax of the OFFSET\n",
    "keyword is:\n",
    "\n",
    "<pre>\n",
    "OFFSET n ROWS FETCH FIRST x ROWS ONLY\n",
    "</pre>\n",
    "\n",
    "The OFFSET n ROWS must precede the FETCH FIRST x ROWS ONLY clause. The OFFSET clause can be used to \n",
    "scroll down an answer set without having to hold a cursor. For instance, you could have the \n",
    "first SELECT call request 10 rows by just using the FETCH FIRST clause. After that you could\n",
    "request the first 10 rows be skipped before retrieving the next 10 rows. \n",
    "\n",
    "The one thing you must be aware of is that that answer set could change between calls if you use\n",
    "this technique of a \"moving\" window. If rows are updated or added after your initial query you may\n",
    "get different results. This is due to the way that Db2 adds rows to a table. If there is a DELETE and then\n",
    "an INSERT, the INSERTed row may end up in the empty slot. There is no guarantee of the order of retrieval. For\n",
    "this reason you are better off using an ORDER by to force the ordering although this too won't always prevent \n",
    "rows changing positions.\n",
    "\n",
    "Here are the first 10 rows of the employee table (not ordered)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT LASTNAME FROM EMPLOYEE\n",
    "  FETCH FIRST 10 ROWS ONLY"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can specify a zero offset to begin from the beginning."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT LASTNAME FROM EMPLOYEE\n",
    "  OFFSET 0 ROWS\n",
    "  FETCH FIRST 10 ROWS ONLY"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can move the answer set ahead by 5 rows and get the remaining\n",
    "5 rows in the answer set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT LASTNAME FROM EMPLOYEE\n",
    "  OFFSET 5 ROWS\n",
    "  FETCH FIRST 5 ROWS ONLY"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## FETCH FIRST and OFFSET in SUBSELECTs\n",
    "\n",
    "The FETCH FIRST/OFFSET clause is not limited to regular SELECT statements. You can also \n",
    "limit the number of rows that are used in a subselect. In this case you are limiting the amount of\n",
    "data that Db2 will scan when determining the answer set.\n",
    "\n",
    "For instance, say you wanted to find the names of the employees who make more than the\n",
    "average salary of the 3rd highest paid department. (By the way, there are multiple ways to \n",
    "do this, but this is one approach).\n",
    "\n",
    "The first step is to determine what the average salary is of all departments."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT WORKDEPT, AVG(SALARY) FROM EMPLOYEE\n",
    "GROUP BY WORKDEPT\n",
    "ORDER BY AVG(SALARY) DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We only want one record from this list (the third one), so we can use the FETCH FIRST clause with\n",
    "an OFFSET to get the value we want (Note: we need to skip 2 rows to get to the 3rd one)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT WORKDEPT, AVG(SALARY) FROM EMPLOYEE\n",
    "GROUP BY WORKDEPT\n",
    "ORDER BY AVG(SALARY) DESC\n",
    "OFFSET 2 ROWS FETCH FIRST 1 ROWS ONLY"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And here is the list of employees that make more than the average salary of the 3rd highest department in the \n",
    "company."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT LASTNAME, SALARY FROM EMPLOYEE\n",
    "  WHERE\n",
    "    SALARY > (\n",
    "       SELECT AVG(SALARY) FROM EMPLOYEE\n",
    "         GROUP BY WORKDEPT\n",
    "         ORDER BY AVG(SALARY) DESC\n",
    "         OFFSET 2 ROWS FETCH FIRST 1 ROW ONLY\n",
    "       )\n",
    "ORDER BY SALARY"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Alternate Syntax for FETCH FIRST\n",
    "\n",
    "The FETCH FIRST n ROWS ONLY and OFFSET clause can also be specified using a simpler LIMIT/OFFSET syntax.\n",
    "\n",
    "The LIMIT clause and the equivalent FETCH FIRST syntax are shown below.\n",
    "\n",
    "\n",
    "|Syntax            |Equivalent\n",
    "|:-----------------|:-----------------------------\n",
    "|LIMIT x           |FETCH FIRST x ROWS ONLY\n",
    "|LIMIT x OFFSET y  |OFFSET y ROWS FETCH FIRST x ROWS ONLY\n",
    "|LIMIT y,x         |OFFSET y ROWS FETCH FIRST x ROWS ONLY\n",
    "\n",
    "The previous examples are rewritten using the LIMIT clause.\n",
    "\n",
    "We can use the LIMIT clause with an OFFSET to get the value we want from the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT WORKDEPT, AVG(SALARY) FROM EMPLOYEE\n",
    "GROUP BY WORKDEPT\n",
    "ORDER BY AVG(SALARY) DESC\n",
    "LIMIT 1 OFFSET 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here is the list of employees that make more than the average salary of the 3rd highest department in the \n",
    "company. Note that the LIMIT clause specifies only the offset (LIMIT x) or the offset and limit (LIMIT y,x) when you do not use the LIMIT keyword. One would think that LIMIT x OFFSET y would translate into LIMIT x,y but that is not the case. Don't try to figure out the SQL standards reasoning behind the syntax!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT LASTNAME, SALARY FROM EMPLOYEE\n",
    "  WHERE\n",
    "    SALARY > (\n",
    "       SELECT AVG(SALARY) FROM EMPLOYEE\n",
    "         GROUP BY WORKDEPT\n",
    "         ORDER BY AVG(SALARY) DESC\n",
    "         LIMIT 2,1 \n",
    "       )\n",
    "ORDER BY SALARY"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='hexadecimal'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Hexadecimal Functions\n",
    "\n",
    "A number of new HEX manipulation functions have been added to Db2 11. There are a class of functions\n",
    "that manipulate different size integers (SMALL, INTEGER, BIGINT) using NOT, OR, AND, and XOR. In addition to\n",
    "these functions, there are a number of functions that display and convert values into hexadecimal values.\n",
    "\n",
    "## INTN Functions\n",
    "\n",
    "The INTN functions are bitwise functions that operate on the \"two's complement\" representation of \n",
    "the integer value of the input arguments and return the result as a corresponding base 10 integer value.\n",
    "\n",
    "The function names all include the size of the integers that are being manipulated:\n",
    " \n",
    "- N = 2 (Smallint), 4 (Integer), 8 (Bigint)\n",
    "\n",
    "There are four functions:\n",
    "\n",
    "- INTNAND - Performs a bitwise AND operation, 1 only if the corresponding bits in both arguments are 1\n",
    "- INTNOR - Performs a bitwise OR operation, 1 unless the corresponding bits in both arguments are zero\n",
    "- INTNXOR Performs a bitwise exclusive OR operation, 1 unless the corresponding bits in both arguments are the same\n",
    "- INTNNOT - Performs a bitwise NOT operation, opposite of the corresponding bit in the argument\n",
    "\n",
    "Six variables will be created to use in the examples. The X/Y values will be set to X=1 (01) and Y=3 (11) \n",
    "and different sizes to show how the functions work."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql -q\n",
    "DROP VARIABLE XINT2; \n",
    "DROP VARIABLE YINT2;\n",
    "DROP VARIABLE XINT4;\n",
    "DROP VARIABLE YINT4;\n",
    "DROP VARIABLE XINT8; \n",
    "DROP VARIABLE YINT8;\n",
    "CREATE VARIABLE XINT2 INT2 DEFAULT(1);\n",
    "CREATE VARIABLE YINT2 INT2 DEFAULT(3);\n",
    "CREATE VARIABLE XINT4 INT4 DEFAULT(1);\n",
    "CREATE VARIABLE YINT4 INT4 DEFAULT(3);\n",
    "CREATE VARIABLE XINT8 INT8 DEFAULT(1);\n",
    "CREATE VARIABLE YINT8 INT8 DEFAULT(3);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This example will show the four functions used against SMALLINT (INT2) data types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "WITH LOGIC(EXAMPLE, X, Y, RESULT) AS\n",
    "  (\n",
    "  VALUES\n",
    "     ('INT2AND(X,Y)',XINT2,YINT2,INT2AND(XINT2,YINT2)),\n",
    "     ('INT2OR(X,Y) ',XINT2,YINT2,INT2OR(XINT2,YINT2)),\n",
    "     ('INT2XOR(X,Y)',XINT2,YINT2,INT2XOR(XINT2,YINT2)),\n",
    "     ('INT2NOT(X)  ',XINT2,YINT2,INT2NOT(XINT2))\n",
    "  )\n",
    "SELECT * FROM LOGIC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This example will use the 4 byte (INT4) data type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "WITH LOGIC(EXAMPLE, X, Y, RESULT) AS\n",
    "  (\n",
    "  VALUES\n",
    "     ('INT4AND(X,Y)',XINT4,YINT4,INT4AND(XINT4,YINT4)),\n",
    "     ('INT4OR(X,Y) ',XINT4,YINT4,INT4OR(XINT4,YINT4)),\n",
    "     ('INT4XOR(X,Y)',XINT4,YINT4,INT4XOR(XINT4,YINT4)),\n",
    "     ('INT4NOT(X)  ',XINT4,YINT4,INT4NOT(XINT4))\n",
    "  )\n",
    "SELECT * FROM LOGIC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, the INT8 data type is used in the SQL. Note that you can mix and match the INT2, INT4, and INT8 values\n",
    "in these functions but you may get truncation if the value is too big. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "WITH LOGIC(EXAMPLE, X, Y, RESULT) AS\n",
    "  (\n",
    "  VALUES\n",
    "     ('INT8AND(X,Y)',XINT8,YINT8,INT8AND(XINT8,YINT8)),\n",
    "     ('INT8OR(X,Y) ',XINT8,YINT8,INT8OR(XINT8,YINT8)),\n",
    "     ('INT8XOR(X,Y)',XINT8,YINT8,INT8XOR(XINT8,YINT8)),\n",
    "     ('INT8NOT(X)  ',XINT8,YINT8,INT8NOT(XINT8))\n",
    "  )\n",
    "SELECT * FROM LOGIC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## TO_HEX Function\n",
    "\n",
    "The TO_HEX function converts a numeric expression into a character hexadecimal representation. For example, the\n",
    "numeric value 255 represents x'FF'. The value returned from this function is a VARCHAR value and its \n",
    "length depends on the size of the number you supply."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%sql VALUES TO_HEX(255)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## RAWTOHEX Function\n",
    "\n",
    "The RAWTOHEX function returns a hexadecimal representation of a value as a character string. The\n",
    "result is a character string itself."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%sql VALUES RAWTOHEX('Hello')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The string \"00\" converts to a hex representation of x'3030' which is 12336 in Decimal.\n",
    "So the TO_HEX function would convert this back to the HEX representation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%sql VALUES TO_HEX(12336)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The string that is returned by the RAWTOHEX function should be the same."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%sql VALUES RAWTOHEX('00'); "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id=\"create\"><a/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Table Creation Extensions\n",
    "\n",
    "The CREATE TABLE statement can now use a SELECT clause to generate the definition and LOAD the data\n",
    "at the same time. \n",
    "\n",
    "## Create Table Syntax\n",
    "\n",
    "The syntax of the CREATE table statement has been extended with the AS (SELECT ...) WITH DATA clause:\n",
    "\n",
    "<pre>\n",
    "CREATE TABLE <name> AS (SELECT ...) [ WITH DATA | DEFINITION ONLY ]\n",
    "</pre>\n",
    "\n",
    "The table definition will be generated based on the SQL statement that you specify. The column names\n",
    "are derived from the columns that are in the SELECT list and can only be changed by specifying the columns names\n",
    "as part of the table name: EMP(X,Y,Z,...) AS (...).\n",
    "\n",
    "For example, the following SQL will fail because a column list was not provided:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%sql -q DROP TABLE AS_EMP\n",
    "%sql CREATE TABLE AS_EMP AS (SELECT EMPNO, SALARY+BONUS FROM EMPLOYEE) DEFINITION ONLY;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can name a column in the SELECT list or place it in the table definition."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%sql -q DROP TABLE AS_EMP\n",
    "%sql CREATE TABLE AS_EMP AS (SELECT EMPNO, SALARY+BONUS AS PAY FROM EMPLOYEE) DEFINITION ONLY;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can check the SYSTEM catalog to see the table definition. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT(NAME), COLTYPE, LENGTH FROM SYSIBM.SYSCOLUMNS \n",
    "  WHERE TBNAME='AS_EMP' AND TBCREATOR=CURRENT USER"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The DEFINITION ONLY clause will create the table but not load any data into it. Adding the WITH DATA \n",
    "clause will do an INSERT of rows into the newly created table. If you have a large amount of data \n",
    "to load into the table you may be better off creating the table with DEFINITION ONLY and then using LOAD\n",
    "or other methods to load the data into the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%sql -q DROP TABLE AS_EMP\n",
    "%sql CREATE TABLE AS_EMP AS (SELECT EMPNO, SALARY+BONUS AS PAY FROM EMPLOYEE) WITH DATA;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The SELECT statement can be very sophisticated. It can do any type of calculation or limit the\n",
    "data to a subset of information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql -q\n",
    "DROP TABLE AS_EMP;\n",
    "CREATE TABLE AS_EMP(LAST,PAY) AS \n",
    " (\n",
    " SELECT LASTNAME, SALARY FROM EMPLOYEE \n",
    "    WHERE WORKDEPT='D11'\n",
    " FETCH FIRST 3 ROWS ONLY\n",
    " ) WITH DATA;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also use the OFFSET clause as part of the FETCH FIRST ONLY to get chunks of data from the\n",
    "original table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql -q\n",
    "DROP TABLE AS_EMP;\n",
    "CREATE TABLE AS_EMP(DEPARTMENT, LASTNAME) AS \n",
    "  (SELECT WORKDEPT, LASTNAME FROM EMPLOYEE\n",
    "     OFFSET 5 ROWS\n",
    "     FETCH FIRST 10 ROWS ONLY\n",
    "  ) WITH DATA;\n",
    "SELECT * FROM AS_EMP;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "#### Credits: IBM 2017, George Baklarz [baklarz@ca.ibm.com]"
   ]
  }
 ],
 "metadata": {
  "hide_code_all_hidden": false,
  "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
}