IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Issue with -911


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
maxsubrat

Active User


Joined: 27 Feb 2008
Posts: 110
Location: india

PostPosted: Fri Feb 10, 2012 3:36 pm
Reply with quote

Hi,

I am getting some issue with -911. Here is the details below.

I am running one DB2 SQL query to fetch data from DB2 tables everyday for report purpose. But most of the time i am getting -911 errors, that means i have to run that query no. of times till it completes successfully to fetch all the data, because most of the time the query is giving -911 error. sometimes while running the query, other production jobs abending with -911. So it has contention with other production jobs.

So how to fix this issue by allocating the resource properly so that no other jobs abend will happen in production while running this query and also will get all the data successfully with a single run of that query.

Any suggestion is welcome. Thanks
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Feb 10, 2012 5:06 pm
Reply with quote

Better to run this query using "WITH UR" clause...

Or you can schedule a job to run this query... and put a proper dependency....
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 691
Location: Earth

PostPosted: Tue Feb 14, 2012 3:14 am
Reply with quote

I think Bharat gave an apt answer your question. I am just adding my two cents-
If you set on manually running this query everyday, you should consider running your query at a time outside the batch window i.e. when production jobs are not accessing this resource.

If this is not possible, WITH UR should avoid the conflict with production jobs but there is one caveat- You may end up doing dirty read.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Feb 14, 2012 9:53 am
Reply with quote

maxsubrat,

though the suggestions provided may enable you to run, most of the time, without encountering a -911,
it would probably be better to review the REASON reason code and the TYPE OF RESOURCE resource type.

a query WITH UR only reads thru locks.
trying to insure that nothing returns a -911 by using WITH UR
may not make good business sense.

myself, i have always handled -911's and -913's in BATCH by
interrogating the reason code and and resource type thru code
and if reasonable,
issue a delay by CALLing an assembler pgm which issues a WAIT Macro for a second or so,
and then retry the query
(if necessary, looping 5 or 10 times)
but that only works with imbedded sql.
otherwise, you need to determine why you are timing out and solve the problem.

and before you ask,
the code necessary for the interrogation and the WAIT Macro
is available on the net for free, or you can pay me, and I will provide it.
Back to top
View user's profile Send private message
Nikhil Jain

New User


Joined: 20 Jul 2011
Posts: 16
Location: India

PostPosted: Tue Feb 14, 2012 3:52 pm
Reply with quote

gylbharat wrote:
Better to run this query using "WITH UR" clause...


If you are running your query in SPUFI, please ensure that the SPUFI Isolation Level is also set to UR. Plese be aware that the SPUFI Isolation Level overrides your SQL Query Isolation Level. icon_smile.gif
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 691
Location: Earth

PostPosted: Wed Feb 15, 2012 3:23 am
Reply with quote

Quote:
Plese be aware that the SPUFI Isolation Level overrides your SQL Query Isolation Level.


Hi Nikhil,
Could you please let us know the source of this information? I don't think it is true.

In fact, it is the other way around. Default isolation level comes into play when user doesn't specify the isolation level. Please check.
Back to top
View user's profile Send private message
Nikhil Jain

New User


Joined: 20 Jul 2011
Posts: 16
Location: India

PostPosted: Wed Feb 15, 2012 6:42 pm
Reply with quote

agkshirsagar wrote:
Quote:
Plese be aware that the SPUFI Isolation Level overrides your SQL Query Isolation Level.


Hi Nikhil,
Could you please let us know the source of this information? I don't think it is true.

In fact, it is the other way around. Default isolation level comes into play when user doesn't specify the isolation level. Please check.


Hi agkshirsagar,

This is something which I have learnt from my own experience. I have in fact got it verified by the DBAs also.

Even after mentioning WITH UR in the SQL Query, the tables were getting locked. We could resolve this issue only when we changed our SPUFI Default to UR from RR.
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 691
Location: Earth

PostPosted: Fri Feb 17, 2012 10:11 am
Reply with quote

Hello,
I did a little experiment and results confused me even more.
I tested a long running query with SPUFI with different isolation levels. For RR, RS and CS, the results were as expected.

When I tested a WITH UR query with CS as the SPUFI default and I got CS claimers. (Round 1 goes to Nikhil icon_smile.gif )

Code:
DSNT397I  -TEST                                                             
 NAME     TYPE PART  STATUS            CONNID   CORRID      CLAIMINFO         
 -------- ---- ----- ----------------- -------- ------------ --------         
 XXXXXXX  TS         RW                TSO      TESTAPP      (CS,C)           
     -                  AGENT TOKEN 64618                                     
     -                  MEMBER NAME TEST                                     
 ******* DISPLAY OF DATABASE XXXXXXXX ENDED      **********************       
 DSN9022I  -TEST DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION               
 ***                                                                         


However, when I executed the same query WITH UR and UR as the SPUFI default, I still got the CS claimers on the tables
Code:
DSNT397I  -TEST                                                           
NAME     TYPE PART  STATUS            CONNID   CORRID      CLAIMINFO     
-------- ---- ----- ----------------- -------- ------------ --------     
XXXXXXX  TS         RW                TSO      TESTAPP      (CS,C)       
    -                  AGENT TOKEN 65514                                 
    -                  MEMBER NAME TEST                                   
******* DISPLAY OF DATABASE XXXXXXXX ENDED      **********************   
DSN9022I  -DB2A DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION             
icon_confused.gif icon_confused.gif icon_confused.gif icon_confused.gif Any thoughts?
I ruled out the usual suspects- the SPUFI plan is bound with isolation UR. Table has level 2 and level 3 indexes. AUTOCOMMIT is YES. With AUTOCOMMIT = NO; as expected, the lock is retained while browsing the results.
Code:
Cm Plan     Owner   Valdat Isolat Valid Operat Acq Rel Bound      Member   Dyn
 ----v----1----v----2----v----3----v----4----v----5----v----6----v----7----v----
    DSNESPUR XXXXXXXX    R      U     Y      Y    U   C  2011-03-13 TEST       
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 691
Location: Earth

PostPosted: Fri Feb 17, 2012 10:55 am
Reply with quote

I did some further reading and turns out a CS claim could indicate read stability (RS), cursor stability (CS), or uncommitted read (UR) isolation. So it doesn't matter if the claim information shows CS. (When I tested the query with RS, the claim code was CS. I should have mentioned that in my last post).
So I decided to see the lockinfo this time and WITH UR does not retain any locks, regardless of SPUFI default.

WITH CS lock info-

Code:
NAME     TYPE PART  STATUS            CONNID   CORRID       LOCKINFO 
-------- ---- ----- ----------------- -------- ------------ ---------
XXXXXXXX TS         RW                TSO      TESTAPP      H-IS,S,C 


Please let me know if I missed anything.
Back to top
View user's profile Send private message
Nikhil Jain

New User


Joined: 20 Jul 2011
Posts: 16
Location: India

PostPosted: Fri Feb 24, 2012 7:05 pm
Reply with quote

Nikhil Jain wrote:

We could resolve this issue only when we changed our SPUFI Default to UR from RR.


Hi agkshirsagar,

Sorry for the late reply but, I was terribly occupied with work.Can you please try the above mentioned combination & let us know your findings.
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 691
Location: Earth

PostPosted: Fri Feb 24, 2012 9:49 pm
Reply with quote

Please read my posts again. I did try your combination. And I posted my results. I was confused initially but I found my answer.

I have shown proof that with UR in query works regardless of the SPUFI defaults and it does not hold any locks.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sat Feb 25, 2012 2:15 am
Reply with quote

Quote:
and it does not hold any locks.


what you meant (should have) is 'it is not blocked by any locks'
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Issue after ISPF copy to Linklist Lib... TSO/ISPF 1
No new posts Facing ABM3 issue! CICS 3
No new posts Panvalet - 9 Character name - Issue c... CA Products 6
No new posts Issue with EXEC CICS QUERY SECURITY c... CICS 6
Search our Forums:

Back to Top