MVSFORUMS.com

Ranjish · Posted: Thu Oct 23, 2003 8:47 am Post subject: Will SPUFI lock the table ?

Hi,

When we execute a SELECT query in a SPUFI and at the same time run a program which will try to delete the same rows, will the program abend ?

I had a case where the SELECT was executed and the output was displayed. But when an online transaction tried to delete the rows same time, it was hanging.

So when we are browsing the SPUFI output, is the table still under hold ?

Thanks
Ranjish

Bithead · Posted: Thu Oct 23, 2003 10:06 am Post subject:

SPUFI selects can lock out online transactions if the results are left open. I know from experience.

kolusu · Posted: Thu Oct 23, 2003 10:48 am Post subject:

Ranjish,

I guess you have defaulted your spufi isolation level to RR (RR=Repeatable Read). With repeatable read (RR) isolation, DB2 acquires the X(exclusive) locks on all pages or rows that fall within the range of the selection expression. All X locks are held until the application commits. Although this option can reduce concurrency, it can prevent some types of deadlocks and can better serialize access to data.

change that isolation level to CS and you will not hold the data.

Also when you are just selecting rows from spufi or QMF try coding WITH UR after the select statement , so that you are not holding up any resource.

Hope this helps...

Cheers

kolusu

Bithead · Posted: Thu Oct 23, 2003 10:57 am Post subject:

We change it when it happens (it is hard for the user to change it since we have cut their fingers off....)

kolusu · Posted: Thu Oct 23, 2003 11:11 am Post subject:

Bithead,

Ranjish · Posted: Thu Oct 23, 2003 11:28 am Post subject:

Kolusu,

Thank you very much for your reply.

Regards
Ranjish

Bithead · Posted: Thu Oct 23, 2003 11:36 am Post subject:

Kolusu,

They can change it, they just don't know that they have to.