topic Re: Running SAS query on DB2 in SAS Procedures

Running SAS query on DB2

Siddhartha — Thu, 18 Aug 2011 13:22:53 GMT

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

Re: Running SAS query on DB2

OS2Rules — Thu, 18 Aug 2011 15:35:20 GMT

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

Re: Running SAS query on DB2

SASKiwi — Thu, 18 Aug 2011 21:07:15 GMT

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'

Re: Running SAS query on DB2

Siddhartha — Fri, 19 Aug 2011 08:46:17 GMT

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