Show TOC

Configuring the Sender JDBC AdapterLocate this document in the navigation structure

Use

You configure the sender JDBC adapter to be able to send content from databases to the Integration Server or to the PCK.

Prerequisites
  • There must be exactly one sender agreement for the defined communication channel.
  • You must add an indicator that specifies the processing status of each data record in the adapter (processed/not processed) to the database table.
  • The UPDATE statement must alter exactly those data records that have been selected by the SELECT statement. You must use the same WHERE clause in the UPDATE and the SELECT statements.

    See below under Defining Processing Parameters, SQL Statement for Query, and SQL Statement for Update.

    Tip

    The example shows the correct specification of the SELECT and UPDATE statement:

    SQL statement for query: SELECT * FROM table WHERE processed = 0;

    SQL statement for update: UPDATE table SET processed = 1 WHERE processed = 0;

    processed is the indicator in the database.

  • Processing can only be performed correctly when theTransaction Isolation Level is set to repeatable_read or serializable.
Procedure
  1. The Transport Protocol is JBDC 2.0.
  2. The Message Protocol is JDBC. Additional values may be added for the message protocol in future versions.
  3. Select the Adapter Engine on the Integration Server, or select a noncentrally installed Adapter Engine.

    This selection is not available in the PCK.

Defining the Database Connection

  1. Select the Connection tab page.
  2. Under JDBC Driver, enter the Java class of the JDBC driver.

    The JDBC adapter must load the class to be able to access the driver.

  3. Under Connection, specify the address with which you can open a database connection using the JDBC driver.
  4. Under User Name and Password, enter the logon data for the database to be read.

    The entries for JDBC Driver and Connection depend on the JDBC driver. You will find this information in the documentation from the provider.

Defining Processing Parameters

  1. Select the Processingtab page.
  2. Under Quality of Service, specify how a message is to be processed by the Integration Engine/PCK.

    For Quality of Service Exactly Once In Order, enter the Queue Name.

    See: Quality of Service

  3. Specify the following for the poll interval:
    • Poll Interval (secs): Number of seconds that the adapter must wait if no files are found for processing
    • Poll Interval (msecs): Number of milliseconds that the adapter must wait if no files are found for processing

      If you set Poll Interval (secs) to null, processing times are short and close to real time.

      If you set Poll Interval (secs) and Poll Interval (msecs) to null, the adapter is called once.

    • Retry Interval (secs): Number of seconds that the adapter is to wait before an SQL statement processed with errors is processed again

      If you set the value to null, the adapter is canceled if an error occurs, even if a value greater than null is specified for Poll Interval (secs).

      If you do not enter a value, the value from Poll Interval (secs) is used.

  4. The specification for Query SQL Statement must correspond to the SQL variant supported by the respective JDBC driver. It can also contain table JOINs.
    • Enter a valid SQL statement that is to be applied to the database once the data (determined from the Query SQL Statement) has been successfully sent to the Integration Server/PCK.

      It must be an INSERT, UPDATE, or DELETE statement.

    • Specify an SQL EXECUTE statement to execute a stored procedure, which contains exactly one SELECT statement.
  5. Enter the Document Name.

    The document name is inserted in the message as the main XML tag.

  6. Enter the Document Namespace.

    The namespace is added to the document name.

  7. Enter the Update SQL Statement.
    • Enter a valid SQL statement that is to be applied to the database once the data (determined from the Query SQL Statement) has been successfully sent to the Integration Server/PCK.

      The SQL statement must be an INSERT, UPDATE, or DELETE statement.

    • If you want the data determined from the Query SQL Statement to remain in the database unchanged after being sent successfully, enter <TEST>.

      This is recommended if the data has not only been read, but also changed by a stored procedure entered under Query SQL Statement.

Defining an Operating System Command

  1. Specify an operating systemCommand Linethat is to be executed following successful database operations.
  2. Under Timeout (secs), specify the maximum runtime of the executing program in seconds.

    When this time interval is exceeded, the adapter continues processing. The executing program continues to run in the background.

  3. If the adapter is to terminate the executing program in the event of a timeout, select Terminate Program After Timeout.

    The adapter writes the output (STDOUT and STDERR) for the operating system command in the system trace.

    Message processing is independent of any errors that occur during the execution of a configured operating system command.

Defining the Adapter Status

  1. Select the Advanced tab page.
  2. Set the adapter to Active to enable messages to be exchanged.

Defining Additional Parameters in Advanced Mode

  1. To specify additional parameters for the adapter configuration, select Advanced Mode.
  2. To define how transactions running in parallel are to influence each other, select the Transaction Isolation Level.

    You can run database transactions at different levels, known as isolation levels. The options correspond to the JDBC constants:

    • Default(default setting of the respective database)
    • None
    • read_uncommitted (weakest setting)
    • read_committed
    • repeatable_read
    • serializable (strongest setting)
      Caution

      Only reduce the security level when necessary and as far as necessary. You must find another way to make sure that data inconsistencies cannot be generated in the database, usually by preventing parallel access.

  3. If the JDBC driver does not support any transactions, deactivate the logical unit of work. To do this, choose Database Auto-Commit-Enabled (No Transaction Handling). The logical unit of work ensures data consistency. If the JDBC driver does not support any transactions, you must find another way to ensure the consistency of the data in the database, usually by preventing parallel access.
    Caution

    Do not set this indicator if the JDBC driver supports transactions.

  4. If you want the database connection to be released and reestablished before each Poll Interval, select Disconnect from Database After Processing Each Message.
  5. If you want to remove the empty tags from the resultset of the sender adapter, select Remove Empty Tags. This helps to reduce the size of XML documents.
    Tip

    If this indicator is not set, the resultset looks as follows:

    <resultset>

    <row>

    <column-name1>column-value</ column-name1>

    <column-name2></column-name2>

    <column-name3>column-value</ column-name3 >

    <column-name4></column-name4>

    </row>

    <row>

    <column-name1>column-value</ column-name1>

    <column-name2></ column-name2>

    </row>

    </resultset>

    If this indicator is set, the resultset looks as follows:

    <resultset>

    <row>

    <column-name1>column-value</ column-name1>

    <column-name3>column-value</ column-name3>

    </row>

    <row>

    <column-name1>column-value</ column-name1>

    </row>

    </resultset>

  6. Specify additional Parameter Names and Parameter Values in the table.

    Due to messages of large size, it is possible that there could be out of memory errors, which could lead to JEE server node failures.

    Following are the parameters to be set in the table to limit the message size:

    • msgLimit : This parameter is used to enable the max message size limit feature for JDBC adapter. When this parameter is set to true, JDBC adapter does not process the message of size higher than the values specified through maxMsgSize and maxRowSize. If msgLimit is set to true then, maxMsgSize and maxRowSize parameters are mandatory.
    • maxMsgSize : This parameter is used to limit the message size to an optimal value. JDBC adapter does not process the message size of more than the value provided for maxMsgSize, if encountered at runtime. The value of the parameter should be provided in KB.

      More information on configuring the maximum message size: SAP note 1253826.

    • maxRowSize : This parameter is used to provide the maximum row size. This parameter is used to calculate the maximum number of rows that could be sent through channel in one interval. The value of the parameter should be provided in KB.

      More information on configuring the maximum row size: SAP note 1253826.

    • maxLimitErrorInterval : This parameter is used to provide a special interval to change the next polling interval incase an error occurs when a  maximum message size is reached for the current interval. This reduces the load on the system. The parameter value should be provided in seconds.
      Note

      Additional parameters are published in SAP Note 801367.

Example

The system converts the table resulting from the query SQL statement into a valid XML document and sends it to the Integration Engine/PCK. The document looks like this:

<resultset>

<row>

<column-name1>column-value</ column-name1>

<column-name2>column-value</ column-name2>

<column-name3>column-value</ column-name3>

</row>

<row>

<column-name1>column-value</ column-name1>

<column-name2>column-value</ column-name2>

<column-name3>column-value</ column-name3>

</row>

</resultset>