<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Db2 OData Tutorial
This tutorial will explain some of the features that are available in the IBM Data Server Gateway for OData Version 1.0.0. IBM Data Server Gateway for OData enables you to quickly create OData RESTful services to query and update data in IBM Db2 LUW.    
An introduction to the OData gateway is found in the following developerWorks article
Step1:  Db2 Extensions
Since we are connecting to a Db2 database, the following command will load the Db2 Jupyter notebook extension (%sql). The Db2 extension allows you to fully interact with the Db2 database, including the ability to drop and create objects. The OData gateway provides INSERT, UPDATE, DELETE, and SELECT capability to the database, but it doesn't have the ability to create or drop actual objects. The other option would be to use Db2 directly on the database server using utilities like CLP (Command Line Processor) or DSM (Data Server Manager).
Step2:  <a id='top'></a>
Table of Contents
A Brief Introduction to Odata
<p>
* [Db2 and OData Connection Requirements](#connect)
* [Connecting through OData](#connectodata)
* [Set Command Syntax](#set)
<p>
* [A Quick Introduction](#quick)
  * [Selecting Data from a Table](#sampleselect)
  * [Displaying the OData Syntax](#viewodata)
  * [Limiting Output Results](#limit)
  * [Persistent Connection Information](#persistant)
  * [Variables in OData Statements](#variables)
  * [Retrieving URL, OData Command, and Parameters](#retrieveurl)
  * [JSON Display in Firefox](#firefox)
<p>
* [SQL Command Syntax](#sqlsyntax)
  * [SELECT Statements](#select)
    * [Selecting Columns to Display](#columns)
    * [FROM  Clause](#from)
    * [Describing the Table Structure](#describe)
    * [WHERE Clause](#where)
    * [LIMIT Clause](#limitclause)
  * [INSERT Statement](#insert)
  * [DELETE Statement](#delete)
  * [UPDATE Statement](#update)
  * [VIEWS](#views)
<p>
* [Summary](#summary)
[Back to Top](#top)
<a id='intro'></a>
## An Brief Introduction to OData
Rather than paraphrase what OData does, here is the official statement from the OData home page
Step3:  If you connected to the SAMPLE database, you will have the EMPLOYEE and DEPARTMENT tables available to you. However, if you are connecting to a different database, you will need to execute the next command to populate the tables for you. Note, if you run this command and the two tables already exist, the tables will not be replaced. So don't worry if you execute this command by mistake.
Step4:  Requesting data from Db2 using the standard %sql (ibm_db) interface is relatively straight-forward. We just need to place the SQL in the command and execute it to get the results.
Step5:  Now that we have a working Db2 connection, we will need to set up an OData service to talk to Db2.
Back to Top
<a id='connectodata'></a>
Connecting through OData
Connecting through OData requires a different approach than a Db2 client. We still need to ask a bunch of questions on how we connect to the database, but this doesn't create a connection from the client. Instead what we end up creating is a service URL. This URL gives us access to Db2 through the OData gateway server.
The OData Server take the URL request and maps it to a Db2 resource, which could be one or more tables. The RESTful API needs this URL to communicate with Db2 but nothing else (userids, passwords, etc...) are sent with the request.
The following %odata command will prompt you for the connection parameters, similar to what happened with the Db2 connect. There are a few differences however. The connection requires the userid and password of the user connecting to the database, and the userid and password of a user with administration (DBABM) privileges.
The administrative user creates the service connection that will be used to communicate through the OData gateway and Db2. The regular userid and password is for the actual user that will connect to the database to manipulate the tables. Finally we need to have the schema (or owner) of the tables that will be accessed. From a Db2 perspective, this is similar to connecting to a DATABASE (SAMPLE) as userid FRED. The EMPLOYEE table was created under the userid DB2INST1, so to access the table we need to use DB2INST1.EMPLOYEE. If we didn't include the schema (DB2INST1), the query would fail since FRED was not the owner of the table.
The %odata PROMPT command will request all of the connection parameters and explain what the various fields are. Note
Step6:  Back to Top
<a id='quick'></a>
A Quick Introduction
The following section will give you a quick introduction to using OData with Db2. More details on syntax and examples are found later on in the notebook.
Selecting Data from a Table
So far all we have done is set up the connection parameters, but no actual connection has been made to Db2, nor has an OData service been created. The creation of a service is done when the first SQL request is issued. The next statement will retrieve the values from our favorite EMPLOYEE table, but use OData to accomplish it.
Step7:  Back to Top
<a id='viewodata'></a>
Displaying the OData Syntax
Under the covers a number of things happened when running this command. The SELECT * FROM EMPLOYEE is not what is sent to OData. The syntax is converted to something that the RESTful API understands. To view the actual OData syntax, the -e option is used to echo back the commands.
Step8:  The results will show the URL service command used (http
Step9:  One drawback of OData is that we don't get the actual error text returned. We know that the error code is, but the message isn't that descriptive. Using the %sql (Db2) command, we can find out that the table doesn't exist.
Step10:  Back to Top
<a id='limit'></a>
Limiting Output Results
The results contain 43 rows. If you want to reduce the amount of rows being returned we can use the LIMIT clause on the SELECT statement. In addition, we can use the -j flag to return the data as JSON records.
Step11:  To limit the results from a OData request, you must add the \$top=x modifier at the end of the service request. The format then becomes
Step12:  The last example illustrates two additional features of the %odata command. First, you can span statements over multiple lines by using the backslash character ('\'). You could also use the %%odata command to do this without backslashes, but it unfortunately will not allow for variable substitution. The current settings being used by OData can be found by issuing the SETTINGS command.
You can specify the command with only the TABLE option and it will take the current DATABASE and SCHEMA names from any prior settings.
Step13:  You can also refer to these values by using the settings['name'] variable. So the DROP statement just took the current DATABASE and SCHEMA settings and deleted the definition for the EMPLOYEE table. You could have done this directly with
Step14:  And this command will show the connection service being created for us.
Step15:  Back to Top
<a id='retrieveurl'></a>
Retrieving URL, OData Command, and Parameters
The %odata command will return the URL command for a select statement as part of the command
Step16:  You can use this URL to directly access the results through a browser, or any application that can read the results returned by the OData gateway. The print statement below will display the URL as an active link. Click on that to see the results in another browser window.
Step17:  When a URL is generated, we need to append the \$format=json tag at the end to tell the OData service and the browser how to handle the results. When we run OData and RESTful calls from a programming language (like Python), we are able to send information in the header which tells the API how to handle the results and parameters. All of the RESTful calls to the OData gateway use the following header information
Step18:  Back to Top
<a id='select'></a>
SELECT Statements
The SELECT statement is the most complicated of the four statements that are allowed in OData. There are generally two forms that can be used when accessing a record. The first method uses the primary key of the table and it requires no arguments. Note that the examples will not show the URL that points to the OData service.
<pre>
/EMPLOYEES('000010')
</pre>
The second method is to use the \$filter query option. \$filter allows us to compare any column against a value. The equivalent OData statement for retrieving an individual employee is
Step19:  You will notice that not all of the rows have been displayed. The output has been limited to 10 lines. 5 lines from the start of the answer set and 5 lines from the bottom of the answer set are displayed. If you want to change the maximum number of rows to be displayed, use the MAXROWS setting.
Step20:  If you want an unlimited number of rows returned, set maxrows to -1.
Step21:  It is better to limit the results from the answer set by using the LIMIT clause in the SELECT statement. LIMIT will force Db2 to stop retrieving rows after "x" number have been read, while the MAXROWS setting will retrieve all rows and then only display a portion of them. The one advantage of MAXROWS is that you see the bottom 5 rows while you would only be able to do that with Db2 if you could reverse sort the output. The current OData implementation does not have the ability to $orderby, so sorting to reverse the output is not possible.
Step22:  Example
Step23:  Back to Top
<a id='columns'></a>
Selecting Columns to Display
OData allows you to select which columns to display as part of the output. The $select query option requires a list of columns to be passed to it. For instance, the following SQL will only display the first name and last name of the top five employees.
Example
Step24:  The COUNT(*) function is available as part of a SELECT list and it cannot include any other column names. If you do include other column names they will be ignored.
Step25:  One of the unusual behaviors of the COUNT(*) function is that will actually return the entire answer set under the covers. The %odata command strips the count out from the results and doesn't display the rows returned. That is probably not would you expect from this syntax! The COUNT function is better described as the count of physical rows returned. Here is the same example with 5 rows returned and the JSON records.
Step26:  One of the recommendations would be not to use the COUNT(*) function to determine the amount of rows that will be retrieved, especially if you expect there to a large of number rows. To minimize the data returned, you can use the form COUNT(column) which will modify the OData request to return the count and ONLY that column in the result set. This is a compromise in terms of the amount of data returned. This example using the -r (raw) flag which results in all of the JSON headers and data to be displayed. The JSON flag (-j) will not display any records.
Step27:  Back to Top
<a id='from'></a>
FROM Clause
The FROM clause is mandatory in any SELECT statement. If an OData service has already been established, there will be no service request sent to OData. Instead, the URL information stored on disk will be used to establish the connection.
If a service has not been established, the %odata command will create the service and then build the OData select statement. If you want to see the command to establish the service as well as the SELECT command, use the -e flag to echo the results.
If the table does not exist in the database you will receive an error message.
Step28:  This actually can cause some issues if you try to reuse the connection information that was created with the UNKNOWN_TBL. Since the service could not determine the structure of the table, the service will not return any column information with a select statement. The next SQL statement will create the UNKNOWN_TBL.
Step29:  Retrying the SELECT statement will result in 43 rows with no columns returned!
Step30:  To correct this situation, you need to DROP the connect that the %odata program is using and reissue the SELECT statement.
Step31:  Now you can try the SQL statement again.
Step32:  Back to Top
<a id='describe'></a>
Describing the Table Structure
The SELECT statement needs to know what columns are going to be returned as part of the answer set. The asterix (*) returns all of the columns, but perhaps you only want a few of the columns. To determine what the columns are in the table along with the data types, you can use the DESCRIBE command. The following statement will show the structure of the EMPLOYEE table.
Step33:  The datatypes are not the same as what one expect from a relational database. You get generic information on the character columns (String), and the numbers (Int16, Decimal). The Decimal specification actually contains the number of digits and decimal places but that isn't returned when using the table display.
| Data Type  |   Contents
|
Step34:  Example
Step35:  Example
Step36:  Example
Step37:  Example
Step38:  Example
Step39:  Converting to OData will mean that the search will look across the entire string, not just the beginning.
Step40:  Back to Top
<a id='limitclause'></a>
Limit Clause
The LIMIT clause was discussed earlier in this notebook. LIMIT allows you to reduce the amount of rows that are returned in the answer set. The LIMIT clause is similar to FETCH FIRST x ROWS ONLY in Db2. The rows are always taken from the beginning of the answer set so there is no way to skip "x" rows before getting results. The facility does exist in the OData spaecification but has not been implemented in this release.
The LIMIT clause also works in conjunction with the %odata command. The default number of rows that are displayed in a table (result set) is set to 10 by default. So if you have 50 rows in your answer set, the first 5 are displayed and then the last 5 with the rows inbetween are hidden from view. If you want to see the entire answer set, you need to change the MAXROWS value to -1
Step41:  Back to Top
<a id='insert'></a>
INSERT Command
OData allows you to insert data into a table through the use of the RESTful POST command and a JSON document that contains the field names and contents of those fields.
The format of the INSERT command is
Step42:  We also need to remove the connection information from the system in the event we've run this example before.
Step43:  A couple of things about the table design. The salary is NOT NULL, while the BONUS allows for nulls. Unfortunately, the DESCRIBE command only tells us about the columns in the table and their OData data type, and no indication of whether table.
Step44:  The initial INSERT will populate the table with valid data. The echo option will show the json document that is sent via the POST command to OData to insert the row.
Step45:  Just to make sure things were inserted properly, we retrieve the contents of the table.
Step46:  OData (and Db2) will return an error message about our missing SALARY column which requires a value.
Step47:  We can try this on the Db2 side as well to get the details of the error.
Step48:  Back to Top
<a id='delete'></a>
DELETE Command
The DELETE command only takes one parameter and that is the key value for the record that we want to delete from the table. The format of the command is
Step49:  A primary key is required to issue a DELETE command. You also need to make sure that the primary key column does not contain NULLs because a primary key must always contain a value. The following SQL tries to fix the primary key issue.
Step50:  Check to see if we can delete the row yet.
Step51:  Adding a primary key after the fact won't help because the service URL would have already recorded the information about the table (and the fact it didn't have a primary key at the time). We need to drop our SERVICE URL and generate another one.
Step52:  We do a describe on the table and this will force another service URL to be generated for us.
Step53:  Trying the DELETE this time will work.
Step54:  Deleting the record again still gives you a successful return code. The call always returns a successful status even if the record doesn't exist.
Step55:  Back to Top
<a id='update'></a>
UPDATE Command
The update command requires both a primary key to update and the name of the field that you want changed. Note that you can only change one field at a time. There is no ability to specify multiple fields at this time.
The format of the UDPATE command is
Step56:  At this point we can update their salary.
Step57:  We doublecheck the results to make sure we got it right!
Step58:  Back to Top
<a id='views'></a>
Views
The OData implemented with Db2 doesn't allow for JOINS between tables. Sometimes you need to be able to look up information from another table in order to get the final result. One option you have to do this is to create a VIEW on the Db2 system.
The VIEW can contain almost any type of SQL so it allows for very complex queries to be created. For instance, the following view joins the EMPLOYEE table and the DEPARTMENT table to generate a row with the employee name and the name of the department that they work for.
Step59:  We also need to drop any service connection you may have created in the past with this table name.
Step60:  Now that we have created the view, we can retrieve rows from it just like a standard table.
Step61:  You can also create sophisticated VIEWS that can take parameters to adjust the results returned. For instance, consider the following SQL statement which gives me count of employees that work in SYSTEMS departments.
Step62:  There are two departments with the name SYSTEMS in them, but there is no easy way to create a view for every possible combination of searches that you may want. Instead what we do is create a table that contains the pattern we want to look for and create the view so that it references this table.
The first step is to create our PATTERN table. Note we make sure it has a primary key so our OData update calls can change it!
Step63:  Now we create a view that access this PATTERN table to do the actual search. Note that values that are inserted into the PATTERN table must have the SQL special characters like % to make sure patterns can be anywhere in the string.
Step64:  In order for our view to work properly, we must populate our PATTERN table with a value. To test the view we will use %SYSTEMS% as our first example.
Step65:  And now we can test our view by selecting from it.
Step66:  Now that we have it working, we can try exactly the same thing but with OData. Our first transaction will update the search key to SERVICE.
Step67:  The next OData statement should select the count of employees working in service departments.
<ASSISTANT_TASK:>
Python Code:
%run db2odata.ipynb
Explanation: Db2 OData Tutorial
This tutorial will explain some of the features that are available in the IBM Data Server Gateway for OData Version 1.0.0. IBM Data Server Gateway for OData enables you to quickly create OData RESTful services to query and update data in IBM Db2 LUW.    
An introduction to the OData gateway is found in the following developerWorks article:
https://www.ibm.com/developerworks/community/blogs/96960515-2ea1-4391-8170-b0515d08e4da/entry/IBM_Data_Server_Gateway_for_OData_Version_1_0_0?lang=en
The code can be obtained through the following link:
https://www-945.ibm.com/support/fixcentral/swg/selectFixes?parent=ibm~Information%2BManagement&product=ibm/Information+Management/IBM+Data+Server+Client+Packages&release=11.1.&platform=Linux&function=fixId&fixids=odataFP001&includeSupersedes=0&source=fc
OData Extensions for Db2
In order to help explain some of the features of the OData Gateway, a Jupyter notebook has been created that includes an %odata command that maps Db2 SQL into the equivalent OData syntax. The next command will load the extension and make the command available to this tutorial.
End of explanation
%run db2.ipynb
Explanation: Db2 Extensions
Since we are connecting to a Db2 database, the following command will load the Db2 Jupyter notebook extension (%sql). The Db2 extension allows you to fully interact with the Db2 database, including the ability to drop and create objects. The OData gateway provides INSERT, UPDATE, DELETE, and SELECT capability to the database, but it doesn't have the ability to create or drop actual objects. The other option would be to use Db2 directly on the database server using utilities like CLP (Command Line Processor) or DSM (Data Server Manager).
End of explanation
%sql connect reset
%sql connect
Explanation: <a id='top'></a>
Table of Contents
A Brief Introduction to Odata
<p>
* [Db2 and OData Connection Requirements](#connect)
* [Connecting through OData](#connectodata)
* [Set Command Syntax](#set)
<p>
* [A Quick Introduction](#quick)
  * [Selecting Data from a Table](#sampleselect)
  * [Displaying the OData Syntax](#viewodata)
  * [Limiting Output Results](#limit)
  * [Persistent Connection Information](#persistant)
  * [Variables in OData Statements](#variables)
  * [Retrieving URL, OData Command, and Parameters](#retrieveurl)
  * [JSON Display in Firefox](#firefox)
<p>
* [SQL Command Syntax](#sqlsyntax)
  * [SELECT Statements](#select)
    * [Selecting Columns to Display](#columns)
    * [FROM  Clause](#from)
    * [Describing the Table Structure](#describe)
    * [WHERE Clause](#where)
    * [LIMIT Clause](#limitclause)
  * [INSERT Statement](#insert)
  * [DELETE Statement](#delete)
  * [UPDATE Statement](#update)
  * [VIEWS](#views)
<p>
* [Summary](#summary)
[Back to Top](#top)
<a id='intro'></a>
## An Brief Introduction to OData
Rather than paraphrase what OData does, here is the official statement from the OData home page:
http://www.odata.org/
>OData (Open Data Protocol) is an ISO/IEC approved, OASIS standard that defines a set of best practices for building and consuming RESTful APIs. OData helps you focus on your business logic while building RESTful APIs without having to worry about the various approaches to define request and response headers, status codes, HTTP methods, URL conventions, media types, payload formats, query options, etc. OData also provides 
guidance for tracking changes, defining functions/actions for reusable procedures, and sending asynchronous/batch requests.
### Why OData for Db2?
Customers have a wealth of data in their databases (not just Db2) and publishing it to different devices is often fraught with many challenges. Db2 requires the use of client code to communicate between the application and the database itself. Many of APIs that are used are well known: JDBC, .Net, ODBC, OLE-DB, CLI and so on. Most programming languages have some sort of connector that maps from the language syntax to the database driver. When a new language gets developed it always needs this driver code to talk to the database. For instance, this Python notebook is communicating to Db2 natively using the ibm_db package. Without some specialized coding, there would be no way to communicate with Db2.
OData tries to remove much of the complexity of communicating with the database. There are no drivers required, no configuration file, nor any administration required on the client that is communicating with the database. All communication is done using RESTful API calls, which are available on all browsers and all operating systems. The calls to the database are replaced with standard POST, GET, DELETE, PUT and PATCH requests.
OData goes one step further and removes the syntactical differences between SQL vendors. The INSERT, DELETE, UPDATE and SELECT statements are coverted to a canonical form that should be interpreted by all vendors. Of course, interoperability depends on how much of the standard a vendor has implemented.
The end result is that an Iphone, Andriod phone, tablet, browser or any application will be able to access the database without having any code installed locally. This simplifies the ability to access the database and makes development considerably easier.
The downside to this approach is that the richness of a particular SQL dialect will not be available through OData. Complex SQL with aggregation functions and moving result windows are not a good candidate to use with OData. However, OData covers much of the query spectrum that traditional applications will use, so it makes it a good choice for agile development.
### OData to Db2 Extension
Writing OData calls to Db2 requires a knowledge of the OData syntax, the RESTful calling sequence, and an understanding of the level of support of OData that Db2 provides. This tutorial will take you through all of the functions that the OData gateway currently provides and show how these calls are implemented. Feel free to use the code and extensions in your own applications.
[Back to Top](#top)
<a id='connect'></a>
## Db2 and OData Connection Requirements
Both the Db2 client and OData calls need connection information. The way that you go about connecting to the database is completely different between these two protocols. Let first start with the Db2 connection.
Db2 requires a userid and password to connect to a database (along with the client code that talks to Db2 over the network). Assuming you have a Db2 database somewhere, the next command will ask you for the following information:
 * DATABASE name - The name of the Db2 database you want to connect to
 * HOST ipaddress - The IP address (or localhost) where the Db2 instance can be found
 * PORT portno - The PORT number that Db2 is listening to (usually 50000)
 * USER userid - The user that will be connecting to Db2
 * PASSWORD pwd - The password for the USER (use a "?" to prompt for the value)
You need to have this information available or the program won't be able to connect. For demonstration purposes, the standard SAMPLE database should be used but in the event you don't have that created, the %sql command will generate the necessary tables for you. It is also good to be a DBADM (database administrator) on the system you are connecting to. This will allow you to create the services requires by the OData gateway. If you don't, someone with that authority will be needed to give you access through OData.
When the next set of commands is issued, the system will prompt you for the information required as well as give you the details for each of the fields.
End of explanation
%sql -sampledata
Explanation: If you connected to the SAMPLE database, you will have the EMPLOYEE and DEPARTMENT tables available to you. However, if you are connecting to a different database, you will need to execute the next command to populate the tables for you. Note, if you run this command and the two tables already exist, the tables will not be replaced. So don't worry if you execute this command by mistake.
End of explanation
%sql SELECT * FROM EMPLOYEE
Explanation: Requesting data from Db2 using the standard %sql (ibm_db) interface is relatively straight-forward. We just need to place the SQL in the command and execute it to get the results.
End of explanation
%odata register
Explanation: Now that we have a working Db2 connection, we will need to set up an OData service to talk to Db2.
Back to Top
<a id='connectodata'></a>
Connecting through OData
Connecting through OData requires a different approach than a Db2 client. We still need to ask a bunch of questions on how we connect to the database, but this doesn't create a connection from the client. Instead what we end up creating is a service URL. This URL gives us access to Db2 through the OData gateway server.
The OData Server take the URL request and maps it to a Db2 resource, which could be one or more tables. The RESTful API needs this URL to communicate with Db2 but nothing else (userids, passwords, etc...) are sent with the request.
The following %odata command will prompt you for the connection parameters, similar to what happened with the Db2 connect. There are a few differences however. The connection requires the userid and password of the user connecting to the database, and the userid and password of a user with administration (DBABM) privileges.
The administrative user creates the service connection that will be used to communicate through the OData gateway and Db2. The regular userid and password is for the actual user that will connect to the database to manipulate the tables. Finally we need to have the schema (or owner) of the tables that will be accessed. From a Db2 perspective, this is similar to connecting to a DATABASE (SAMPLE) as userid FRED. The EMPLOYEE table was created under the userid DB2INST1, so to access the table we need to use DB2INST1.EMPLOYEE. If we didn't include the schema (DB2INST1), the query would fail since FRED was not the owner of the table.
The %odata PROMPT command will request all of the connection parameters and explain what the various fields are. Note: If you have DBADM privileges (and you created the sample tables yourself), you can leave the USERID/PASSWORD/SCHEMA values blank and they will default to the administrative user values.
Back to Top
<a id='set'></a>
End of explanation
%odata RESET TABLE EMPLOYEE
s = %odata -e SELECT lastname, salary from employee where salary > 50000
Explanation: Back to Top
<a id='quick'></a>
A Quick Introduction
The following section will give you a quick introduction to using OData with Db2. More details on syntax and examples are found later on in the notebook.
Selecting Data from a Table
So far all we have done is set up the connection parameters, but no actual connection has been made to Db2, nor has an OData service been created. The creation of a service is done when the first SQL request is issued. The next statement will retrieve the values from our favorite EMPLOYEE table, but use OData to accomplish it.
End of explanation
s = %odata -e SELECT * FROM EMPLOYEE
Explanation: Back to Top
<a id='viewodata'></a>
Displaying the OData Syntax
Under the covers a number of things happened when running this command. The SELECT * FROM EMPLOYEE is not what is sent to OData. The syntax is converted to something that the RESTful API understands. To view the actual OData syntax, the -e option is used to echo back the commands.
End of explanation
%odata select * from unknown_table
Explanation: The results will show the URL service command used (http:// followed by details of the host location and service ID) and the OData command. In this case the command should be /EMPLOYEES. This may seem like a spelling mistake, but the OData service creates a mapping from the database table (EMPLOYEE) to a service request. To give the service request a unique name, the letter "S" is appended to the table name. Do not confuse the service 
name with the table name. That can sometimes lead to coding errors!
If we tried to request a table that didn't exist in the database, we would get an error message instead.
End of explanation
%sql select * from unknown_table
Explanation: One drawback of OData is that we don't get the actual error text returned. We know that the error code is, but the message isn't that descriptive. Using the %sql (Db2) command, we can find out that the table doesn't exist.
End of explanation
s = %odata -e -j SELECT * FROM EMPLOYEE LIMIT 1
Explanation: Back to Top
<a id='limit'></a>
Limiting Output Results
The results contain 43 rows. If you want to reduce the amount of rows being returned we can use the LIMIT clause on the SELECT statement. In addition, we can use the -j flag to return the data as JSON records.
End of explanation
%odata \
  RESET \
    DATABASE {odata_settings['database']} \
    SCHEMA   {odata_settings['schema']} \
    TABLE    EMPLOYEE
Explanation: To limit the results from a OData request, you must add the \$top=x modifier at the end of the service request. The format then becomes:
<pre>
  \[service url\]/\[service name\]?&#36;top=value
</pre>
You will notice that the OData syntax requires that a "?" be placed after the name of the service. In our  example, EMPLOYEES is the name of the service that accesses the EMPLOYEE table. We add the ? after the end of the service name and then add the $top modifier. If there were multiple modifiers, each one must be separated with an ampersand (&) symbol. 
Back to Top
<a id='persistant'></a>
Persistent Connection Information
What you should have received when running the previous command was a single JSON record, the service URL and the OData command. The URL will be identical to the one in the previous %odata request. There is no need to recreate a service if you are using the same table. The program created a new service when you did a SELECT for the first time. After that it keeps the service information in a file called TABLE@SCHEMA@DATABASE.pickle in the directory where the Jupyter notebook is running. If you try this statement at another time, this service URL will be retrieved from this file rather than creating another service.
Dropping a Connection
If you want to delete the connection information, use the RESET command with the database, schema, and table name in it. This doesn't drop the object or anything associated with the table. All this does is remove the service information from your system. It also does not remove the service from the OData gateway.
End of explanation
%odata settings
Explanation: The last example illustrates two additional features of the %odata command. First, you can span statements over multiple lines by using the backslash character ('\'). You could also use the %%odata command to do this without backslashes, but it unfortunately will not allow for variable substitution. The current settings being used by OData can be found by issuing the SETTINGS command.
You can specify the command with only the TABLE option and it will take the current DATABASE and SCHEMA names from any prior settings.
End of explanation
%odata set DATABASE {odata_settings['database']} SCHEMA {odata_settings['schema']}
Explanation: You can also refer to these values by using the settings['name'] variable. So the DROP statement just took the current DATABASE and SCHEMA settings and deleted the definition for the EMPLOYEE table. You could have done this directly with:
<pre>
RESET DATABASE SAMPLE SCHEMA DB2INST1 TABLE EMPLOYEE
</pre>
The list of settings and their variable names are listed below.
| Setting       | Variable name      
|:--------------|:---------------------
| DATABASE      | odata_settings['database']
| SCHEMA        | odata_settings['schema']
| ADMIN         | odata_settings['admin']
| A_PWD         | odata_settings['a_pwd']
| USER          | odata_settings['userid']
| U_PWD         | odata_settings['u_pwd']
| HOST          | odata_settings['host']
| PORT          | odata_settings['port']
| MAXROWS       | odata_settings['maxrows']
Back to Top
<a id='variables'></a>
Variables in %OData Statements
To use local Jupyter/Python variables in a notebook, all you need to do is place braces {} around the name of the variable. Before we illustrate this, we need to create another connection (since we just dropped it in the last example). Fortunately, none of the settings have been removed, so we still have the connection information (DATABASE, SCHEMA, ...) available. 
In the event you have closed the notebook and started up from scratch, there is no need to do a full connect command (or prompt). The settings are automatically written to disk and then restored when you start up another session. If you want to connect to another database then you will need to use the following SET statement.
End of explanation
u = %odata -e select * from employee limit 1
Explanation: And this command will show the connection service being created for us.
End of explanation
url = %odata -e select * from employee limit 1
Explanation: Back to Top
<a id='retrieveurl'></a>
Retrieving URL, OData Command, and Parameters
The %odata command will return the URL command for a select statement as part of the command:
<pre>
&lt;url&gt; = %odata -e select * from employee limit 1
</pre>
The variable "url" will contain the full URL required to retrieve data from the OData service. The next command illustrates how this works. You must use the echo (-e) option to get the URL returned. Note that you cannot use this syntax with the %%odata version of the command.
End of explanation
print(url)
Explanation: You can use this URL to directly access the results through a browser, or any application that can read the results returned by the OData gateway. The print statement below will display the URL as an active link. Click on that to see the results in another browser window.
End of explanation
%odata delete
Explanation: When a URL is generated, we need to append the \$format=json tag at the end to tell the OData service and the browser how to handle the results. When we run OData and RESTful calls from a programming language (like Python), we are able to send information in the header which tells the API how to handle the results and parameters. All of the RESTful calls to the OData gateway use the following header information:
<pre>
  {
  "Content-Type":"application/json",
  "Accept":"application/json"
  }
</pre>
<br>When we send the URL to the OData gateway, it needs to be told how to return the information. We need to append the $format=json flag at the end of our query when sending the request via a browser. Note that the ampersand must be appended to the end of the existing URL since we already have one parameter in it.
Back to Top
<a id='firefox'></a>
JSON Display in Firefox
Depending on what version of Firefox you have, you may not get the JSON to be displayed very nicely. To use the built-in JSON formatter, issue the following commands in a separate browser window:
<pre>
about:config
Search for devtools.jsonview.enabled
</pre>
<br>Right click on the jsonview setting and enable it. This will result in the JSON being easier to view.
Back to Top
<a id='sqlsyntax'></a>
SQL Command Syntax
The %odata command has been designed to translate the SQL syntax for INSERT, DELETE, UPDATE, and SELECT into an equivalent OData format. There are very specific ways of requesting data from OData, so this ends up placing some limitations on what SQL you can use. This section will cover the four major SQL commands and how they can be used with OData. If you need the syntax for a particular SQL command, just enter the command name by itself on the %odata line and it will give you a brief summary of the syntax. Here is the DELETE help.
End of explanation
s = %odata -e SELECT * FROM EMPLOYEE
Explanation: Back to Top
<a id='select'></a>
SELECT Statements
The SELECT statement is the most complicated of the four statements that are allowed in OData. There are generally two forms that can be used when accessing a record. The first method uses the primary key of the table and it requires no arguments. Note that the examples will not show the URL that points to the OData service.
<pre>
/EMPLOYEES('000010')
</pre>
The second method is to use the \$filter query option. \$filter allows us to compare any column against a value. The equivalent OData statement for retrieving an individual employee is:
<pre>
/EMPLOYEES?$filter=EMPNO eq '000010'
</pre>
The generated SELECT statements will always use this format, rather than relying on a primary key. This becomes more important when we deal with Views.
SELECT Syntax
The SELECT command will return data from one table. There is no ability to join tables with the current implementation of OData. If you do want to join tables, you may want to create a VIEW on the Db2 system and then use that as the TABLE. This will allow for SELECT, but no INSERT/DELETE/UPDATE.
You do not need to use the primary key in the WHERE clause to use this statement. By default, any results will be displayed in a table. If you want to retrieve the results as JSON records, use the -j option on the %odata command. 
<pre>
SELECT \[col1, col2, ... | count(\*)\] FROM &lt;table&gt; \[ WHERE logic\] \[ LIMIT rows \]
</pre>
The column list can contain as many values as you want, or just COUNT(*). COUNT(*) will return the count of rows found. If you use the -r or -j flags to display everything in JSON format, you will also get the entire answer set along with the row count. This is the behavior of using count in OData.
The FROM clause must contain the name of the table you want to access. 
The WHERE clause is optional, as is the LIMIT clause. The WHERE clause can contain comparisons between columns and constants (EMPNO='000010'), logic (AND, OR) as well as LIKE clauses (COLUMN LIKE 'xxx'). The current version cannot use arithmetic operators (+, -, *, /) or the NOT operator.
The LIMIT clause will restrict the results to "x" number of rows. So even if there are 500 rows that meet the answer set, only "x" rows will be returned to the client.
Example: Select statement with no logic
The following SELECT statement will retrieve all of the data from the EMPLOYEE table.
End of explanation
%odata set maxrows 10
Explanation: You will notice that not all of the rows have been displayed. The output has been limited to 10 lines. 5 lines from the start of the answer set and 5 lines from the bottom of the answer set are displayed. If you want to change the maximum number of rows to be displayed, use the MAXROWS setting.
End of explanation
%odata set maxrows -1
%odata select * from employee
Explanation: If you want an unlimited number of rows returned, set maxrows to -1.
End of explanation
%odata set maxrows 10
Explanation: It is better to limit the results from the answer set by using the LIMIT clause in the SELECT statement. LIMIT will force Db2 to stop retrieving rows after "x" number have been read, while the MAXROWS setting will retrieve all rows and then only display a portion of them. The one advantage of MAXROWS is that you see the bottom 5 rows while you would only be able to do that with Db2 if you could reverse sort the output. The current OData implementation does not have the ability to $orderby, so sorting to reverse the output is not possible.
End of explanation
s = %odata -e SELECT * FROM EMPLOYEE LIMIT 5
Explanation: Example: Select statement limiting output to 5 rows
This SELECT statement will limit output to 5 rows. If MAXROWS was set to a smaller value, it would still read all rows before displaying them.
End of explanation
s = %odata -e SELECT FIRSTNME, LASTNAME FROM EMPLOYEE LIMIT 5
Explanation: Back to Top
<a id='columns'></a>
Selecting Columns to Display
OData allows you to select which columns to display as part of the output. The $select query option requires a list of columns to be passed to it. For instance, the following SQL will only display the first name and last name of the top five employees.
Example: Limiting the columns to display
The column list must only include columns from the table and cannot include any calculations like SALARY+BONUS.
End of explanation
s = %odata -e SELECT COUNT(*) FROM EMPLOYEE LIMIT 1
Explanation: The COUNT(*) function is available as part of a SELECT list and it cannot include any other column names. If you do include other column names they will be ignored.
End of explanation
s = %odata -e -r SELECT COUNT(*) FROM EMPLOYEE LIMIT 5
Explanation: One of the unusual behaviors of the COUNT(*) function is that will actually return the entire answer set under the covers. The %odata command strips the count out from the results and doesn't display the rows returned. That is probably not would you expect from this syntax! The COUNT function is better described as the count of physical rows returned. Here is the same example with 5 rows returned and the JSON records.
End of explanation
s = %odata -e -r SELECT COUNT(EMPNO) FROM EMPLOYEE LIMIT 5
Explanation: One of the recommendations would be not to use the COUNT(*) function to determine the amount of rows that will be retrieved, especially if you expect there to a large of number rows. To minimize the data returned, you can use the form COUNT(column) which will modify the OData request to return the count and ONLY that column in the result set. This is a compromise in terms of the amount of data returned. This example using the -r (raw) flag which results in all of the JSON headers and data to be displayed. The JSON flag (-j) will not display any records.
End of explanation
%sql -q DROP TABLE UNKNOWN_TBL
%odata RESET TABLE UNKNOWN_TBL
s = %odata -e SELECT * FROM UNKNOWN_TBL
Explanation: Back to Top
<a id='from'></a>
FROM Clause
The FROM clause is mandatory in any SELECT statement. If an OData service has already been established, there will be no service request sent to OData. Instead, the URL information stored on disk will be used to establish the connection.
If a service has not been established, the %odata command will create the service and then build the OData select statement. If you want to see the command to establish the service as well as the SELECT command, use the -e flag to echo the results.
If the table does not exist in the database you will receive an error message.
End of explanation
%sql CREATE TABLE UNKNOWN_TBL AS (SELECT * FROM EMPLOYEE) WITH DATA
Explanation: This actually can cause some issues if you try to reuse the connection information that was created with the UNKNOWN_TBL. Since the service could not determine the structure of the table, the service will not return any column information with a select statement. The next SQL statement will create the UNKNOWN_TBL.
End of explanation
s = %odata -e SELECT * FROM UNKNOWN_TBL
Explanation: Retrying the SELECT statement will result in 43 rows with no columns returned!
End of explanation
%odata RESET TABLE UNKNOWN_TBL
Explanation: To correct this situation, you need to DROP the connect that the %odata program is using and reissue the SELECT statement.
End of explanation
s = %odata -e SELECT * FROM UNKNOWN_TBL
Explanation: Now you can try the SQL statement again.
End of explanation
%odata DESCRIBE EMPLOYEE
Explanation: Back to Top
<a id='describe'></a>
Describing the Table Structure
The SELECT statement needs to know what columns are going to be returned as part of the answer set. The asterix (*) returns all of the columns, but perhaps you only want a few of the columns. To determine what the columns are in the table along with the data types, you can use the DESCRIBE command. The following statement will show the structure of the EMPLOYEE table.
End of explanation
s = %odata -e SELECT EMPNO, WORKDEPT, SALARY FROM EMPLOYEE WHERE SALARY < 40000
Explanation: The datatypes are not the same as what one expect from a relational database. You get generic information on the character columns (String), and the numbers (Int16, Decimal). The Decimal specification actually contains the number of digits and decimal places but that isn't returned when using the table display.
| Data Type  |   Contents
|:-----------|:---------------
| Binary     |   Binary data
| Boolean    |   Binary-valued logic
| Byte       |   Unsigned  8-bit integer
| Date       |   Date without a time-zone offset
| Decimal    |   Numeric values with fixed precision and scale
| Double     |   IEEE 754 binary64 floating-point number (15-17 decimal digits)
| Duration   |   Signed duration in days, hours, minutes, and (sub)seconds
| Guid       |   16-byte (128-bit) unique identifier
| Int16      |   Signed 16-bit integer
| Int32      |   Signed 32-bit integer
| Int64      |   Signed 64-bit integer
| SByte      |   Signed 8-bit integer
| Single     |   IEEE 754 binary32 floating-point number (6-9 decimal digits)
| String     |   Sequence of UTF-8 characters
| TimeOfDay  |   Clock time 00:00-23:59:59.999999999999
Back to Top
<a id='where'></a>
WHERE Clause
The WHERE clause is used to filter out the rows that you want to retrieve from the table. The WHERE clause allows the following operators:
&gt;, =&gt;, &lt;, &lt;=, =, !=, <>, LIKE
AND, OR
Parenthesis to override order () of operators
The WHERE clause does not allow for mathematical operators at this time (*, -, +, /) or the unary NOT or "-" operators. 
The LIKE clause can contain the special % character, but the equivalent OData syntax always searches the entire string and does not anchor at the beginning of the string. What this means is that the LIKE clause will turn into a search of the entire string whether you use the % character in your search string or not.
Example: Single comparison
The following select statement will search for employees who have a salary less than 40000.
End of explanation
s = %odata -e SELECT EMPNO, WORKDEPT, SALARY FROM EMPLOYEE WHERE SALARY < 40000 AND WORKDEPT = 'E21'
Explanation: Example: Two comparisons in a WHERE clause
We add an additional comparison to our SQL to check for only employees in a particular department.
End of explanation
s = %odata -e \
SELECT EMPNO, WORKDEPT, SALARY \
       FROM EMPLOYEE \
WHERE SALARY < 40000 AND WORKDEPT = 'E21' OR WORKDEPT = 'E11'
Explanation: Example: OR Logic in the WHERE clause
We add some additional complexity by requesting employees who are in department E11 as well as those who make less than 40000 and work in department E21.
End of explanation
s = %odata -e \
SELECT EMPNO, WORKDEPT, SALARY \
       FROM EMPLOYEE \
WHERE SALARY < 40000 AND (WORKDEPT = 'E21' OR WORKDEPT = 'E11')
Explanation: Example: Overriding the order of comparisons
You can override the order of comparisons in the WHERE clause by using parenthesis. Here we are asking for employees in department E21 or E11 and have a salary less than 40000.
End of explanation
s = %odata -e SELECT LASTNAME FROM EMPLOYEE WHERE LASTNAME LIKE '%AA%'
Explanation: Example: Using a LIKE clause
The LIKE clause in Db2 will look for a string within a character column. Normally the LIKE statement will allow for the use of the % (wildcard) and _ (one character match) operators to look for patterns. These special characters do not exist in OData, so the %odata command will remove the % character and convert it to an equivalent OData statement. What this means is that the string search will look at the entire string for the pattern, while LIKE can be anchored to look only at the beginning of the string. This capability does not  current exist with the current OData implementation.
Example: Search for a lastname that has 'AA' in it.
This SQL will look for a lastname that has the string 'AA' in it.
End of explanation
%sql SELECT LASTNAME FROM EMPLOYEE WHERE LASTNAME LIKE '%ON'
Explanation: Example: Beginning of string search
In SQL, you can search for a name that ends with the letters ON by using LIKE '%ON'
End of explanation
s = %odata -e SELECT LASTNAME FROM EMPLOYEE WHERE LASTNAME LIKE '%ON'
Explanation: Converting to OData will mean that the search will look across the entire string, not just the beginning.
End of explanation
s = %odata -e SELECT * FROM EMPLOYEE LIMIT 5
Explanation: Back to Top
<a id='limitclause'></a>
Limit Clause
The LIMIT clause was discussed earlier in this notebook. LIMIT allows you to reduce the amount of rows that are returned in the answer set. The LIMIT clause is similar to FETCH FIRST x ROWS ONLY in Db2. The rows are always taken from the beginning of the answer set so there is no way to skip "x" rows before getting results. The facility does exist in the OData spaecification but has not been implemented in this release.
The LIMIT clause also works in conjunction with the %odata command. The default number of rows that are displayed in a table (result set) is set to 10 by default. So if you have 50 rows in your answer set, the first 5 are displayed and then the last 5 with the rows inbetween are hidden from view. If you want to see the entire answer set, you need to change the MAXROWS value to -1:
<pre>
%odata SET MAXROWS -1
</pre>
This will display all rows that are returned from the answer set. However, the number of rows actually returned in the anwer set will be determined by the LIMIT clause. If you set LIMIT 5 then only five rows will be returned no matter what MAXROWS is set to. On the other hand, if you set MAXROWS to 10 and LIMIT to 20, you will get 20 rows returned but only 10 will be displayed.
Example: Limit result to 5 rows
This SQL will retrieve only the top 5 rows of the EMPLOYEE table.
End of explanation
%%sql -q 
DROP TABLE TESTODATA; 
CREATE TABLE TESTODATA 
  ( 
  EMPNO INT NOT NULL, 
  LASTNAME VARCHAR(10) NOT NULL,
  SALARY INT NOT NULL,
  BONUS INT
  );
%sql select * from testodata
%odata -e select * from testodata
Explanation: Back to Top
<a id='insert'></a>
INSERT Command
OData allows you to insert data into a table through the use of the RESTful POST command and a JSON document that contains the field names and contents of those fields.
The format of the INSERT command is:
<pre>
INSERT INTO &lt;table&gt;(col1, col2, ...) VALUES (val1, val2, ...)
</pre>
The TABLE must be defined before you can issue this statement. There is no requirement to have a primary key on the table, but this will prevent you from updating it with the OData interface because filtering (WHERE) is not allowed on UPDATEs or DELETEs. The column list and value list must match (i.e. there must be a value for every column name). If you do not supply the list of all columns in the table, the missing columns will have null values assigned to them. The insert will fail if any of these missing columns requires a value (NOT NULL).
Example: Insert into a table
In this example we will insert a single row into a table. We start by defining the table within Db2 and then doing a DESCRIBE to get the column definitions back with OData.
End of explanation
%odata RESET TABLE TESTODATA
Explanation: We also need to remove the connection information from the system in the event we've run this example before.
End of explanation
%odata -e DESCRIBE TESTODATA
Explanation: A couple of things about the table design. The salary is NOT NULL, while the BONUS allows for nulls. Unfortunately, the DESCRIBE command only tells us about the columns in the table and their OData data type, and no indication of whether table.
End of explanation
%odata -e INSERT INTO TESTODATA(EMPNO, LASTNAME, SALARY, BONUS) VALUES (1,'Fred',10000,1000)
Explanation: The initial INSERT will populate the table with valid data. The echo option will show the json document that is sent via the POST command to OData to insert the row.
End of explanation
%odata SELECT * FROM TESTODATA
Explanation: Just to make sure things were inserted properly, we retrieve the contents of the table.
End of explanation
%odata -e INSERT INTO TESTODATA(EMPNO, LASTNAME, BONUS) VALUES (2,'Wilma',50000)
Explanation: OData (and Db2) will return an error message about our missing SALARY column which requires a value.
End of explanation
%sql INSERT INTO TESTODATA(EMPNO, LASTNAME, BONUS) VALUES (2,'Wilma',50000)
Explanation: We can try this on the Db2 side as well to get the details of the error.
End of explanation
%odata -e DELETE FROM TESTODATA WHERE EMPNO=1
Explanation: Back to Top
<a id='delete'></a>
DELETE Command
The DELETE command only takes one parameter and that is the key value for the record that we want to delete from the table. The format of the command is:
<pre>
DELETE FROM &lt;table&gt; WHERE KEY=VALUE
</pre>
Key refers to the column that is the primary key in the table we are deleting from. Unless you have a primary key, the DELETE command will not work.
End of explanation
%sql ALTER TABLE TESTODATA ADD CONSTRAINT PKTD PRIMARY KEY (EMPNO)
Explanation: A primary key is required to issue a DELETE command. You also need to make sure that the primary key column does not contain NULLs because a primary key must always contain a value. The following SQL tries to fix the primary key issue.
End of explanation
%odata -e DELETE FROM TESTODATA WHERE EMPNO=1
Explanation: Check to see if we can delete the row yet.
End of explanation
%odata RESET TABLE TESTODATA
Explanation: Adding a primary key after the fact won't help because the service URL would have already recorded the information about the table (and the fact it didn't have a primary key at the time). We need to drop our SERVICE URL and generate another one.
End of explanation
%odata DESCRIBE TESTODATA
Explanation: We do a describe on the table and this will force another service URL to be generated for us.
End of explanation
%odata -e DELETE FROM TESTODATA WHERE EMPNO=1
Explanation: Trying the DELETE this time will work.
End of explanation
%odata -e DELETE FROM TESTODATA WHERE EMPNO=2
Explanation: Deleting the record again still gives you a successful return code. The call always returns a successful status even if the record doesn't exist.
End of explanation
%odata -e \
INSERT INTO TESTODATA(EMPNO, LASTNAME, SALARY, BONUS) \
VALUES (1,'Fred',10000,1000)
Explanation: Back to Top
<a id='update'></a>
UPDATE Command
The update command requires both a primary key to update and the name of the field that you want changed. Note that you can only change one field at a time. There is no ability to specify multiple fields at this time.
The format of the UDPATE command is:
<pre>
UPDATE &lt;table&gt; SET column=value WHERE key=keyvalue
</pre>
You must have a primary key on the table if you want an update to work. The filtering (WHERE) is allowed only to specify the primary key for the row and no filtering is allowed. The primary can be changed in the statement, but the update will fail if the key already exists in another record.
The other restriction is that no calculations can be done as part of the SET clause. You can only pass atomic values to the UPDATE statement.
Example: Update a BONUS value of employee
This SQL will update employee number 1 bonus to 2000. The first step is to put the employee back into the table.
End of explanation
%odata -e UPDATE TESTODATA SET BONUS=2000 WHERE EMPNO=1
Explanation: At this point we can update their salary.
End of explanation
%odata SELECT * FROM TESTODATA
Explanation: We doublecheck the results to make sure we got it right!
End of explanation
%%sql
CREATE OR REPLACE VIEW EMPDEPT AS 
  (
  SELECT LASTNAME, DEPTNAME 
  FROM EMPLOYEE E, DEPARTMENT D
  WHERE E.WORKDEPT = D.DEPTNO
  )
Explanation: Back to Top
<a id='views'></a>
Views
The OData implemented with Db2 doesn't allow for JOINS between tables. Sometimes you need to be able to look up information from another table in order to get the final result. One option you have to do this is to create a VIEW on the Db2 system.
The VIEW can contain almost any type of SQL so it allows for very complex queries to be created. For instance, the following view joins the EMPLOYEE table and the DEPARTMENT table to generate a row with the employee name and the name of the department that they work for.
End of explanation
%odata RESET TABLE EMPDEPT
Explanation: We also need to drop any service connection you may have created in the past with this table name.
End of explanation
%odata SELECT LASTNAME, DEPTNAME FROM EMPDEPT LIMIT 5
Explanation: Now that we have created the view, we can retrieve rows from it just like a standard table.
End of explanation
%%sql
SELECT 
  COUNT(*)
FROM 
  EMPLOYEE E, DEPARTMENT D
WHERE 
  E.WORKDEPT = D.DEPTNO
  AND D.DEPTNAME LIKE '%SYSTEMS%'
Explanation: You can also create sophisticated VIEWS that can take parameters to adjust the results returned. For instance, consider the following SQL statement which gives me count of employees that work in SYSTEMS departments.
End of explanation
%%sql -q
DROP TABLE PATTERN;
CREATE TABLE PATTERN
  (
  PATTERN_NUMBER INT NOT NULL PRIMARY KEY,
  SEARCH VARCHAR(16)
  );
Explanation: There are two departments with the name SYSTEMS in them, but there is no easy way to create a view for every possible combination of searches that you may want. Instead what we do is create a table that contains the pattern we want to look for and create the view so that it references this table.
The first step is to create our PATTERN table. Note we make sure it has a primary key so our OData update calls can change it!
End of explanation
%odata RESET TABLE EMPDEPT
%odata RESET TABLE PATTERN
%%sql
CREATE OR REPLACE VIEW EMPDEPT AS 
  (
  SELECT 
    COUNT(*) AS COUNT
  FROM 
    EMPLOYEE E, DEPARTMENT D
  WHERE 
    E.WORKDEPT = D.DEPTNO
    AND D.DEPTNAME LIKE
        (
        SELECT SEARCH FROM PATTERN WHERE PATTERN_NUMBER=1
        )
  );
Explanation: Now we create a view that access this PATTERN table to do the actual search. Note that values that are inserted into the PATTERN table must have the SQL special characters like % to make sure patterns can be anywhere in the string.
End of explanation
%sql INSERT INTO PATTERN VALUES(1,'%SYSTEMS%')
Explanation: In order for our view to work properly, we must populate our PATTERN table with a value. To test the view we will use %SYSTEMS% as our first example.
End of explanation
%sql SELECT * FROM EMPDEPT
Explanation: And now we can test our view by selecting from it.
End of explanation
%odata UPDATE PATTERN SET SEARCH = '%SERVICE%' WHERE PATTERN_NUMBER = 1
Explanation: Now that we have it working, we can try exactly the same thing but with OData. Our first transaction will update the search key to SERVICE.
End of explanation
%odata SELECT * FROM EMPDEPT
Explanation: The next OData statement should select the count of employees working in service departments.
End of explanation
<END_TASK>