Running SAS query on DB2

[Siddhartha]

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

[OS2Rules]

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]

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]

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