BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
putteringpluie
Obsidian | Level 7

Users successfully run a proc sql statement connecting to Greenplum server from Enterprise Guide. But 2 hours after the statement was successfully completed, the sql process continues to linger in the database server. The DB admins are not happy about it. Is this common? I think that EG query builder automatically adds a "quit;" statement at the end of the query, no?

 

Thanks,

 

1 ACCEPTED SOLUTION

Accepted Solutions
JuanS_OCS
Amethyst | Level 16

Hello @putteringpluie,

 

as indicated, if your users just use a libname statement, the connection will last as long as the libname statement is assigned.

For this purpose, once finished, the code will have to include a libname clear statement (http://support.sas.com/documentation/cdl/en/hostwin/69955/HTML/default/viewer.htm#chloptfmain.htm + http://support.sas.com/documentation/cdl/en/hostwin/69955/HTML/default/viewer.htm#p1janotyd8q0qrn1jh...)

 

A very recommended feature, if your SAS/ACCESS engine allows it, is to pass the query statements with passthrough statements, which alllows to use the CONNECT andDISCONNECT functionality. You will be in control of your DB connections and will run with improved performance:

http://documentation.sas.com/?docsetId=lrcon&docsetTarget=n1kbstf7vw0qcjn1ibfc8c78a9lc.htm&docsetVer...

 

At last but not the least, I would like to recommend you to read this great paper, of DBMS usage on SAS Enterprise Guide: https://support.sas.com/resources/papers/proceedings11/306-2011.pdf

 

Please note: also, as long as your EG session is open, connected to the Workspace Server (SASApp), the SAS session is running on the server, meaning that ig the code has not closed the connection, the SAS process will have the DB connection open, indefinitely, until the EG session is closed or times-out.

 

All in all, I think the short and important message and recommendation is to adapt some working standards.

 

View solution in original post

4 REPLIES 4
boemskats
Lapis Lazuli | Level 10

When you say the 'sql process', do you mean the session's connection to the database?

 

Having known a few DBAs, it's the kind of thing they'd get itchy about (for very little reason). The thing is, the connection won't be closed at the end of your PROC SQL statement, but rather for the duration of your libname assignment. This link will give you more insight into how it all works:

 

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001342247.htm

 

Nik

putteringpluie
Obsidian | Level 7

Thanks Nik for the reply,

 

When the DBA looks for 'long-running' jobs on the DB server he can see the select statement and the join statement generated by the EG Query Builder, and it looks as if the query is still running on the DB server. 

 

The EG user tells me that the query was completed a while back and the data was returned to him successfully, so my question is shouldn't the query disappear from the DB server after it completes? It goes away when EG application is closed completely but not when the user just minimizes EG and steps away from the computer. 

SASKiwi
PROC Star

If the EG Query Builder is connecting to the db server with a LIBNAME statement then the database connection will remain active but not consuming any resources until the LIBNAME itself is cleared (libname libref clear;) or the EG session is closed. This appears to be the behaviour you are seeing.

JuanS_OCS
Amethyst | Level 16

Hello @putteringpluie,

 

as indicated, if your users just use a libname statement, the connection will last as long as the libname statement is assigned.

For this purpose, once finished, the code will have to include a libname clear statement (http://support.sas.com/documentation/cdl/en/hostwin/69955/HTML/default/viewer.htm#chloptfmain.htm + http://support.sas.com/documentation/cdl/en/hostwin/69955/HTML/default/viewer.htm#p1janotyd8q0qrn1jh...)

 

A very recommended feature, if your SAS/ACCESS engine allows it, is to pass the query statements with passthrough statements, which alllows to use the CONNECT andDISCONNECT functionality. You will be in control of your DB connections and will run with improved performance:

http://documentation.sas.com/?docsetId=lrcon&docsetTarget=n1kbstf7vw0qcjn1ibfc8c78a9lc.htm&docsetVer...

 

At last but not the least, I would like to recommend you to read this great paper, of DBMS usage on SAS Enterprise Guide: https://support.sas.com/resources/papers/proceedings11/306-2011.pdf

 

Please note: also, as long as your EG session is open, connected to the Workspace Server (SASApp), the SAS session is running on the server, meaning that ig the code has not closed the connection, the SAS process will have the DB connection open, indefinitely, until the EG session is closed or times-out.

 

All in all, I think the short and important message and recommendation is to adapt some working standards.

 

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 2277 views
  • 3 likes
  • 4 in conversation