topic Re: PROC SQL - db2 timeout error i.e SQLCODE -905 in SAS Procedures

PROC SQL - db2 timeout error i.e SQLCODE -905

saroj — Mon, 22 Mar 2010 05:12:33 GMT

Hi I am trying to retrieve a large amount of data from Db2 database table using proc SQL and recieving a timeout error.

ERROR: Error fetching from cursor. DB2 error is DSNT408I SQLCODE = -905,
ERROR: UNSUCCESSFUL EXECUTION DUE TO RESOURCE LIMIT BEING EXCEEDED, RESOURCE NAME = ASUTIME LIMIT = 000000000054 CPU SECONDS

is there anyway to increase the time ??

Re: PROC SQL - db2 timeout error i.e SQLCODE -905

sbb — Mon, 22 Mar 2010 12:36:59 GMT

First off, it's quite useful to share your SAS code in order to gain feedback from other individuals about optimizing the query possibly.

But it's likely you will need to discuss your situation with your DB2 SYSPROG / DBA personnel with your particular question.

And an Internet search on your keywords is also quite revealing with various answers, as well.

Scott Barry
SBBWorks, Inc. Message was edited by: sbb

Re: PROC SQL - db2 timeout error i.e SQLCODE -905

OS2Rules — Tue, 23 Mar 2010 13:59:32 GMT

You are going to have to talk to the DBA responsible for DB2.

The timeout is caused by a default that prevents one task from "hogging" the database when other tasks need it. (From experience, the DBA will usually NOT change the timeout value for you). I also find the a production job has a higher timeout than a test job so you can see what you can do there.

Other things to consider: make your SQL more efficient by using index variables in your SELECT or WHERE clauses, or use PASSTHRU.