BookmarkSubscribeRSS Feed
bmsampath
Obsidian | Level 7

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

5 REPLIES 5
JuanS_OCS
Amethyst | Level 16

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

 

bmsampath
Obsidian | Level 7

Thanks for the info.

 

How to check ODBC connection pools?

JuanS_OCS
Amethyst | Level 16

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:

 

https://www.google.nl/search?q=check+odbc+connection+pool&oq=check+odbc+connection+pool&aqs=chrome.....

bmsampath
Obsidian | Level 7

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:

Access the Windows Control Panel by selecting Start > Settings >Control Panel.
Find the ODBC Data Sources or ODBC Administrator icon. This icon might be located in the Control Panel group, an ODBC group, or in the Administrative Tools group. If you have installed a package of other ODBC drivers, this icon might be in a group that is associated with that package.
 
Anyway  I will try again. Thanks.

 

 

JuanS_OCS
Amethyst | Level 16

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.

http://www.unixodbc.org/doc/conn_pool.html

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1012 views
  • 2 likes
  • 2 in conversation