How to configure for JDBC Receiver channel to avoid "Delivering" message?

[Former Member]

HI experts,

We have a ECC to DB scenario. JDBC adapter is used in receiver channel to connect to Oracle 10g DB.

Now our issue is that some message would be blocked in Adapter Engine with "Delivering" status in RWB monitor. The message could not be deleted or cancelled.

Also, the target DB team tell me that the table is locked by PI when the message in Delivering status.

I research on forum and blog and some said that threads and timeout parameters should be configured.

My question is:

1. could you give me some suggestion on how to configure in JDBC adapter? Any parameter could be configured in Advance tag?

2. Could you suggest how to configure the JDBC adapter on PI server? Any document/blog on how to configure?

3. Any idea to handle "Delivering" message? When one message in Delivering status, all the following would be blocked in AE.

Any input would be appreciated.

Thanks a lot.

Kevin Song

[Former Member]

Hello!

The "Delivering" status means that the message is already being handled by a worker thread. So, it means that the content is already being processed on the JDBC receiver.

Since it seems this is a performance issue:

a) On the mapping program (Integration Repository), try referencing all the target tables with the <SCHEMA>.table name. This will prevent prior schema lookup, increasing the performance and reducing the "delivering" time;

b) Check if you have enough worker threads for the JDBC adapter. For an overview of the current engine status, please go to RWB -> Component Monitoring -> Adapter Engine -> Engine Status. From here, you can see the backlog and also the status of the PI threads. Check the JDBC_* queues. This won't affect "delivering", but gives a backlog status check. There are a couple of notes that describes how to change this, search for "messaging.connectionDefinition" parameter of XPI Service: AF Core.

As for your concerns:

1) The only performance relevant parameter on JDBC receiver channel would be "Maximum Concurrency". Try setting it to "1" to prevent further connections in parallel from the same channel, and check if the "delivering" time will reduce (also ensure to review the pollWaitingTime parameter accordingly. Refer to the JDBC FAQ note for details).

- Also as mentioned by Baskar, just check "Disconnect from DB" if you have low amount of throughput on the channel.

2) - I guess you have it covered by now.

3) First, never manually remove the table locks on SM12. When the RFCs are executing, this is a synchronous call and it will wait for a response from the Sender RFC adapter. Depending on the RFC program, this can cause consistency/data loss.

- If the messages aren't stuck on delivering, check recommendations above;

- If they are stuck (even though they should timeout), then retrieve some Thread Dumps (to check the thread status) and defaultTrace.trc file. There are some bug fixes on OSS notes that relates JDBC threads stuck on delivering status. With the thread dumps, check the status of the JDBC_* threads and what are they doing (probably with socket open). This might need some review by SAP Support.

Best wishes,

Lucas Santos

[marksmyth]

Hi Kevin,

3. Any idea to handle "Delivering" message? When one message in Delivering status, all the following would be blocked in AE.

- see this blog How to deal with stuck EOIO messages in the XI 3.0 Adapter Framework (still appicable for later releases).

Regards

Mark

[naveen_chichili]

Hi Kevin,

They might be taking long time to insert into DB and because of that those perticular messages might be locked by PIAFUSER.Go to SM12 and delete the locks associated to those messages and then try to reprocess them.

Regards,

Naveen.

[baskar_gopalakrishnan2]

Couple of things.. Is this interface using SP or Statement Structure?

a) The message could be delayed to reach because of the earlier message got stuck due to data issues. Please work with DB and find the root cause of the issues.

b) If this thing happens so often, you might want to check with DB whether db table is properly indexed.

c) If you deal with large volume of messages then disconnect from database after each transaction is not helpful

d) You can set logSQLStatement = true in the advanced tab to check the logs of the query.

e) Sometimes if the receiver adapter is servicing the previous message and the next message tries to reach DB during that time might cause giving error message locked by PI.

f) you can refer the help link for jdbc receiver configuration.

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

[former_member223322]

Kevin,

Cancel all the messages in 'System error', if in delivering status then try to restart the AF messaging service and also kill all the open connections with the help of DB team. Now in the Advanced tab of your receiver channel, check the 'Advanced Mode'. There you check the option 'Disconnect from DB after processing each message' and then try to run the interface again.

~Srini

[gagandeep_batra]

Hi

the follwong link might help you

http://www.saptechnical.com/Tutorials/XI/RFC2JDBC/Index.htm

Regards

Gagandeep