JDBC to IDOC Scenario.

[Former Member]

HI Experts,

Requirement is i need to fetch the data from multiple tables, but my sender adapter has a query statement where i can select and update the records,

If my client creates a view by fetching the records from differient tables, is there any option to fetch data from the view else sender adapter requires table name to fetch data -- please clarify. ( Simple i can ask like is it possible to fetch data from view provided by them - Does our JDBC sender adapter supports to it)

Is there any other way to pull data from differient tables and convert them to xml or txt file, so that PI can easily pick the files.

Note: The size of the table is too high, so when i use some query statement, how abt the performance

Thanks,

Krishna

[MichalKrawczyk]

Hi,

>>> is there any option to fetch data from the view

think about how to make sure that the ones selected will not be selected anymore...

maybe you can use a stored procedure which will select from many tables and remove them once the SP is executed ? - you can use stored procedures with jdbc adapter in most cases

>>>Is there any other way to pull data from differient tables and convert them to xml or txt file, so that PI can easily pick the files.

most DB systems allow creation of XML output from stored procedures - oracle, MSSQL etc. so this is for sure possible however many DBAs don't allow that

>>>>Note: The size of the table is too high, so when i use some query statement, how abt the performance

inside the stored procedure you can check how many to send to PI at once (like first 1000 etc...)

Regards,

Michal Krawczyk

[Former Member]

Hi Michal.

>>> is there any option to fetch data from the view

think about how to make sure that the ones selected will not be selected anymore...

maybe you can use a stored procedure which will select from many tables and remove them once the SP is executed ? - you can use stored procedures with jdbc adapter in most cases

Here -- for the above statement -- if the stored procedure is executed succesffully, if it fails in PI will PI roll backs the entired records or failed records, because once the stored procedure is executed it will update differient tables that this records are processed then how do we handle roll back situation-- how to resolve such issues..

How this happens in table..if i fetch records from the table if it roll backs due to mapping failure in PI how roll back is handeled.

could you please explain this

Thanks,

--sai

[Former Member]

Hi Michal,

Probably i might be framed the query improperly..

the query is :

when i use stored procedure in Sender JDBC, if the interface fails at PI Integration Server level, how roll back of records are handeled

Stroed Procedure Fucntionsas: It collects fields of data from multiple tables and updates in those tables too.

So during PI failures will JDBC takes care of this roll back, or how to handle such scenarios.

Thanks,

--Sai

[baskar_gopalakrishnan2]

If stored procedure SP executes (select and update collectively) successful then if PI fails due to your data error in the mapping step (example) ,  You will not get roll back  behaviour with respect to the database status.  You need to fix the cause of errors example if any in the mapping.  Most likely this is not going to happen

[Former Member]

Hi Baskar,

Thanks for your suggestion.

If I pick the data from the table directly, is rollback taken care by the JDBC sender adapter?

With respect to stored procedures think like this if any mandatory field is missing for some records, then entire mapping will fail in this case stored procedure will update those records at the database level, here it is too difficult that to search as many number of records manually to reset the status.

may i know in which cases i can use JDBC lookups.

Thanks,

--Sai

[Former Member]

Hi Mich,

I just want to know while PI fetching the records from the database are the tables locked by PI.

Do we have any provision to have write access to that table while PI accessing that particular table.

how the table lock works for JDBC sender adapter, our requirement is for every 3 sec the new records will fall in the table and even JDBC sender adapter needs to fetch those records in realtime.

how can we achieve this functionality..

Thanks,

--Sai

[baskar_gopalakrishnan2]

With respect to stored procedures think like this if any mandatory field is missing for some records, then entire mapping will fail in this case stored procedure will update those records at the database level, here it is too difficult that to search as many number of records manually to reset the status.

This is data issue. If the mandatory fields are not showing values after SP result that means you might have to fix the data in the database level.

>may i know in which cases i can use JDBC lookups.

THis is basically for the read operation and certainly not for insert/update/delete.  To read a value for a specific column or for a group of few columns in the table. Please search scn for the jdbc lookup.

Also you might want to see the sap help link about the database transaction level settings for rollback.

http://help.sap.com/saphelp_nw04/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/content.htm

[Former Member]

Hi,

You may use join or stored procedure in sender jdbc adapter.

Please see this link:

http://help.sap.com/saphelp_nw73/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/content.htm?frameset=...

http://scn.sap.com/thread/902636

Regards,

Beena.