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: 1534
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: 1534
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: 1534
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
No new posts LISTIDR compiled date/time jerryte IBM Tools 3 Thu Apr 20, 2017 7:37 pm
No new posts Application not run by time HH:MM tri... jzhardy IBM Tools 1 Sun Apr 09, 2017 3:22 pm
No new posts Date and time format in CICS Chandru3183 CICS 2 Sat Mar 18, 2017 12:46 pm
No new posts How to get current date -1 and curren... murali.andaluri DFSORT/ICETOOL 3 Wed Mar 08, 2017 2:09 pm
No new posts CICS transaction slow response time vasanthz All Other Mainframe Topics 3 Thu Jan 19, 2017 1:31 am


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