N.B. Данные параметры адаптера доступны и корректно работают в зависимости от версии, SP и PL компонент системы. Просьба не ругать авторов, если ваша версия компонент ниже требуемого

N.B. Минимальная версия SP указана ниже для каждой версии NetWeaver. Отражён минимальный SP, который содержит указанную функциональность. Функциональность может быть доступна только начиная с определённого PL в рамках указанной или последующих SP. Детали применимости -- см. релевантные ноты

Описанные ниже параметры ведутся через табличную настройку, скрытую за опцией Advanced Mode.

Если Advanced Mode выключен, то по умолчанию poolWaitingTime = 0. Если Advanced Mode включен, то poolWaitingTime = 5000.

The JDBC Adapter documentation states that parameter settings for the parameter table in the "advanced mode" section of the configuration are documented via OSS note.

You are looking for additional parameter settings. There are two possible reasons why a feature is available via the "additional parameters" table in the "advanced mode" section of the configuration, but not as documented parameter in the configuration UI itself:

• Category 1: The parameter has been introduced for a patch or a SP upgrade where no UI upgrade and/or documentation upgrade was possible. In this case, the parameter will be moved to the UI and the documentation as soon as possible. The parameter in the "additional parameters" table will be deprecated after this move, but still be working. The parameter belongs to the supported adapter functionality and can be used in all, also productive, scenarios.
• Category 2: The parameter has been introduced for testing purposes, proof-of-concept scenarios, as workaround or as pre-released functionality. In this case, the parameter may or may not be moved to the UI and documentation, and the functionality may be changed, replaced or removed. For this parameter category there is no guaranteed support and usage in productive scenarios is not supported.

When you want to use a parameter documented here, please be aware to which category it belongs!

The following list shows all available parameters of category 1 or 2. Please note:

• Parameter names are always case-sensitive! Parameter values may be case-sensitive; this is documented for each parameter.
• Parameter names and values as documented below must be used always without quotation marks ("), if not explicitly stated otherwise.
• The default value of a parameter is always chosen that it does not change the standard functionality

JDBC Sender Adapter Parameters:

1. clusterSyncMode

Parameter:

Parameter name	:	String
Parameter type	:	clusterSyncMode
Possible Parameter values	:	"lock" or "scheduler"
Parameter value default	:	scheduler
Category 	:	1

scheduler: This is the default option that guarantees that at any time only one server node will have any channel instance active. The adapter runs locally on a server node which is assigned by the scheduler. The polling of the adapter continues on the same server node, until the node crashes or relocMode value is reached (refer Note #1355715 to understand the purpose of relocMode)

lock: This option will ensure that there is only one instance of the channel running across server node. When this option is set, JDBC adapter acquires an enqueue service lock on the server node. Lock is taken on channel object and it is exclusive and non-cumulative such that no second instance (even it is from the same channel) can fetch the data from configured Database tables unless and until first instance has completed (success/fail) it’s processing. Another key aspect of this setting is that, the polling is submitted to all the server nodes. Therefore, the processing happens at all server nodes depending on the lock acquired by the instance.

2. msgLimit

Parameter:

Parameter name	:	msgLimit
Parameter type	:	Boolean
Possible Parameter Values	:	true (OR) false
Parameter value default	:	false
Category	:	1

By default, the JDBC sender channel picks all the available rows from the database which satisfies the query provided in the channel configuration. Sometimes, due to overload in the number of tuples fetched, the channel runs into an OOM (OutOfMemory) error. To limit the number of tuples/size of tuples fetched by the channel, msgLimit was introduced. The idea is to have a limited number of tuples to fetch inorder to not make the channel run in OOM. Note #1296819 to have a clear understanding and usage of the parameter and its dependencies.

3. sqlquerytimeout

Parameter:

Parameter name	:	sqlquerytimeout
Parameter type	:	int
Possible Parameter Values	:	non-negative number
Parameter value default	:	0
Category	:	1

The parameter is available to set a database level query time out parameter. The default value is 0, but if the query time out is retained as per database default, if there value is 0. Any number, greater than 0 is considered and is set accordingly in terms of seconds. Please refer note # 1078420.

4. driver:<parameter>

Parameter:

Parameter name	:	driver:<database-specific-timeout-parameter>
Parameter type	:	Stirng
Possible Parameter Values	:	database-specific
Parameter value default	:	NA
Category	:	1

These parameters are pre-fixed with the string driver:<xxxx> to any database specific timeout parameter. The parameters specified under this not check for relevance for each database. Refer note # 1078420 for in detail understanding and usage.

JDBC Receiver Adapter Parameters

1. logSQLStatement

Parameter:

Parameter name	:	logSQLStatement
Parameter type	:	Boolean
Possible Parameter Values	:	true (OR) false
Parameter value default	:	false
Category	:	2

When implementing a scenario with the JDBC receiver adapter, it may be helpful to see which SQL statement is generated by the JDBC adapter from the XI message content for error analysis. Before SP009, this can only be found in the trace of the JDBC adapter if trace level DEBUG is activated. With SP009, the generated SQL statement will be shown in the details page (audit protocol) of the message monitor for each message directly.

This should be used only during the test phase and not in productive scenarios.

2. sqlBindMode

Parameter:

Parameter name	:	sqlBindMode
Parameter type	:	Boolean
Possible Parameter Values	:	true (OR) false
Parameter value default	:	false
Category	:	1

This parameter is used in case, where there is a need to use there are many similar queries to be execute. The purpose of using this is to have a PreparedStatement at the implementation level which is used to execute queries on the same table. This enhances the performance of execution of queries. The idea is to make a generic query and have placeholders to fill into them later. This query is compiled once and executed many times. This saves the time, to compile the query at the database thereby, reducing the time to a certain extent.

3. dateWithBindMode

Parameter:

Parameter name	:	dateWithBindMode
Parameter type	:	Boolean
Possible Parameter Values	:	true (OR) false
Parameter value default	:	false
Category	:	1

This parameter is used in addition to parameter #2 under category-"JDBC Receiver Adapter Parameters". The purpose of having this parameter is that when there is a necessity to have a standard database function which needs to be a part of the query. This parameter helps in adjusting the query accordingly. In addition to this parameter, some modifications are needed to be done to the input XML. Please refer note#: 2072891; in order to understand the construction of the XML.

You have configured multiple SAP XI 3.0 J2EE JDBC Adapter Receiver channels. When a single channel cannot connect to the database and blocks, e.g. due to a mis-configured packet filter or firewall, other channels are no longer able to establish database conections and will block, too. If this situation happens during the J2EE server startup, the JDBC Adapter service com.sap.aii.adapter.jdbc will time out.

JDBC adapter verifies the connection to database while starting of a receiver channel. Firewall timeout for idle-connections may sever a connection. This can cause JDBC applications to hang while waiting for a connection. Due to firewall timeout present at remote database, JDBC driver hangs upto firewall timeout. JDBC driver does not throw any exception to JDBC adapter that initiated the call. Typically, default value of this timeout is around 2 hours. Meanwhile, J2EE engine JDBC service would timeout without waiting for firewall timeout to elapse.

Starting from SP17, we provide an option lazyConnection to set at both service level and receiver channel level.

To set at service level, goto visual admin tool and JDBC adapter service properties and set lazyConnection property to true. Default value for this option is false, this means, while starting all receiver channels, connection to database would be established automatically. When we set this option to true, this means while starting all receiver channels, connection to DB would be skipped for all receiver channels.

To set at receiver channel level, goto advanced mode options and add a new row in the table with key as lazyConnection and value as true. When set to true, this means, while starting this particular receiver channel, connection to database would be skipped. If you set any value other than true, that means it will take Service level setting.

The exact behavior will be as follows:

1. If channel level property is set to true then, it will take channel level property irrespective of whatever is set at Service level.
2. For any value other than true in channel properties, it would always take Service level property.

To avoid confusion, we advise you to set only at service level. Channel level setting could be used only when you know that the particular channel will have connection time-out problems.

FTP/JDBC Adapter is being used in the business scenario for message processing. At the receiver side, 'maximum Concurrency' Parameter is used to acquire parallel FTP/DB connections for the same receiver FTP/JDBC channel. When Maximum concurrency is 1, then the value of the parameter poolWaitingTime will be 0 (by default). This is to make sure that the channel can wait infinitely to get the FTP/DB connection. For more information about these parameters please have a look at point 47 in SAP Note 821267. Due to the above settings of poolWaitingTime, the thread of receiver file/jdbc adapter will remain in runnable state leaving the message in waiting status forever.

Special development

As a result of this special development, threads of receiver file/jdbc adapter will be prevented to go into 'wait' status forever. Now, the parameter poolWaitingTime can be set to some positive value (i.e., > 0) under advanced mode table in the channel configuration even if Maximum Concurrency is 1. In this case, the value of the parameter poolWaitingTime will be taken from the channel configuration while execution. If the parameter poolWaitingTime is not set in the advanced mode table, then no action would be taken on the message processing and the behaviour will be same as before ie. message will wait in the resource pool till it gets the resource.

JDBC adapter is being used in the business scenario in mapping step via JDBC lookup. It is observed that when an SQL query (SELECT) is sent with apostorphies inside the column value, lookup is failing due to following error Exception:[com.sap.aii.mapping.lookup.LookupException: Exception during processing the payload. Error when calling an adapter by using the communication channel

Advanced Development

Code changes have been made in the receiver side JDBC adapter processing to parse the SQL query (SELECT) and check for any apostorphies. If yes then, append it with an escape character and handover the query to JDBC API (for execution on the DB). To enable this functionality, please set the parameter escQuote to true in the advanced mode table in JDBC receiver channel (configured in the JDBC lookup during mapping step). By default the value for the parameter escQuote will be false.

While running JDBC Sender Adapter, the source table may suddenly fill with large amount of data. As JDBC Sender Adapter is a polling adapter, this can cause large payload creation in memory. This can lead to an Out Of Memory error.

If a large amount of data is pulled from DB into JDBC Adapter and system memory consumption is high at that time (or increases during processing of the message), an OutOfMemory error occurs.

Configuring the Maximum Message Size :

This is explained in the following example.

1. Assume the you want to restrict maximum memory usage to 150KB (This is where OOM occurs).
2. Assume Maximum Message Size is X. Aim is to calculate X.
3. Lets say current Resultset's Maximum Size is Y = (No of rows returned by query) * (max row size)
4. Now to create an XI message using this Result set of size Y, you need 2 * Y amount of space. This is applicable to most of the cases (adding XML content to result set, to format it to XI message)
5. So total memory usage for a result set of size Y is 3 * Y. (Y amount to get result set into memory and 2 * Y amount of memory to create XI message, and all are existing in memory at same time).
6. So there is (maximum memory usage) 150KB = 3 * Y --> Y = 50KB.
7. Actual XI message size is 2 * Y. This is the amount needed by system to create XI message. Find maximum limit of this.
8. Maximum Message Size X = 2 * Y = 2 * 50KB = 100KB.
9. So if you want to keep maximum memory usage to 150KB, the Maximum Message Size should be ⅔ * 150KB = 100 KB. The Multiplying factor ⅔ can always be applied safely.

Configuring the Maximum Row size:

This is explained in following example.

1. Consider a select query, Select a.col1, a.col2, b.col3 from table1 a, table2 b where (..)
2. Here one row consists of fields col1, col2 from table1 and col3 from table2.
3. So Max Row size = maxsize of col1 + maxsize of col2 + maxsize of col3.

Implications :

1. Assume that you want to configure the following values: Max Message Size = 100 KB and Max Row size = 5 KB.

2. No of rows that can be sent at a time = 100 KB / (2 * 5 KB) = 10 Rows. 100KB / 2 will gives us the Maximum Result Set Size, this division by 2 is required as fetched result set is formatted with XML to form XI message. Then Maximum Result Set size is divided by Maximum Rows Size to get Maximum Number of rows that can be processed.

3. So the select query at any instant should return less than 10 rows. If the query returns more than 10 rows it will not be processed. A alert can be seen in channel monitor page.

4. Also XI message will never be greater than 100 KB. (It might be very well less than 100 KB)

5. Effectively you can limit the number of rows to be processed at a Time. So Maximum Number Of Rows to be processed. Max Rows = Maximum Message Size / ( 2 * Maximum Row Size )

So if Number of Rows to be processed is to be limited, the Maximum Message Size to be configured can be calculated from above equation.

Actions For Administrator:

Following are few ways in which a recovery from above situation can be done.

1. When the DB is filled suddenly with more no of rows than Maximum Rows specified, the channel gets into an error state. Move the excess number of rows into a Temporary state. This state should be such that the excess rows should not appear in the result set. Now with the limited data in the DB satisfying the selection criteria, channel will be able to process those records. Now depending on the excess data, move the number of rows (calculated from above explanations) into the valid state, which will get processed in the next interval. Continue this until the excess data is processed completely.

2. When Channel gets into an error state with Maximum Row limit reached,The polling will still continue in anticipation of change in the DB. But if this change is not expected within poll interval time, then the polling for this channel is unnecessary as it will end channel in the error state again with Maximum Row Limit reached. To avoid this unnecessary load a new configuration parameter in advanced mode table is introduce with name msgLimitErrorInterval value in sec. When this interval is provided, incase channel gets into an error state due to Maximum Row Limit, the next polling will happen after msgLimitErrorInterval interval. So this parameter can be configured to avoid the load on the system.

3. When channel gets into an error state with Maximum Row limit reached, you can stop this channel. Temporarily Reconfigure the channel to process large amount of data, so that excess data is processed in one interval. This should be timed such that the system is at low load when this processing happens.

When NUMERIC datatype is used in any of the DB table in MS SQL DB and when JDBC adapter is trying to retrieve the data from this table, it interprets the values of those columns (whose data type is NUMERIC) as normal strings and hence the value are displayed in exponential manner.

For example, if Value in the DB table is '0.0000000' then the corresponding value in JDBC adapter looks like '0E-7'.

Interpretation of NUMERIC data type has been changed from JDK 1.5.

We have code changes in PI JDBC sender adapter to avoid the representation of Column Values (whose datatype is NUMERIC) in exponential manner. In order to acheive this, please configure an additional parameter treatNumericAsDecimal to true in the additional parameter table of JDBC sender channel. The default value of this parameter is false.

JDBC Sender Adapter is being used in the Business Scenario for message processing. It is observed that JDBC sender adapter (when connecting to HANA DB) returns Decimal values in exponential format.

JDBC sender adapter returns Decimal values in exponential form when fetching data from HANA DB. This happens due to upgrade in the JDBC Driver for HADA Database.

For the fix to work, configure an additional parameter treatAsBigDecimal and set it to true. Default value is set to false.

JDBC Adapter is being used in the business scenario for message processing. During message processing, it is noticed that when a PL/SQL function is called (using a SELECT statement) on any database (like Oracle, MS SQL) from JDBC Sender adapter, a PI message gets created for the same, even though the PL/SQL function does not return any value.

If a PL/SQL fFunction is called using SELECT query and the function returns NULL, then JDBC sender adapter treats the return value as a resultset value and creates a PI message for the same.

An advanced mode parameter emptyResult has been introduced. If this parameter is set to true, then an additional check will be performed from JDBC Adapter side while calling a PL/SQL Function with a SELECT query, to check whether the resultset object (and not the resultset itself) is NULL. This advanced mode parameter has to be configured in the JDBC sender channel. If the function returns NULL, then the JDBC sender adapter will skip creating a PI message, as the SELECT query returns empty resultset object and no further processing is required. Otherwise, the normal execution will take place.

The default value for the parameter emptyResult is false.

JDBC Adapter is being used in the business scenario for message processing. During message processing, it is noticed that when Oracle database is used and the additional parameter msgLimit has been configured to true in the JDBC channel, the value of the date fields (if any) appear in an unexpected format.

Program error

The code has now been improved by introducing an advanced mode parameter called dateWithMsgLimit. When this parameter is configured as true in the advanced mode table of the JDBC sender channel, an additional check is done for the DATE datatype.

JDBC adapter is being used in the business scenario for message processing. It is observed that JDBC sender adapter doesn't perform the connection test to the database after the execution of the "SQL Statement" and before the execution of the "SQL update statement".

If the database is on an unreliable network, then there is a chance that the JDBC connection to the database could be lost due to some network issue. This can lead to the rollback of all the changes done by the execution of the "SQL Statement" at the database end, but at the JDBC sender channel the execution completes successfully if "SQL update statement" is configure as <TEST>. This could lead to duplicate processing of the same data.

Reason: Program Error

If the database is on an unreliable network, then an additional parameter connectionTest need to be configured to true at the JDBC sender channel which will allow an extra connection test to be performed after the execution of the SQL statement and before the execution of the SQL update statement.

Parameter: connectionTest
Value: true

Performing this additional test can have some performance issues. So this additional parameter should only be configured if the database is on an unreliable network.

JDBC adapter is being used in the business scenario for message processing. It is observed that JDBC sender adapter doesn't close the connection even after stopping of the channel.

Previously the JDBC Sender channel wasn't closing the connection to the database even after the channel was stopped manually.

The code has now been improved by introducing an advanced mode parameter called killConnectionOnStop. When this parameter is configured to true in the advanced mode table of the JDBC sender channel, the JDBC connection to the database is closed as soon as the JDBC sender channel is stopped.