MVSFORUMS.com

sairamr · Beginner Joined: 08 Jun 2010 Posts: 38 Topics: 6

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

kolusu · 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

sairamr · Beginner Joined: 08 Jun 2010 Posts: 38 Topics: 6

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

kolusu · Posted: Tue Feb 15, 2011 3:17 pm Post subject:

sairamr · Beginner Joined: 08 Jun 2010 Posts: 38 Topics: 6

One question..does this have to do with the complete SP execution ?
Or is it one single query that is causing this issue ?

papadi · Supermod Joined: 20 Oct 2009 Posts: 594 Topics: 1

If i understand what your process is doing, the time-out is on the accumulated time for the process.
_________________
All the best,

di

sairamr · Beginner Joined: 08 Jun 2010 Posts: 38 Topics: 6

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

papadi · Supermod Joined: 20 Oct 2009 Posts: 594 Topics: 1

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

jsharon1248 · 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.

sairamr · Beginner Joined: 08 Jun 2010 Posts: 38 Topics: 6

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..

dbzTHEdinosauer · 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

sairamr · Beginner Joined: 08 Jun 2010 Posts: 38 Topics: 6

Dick..That kind of questions the basic requirement..

So you say even 1 or 2 EXCI calls could drain the CPU time ?

dbzTHEdinosauer · 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

sairamr · Beginner Joined: 08 Jun 2010 Posts: 38 Topics: 6

SP is invoked by .NET front end..hence we are linking to CICS onlines via EXCI..

sairamr · Beginner Joined: 08 Jun 2010 Posts: 38 Topics: 6

To answer the other question..we are uploading a CSV file through front end ..this file can contain like 30K records..