topic Set Connection time out for DB2 in SAS Enterprise Guide

Set Connection time out for DB2

deleted_user — Wed, 10 Nov 2010 09:18:12 GMT

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

Re: Set Connection time out for DB2

TomKari — Sun, 14 Nov 2010 14:08:54 GMT

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

Re: Set Connection time out for DB2

deleted_user — Thu, 25 Nov 2010 11:38:43 GMT

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