IBM Db2 for i Data Source

You can modernize or extend your applications by enabling data and services from IBM Db2 for i (formerly known as Db2 for iSeries). You can then unlock and expose this data using . This article includes information about how to install the JDBC driver for Db2 i and how to connect to a Db2 for i database. It also includes a list of the Db2 for i data types that  supports. With the JDBC driver installed, the Db2 for i data source is available as an option in the connection wizard when creating an API.
lac52
You can modernize or extend your applications by enabling data and services from IBM Db2 for i (formerly known as Db2 for iSeries). You can then unlock and expose this data using 
CA Live API Creator
. This article includes information about how to install the JDBC driver for Db2 i and how to connect to a Db2 for i database. It also includes a list of the Db2 for i data types that 
CA Live API Creator
 supports. With the JDBC driver installed, the 
Db2 for i
 data source is available as an option in the connection wizard when creating an API.
With a connection to a Db2 for i database, you can:
  • Expose tables, views, stored procedures, and physical and logical files as REST endpoints.
    The 
    db2i
     sample API demonstrates updating the sample data source connection and testing your tables, views, stored procedures, and physical and logical files as REST endpoints in the REST Lab.
    For more information about this API sample, see the db2i API Sample documentation on GitHub.
  • Subscribe to messages from data queues and publish messages to data queues residing on Db2 for i databases using Db2iDataQueue listeners and functions.
    The 
    db2i
     sample API includes:
    • Example Db2iDataQueue listeners that demonstrate subscribing to messages from data queues residing on Db2 for i databases.
    • Example functions that demonstrate publishing messages to data queues residing on Db2 for i databases.
    For more information about this sample API, see the db2i API Sample documentation on GitHub.
  • Expose RPG programs as API endpoints.
    The 
    db2i
     sample API includes example functions that demonstrate exposing RPG programs as API endpoints. The functions include parameters that are passed into the RPG program.
    For more information about these functions, see the db2i API Sample documentation on GitHub.
For more information about Db2 for i, see the IBM Db2 for i documentation.
In this article:
 
 
Install the JDBC Driver for Db2 for i
Copy the IBM Toolbox for Java JDBC driver (the 
jt400.jar
 file) into the directory that is based on the Java container on which you have installed 
CA Live API Creator
:
 
The self-contained, single-user version of Live API Creator that is based on Jetty
Copy the file into the 
%JETTY_HOME%/caliveapicreator/lib/ext
 directory.
 
Apache Tomcat
Copy the file into the 
%{CATALINA_HOME}/lib
 directory.
 
Oracle WebLogic
Copy the file 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. Then, 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
For more information about the JDBC driver versions that 
CA Live API Creator
 supports, see Installation Requirements and Supported Platforms.
Connect to a DB2 for i Database
You can connect to your DB2 for i 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 a DB2 for i 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.
When you connect to Db2 for i, 
CA Live API Creator
 sends the following parameters in the database URL:
time format=iso;date format=iso;prompt=false
Prerequisites:
 
  • You know your JDBC connection information.
  • Your Db2 for i database is set up for journaling.
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 i
     as the data source type.
    The third step in the wizard appears.
  4. Complete the 
    Host 
    and 
    Password
     fields, including the following fields, and then click 
    Continue
    :
    Database
     
    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
    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 i
    LACConn_DB2Fori.png 
You are connected to the database.
DB2 for i Supported Data Types
When you are setting up a connection to an external data source, 
CA Live API Creator
 must map the data types in the data source to data types in 
CA Live API Creator
. The following table lists the different data type mappings applicable when working with a DB2 for i database.
For more information about the data type definitions, see the IBM Knowledge Center.
You can read and write the following Db2 for i data types in 
CA Live API Creator
:
Data Type
Modeled as...
Notes
BIGINT
long
BLOB
binary
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
 
CA 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
CA 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 or 
myFunction
 database function  in 
CA Live API Creator
:
<prefix>:myProcedure
<prefix>:myFunction
Use Overloaded Database Functions and Stored Procedures
Your DB2 for i 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. 
CA 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 database 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 for i Data Sources
DB2 for i 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 a DB2 for i data source, 
CA Live API Creator
 detects the sequences that are defined in your schema.
For more information about the approaches you can use to generate sequential values, see Associate Entities to Sequences.
DB2 for i Data Source Limitations
DB2 for i journaling provides an audit trail and a forward and backward recovery mechanism in the database. If your Db2 for i database is not set up for journaling and you attempt to insert or update to a table, you can get an SQL7008 error. The workaround is to disable transaction isolation in your connection by setting the translation isolation attribute to 
none
 .
The following example shows how to set the translation isolation attribute to 
none
 :
jdbc:as400://<hostname>:<portnumber>;time format=iso;date format=iso;
transaction isolation=none
For more information about DB2 for i journaling, see the IBM Db2 for i documentation.