Error in ECC table

[harish_babu5]

Hi all,

I have a database to ECC scenario.

PI is using this query in the sender Communication channel

select A, B, C, D, E, F from <table> where  InsertionDateTime >= DATEADD(HH,-24,getdate())

This query is taking data from last twenty four hours and the polling is happening in every five minutes. PI parses the message successfully.

In ECC, we are getting this error

Error during proxy processing An Exception with the type CX_SY_OPEN_SQL_DB occured, but was neither handled locally, nor delcared in a RAISING clause Database returned the SQL Code 133 Error text: Transaction rolled back by detected deadlock.

I guess ECC is not able to process the data in five minutes and after five minutes another set of data comes.

Client refused to allow permission to use "Flag" field which is why we had to go with this SQL squerry

Any inputs to resolve this question?

Harish

[harish_babu5]

Is there any way to avoid this?

I am not happy with the SQL querry. We are taking 24 hours of data every five minutes. This means except for the last five minutes, all the records pulled out of the JDBC database in the 23 hour fifty five minutes is the same data that was taken in the previous polling.

This is our current SQL Querry

select A, B, C, D, E, F from <table> where  InsertionDateTime >= DATEADD(HH,-24,getdate())

The Client is not allowing "Flag" field. We have to avoid repeating of data in the SQL Query.

Any other inputs?

Harish

[iaki_vila]

Hi Harish,

From my point of view the second suggestion of Praveen is the best way to do it.

Another way it is to change your scenario.

The main problem is that you don't have a flag field in the DB, therefore you can do it with a synch Proxy - JDBC scenario, and to have the main report in ECC in a job scheduled every X minutes. The communication start in the ECC then you know what is the last register processed and you can do it a more restrictive select in the JDBC.

Regards.

[harish_babu5]

Hi Inaki

We are not able to do that as the records are not in any particular order.

Harish

[iaki_vila]

Hi Harish,

If you have the InsertionDateTime i don't know why my answer doesn't work. You do an ECC report wich takes the hour (SY-UZEIT), you do a time interval to pass to your select (SY-UZEIT - 1 , SY-UZEIT). With a Proxy to JDBC scenario you can do the SELECT between (SY-UZEIT - 1 , SY-UZEIT). You can schedule a job in ECC every one hour, and you will take only 1 hour registers every time.

Please, correct me if i suppose anytihing wrong.

Regards.

[former_member182412]

Hi Harish,

• You can increase the polling interval in sender JDBC channel to prevent this issue.

• You can check is the record already exist in the database in inbound proxy if the record already exist you can ignore in inbound proxy, only insert the new values which are not in the database.

Regards,

Praveen.

[harish_babu5]

Hi Praveen,

I thought of increasing the time period. The load will be same. However, if I increase my time period, and if JDBC adapter polls only in ten or fifteen minutes then the ECC system will get more time to process the files.

Harish