Hi ,
SAS EG session hangs when we try to connect ODBC multiple times for each query. This issue was raised by our business. I replicated using below code.
If we use single connection for all queries, this issue is not coming.
Multi- connection(Not working. Log shows looping stops after 10 times):
-----------------------
%MACRO LOOP1;
PROC PRINTTO LOG="/app/sas/config/Lev1/SASApp/WorkspaceServer/Logs/multiconnection";
%DO I=1 %TO 100;
proc sql;
connect to odbc(dsn=SSDATAGX_DEV authdomain=SSDATAGX);
create table dbs_test&I as select * from connection to odbc
(
select * from datamart
);
disconnect from odbc;
quit;
%END;
%MEND LOOP1;
%LOOP1
Single - Connection(Working without issues):
---------------------------
%MACRO LOOP1;
PROC PRINTTO LOG="/app/sas/config/Lev1/SASApp/WorkspaceServer/Logs/singleconnection";
proc sql;
connect to odbc(dsn=SSDATAGX_DEV authdomain=SSDATAGX);
%DO I=1 %TO 100;
create table dbs_test&I as select * from connection to odbc
(
select * from datamart
);
%END;
disconnect from odbc;
quit;
%MEND LOOP1;
%LOOP1
Could you please suggest a solution to resolve this issue?
Thanks,
Sampath
Hello @bmsampath,
you should share this with your DBAs and system admins. I would check the size of your ODBC connection pools.
I also suggest to enable logging in your ODBC connections, and in SAS EG.
For SAS EG:
http://support.sas.com/kb/55/414.html
or
http://support.sas.com/kb/17/730.html
Thanks for the info.
How to check ODBC connection pools?
In the settings of your ODBC manager. If you or your system admins do not know (at least they should), google can help to the rescue:
In our platform, ODBC drivers are installed in Linux.
I don't know how to check it.
In google, I got info about Windows based system.
For example:
Hello @bmsampath,
in the Google link I have shared with you, the 3rd search occurrence is actually Linux related.
In Linux, ODBC can be configured with unixODBC, iODBC and a few others.
The 3rd search occurrence is about unixODBC but it illustrative quite well for all of them.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.