View previous topic :: View next topic
|
Author |
Message |
maxsubrat
Active User
Joined: 27 Feb 2008 Posts: 110 Location: india
|
|
|
|
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 |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
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 |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007 Posts: 691 Location: Earth
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
Nikhil Jain
New User
Joined: 20 Jul 2011 Posts: 16 Location: India
|
|
|
|
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. |
|
Back to top |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007 Posts: 691 Location: Earth
|
|
|
|
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 |
|
|
Nikhil Jain
New User
Joined: 20 Jul 2011 Posts: 16 Location: India
|
|
|
|
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 |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007 Posts: 691 Location: Earth
|
|
|
|
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 )
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 |
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 |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007 Posts: 691 Location: Earth
|
|
|
|
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 |
|
|
Nikhil Jain
New User
Joined: 20 Jul 2011 Posts: 16 Location: India
|
|
|
|
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 |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007 Posts: 691 Location: Earth
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Quote: |
and it does not hold any locks.
|
what you meant (should have) is 'it is not blocked by any locks' |
|
Back to top |
|
|
|