Can a query from an Oracle to a DB2 through a dblink block the DB2 table?

Question

I have a middle size query with 500.000 registers from an Oracle to an DB2 system through a DBLink. Can this query block the DB2 table so that it can be updated until i close the DBLink or until i finish the job whith the query (an insert-select into an Oracle table)

Answer 1

The DB2 engine will see the DBLink as a remote application that is executing a select statement. If that application is using cursor stability (CS) or a similarly reasonable isolation level, the DB2 database should not be locking any rows for very long. At most there may be a series of very short row-level locks as the cursor from the DBLink iterates through the result set of the DB2 query. If the DBLink isn't changing things on the DB2 side, there shouldn't be any significant locking occuring in DB2, so other applications should be able to connect to DB2 to perform updates. Please let me know if I've misunderstood something about your question and I will clarify my answer.

Answer 2

It seems that it's posible to block tables with selects through Oracle dblinks, because it's not posible to define the DBLINK as READ UNCOMMITED, but the problem can be solved with another DB system which supports that feature, like SQLServer. That is what says the last post in this forum:

Forum

"There is no way to set or fool oracle into selecting with a uncommitted read even when selecting data from an external database over a DB_LINK

What we did to get around this was to by-pass the odbc driver from oracle to progress all together as any locked columns on the progress side bombed out for us when selecting over the DB_LINK even with us set transaction read only.

We instead used SQLServer as the buffer between ORACLE and Progress. With SQLServer you can set the isolation level to read uncommitted. so we created SQLServer views of progress tables via a SQLServer Linked server and then we select from those views from a DB_Link from oracle to SQLServer.

This seems to be working fine"