IBM DB2 Data Source

IBM DB2 Data Source
calac41
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
CA Live API Creator
 supports.
In this article:
Install the DB2 JDBC Driver
After you have installed the JDBC driver, the DB2 data source is available as an option in the connection wizard when creating an API.
For more information about the supported versions of JDBC drivers, see Installation Requirements and Supported Platforms.
You can verify the version of your JDBC driver by issuing the following command:
java -cp db2jcc.jar com.ibm.db2.jcc.DB2Jcc -version
If you have installed 
CA Live API Creator
 on Tomcat, copy the license file and the DB2 JDBC driver into the 
%{CATALINA_HOME}/lib
 directory.
If you have installed the self-contained, single-user version of 
CA Live API Creator
 that is based on Jetty, copy the license file and the DB2 JDBC driver into the 
%JETTY_HOME%/caliveapicreator/lib/ext
 directory.
Connect to a DB2 Database
Connect to a DB2 database by creating your API.
Prerequisites:
You know your JDBC connection information. 
For more information:
  1. In the connection wizard, select
    DB2 for z/OS
    or
    DB2 for LUW
    .
    The connection parameters are displayed.
  2. Complete the
    H
    ost
    ,
    P
    ort
    , and
    P
    assword
    fields, including the following fields, and then click
    Continue
    :
    Database (for Linux, UNIX and Windows)
    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 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 image shows the connection parameters in the connection wizard for IBM DB2 for Linux, UNIX and Windows:
    Screen Shot 2017-03-20 at 8.42.08 AM.png
    The following image shows the connection parameters in the connection wizard for IBM DB2 for z/OS:
    Screen Shot 2017-03-20 at 8.44.20 AM.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, 
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 database.
For more information about the data type definitions, see the IBM Knowledge Center.
You can read and write the following DB2 for LUW data types in
CA Live API Creator
:
Data Type
Modeled as...
Notes
BOOLEAN
boolean
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.
DATE
SimpleDate
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
BLOB
binary
CHAR
string
GRAPHIC
string
REAL
float
DOUBLE
double
VARCHAR
string
LONG VARCHAR
string
CLOB
string
VARGRAPHIC
string
LONG VARGRAPHIC
string
DBCLOB
string
SMALLINT
integer
INTEGER
integer
BIGINT
long
DECIMAL
decimal
DECFLOAT
decimal or double
Infinity and NaN are modeled as doubles.
DISTINCT TYPE
The underlying type
Manage Database Functions and Stored Procedures within Packages
CA Live API Creator
 exposes the stored procedures and database functions that are contained in packages 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. 
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 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
The following example shows how you can identify or reference the 
procA
 stored procedure that is declared within the 
package_name
 package:
package_name.procA
Use Overloaded Database Functions and Stored Procedures
When you use an overloaded database function and stored procedure, the name of the overloaded database function or stored procedure remains the same but the argument data type changes. 
CA Live API Creator
 assigns a version number to each overloaded database function or stored procedure.
The following example shows how you can use the 
myFunction
 overloaded database function within the 
package
 package by using the specific name argument data types:
package.myFunction-int (number)
package.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, 
CA Live API Creator
 detects the sequences that are defined in your schema.
You can have 
CA 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, 
CA Live API Creator
 detects the sequences that are defined in your schema. 
CA 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 
CA 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 
CA 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 
    CA 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
    CA 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
    CA 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
In certain DB2 versions, DB2 limits 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.