Hi!

Let's say that I have a table TABLE1 in schema A and in SCHEMA B.
I have a stored procedure that does a select from A.TABLE1 and inserts rows
from that select in B.TABLE1.
When the procedure is running and I do a SELECT * FROM A.TABLE1 then this
statement get's locked. Why?

Any pointers to the docs I should read ?

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gregor.Kovac@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

"Gregor Kovac" <gregor.kovac@mikropis.si> wrote in message
news:wPNVf.1210$oj5.476162@news.siol.net...
> Hi!
>
> Let's say that I have a table TABLE1 in schema A and in SCHEMA B.
> I have a stored procedure that does a select from A.TABLE1 and inserts
> rows
> from that select in B.TABLE1.
> When the procedure is running and I do a SELECT * FROM A.TABLE1 then this
> statement get's locked. Why?
>
> Any pointers to the docs I should read ?
>
> Best regards,
> Kovi
> --

Statements do no get locked, rows or tables get locked. The only exception
is that the there is some locking going on for the package, but you should
probably ignore that unless you have some specific problem with that..

The table that is being selected will have a share lock on the rows
selected, and the table inserted will have an exclusive lock on the rows
inserted.

Share locks are released depending on the isolation level. For CS (cursor
stability), share locks will released when the cursor moves off the row to
the next row.

Exclusive locks will be released when a commit or rollback is taken.

Locking is probably discussed in the Administration:Planning and/or
Administration:Implementation manuals.

Gregor Kova� wrote:
> Hi!
>
> Let's say that I have a table TABLE1 in schema A and in SCHEMA B.
> I have a stored procedure that does a select from A.TABLE1 and inserts rows
> from that select in B.TABLE1.
> When the procedure is running and I do a SELECT * FROM A.TABLE1 then this
> statement get's locked. Why?
>
> Any pointers to the docs I should read ?
I take a wild guess here and assume you use a cursor to select from
A.table1? Append FOR READ ONLY to the cursor.
The cursor is "ambiguous" in the sense that it could be updated.
Depending on your settings DB2 may either default to FOR UPDATE or FOR
READ ONLY. If it defaults to FOR UPDATE DB2 will acquire update locks.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge Rielau wrote:

> Gregor Kovaè wrote:
>> Hi!
>>
>> Let's say that I have a table TABLE1 in schema A and in SCHEMA B.
>> I have a stored procedure that does a select from A.TABLE1 and inserts
>> rows from that select in B.TABLE1.
>> When the procedure is running and I do a SELECT * FROM A.TABLE1 then this
>> statement get's locked. Why?
>>
>> Any pointers to the docs I should read ?
> I take a wild guess here and assume you use a cursor to select from
> A.table1? Append FOR READ ONLY to the cursor.
> The cursor is "ambiguous" in the sense that it could be updated.
> Depending on your settings DB2 may either default to FOR UPDATE or FOR
> READ ONLY. If it defaults to FOR UPDATE DB2 will acquire update locks.
>
> Cheers
> Serge

Well, not exactly. The procedure has couple of FOR loops, not CURSORs. The
truth is that the procedure itself is building SQL statements that are then
executed via
PREPARE S1 FROM SQLSTMT;
EXECUTE S1;

Maybe this is the problem?

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gregor.Kovac@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

Gregor Kovaè wrote:
> Serge Rielau wrote:
>
>> Gregor Kovaè wrote:
>>> Hi!
>>>
>>> Let's say that I have a table TABLE1 in schema A and in SCHEMA B.
>>> I have a stored procedure that does a select from A.TABLE1 and inserts
>>> rows from that select in B.TABLE1.
>>> When the procedure is running and I do a SELECT * FROM A.TABLE1 then this
>>> statement get's locked. Why?
>>>
>>> Any pointers to the docs I should read ?
>> I take a wild guess here and assume you use a cursor to select from
>> A.table1? Append FOR READ ONLY to the cursor.
>> The cursor is "ambiguous" in the sense that it could be updated.
>> Depending on your settings DB2 may either default to FOR UPDATE or FOR
>> READ ONLY. If it defaults to FOR UPDATE DB2 will acquire update locks.
>>
>> Cheers
>> Serge
>
> Well, not exactly. The procedure has couple of FOR loops, not CURSORs. The
> truth is that the procedure itself is building SQL statements that are then
> executed via
> PREPARE S1 FROM SQLSTMT;
> EXECUTE S1;
FOR loops are cursors
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge Rielau wrote:

> Gregor Kovaè wrote:
>> Serge Rielau wrote:
>>
>>> Gregor Kovaè wrote:
>>>> Hi!
>>>>
>>>> Let's say that I have a table TABLE1 in schema A and in SCHEMA B.
>>>> I have a stored procedure that does a select from A.TABLE1 and inserts
>>>> rows from that select in B.TABLE1.
>>>> When the procedure is running and I do a SELECT * FROM A.TABLE1 then
>>>> this statement get's locked. Why?
>>>>
>>>> Any pointers to the docs I should read ?
>>> I take a wild guess here and assume you use a cursor to select from
>>> A.table1? Append FOR READ ONLY to the cursor.
>>> The cursor is "ambiguous" in the sense that it could be updated.
>>> Depending on your settings DB2 may either default to FOR UPDATE or FOR
>>> READ ONLY. If it defaults to FOR UPDATE DB2 will acquire update locks.
>>>
>>> Cheers
>>> Serge
>>
>> Well, not exactly. The procedure has couple of FOR loops, not CURSORs.
>> The truth is that the procedure itself is building SQL statements that
>> are then executed via
>> PREPARE S1 FROM SQLSTMT;
>> EXECUTE S1;
> FOR loops are cursors
hmm.. that explains a lot of things ))

Thanks a million times
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gregor.Kovac@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

Gregor Kovaè wrote:

> Serge Rielau wrote:
>
>> Gregor Kovaè wrote:
>>> Serge Rielau wrote:
>>>
>>>> Gregor Kovaè wrote:
>>>>> Hi!
>>>>>
>>>>> Let's say that I have a table TABLE1 in schema A and in SCHEMA B.
>>>>> I have a stored procedure that does a select from A.TABLE1 and inserts
>>>>> rows from that select in B.TABLE1.
>>>>> When the procedure is running and I do a SELECT * FROM A.TABLE1 then
>>>>> this statement get's locked. Why?
>>>>>
>>>>> Any pointers to the docs I should read ?
>>>> I take a wild guess here and assume you use a cursor to select from
>>>> A.table1? Append FOR READ ONLY to the cursor.
>>>> The cursor is "ambiguous" in the sense that it could be updated.
>>>> Depending on your settings DB2 may either default to FOR UPDATE or FOR
>>>> READ ONLY. If it defaults to FOR UPDATE DB2 will acquire update locks.
>>>>
>>>> Cheers
>>>> Serge
>>>
>>> Well, not exactly. The procedure has couple of FOR loops, not CURSORs.
>>> The truth is that the procedure itself is building SQL statements that
>>> are then executed via
>>> PREPARE S1 FROM SQLSTMT;
>>> EXECUTE S1;
>> FOR loops are cursors
> hmm.. that explains a lot of things ))
>
> Thanks a million times
Hmm...

So if I have a FOR loop defined like this:
FOR FOR1 AS SELECT F1, F2 FROM TABLE1 DO
UDPATE TABLE2 SET B = FOR1.F1 WHERE ID = F2;
END FOR

how do I apply FOR READ ONLY to it?
Maybe:
FOR FOR1 AS SELECT F1, F2 FROM TABLE1 FOR READ ONLY DO
UDPATE TABLE2 SET B = FOR1.F1 WHERE ID = F2;
END FOR
but these does not work.

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gregor.Kovac@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~