locking tables without errors - like SQL Server 1

[gabegg]

Here's my question...

I usually do SQL Server development, and I am used to the way that SQL Server manages table locks. Meaning, that I hardly notice when someone locks a table with an update, etc., it may block other SQL statements against that table. Once the first statement completes, the next statement executes.

All of this is managed by SQL Server though, so from the client side, all I notice is an excessive query time (maybe), but no errors.

Anyway, I'm now trying to run queries against a DB2 database. I've already caused problems with our mainframe people. They've told me that they can't have anyone else reading records from the tables because they run large updates at various times.

That part isn't unusual, but they claim that if they can't immediately lock the table, it causes a DB2 error to be generated.

I guess my question is, why doesn't DB2 manage the blocks like SQL Server? Why won't either my statement, or there statement just wait for the first one to complete... and then carry on as normal? Is there an easy way for me to read a view, or mirror of the table, that won't cause a block?

Thanks

 

[Hattusas]

What is the error syntax coming from DB2
something like that
"The command couldn't be performed because of a deadlock or lock timeour reson code=68"? Salih Sipahi
Software Engineer.
City of Istanbul Turkey
openyourmind77@yahoo.com

 

[Hattusas]

What is the error syntax coming from DB2
something like that
"The command couldnt be performed because of a deadlock or lock timeout reson code=68"? Salih Sipahi
Software Engineer.
City of Istanbul Turkey
openyourmind77@yahoo.com

 

[gabegg]

Here's what my DB2 DBA sent me:

Below is the error messages that occur as a result of contention. The first is the message that was in the DB2 Log. The second is the message that was in the SYSOUT of the abending mainframe (batch) job:

STC08573 DSNT376I -D2P8 PLAN=DSNTEP2 WITH
CORRELATION-ID=FRGDOP60
CONNECTION-ID=BATCH
LUW-ID=MAGLUX00.ADYDB2S1.B765ED6E18A0=14289
THREAD-INFO=FRGDRGP:*:*:*
IS TIMED OUT. ONE HOLDER OF THE RESOURCE IS
PLAN=DISTSERV
WITH
CORRELATION-ID=sqlservr.exe
CONNECTION-ID=SERVER
LUW-ID=GA4903B1.IA04.01E829060553=14141
THREAD-INFO=EHPPUB:*:ehppub:sqlservr.exe
ON MEMBER D2P8

This is what was in FRGDOP60's sysout :

PAGE 1
***INPUT STATEMENT:
DELETE FROM FRGDDBA.SNAP_ORD_HDR;
SQLERROR ON DELETE COMMAND, EXECUTE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -911, ERROR: THE CURRENT UNIT OF WORK HAS BEEN ROLLED
BACK
TYPE OF RESOURCE 00000D01, AND RESOURCE NAME 00000287.00000008
DSNT418I SQLSTATE = 40001 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXRSTD SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -120 13172746 0 13226969 -1026420735 536871168
SQL D
DSNT416I SQLERRD = X'FFFFFF88' X'00C9000A' X'00000000' X'00C9D3D9'
X'C2D
INFORMATION

Thanks,
Gabe

 

[MarcLodge]

Hi Gabe,
The mainframe will -911 when it can't get a lock on the info it requires after a pre-determined period of time, set up somewhere in the DB2 parms, but often about a minute. The batch program should have it BIND options set up so that it does not lock the whole table, but just the page of data that it is after. Ask the DB2 DBA to check the locking options for the plan: It may require changing to make it more efficient. It may also possibly require COMMITS.

I would also look at what locking options are coming through from the server side as I would not expect to see that end of things taking a lock for over a minute.

Hope this is of some help.

Marc

 

[legacy370]

One suggestion: on the queries, you can specify
FOR FETCH ONLY WITH UR.

The "WITH UR" will cause the query to do a "dirty read," meaning that it will blast right through any locks and read the data anyway. "FOR FETCH ONLY" will cause your query to avoid taking any locks as it gathers the data.

Please do heed the suggestion above--make sure that DB2 is locking at the page level.

 

[matchum]

gabegg,

Interestingly, the statement in effect at the time was your
"DELETE FROM FRGDDBA.SNAP_ORD_HDR;" but of course it may well be one of many SQL statements and may not be the only problem.

Another party could have issued a
"LOCK TABLE FRGDDBA.SNAP_ORD_HDR IN EXCLUSIVE MODE" and it could just be bad timing.

As well as following the above advice, particularly the BIND rules, ask your DBA to investigate dependencies. You may well be using dynamic SQL almost exclusively but other applications can be identified as possible causes of contention.