1. Using the IBM DB2 Adapter with Oracle Integration Generation 2
  2. Understand the IBM DB2 Adapter
  3. IBM DB2 Adapter Capabilities

IBM DB2 Adapter Capabilities

The IBM DB2 Adapter enables you to integrate the IBM DB2 Database residing behind the firewall of your on-premises environment with Oracle Integration through use of the on-premises connectivity agent. Use the IBM DB2 Adapter to poll for new and updated records for processing in Oracle Integration. For example, any new record added to the Employee table in your IBM DB2 Database can be synchronized with Oracle Fusion Cloud Human Capital Management using Oracle Integration. In addition, use the IBM DB2 Adapter to execute SQL queries or stored procedures in the IBM DB2 Database. For example, quotes in Oracle Oracle Fusion Cloud Configure, Price, Quote can be created as Orders in the on-premises IBM DB2 Database by sending SQL statements or stored procedures using the IBM DB2 Adapter.

The IBM DB2 Adapter provides the following capabilities:

  • Support for invocation of stored procedures in the IBM DB2 Database.

  • Support for execution of DML statements and SQL queries: Select, Insert, Update, and Delete.

    Select the Run a SQL Statement option on the Basic Info page of the Adapter Endpoint Configuration Wizard to execute simple SQL queries. For complex SQL queries, use stored procedures by selecting the Invoke a Stored Procedure option on the Basic Info page of the Adapter Endpoint Configuration Wizard. Stored procedures can reduce the complexity of a SQL query.

  • Support for generating XSD from PureSQL. This feature generates an XSD from a PureSQL statement provided by dynamically querying the table.

  • Support for polling new and updated records for processing in the IBM DB2 Database. The IBM DB2 Adapter supports distributed polling. Distributed polling helps eliminate duplicate polling of the same records.

  • Support for a logical delete polling strategy. This strategy involves updating a special field on each row once it is processed.

  • Support for performing a SELECT operation against database tables.

  • Support for bulk data import and extract by choosing the SELECT operation from the Perform an Operation On a Table list on the Basic Info page.

  • Support for processing message payloads up to 10 MB in size. In the case of polling, you must set the Rejected Value property to REJECTED on the Polling Strategy and Options page. If the incoming message is greater than the 10 MB threshold size, that particular record is updated to REJECTED instead of READ. If the outbound operation returns a response greater than the 10 MB threshold size, the response message is ignored and a fault response is sent to the calling client.

    Note:

    In Java, Unicode characters are represented as 2 bytes.

The IBM DB2 Adapter is one of many predefined adapters included with Oracle Integration. You can configure the IBM DB2 Adapter as a connection in an integration in Oracle Integration.

Supported Data Types for SQL Stored Procedures

The IBM DB2 Adapter supports the following data types for SQL stored procedures.

SQL Data Type XML Schema Type
BIGINT long

BLOB

CHAR FOR BIT DATA

VARCHAR FOR BIT DATA

base64Binary

CHARACTER

CLOB

VARCHAR

string

DATE

TIME

TIMESTAMP

dateTime
DECIMAL decimal
DOUBLE double
INTEGER int
REAL float
SMALLINT short