View previous topic :: View next topic |
Author |
Message |
sairamr Beginner
Joined: 08 Jun 2010 Posts: 38 Topics: 6
|
Posted: Tue Feb 15, 2011 2:17 pm Post subject: SQL0905N Unsuccessful execution due to resource limit - DB2 |
|
|
Hi
I am trying to run a Stored Procedure and it results in SQL0905..I tried to google and arrived at issue with ASUTIME. The ASUTIME defined for the SP is huge..but somehow it does not complete within that time frame.
The SP reads like 20K records and some of the things done are
1) DB2 queries ofcourse (they do use the index) and look optimal. There are roughly about 5-6 queries ran for each record.
2) EXCI links to 2 online programs
3) Normal CALL to 2 simple programs
Note that the elapsed time is hardly 2 minutes but still the SP fails with ASUTIME excess. Please comment if there are any pointers to look for |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12369 Topics: 75 Location: San Jose
|
Posted: Tue Feb 15, 2011 2:29 pm Post subject: |
|
|
sairamr,
When you get the SQL0905N you also get the limits and the source listed along with the message. Check and see if the limit you are checking and the limit SP is validating is the same.
Unsuccessful execution due to resource limit being exceeded. Resource name = resource-name, limit = limit-amount1 CPU seconds (limit-amount2 service units) derived from limit-source.
Kolusu |
|
Back to top |
|
|
sairamr Beginner
Joined: 08 Jun 2010 Posts: 38 Topics: 6
|
Posted: Tue Feb 15, 2011 2:33 pm Post subject: |
|
|
Failed to upload file. Cause: ERROR [57014] [IBM][DB2] SQL0905N Unsuccessful execution due to resource limit being exceeded. Resource name = "DBTPIAC.IACS315A", limit = "0000000021" CPU seconds ("0000764532" service units) derived from "". SQLSTATE=57014 |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12369 Topics: 75 Location: San Jose
|
Posted: Tue Feb 15, 2011 3:17 pm Post subject: |
|
|
sairamr wrote: | Failed to upload file. Cause: ERROR [57014] [IBM][DB2] SQL0905N Unsuccessful execution due to resource limit being exceeded. Resource name = "DBTPIAC.IACS315A", limit = "0000000021" CPU seconds ("0000764532" service units) derived from "". SQLSTATE=57014 |
Sairamr,
I don't see the source of the limit. ASUTIME parameter is found in the system table named SYSIBM.DSNRLST01. Check with your DBA as to why that source is coming up as empty. Learn more here
https://www-304.ibm.com/support/docview.wss?uid=swg21233702
Kolusu _________________ Kolusu
www.linkedin.com/in/kolusu |
|
Back to top |
|
|
sairamr Beginner
Joined: 08 Jun 2010 Posts: 38 Topics: 6
|
Posted: Tue Feb 15, 2011 3:35 pm Post subject: |
|
|
One question..does this have to do with the complete SP execution ?
Or is it one single query that is causing this issue ? |
|
Back to top |
|
|
papadi Supermod
Joined: 20 Oct 2009 Posts: 594 Topics: 1
|
Posted: Tue Feb 15, 2011 3:51 pm Post subject: |
|
|
If i understand what your process is doing, the time-out is on the accumulated time for the process. _________________ All the best,
di |
|
Back to top |
|
|
sairamr Beginner
Joined: 08 Jun 2010 Posts: 38 Topics: 6
|
Posted: Tue Feb 15, 2011 3:59 pm Post subject: |
|
|
My primary suspects include
1) EXCI calls
2) XML PARSE of input done by SP
3) DB2 queries (note that there are no dynamic queries)
4) non-EXCI calls |
|
Back to top |
|
|
papadi Supermod
Joined: 20 Oct 2009 Posts: 594 Topics: 1
|
Posted: Wed Feb 16, 2011 12:09 am Post subject: |
|
|
It sounds like the process is trying to do too many "things" for an online transaction.
XML processing sucks a lot of cpu and poorly designed db2 queries (not dynamic) have been known to shut down a z10. . .
Suggest you do some timing tests of the individual pieces and see which are the worst offenders. _________________ All the best,
di |
|
Back to top |
|
|
jsharon1248 Intermediate
Joined: 08 Aug 2007 Posts: 291 Topics: 2 Location: Chicago
|
Posted: Wed Feb 16, 2011 8:36 am Post subject: |
|
|
The defined ASUTIME seems adequate. Have you done analysis to see which process is the hog? If not, load up the stored proc with DISPLAYs to show the start/end times of each process. That won't show CPU time, but the elapsed times might give you some insight into where the problem is. Have you conducted a recent performance evaluation of the SQL? Any recent DB2 maintenance, REORGs, RUNSTATs, BINDs/REBINDs? What is the I/O like in the CALLed modules? Has the process ever completed in reasonable times before? Identifying what a process does is only scratching the surface of a performance problem. You need to isolate the issue before you can resolve it. |
|
Back to top |
|
|
sairamr Beginner
Joined: 08 Jun 2010 Posts: 38 Topics: 6
|
Posted: Wed Feb 16, 2011 9:58 am Post subject: |
|
|
Many thanks for the inputs..
The only area which has never been explored is XML parse..and we are trying to see if it is a CPU drain
From the start we have been facing these issues..initially the SP struggled to load 10k records. We did the following and now we are stuck at 20K
1) Removed display statements (there were many)
2) Removed some EXCI calls and replaced with table calls
The elapsed time is small most of the time (as low as 2 minutes many times). So that does not give an idea of CPU time.
But my biggest suspects are
1) INSERT statements (we dont have COMMIT inbetween). Could it be a reason ?
2) XML parse..So far we have not seen how the performance can be improved for this section.. |
|
Back to top |
|
|
dbzTHEdinosauer Supermod
Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
|
Posted: Wed Feb 16, 2011 11:24 am Post subject: |
|
|
Though everything can be configured to finally get these row-crunching ONLINE modules to work,
when you are talking about more than 10 -20 rows, you should be running this process in batch...... _________________ Dick Brenholtz
American living in Varel, Germany |
|
Back to top |
|
|
sairamr Beginner
Joined: 08 Jun 2010 Posts: 38 Topics: 6
|
Posted: Wed Feb 16, 2011 11:36 am Post subject: |
|
|
Dick..That kind of questions the basic requirement..
So you say even 1 or 2 EXCI calls could drain the CPU time ? |
|
Back to top |
|
|
dbzTHEdinosauer Supermod
Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
|
Posted: Thu Feb 17, 2011 8:06 am Post subject: |
|
|
They as well as XML.
why do you need EXCI calls?
is your SP not invoked via some front end thru CICS?
That means the SP starts in CICS. why jump to batch?
What I meant by my previous post, is that the process should not be done in a CICS environment.
What is the front end that requires manipulation of 20,000 rows?
I would propose: The CICS environment activity could be a write to a MQ, which would trigger batch to do all this processing and then write the results back to MQ.
which could be read by a front-end task. _________________ Dick Brenholtz
American living in Varel, Germany |
|
Back to top |
|
|
sairamr Beginner
Joined: 08 Jun 2010 Posts: 38 Topics: 6
|
Posted: Thu Feb 17, 2011 1:21 pm Post subject: |
|
|
SP is invoked by .NET front end..hence we are linking to CICS onlines via EXCI.. |
|
Back to top |
|
|
sairamr Beginner
Joined: 08 Jun 2010 Posts: 38 Topics: 6
|
Posted: Thu Feb 17, 2011 1:22 pm Post subject: |
|
|
To answer the other question..we are uploading a CSV file through front end ..this file can contain like 30K records.. |
|
Back to top |
|
|
|
|