Distributed transaction question - SQL SP in Oracle reading DB2 Table

[chrisr]

Feel like I'm the blind leading the blind but here goes..
A third party has some sql stored procs in their Oracle DB querying both Oracle tables and DB2 tables thru a dblink.
In their dev database instance they have no problems running the SPs (multiple procs within one unit of work BEGIN/END). But when trying to run in a different Oracle db instance they are having issues with "ORA2049:timeout: distributed transaction waiting for lock".

Now there seems to be some misunderstandings regarding the commit processing requirements when only doing sql selects. Suggestions made that the procs need to issue commit before and after select statements...

I'm way out of my depth and it shows, but the people doing this seem to know less than I. I'm sat here looking at their job on our as400 doing nothing and they are sitting there waiting for their timeout. Anyone able to shine a light on what might be wrong?

[MichaelCatalani]

Re: Distributed transaction question - SQL SP in Oracle reading DB2 Table

Without knowing or seeing anything else, it sounds like they are running with commitment control, without regards to the impact of doing so. The first thing I would look at is the commit lock level. If they are specifyig *ALL, then an SQL select could lock a slew of records (and may not need to lock them or even be under commitment control for what it is doing) and that would keep another process from even performing a simple read on the record.

Commitment control is fabulous for what it does. And when you use it in conjunction with native RPG IO, it's fairly easy to use, and designing the applications and the transaction boundries is fairly straightforward.

But you can get into a whole lot of hurt real quick with commitment control and SQL, because you can affect a lot of records without even trying.

The first step is to look at the code in the stored procedures, and determine why in hades they would need an SQL select statement to be running under commitment control. If they determine that they need to, then they need to identify the transaction boundries, and ensure they are committing the transactions at the proper locations. And they need to determine the proper lock level for the transaction.