IBM Db2 Data Source

IBM Db2 Data Source
lac52
This article includes information about how to install the IBM Db2 JDBC driver, how to connect to a Db2 database, and a list of the Db2 data types that
Layer7 Live API Creator
 supports. With the JDBC driver installed,
Db2 for z/OS
and 
Db2 for LUW
 are available as options in the connection wizard when creating an API.
In this article:
Install the Db2 JDBC Driver
Copy the license file and the
db2jcc.jar
file (the JDBC driver) into the directory that is based on the Java container on which you have installed 
Layer7 Live API Creator
:
The self-contained, single-user version of Live API Creator that is based on Jetty
Copy the files into the 
%JETTY_HOME%/caliveapicreator/lib/ext
 directory.
Apache Tomcat
Copy the files into the 
%{CATALINA_HOME}/lib
 directory.
Oracle WebLogic
Copy the files into the 
<Your WebLogic Domain Directory>/lib
 directory.
JBoss/WildFly
For more information, see Install on JBoss.
IBM WebSphere
Add the JDBC driver into your Web application server.
Using the WebSphere administrative console, create a JDBC provider for the driver in the server.
For more information about how to create a JDBC provider, see the IBM WebSphere documentation
You can verify what version of the JDBC driver you have installed by issuing the following command:
java -cp <Db2 JDBC JAR file name> com.ibm.db2.jcc.DB2Jcc -version
For more information about the JDBC driver versions that
Layer7 Live API Creator
 supports, see Installation Requirements and Supported Platforms.
Connect to a Db2 Database
You can connect to your Db2 database using the following methods:
  • (If you have not created your API yet) When you create your API.
    For more information about how to create an API, see Creating APIs.
  • (If you have an existing API) By adding a connection to an Db2 data source.
    For more information about how to add a connection to a data source, see Database Connectivity
The following procedure details how to connect to your Db2 database when creating your API.
Prerequisites:
You know your JDBC connection information. 
Follow these steps:
  1. In API Creator, on the APIs page, click 
    Create New API
    .
    The connection wizard opens.
  2. Click 
    Database First
    .
    The second step in the wizard appears.
  3. Select 
    Db2 for z/OS
     or 
    Db2 for LUW
     as the data source type.
    The third step in the wizard appears.
  4. Complete the
    H
    ost
    ,
    P
    ort
    , and
    P
    assword
    fields, including the following fields, and then click
    Continue
    :
    Database
    (for Linux, UNIX, and Windows)
    Enter the name of the database. This database value accepts double quotes. Use double quotes to accept the value of mixed-case (non-standard) database value.
    Example:
     DATABASE 
    Location
    (for z/OS)
    The location of the database. Use double quotes to accept the value of mixed-case (non-standard) location value.
    Example: 
    AFT23V
     Some Java Virtual Machine (JVM) systems might have problems with specific EBCDIC code pages and the 
     file. Copy this JAR file into the following directory:
    • (Mac OS X) The 
      /Library/Java/JavaVirtualMachines/<JDK version>/Contents/Home/jre/lib/ext/
       directory.
    • (Linux) The 
      /usr/lib/jvm/<JDK version>/jre/lib/ext/
       directory.
    • (Windows) The 
      C:\Program files\Java\jreXXX\lib\ext
       directory.
    Schema
    The database-dependent name that points to your schema on the database server.
    Example: 
    MYSCHEMA
    Case Sensitive: 
    Lowercase is equivalent to uppercase. For example, foo, Foo, and FOO are all equivalent and are converted to uppercase (FOO). If the value uses mixed case, enclose the name in double quotes ("), for example "Foo". You can enclose the value with the correct case and unusual characters using double-quotes (").
    Username
    The user name under which you access your database. You database administrator (DBA) assigns you a user name.
    Example:
     DBADMIN
    Case Sensitive: 
    Lowercase is equivalent to uppercase. For example, foo, Foo, and FOO are all equivalent and are converted to uppercase (FOO). If the value uses mixed case, enclose the name in double quotes ("), for example "Foo". You can enclose the value with the correct case and unusual characters using double-quotes (").
    The following images show the connection parameters in the connection wizard:
    IBM DB2 for Linux, UNIX, and Windows
    Screen Shot 2018-11-27 at 3.34.27 PM.png
    IBM DB2 for z/OS
    Screen Shot 2018-11-27 at 3.35.04 PM.png
You are connected to the database.
Update the Database URL
For Db2 for LUW, posting 24:00:00 to a TIME data type might return 00:00:00. To work around this known issue, add the following to the JDBC URL, including the leading colon and trailing semi-colon:
:sendDataAsIs=true;
Db2 Supported Data Types
When you are setting up a connection to an external data source, 
Layer7 Live API Creator
 must map the data types in the data source to data types in 
Layer7 Live API Creator
. The following table lists the different data type mappings applicable when working with a Db2 database.
For more information about the data type definitions, see the IBM Knowledge Center.
IBM DB2 for LUW
You can read and write the following Db2 for LUW data types in 
Layer7 Live API Creator
:
Data Type
Modeled as...
Notes
BIGINT
long
BLOB
binary
BOOLEAN
boolean
CHAR
string
CLOB
string
DATE
SimpleDate
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
DECIMAL
decimal
DECFLOAT
decimal or double
Infinity and NaN are modeled as doubles.
DISTINCT TYPE
The underlying type
DOUBLE
double
GRAPHIC
string
INTEGER
integer
LONG VARCHAR
string
LONG VARGRAPHIC
string
REAL
float
SMALLINT
integer
TIME
SimpleTime
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
TIMESTAMP
SimpleTimestamp
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
VARCHAR
string
VARGRAPHIC
string
IBM DB2 for z/OS
You can read and write the following Db2 for z/OS data types in 
Layer7 Live API Creator
:
Data Type
Modeled as...
Notes
BIGINT
long
BLOB
binary
BOOLEAN
boolean
CHAR
string
CLOB
string
DATE
SimpleDate
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
DECIMAL
decimal
DECFLOAT
decimal or double
Infinity and NaN are modeled as doubles.
DISTINCT TYPE
The underlying type
DOUBLE
double
GRAPHIC
string
INTEGER
integer
LONG VARCHAR
string
LONG VARGRAPHIC
string
REAL
float
SMALLINT
integer
TIME
SimpleTime
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
TIMESTAMP
SimpleTimestamp
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
VARCHAR
string
VARGRAPHIC
string
Manage Database Functions and Stored Procedures
Layer7 Live API Creator
 exposes the stored procedures and database functions as RESTful resource endpoints.
View Database Functions and Stored Procedures
You can view the stored procedures and database functions on the Schema page in API Creator. To view the Schema page, with your API open, in the Create section, click 
Schema
Layer7 Live API Creator
 displays your database functions and stored procedures as a period-separated list between the package name and the name of the database function or stored procedure.
For more information about how to view your stored procedures, including how to invoke them, see Manage Stored Procedures.
Identify and Reference Database Functions and Stored Procedures
Database functions and stored procedures with unique names appear as the REST endpoint.
The following example shows how you can identify or reference the 
myProcedure
 stored procedure and identify or reference the 
myFunction
 database function in 
Layer7 Live API Creator
:
<prefix>:myProcedure
<prefix>:myFunction
Use Overloaded Database Functions and Stored Procedures
Your Db2 database can include overloaded database functions and stored procedures. The name for the overloaded database function or stored procedure appears as part of the REST endpoint. For example, it has the same name but different IN parameters. 
Layer7 Live API Creator
 models the overloaded database function of stored procedure name by assigning an argument data type to each overloaded database function or stored procedure.
The following example shows how you can use the 
myFunction
 overloaded database function by using the specific name argument data types:
myFunction.myFunction-int (number)
myFunction.myFunction-lvarchar (string)
Test Database Functions and Stored Procedures
Test your functions and stored procedures in the REST Lab. Issue a POST request while passing the 
IN
 argument values.
For more information:
Use Sequences in Db2 Data Sources
Db2 typically handles auto-generated primary keys using sequences, which are database objects that the sequence generator in the database uses to generate sequential values. When you connect to an Db2 data source, 
Layer7 Live API Creator
 detects the sequences that are defined in your schema.
You can have 
Layer7 Live API Creator
 auto-generate primary keys using these sequences using the following methods:
Associate Entity Columns to Sequences
When you connect to a database that supports sequences, such as Db2, 
Layer7 Live API Creator
 detects the sequences that are defined in your schema. 
Layer7 Live API Creator
 cannot know which entity (table or view) uses which sequence. If your database includes sequences, you want it to generate unique primary keys during row inserts, and you want 
Layer7 Live API Creator
 to auto-generate numbers for the entity's column using the sequence, associate the sequence to that entity's column.
For more information, see Associate Entities to Sequences.
Define Rules that Return a Unique Primary Key
You can have 
Layer7 Live API Creator
 generate the following primary keys for the table using sequences and assign it to a column by way of event rules:
  • Alphanumeric unique primary key:
     Define a 
    pre-insert event rule
    . Within the code for the rule, define how 
    Layer7 Live API Creator
     computes the key. Pre-insert event rules fire only on client inserts. Rules of this type automatically handle inserting a parent and a set of children in a single transmission (Cascade Add).
    The
    Oracle Sequence Example
    API sample illustrates an example of how you can have
    Layer7 Live API Creator
    insert a parent and a set of children in the same request.
    For more information about this example, see Oracle Sequence Example API Sample.
  • Unique primary key:
     Define an 
    early event rule
    . Define a rule of this type for row inserts that are triggered from within your rule logic (for example, audit changes into child tables).
    The
    Oracle Sequence Example
    API sample illustrates an example of how you can have
    Layer7 Live API Creator
    compute an alphanumeric primary key in an early event rule using sequences.
    For more information about this example, see Oracle Sequence Example API Sample.
For more information about how to define pre-insert and early event rule types, see Event Rule Types.
Db2 Data Source Limitations
Some Db2 versions limit SQL queries to column widths up to 32K page size. If you pass in SQL queries that exceed this limit, the Db2 optimizer attempts to use a system temporary table. If the record has a row size that is larger than the largest possible temporary table space, the Db2 optimizer fails and Db2 returns the following error:
SQLCODE=-1585, SQLSTATE=54048. A system temporary table space with sufficient page size does not exist.
Your DBA can address this limitation in Db2 by reducing the column size of the 
VARCHAR
 columns.