JDBC receiver error No response available

[Muniyappan]

Hi All,

I am getting below error in Proxy to JDBC synchronous scenario. This interface is to select the records based on between two dates. when number of records comes above 20000, it is failing with below error in sxmb_moni. i am getting this error in QA system.

With same number of records, it works fine in Development system.

please share your thoughts to overcome this issue.

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

- <!--

Inbound Message

-->

- <SAP:Error SOAP:mustUnderstand="1" xmlns:SAP="http://sap.com/xi/XI/Message/30" xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">

<SAP:Category>XIAdapterFramework</SAP:Category>

<SAP:Code area="MESSAGE">GENERAL</SAP:Code>

<SAP:P1 />

<SAP:P2 />

<SAP:P3 />

<SAP:P4 />

<SAP:AdditionalText>com.sap.engine.interfaces.messaging.api.exception.MessagingException: No response available.</SAP:AdditionalText>

<SAP:Stack />

<SAP:Retry>M</SAP:Retry>

</SAP:Error>

Regards,

Muni

[iaki_vila · Accepted solution]

Hi Muniyapan,

In which PI version are you getting this problem?, haver you tried to increase the jdbc reveiver timeout? (Setting a Channel-Specific Timeout - Advanced Adapter Engine - SAP Library)

Have you checked the communication channel monitoring in order to check if the exception is more detailed?

Have you checked with the DB team if there is any restriction in the amount of the data selection?, may be any exception could be happen in the target system

Regards.

[Muniyappan · Accepted solution]

Hi Inaki,

I am on version 7.4 dual stack sp 06.

will check this timeout settings and let you know the results.

Regards,

Muni.

[Muniyappan · Accepted solution]

Hi Inaki,

i check the channel logs. getting below error.

Delivery of the message to the application using connection JDBC_http://sap.com/xi/XI/System failed, due to: com.sap.engine.interfaces.messaging.api.exception.MessageExpiredException: Sync application request expired.. Setting message to status failed

I tried using syncTimeout in the channel. set the value 300000. no luck.

PI dev and QA systems are connected to QA DB.

in the development it takes less than 3 mins to fetch the records. that is why it is successful.

But in the QA , it is taking more than 3 mins. so getting error-ed out. Not sure why syncTimeout  setting is not having any impact.

Regards,

Muni.

[iaki_vila · Accepted solution]

Hi Muniyappan,

You can try with the parameter dummySQLQueryTimeout as this note mentioned 1356789 - Configuring timeout for dummy SQL query in advanced mode

Regards.

[Muniyappan · Accepted solution]

Inaki,

This parameter setting did not help. still getting the same error. is there any other setting required in order to make this parameter work.

Requirement: Records will be created and updated in database system. in ECC business user will trigger the pi interface to get the records from DB. in the select statement we have used date as key(fromdate and todate). it will select the records and send it to PI.

please let me know if you have any alternative design for this requirement.

Regards,

Muni

[Muniyappan]

Business itself came up with restricting volume of data. they will be updating below 15k per day. in this case ECC is able to get the response.

Regards,

Muni.

[Muniyappan]

just had a discussion with ECC team.

we are thinking to implement below logic.

we will create some number of stored procedures. sp1,sp2,sp3,sp4,..etc.

each stored procedure will fetch 10000 records.

consider we have 50000 records in DB. here five stored procedures will be called one by one.

sp1 will fetch first 10000,sp2 will fetch next 10000,etc.

Ecc will try to pass each sp name one by one. when it does not receive  any records it will terminate the call.

Regards,

muni.

[Former Member]

You can add a column as flag where you put X when you retrieve the record using your PS. The PS should retrieve the top 10000 where the flag <> X

There is an update SQL statement in your CC where it will resent the flag to " " when all the rows has their X.

NOTE: You can add a parameter in the ID to make the value 10000 dynamic, May be in Prod you can get 30000 rows.

Cheers,

F

[Former Member]

Hello,

I would suggest you to check why DB is taking so long to return 20000 records which i don't think is big in terms of resultset, instead of making 4-5 DB calls which is not a good idea.

In addition to that, why don't u just reduce the date range while making a proxy call and get the data in multiple transactions in SAP?

Thanks

Amit Srivastava

[former_member257758]

Muni,

What is your default time out set for ICM (proxy) at SAP side? since it is synchronous interface , All the the systems should be performance efficient other wise you will have timeout/no response issues.

we has same kind of issue, We did the change at ABAP side to process the messages one by one in a loop. we fetched the data from SAP and store it in ztable and then loop the table to send records in sequence.

Timeout starts as soon as the message put in SAP queue, so if the program send 20K records at a time, then first few will get processed and next will fail.

Also your legacy DB response should be effective other wise even for 5K records you will get timeout/No response.

Thx

Arun

[Former Member]

Can you run the same query directly on the DB (Dev and QA) and check if the latency is caused by the DB instead?

Cheers

Fouad

[Former Member]

Hi Muni,

Try if you can narrow down your select query. If not, try to use stored procedure call to fetch the data.

I think SP call would be better than the direct select statement.

[former_member200386]

Hello Muni,

Better apply the condition in Proxy code to process 1000 records per single call. You don't face any issue in DEV because you may not  have much data. Please discuss with ABAP consultant and solve this issue.

Thanks,

Pavan T,

+9901742424

[Muniyappan]

Hi,

we don't have option to put condition in ECC to restrict the record numbers. from ECC we will send the from date and todate.

whatever records present in between these dates we have to fetch it from DB.even if selecting records between yesterday and today gives more than 20000.

[Former Member]

Instead of selecting all the records in one time, go in batches.. Modify your select query to fetch 5000 records/time.

you can use rownum < 5000 or TOP commands and make sure to use the update query.