Desktop productivity for business analysts and programmers

Set Connection time out for DB2

Reply
N/A
Posts: 0

Set Connection time out for DB2

Hi all,

I would like to set a time out for my connections to DB2 i.e. that the connection will drop after x seconds. I have been using MS Access for testing SQL but would like to use EG going forwards. The remaining hurdle is that SQL will only be killed by the Query governor server side where as in Access I can easily set a time out of say 60 seconds.

I am using the following method to connect:-

libname wrkcon db2 database=db/connection name schema=wrkcon;
PROC SQL;
SELECT *
FROM CONNECTION TO DB2(
SELECT * FROM SCHEMA.TABLE
FETCH FIRST 10 ROWS ONLY
FOR READ ONLY
WITH UR
Smiley Wink
quit;
run;

I have found various sources of information re CONNECTION properties but these all relate to the period that EG will try to connect rather than how long it will remain open.
I am hoping it's an option in Guide (4.2) that I have missed

Many thanks.
Trusted Advisor
Posts: 1,061

Re: Set Connection time out for DB2

You need to look in the SAS/Access for DB2 documentation to find the correct LIBNAME option that will change the behavior of the DB2 server. I've had to do this a few times with a different database, and some trial and error experimentation usually works quite quickly.

The document you want from support.sas.com is:
SAS/ACCESS(R) 9.2 for Relational Databases

and the section is the "LIBNAME Statement Specifics for DB2", either for UNIX/PC or ZOS.

Looking at the UNIX page, I would be tempted to try "QUERY_TIMEOUT". Another option is to pass the setting in through "DBCONINIT".

Tom
N/A
Posts: 0

Re: Set Connection time out for DB2

Hi Tom,

Many thanks for the response and sorry for not responding sooner (vacation time:-)). Good point well made. I think I might have been into that doc or one like it. The TIMEOUT setting looked to be how long the connection would try to establish for before giving up. I'll double check your doc though plus the CONNINIT definately looks worth a try.

Thanks again
Ask a Question
Discussion stats
  • 2 replies
  • 1487 views
  • 0 likes
  • 2 in conversation