BookmarkSubscribeRSS Feed
Siddhartha
Calcite | Level 5

Hi,

I tried to use the MVSW as well as analysis environment to execute the below query on DB2 (SAS code) for single day, as I need to fetch the data for the entire month. Unfortunately, I still get an error due to insufficient ASU time:

ERROR: Error fetching from cursor. DB2 error is DSNT408I SQLCODE = -905, ERROR: UNSUCCESSFUL EXECUTION DUE TO RESOURCE LIMIT

BEING EXCEEDED, RESOURCE NAME = ASUTIME LIMIT = 000000000009 CPU SECONDS (000000450000 SERVICE UNITS) DERIVED FROM

SYSIBM.DSNRLST01.

Could any one advise on this.

SAS code:

signoff host;
%let host = mvsw  9450;
signon host user = sid_14 pass = _prompt_;

rsubmit host;
libname outlib "sid_14.SAS.COLL.SERV.DATA" disp=old;
proc sql inobs =max;
  connect to db2;
  create table outlib.opk_r_A as
    select * from connection to db2
(select opts from  DKDDBPE_DB2P.EO.CS_OPK_FASTE_S  
   WHERE   DATE(OPTS) >= '2011-06-01'                                 
AND     DATE(OPTS) <=  '2011-06-01'                                 
AND     BEAKST IN ('U') 
    
);
  %put &sqlxrc &sqlxmsg;
  disconnect from db2;
quit;

proc download in=outlib out=sid;
select opk_r_A;
run;
endrsubmit;

Thanking you,

Siddhartha

3 REPLIES 3
OS2Rules
Obsidian | Level 7

Siddhartha:

I assumme that you are running this query agaist a mainframe DB2 database?

I think you may have run into a processing limitation that has been put in place by the mainfram DBA's to prevent queries taking too long a slowing down the database.  There are several possible solutions:    

1) Call you friendly mainframe DBA and appeal to their good nature to change the processing limit,

2) See if you can run the query from an account that has more priority on the mainframe - often these accounts have different limits.

3) Run the query in smaller 'chunks' - such as weeks, and then combine the results,

4) Call your friendly mainframe DBA (again) as get the index variables for the database to optimize your query - changing (or adding/removing) just one variable from the query can make a huge difference to the CPU time.

Your chances of 1) or 2) working are slim.  I recommend trying to make the query as efficient as possible to reduce the CPU overhead.  Check how many records are returned in a 1 day query and the CPU time and then multiply by the total number of days your are extracting as a (very) rough estimate of the total CPU time you need.  Then you can check how many records are returned by the failed query with the full month.  That can give you an idea of how far the query got before it failed and how much time the query will need.

As you can see I've hit this wall too...

SASKiwi
PROC Star

I suggest you also look at this part of your query:

WHERE   DATE(OPTS) >= '2011-06-01'                                 

AND     DATE(OPTS) <=  '2011-06-01'

This brings back ALL rows. I am wondering if you meant to do:

WHERE   DATE(OPTS) >= '2011-06-01'                                 

AND     DATE(OPTS) <  '2011-07-01'                                 

Siddhartha
Calcite | Level 5

Hi,

I am extracting the data for one day ( i.e., 2011-06-01) but I need to extract for the whole month.

So for single day only I am getting the error regarding ASU time.

Regards,

Siddhartha

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2444 views
  • 0 likes
  • 3 in conversation