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