DB2 Locking: Subquery to a Long Federated Query

[Zri Man]

I have a situation that is as follows:

First an Index of what is discussed below:

FED_A ->> Federated Objected/Table that has lots of rows

LOC_B ->> Local Table which is selected and updated.

My data flow works through a Informatica workflow and is therefore not
exactly like a single transaction.

This is the start of this Data Flow:

SELECT * FROM FED_A
WHERE FED_A. COL_A >= ( SELECT MAX(LOC_B.COL_A)
FROM LOC_B )

Question One is:

If the data from FED_A takes 5 minutes to fetch and get back which
means it will have a share lock on selected rows for at least that
long.

And if The Subquery on LOC_B took 1 second to execute.

Is there any lock being held at all on Table LOC_B.

My own opinion is that none should be held and not for long.

The LOC_B.COL_A has an index on it and it returns fast when
executed independently (less than a second).

However we are finding that when the above query is in execution
there is SHARE ROW LEVEL LOCK on Table LOC_B which is locking UPDATES
to LOC_B further down the workflow out.

My main question is what is DB2's behaviour for locking on Table
LOC_B in this situtation. Does it hold a lock on LOC_B until the Unit
of Work(UOW) denoted by the whole query is returned?

And if so why does it appear to hold a Share Row Level Lock? Which
Row it will lock?

Sridhar

[Serge Rielau]

Zri Man wrote:

I have a situation that is as follows:

First an Index of what is discussed below:

FED_A ->> Federated Objected/Table that has lots of rows

LOC_B ->> Local Table which is selected and updated.

My data flow works through a Informatica workflow and is therefore not
exactly like a single transaction.

This is the start of this Data Flow:

SELECT * FROM FED_A
WHERE FED_A. COL_A >= ( SELECT MAX(LOC_B.COL_A)
FROM LOC_B )

Question One is:

If the data from FED_A takes 5 minutes to fetch and get back which
means it will have a share lock on selected rows for at least that
long.

And if The Subquery on LOC_B took 1 second to execute.

Is there any lock being held at all on Table LOC_B.

My own opinion is that none should be held and not for long.

The LOC_B.COL_A has an index on it and it returns fast when
executed independently (less than a second).

However we are finding that when the above query is in execution
there is SHARE ROW LEVEL LOCK on Table LOC_B which is locking UPDATES
to LOC_B further down the workflow out.

My main question is what is DB2's behaviour for locking on Table
LOC_B in this situtation. Does it hold a lock on LOC_B until the Unit
of Work(UOW) denoted by the whole query is returned?

And if so why does it appear to hold a Share Row Level Lock? Which
Row it will lock?

Sridhar

What's the isolation level? For CS I woudl not expect LOCB to hold the lock.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Zri Man]

It is CS