Configuring the Receiver JDBC Adapter

Use

You need to configure the receiver JDBC adapter so that you can convert XML messages from the Integration Engine into the contents of database tables.

To configure the adapter you must specify the following:

A JDBC database driver to access the database
This driver is not supplied with the adapter but must be supplied by the database provider or third-parties.
Logon data for the database to be written to
The address (HTTP port and URL) where the adapter can be contacted from the Integration Engine.

Prerequisites

You have:

Installed a JDBC driver (Version 2.0) for the database.
Installed the corresponding adapter.
Selected the adapter on the configuration screen.
Chosen Configure to call the configuration of the adapter module.

Procedure

The configuration of the receiver JDBC adapter comprises five functional subareas:

Java class name for the receiver JDBC adapter
Specify the class name as follows:

classname=com.sap.aii.messaging.adapter.ModuleXMB2DB

This specification is mandatory.
The version specification for the configuration
version=
```
30
```
This specification is mandatory. If no specification is entered, the configuration is interpreted as an XI 2.0 adapter configuration. Other values are not permitted and trigger an error.
JDBC adapter mode
Specify the mode of the receiver JDBC adapter. The following values are permitted:
- mode=
```
XMB2DB
```
- mode=
```
XMB2DB_XML
```
- mode=
```
XMB2DB_RAWSQL
```
Depending on the mode you select, the adapter has different functions and expects the message received from the Integration Server to have a particular document format in each case. The following basic rules apply:
- In mode
```
XMB2DB
```
  , table values are inserted in a table that is specified in the adapter configuration. This mode has been kept for reasons of compatibility with older versions of the JDBC adapter, but should principally no longer be used.
- In mode
```
XMB2DB_XML
```
  , table values can be inserted, updated, or deleted in one or more tables. Furthermore, stored procedures in the database can be called using transfer parameters. In the case of synchronous queries, results from database queries or return values of stored procedures can also be transferred.
  This function is described by the transmission of predefined XML schema, which is explained in more detail below. The type and number of actions that can be bundled in a message is not restricted. All actions are then executed within a database transaction - either completely or not at all.
- In mode
```
XMB2DB_RAWSQL
```
  , an SQL statement is expected that is transferred to the database for processing unchanged. This mode is primarily for test purposes only.
Subsequent versions may permit additional processing modes.
Specifications for addressing by the Integration Engine
- XI. httpPort=<port_no>
```
<port_no>
```
  specifies the HTTP server port that receives the messages from the Integration Engine.
- XI. httpService=<service>
  <service> describes the service part of the address where the Integration Engine must send its messages.
These specifications are mandatory.
Note
If, for example, you have specified
```
XI.httpPort=1234XI.httpService=/db/Receiver
```
and , the end point address of the JDBC adapter in the Integration Engine must be specified as follows:
```
http://<Databaseadapterhost>:1234/db/Receiver
```
Note
The end point address must be extended as follows for the Integration Engine in Release 1.0:
```
http://<Databaseadapterhost>:1234/db/Receiver?action=execute&pipelineid=Receiver
```
If the Integration Engine message is sent to a non-specified adapter service, the system displays the following error message:

No registered listener for <service> found
The system displays the same message if the adapter is initialized, but has not been started (status
```
STOPPEDINITIALIZED
```
or ).
- XI.ReceiverAdapterForService=<Service>
  This entry is optional.
```
<Service>
```
  is the logical name of a business system in the System Landscape Directory (SLD). If the Adapter Engine connection to the SLD is configured, an association for the receiver JDBC adapter is created for this business system. When you create an end point for this business system in the Integration Directory, the address of the adapter is determined from this association as the default value.
  If no connection can be established with the SLD, or if the business system does not exist in the SLD, the specification is meaningless.
Specifications for database access
These specifications are mandatory and have no default values.
- db.jdbcDriver=<java_class_of_jdbcdriver>
  Specify the Java class of the JDBC driver that the JDBC adapter must load to be able to access the driver. The exact specification varies according to the JDBC driver; see documentation from the respective provider for exact details.
- db.connectionURL=<jdbc_driver_connection_url>
  Specify the address with which you can establish a database connection using the JDBC driver. The exact format of the address can vary; see documentation from the respective provider for more details.
  
  Note
  
  The JDBC adapter automatically reconnects to the database when a database connection error occurs.
- db.execute=<operating system command>
  An operating system command specified here is executed following successful database operations. The default value is an empty character string (no command).
- db.noOfRetries=<n>
  Specifies how often the system attempts to re-establish the database connection and access the database following an SQL exception. If the number of retries is reached, the last status is returned to the sender Integration Server. In the case of an error, the message is therefore only processed again when it is resent by the Integration Server.
  
  The default value is 0.
- db.disconnect=NO|YES
  The database is closed after each database transaction and the database connection is disconnected (YES) or maintained (NO).
  
  The default value is NO.
- db.autoCommit=NO|YES
  Use this parameter to deactivate the logical unit of work required by the JDBC adapter to guarantee that the data in the database is consistent. This option is required for JDBC drivers that do not support transactions. To avoid data inconsistencies in the database when the logical unit of work is deactivated, ensure that multiple database transactions cannot access the database simultaneously.
  
  The default value is NO.
  
  Caution
  Do not set this parameter to YES if the JDBC driver supports transactions, that is, if the system does not display a corresponding error message during normal operation.
- db.transactionIsolation=0|1|2|4|8
  There are different levels of database transactions known as isolation levels. The JDBC adapter uses the highest isolation level by default to avoid database inconsistencies arising from parallel database transactions. Some JDBC drivers or database products do not support this isolation level and it may therefore be necessary to lower it accordingly. Note, however, that lowering the isolation level can have an adverse effect on transaction security. Therefore, only do this when necessary. The values listed above correspond to the following JDBC constants:
  - 0 - TRANSACTION_NONE
  - 1 - TRANSACTION_READ_UNCOMMITED
  - 2 - TRANSACTION_READ_COMMITED
  - 4 - TRANSACTION_REPEATABLE_READ
  - 8 - TRANSACTION_SERIALIZABLE
  If the parameter is not set, then the default value of the connected database takes effect.
  
  Caution
  You must only lower the isolation level where necessary and as far as necessary. To avoid data inconsistencies in the database when the isolation level is lowered, ensure that multiple database transactions cannot access the database simultaneously.
Additional specifications to handle messages with Quality of Service = Exactly Once (In Order)
- db.exactlyOnceErrorInPendingState=IGNORE|ERROR
  As in the other adapters, Exactly Once messages are handled by default using status information in the file system. All adapter error statutes and program terminations initialized externally (shutdown of the adapter operating system process) are also handled in this mode.
  
  However, this does not include external program terminations during a database commit. In this case, the status of message processing is unclear since it can only be changed in the management file once the database commit has been completed.
  
  However, this kind of situation is identified when the application is started and editing of the message terminated during processing can be controlled using db.exactlyOnceErrorInPendingState.
  
  This specification is only effective for handling errors that occur when a message is being processed for a second time after initial processing remained in the unclear status described above.
  
  If an error now occurs during this second processing attempt, this is returned by default to the sender system as an error (or if the value ERROR is set explicitly).
  
  However, if the error occurs because the message was saved in the database when it was first processed and it is still located there, processing can be completed for the sender system using the value IGNORE (note that the database interface normally triggers the error duplicate insertif at least one of the table fields was defined as a primary key). Otherwise the sender system will continue to send the message and the error will continue to occur.
  
  Nevertheless, it is possible that a message can become duplicated during Exactly Once handling. For example, if there is no primary key field in the database table or if the data is already being processed by another application and it is then deleted, when the first attempt at message processing is interrupted by an external termination of the adapter process immediately after the database commit, a message can be duplicated. This problem can only be solved if message processing and status information management take place in the same database so that the processing steps have the same commit cycle.
  
  In the database where the write-to tables are located you must create an additional table with two columns for this purpose. Column 1 must be type character with length 36 (or more) and have the name XIMessage_ID. Column 2 must be of type integer and have the name XIMessage_Ts. This table is made known to the adapter as follows:
- db.tableForExactlyOnceHandling=<table name>
  Set this value to use the specified database table instead of file-based Exactly-Once processing. If the table does not exist or the columns cannot be found or are of the wrong type, then a corresponding error is triggered and the adapter will not be started.
- db.tableEOColumnNameId=<column name>
  If you use a different column name to XIMessage_Id (for example, because you want to use a table that already exists), you can specify this value for the type char(36) column. If a greater field length is specified the table can nevertheless be used. However, the minimum field length is 36. This value is only evaluated if you set db.tableForExactlyOnceHandling.
- db.tableEOColumnNameTs=<column name>
  If you use a different column name to XIMessage_Is (for example, because you want to use a table that already exists), you can specify this value for the type integer) column. This value is only evaluated if you set db.tableForExactlyOnceHandling.
- db.exactlyOnceHandlingIDsExpiration=<n Days>
  Specify the number of days that the management information for received messages with the service attribute Exactly Once (In Order) should be kept. This information is required to avoid messages being duplicated, if when handling an error the Integration Engine sends the same message to an adapter more than once. The default value is
```
60
```
  days. This value applies to both file-based and database-based status management.
  Caution
  If the value is set to 0 or to a negative value, all management information is deleted each time the adapter is initialized. This can be useful for test purposes but must not be used for production operation.
The required XML document format
As already explained, the XML document format depends on the mode of the JDBC adapter.
The adapter can send back a synchronous system acknowledgement if the sender requests it. The acknowledgment confirms that the message was delivered to the receiver.
- If you want to send back a synchronous system acknowledgment, set XI.AckFinal=true. This is the default.
- If you want to prevent a synchronous system acknowledgement from being sent back, even if the sender requests it, set XI.AckFinal=false.