Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Time out -911

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
girishbs13

New User


Joined: 11 Nov 2011
Posts: 24
Location: India

PostPosted: Sat Jun 02, 2012 9:17 am    Post subject: Time out -911
Reply with quote

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
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Sat Jun 02, 2012 12:41 pm    Post subject:
Reply with quote

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
View user's profile Send private message
girishbs13

New User


Joined: 11 Nov 2011
Posts: 24
Location: India

PostPosted: Sat Jun 02, 2012 2:45 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Sat Jun 02, 2012 3:23 pm    Post subject:
Reply with quote

Check herefor explanation of H-X,P,C
Back to top
View user's profile Send private message
girishbs13

New User


Joined: 11 Nov 2011
Posts: 24
Location: India

PostPosted: Mon Jun 04, 2012 10:37 am    Post subject:
Reply with quote

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:
-DIS THREAD(*) 


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
View user's profile Send private message
saiprasadh

Active User


Joined: 20 Sep 2006
Posts: 154
Location: US

PostPosted: Tue Jun 05, 2012 9:22 am    Post subject:
Reply with quote

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
View user's profile Send private message
girishbs13

New User


Joined: 11 Nov 2011
Posts: 24
Location: India

PostPosted: Tue Jun 05, 2012 2:21 pm    Post subject:
Reply with quote

Sai,

Can you please tell me how I can view privileges for my userid?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jun 05, 2012 2:38 pm    Post subject:
Reply with quote

First am afraid if they would provide the needed authority anyway check with your DB2 Administrators
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Get a job submitted itself every time... arunsoods JCL & VSAM 3 Tue Nov 22, 2016 4:05 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am
This topic is locked: you cannot edit posts or make replies. Get correct date and time when curren... balaji81_k DB2 24 Fri Oct 14, 2016 10:40 pm
No new posts cics & real time monitoring icehat CICS 5 Wed Sep 07, 2016 2:29 am
No new posts Get system time in micro second or cl... lind sh CICS 11 Mon Sep 05, 2016 11:14 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us