Decoding Max Concurrency ,poolWaitTime ,Driver properties in JDBC receiver Adapter Configuration

[rajasekhar_reddy14]

PI point of view the JDBC receiver adapter configuration is not complex but while dealing with high volume / multiple JDBC receiver interfaces, setting up right value to some of the parameters plays vital role,this blog covers below points,

1. How many JDBC receiver channels required?
2. Maximum concurrency.
3. Pool Wait time.
4. How to increase the JDBC thread count.
5. Additional Parameters configuration in JDBC receiver adapter:

1) How many JDBC receiver channels required?

If you see JDBC adapter configuration, nowhere we use SQL query to perform an operation on data base because mapping execution forms a JDBC statement.so we can use one channel for multiple interface,unless if you are conencting to different data base system. Let’s take an example we have 20 JDBC receiver interfaces and every interface pointing to different table.This case we have two options while creating a JDBC channel,

          a ) Creating an individual channel for every JDBC receiver interface.

          b)  Creating one JDBC receiver channel for all interfaces.

My decision always would be creating one channel for all interfaces right idea because JDBC receiver adapter by default can handle 5 parallel transactions/Connections, if we create multiple channels then every channel try to grab connection from available threads if all 5 threads were used by other channels then JDBC adapter throws below error.

      

So create very few channels like one channel for Sync scenarios and one or 2 channels for Async scenarios.

2) Maximum concurrency:

Maximum Concurrency is the number of parallel database connections that receiver channel can open, maximum JDBC adapter can open 5 JDBC connections by default value is 1.While configuring JDBC receiver adapter under processing tab we can set Maximum concurrency .Most of the times we get confused like what is the correct value to set.

  
If you are using only one channel for all JDBC receiver interfaces then better to set Max Concurrency  value to 4 (max 5),if multiple channels used in JDBC receiver scenario  then better to set maxconcurrency value to 2,even if we use maximum concurrency value greater than 5 then it is not going to help to perform parllel connections greter than 5.

PS: If you increase thread count then you can change max concurrency value ,refer Point 4 .

3) Pool Wait time:

Poolwait time configuration plays very important role while dealing with high volume scenarios, while JDBC channel negotiating a connection to process a message,poolWaitingTime is the amount of time( in milli seconds) the JDBC receiver channel waits for a free connection when all the parallel connections are being used.

If maximum concurrency value is set to more than one then add an entry "poolWaitingTime" and set to a time in Milli Secs (without any quotation marks) to  the "Additional Parameters" section.This parameter is case-sensitive,this avoids below error

1. com.sap.engine.interfaces.messaging.api.exception.MessagingException:Channel has reached maximum concurrency (5,000 concurrent messages) and no free resource found within 5,000 milliseconds; increase the maximum concurrency level.

If maximum concurrency value set to default to 1 then no need to set poolWait Time.

4. How to increase the JDBC thread count:

By default JDBC adapter can open 5 parallel connections as I mentioned above but to deal with high volume scenarios 5 parallel connections not sufficient enough,so in this case increasing thread count for JDBCscenario helps to open multiple parallel connections.

We need to add below property value in NWA to increase JDBC receiver adapter thread count.

     (name=JDBC_http://sap.com/xi/XI/System,
     messageListener=localejbs/AFWListener, exceptionListener=localejbs/AFWListener,
     pollInterval=60000, pollAttempts=60, Send.maxConsumers=5, Recv.maxConsumers=20,
     Call.maxConsumers=5, Rqst.maxConsumers=5)

Refer below wonderful blog, it explained clearly .

http://scn.sap.com/community/pi-and-soa-middleware/blog/2011/01/26/tuning-the-pi-messaging-system-qu...

Recently I worked on High volume JDBC receiver scenarios, every quarter first week we perform more than 5 million operations on DB, performance was improved significantly after setting value to 20.

5) Additional Parameters configuration in JDBC receiver adapter:

i) logSQLStatement:

       When JDBC adapter performs a operation on Data base system it forms a SQL statement,to view complete SQL statement to logSQLStatement value to true, it enables more logging details in JDBC channel Audit log.

        ii) CONNECT_TIMEOUT , Read Timeout, SQL Query time out:

Sometimes JDBC adapter takes long time to establish a connection to data base, JDBC adapter takes very long time to execute a statement and      Sometimes call made to Driver will hang permanently.Setting below parameter would resolve above issues,ReadTimeOut/Connect_time out value in milli seconds. Sqlquerytimeout in seconds.    

           
       iii) Batch Mode:

Using batch mode improves performance and if you want to collects SQL statements in batch then use batch mode.Batch mode is not supported for SELECT, UPDATE_INSERT,EXECUTE and SQL_QUERY statements.

                    

References:

http://help.sap.com/saphelp_nwpi711/helpdata/en/48/465581929734d9e10000000a42189d/content.htm

SAP Note 1136474, 1604091 and 1078420.