|
View previous topic :: View next topic
|
| Author |
Message |
girishbs13
New User
Joined: 11 Nov 2011
Posts: 24
Location: India
|
|
|
|
Hi,
I am executing the below query in a test region with RR isolation level.
| Code: |
| SELECT * FROM DB2TAA40.USER_TABLE FETCH FIRST 2 ROWS ONLY; |
I get a Deadlock/Time out error
| Code: |
DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNT408I SQLCODE = -911, ERROR: THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK
DUE TO DEADLOCK OR TIMEOUT. REASON 00C9008E, TYPE OF RESOURCE
00000210, AND RESOURCE NAME BMACS40 .SMA0007 .00000001
DSNT418I SQLSTATE = 40001 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXRRC SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -190 -100 0 -1 -991424511 536870912 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'FFFFFF42' X'FFFFFF9C' X'00000000' X'FFFFFFFF'
X'C4E81001' X'20000000' SQL DIAGNOSTIC INFORMATION |
The reason code 00C9008E says
| Code: |
| A lock request for the resource identified by NAME could not be granted, and the request waited for a period longer than the maximum specified by the installation. |
Is there anyway to determine which application holds the lock on the table? |
|
| Back to top |
|
 |
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Try executing the command
| Code: |
-DISPLAY DATABASE(<Dbname>) SPACENAM(<Ts name>) LOCKS |
dbname - database in which table space resides
spacename - Table space in which table resides
or you could even check Db2 master log
and any specific reason to use RR in your test query? |
|
| Back to top |
|
|
girishbs13
New User
Joined: 11 Nov 2011
Posts: 24
Location: India
|
|
|
|
Thanks for the quick response.
This is what i get when i execute the command
| Code: |
SMA0007 TS 0005 RW TSO DMA069 H-X,P,C
- AGENT TOKEN 74477
SMA0007 TS 0006 RW TSO DMA069 H-X,P,C
- AGENT TOKEN 74477
SMA0007 TS 0007 RW TSO DMA069 H-X,P,C
- AGENT TOKEN 74477
SMA0007 TS 0008 RW TSO DMA069 H-X,P,
- AGENT TOKEN 74477
SMA0007 TS 0009 RW TSO DMA069 H-X,P,
- AGENT TOKEN 74477
SMA0007 TS 0010 RW TSO DMA069 H-X,P,
- AGENT TOKEN 74477
SMA0007 TS 0011 RW TSO DMA069 H-X,P,
- AGENT TOKEN 74477
SMA0007 TS 0012 RW TSO DMA069 H-X,P,
- AGENT TOKEN 74477
SMA0007 TS 0013 RW TSO DMA069 H-X,P,
- AGENT TOKEN 74477
SMA0007 TS 0014 RW TSO DMA069 H-X,P,
- AGENT TOKEN 74477
SMA0007 TS 0015 RW TSO DMA069 H-X,P,
- AGENT TOKEN 74477
SMA0007 TS 0016 RW TSO DMA069 H-X,P,
- AGENT TOKEN 74477
SMA0007 TS 0017 RW TSO DMA069 H-X,P,
- AGENT TOKEN 74477
SMA0007 TS 0018 RW TSO DMA069 H-X,P, |
But the User id DMA069 is not logged in.
No specific reason for using RR, I tried running the query with UR it executed successfully.
But we need to run jobs which update the table, so can you please advice me on how I can terminate the locks held by DMA069. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
| Back to top |
|
|
girishbs13
New User
Joined: 11 Nov 2011
Posts: 24
Location: India
|
|
|
|
Hi,
From what I understand its an exclusive lock held by a TSO thread on a partion, and the lock is freed at commit.
I tried executing a COMMIT but the table still remains locked, I also tried
| Code: |
DSNV401I ) DISPLAY THREAD REPORT FOLLOWS -
DSNV402I ) ACTIVE THREADS -
NAME ST A REQ ID AUTHID PLAN ASID TOKEN
TSO T * 3 DMA069 DMA069 04B3 75321
TSO D * 10 DMA069 DMA069 DSNESPCS 00C6 74477
DB2CALL T 649 LDAPR14 CMGRUSR DSNACLI 048A 75318
DB2CALL T 10915 IDB2ADC IDB2ADC 0095 2
DB2CALL T 19325 IDB2ADC IDB2ADC 0095 3
DB2CALL T 1448 IDB2ADC IDB2ADC 0095 4
DISPLAY ACTIVE REPORT COMPLETE
DSN9022I ) DSNVDT '-DIS THREAD' NORMAL COMPLETION |
I tried terminating the thread by issuing
| Code: |
| -CANCEL THREAD(74477) |
| Code: |
DSN9016I ) '-CANCEL' COMMAND REJECTED, UNAUTHORIZED REQUEST
DSN9023I ) DSN9SCND '-CANCEL ' ABNORMAL COMPLETION |
Does this mean that the thread has to be canceled by my DBA, Is this the correct way to proceed? please help. |
|
| Back to top |
|
|
saiprasadh
Active User
Joined: 20 Sep 2006
Posts: 154
Location: US
|
|
|
|
You should have any one of the authority to cancel thread.
| Quote: |
To execute this command, you must use a privilege set of the process that includes
one of the following authorities:
SYSOPR authority
SYSCTRL authority
SYSADM authority
DB2 commands that are issued from a logged-on z/OS console or TSO SDSF can
be checked by DB2 authorization using primary and secondary authorization IDs.
|
|
|
| Back to top |
|
|
girishbs13
New User
Joined: 11 Nov 2011
Posts: 24
Location: India
|
|
|
|
Sai,
Can you please tell me how I can view privileges for my userid? |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| First am afraid if they would provide the needed authority anyway check with your DB2 Administrators |
|
| Back to top |
|
|
|
|
