Architecting, installing and maintaining your SAS environment

proc sql statement continues to run in db server

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

proc sql statement continues to run in db server

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,

 


Accepted Solutions
Solution
‎02-20-2018 07:27 AM
Trusted Advisor
Posts: 1,737

Re: proc sql statement continues to run in db server

[ Edited ]
Posted in reply to putteringpluie

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


All Replies
Frequent Contributor
Posts: 133

Re: proc sql statement continues to run in db server

Posted in reply to putteringpluie

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

Occasional Contributor
Posts: 9

Re: proc sql statement continues to run in db server

Posted in reply to boemskats

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. 

Super User
Posts: 3,853

Re: proc sql statement continues to run in db server

Posted in reply to putteringpluie

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 clearSmiley Wink or the EG session is closed. This appears to be the behaviour you are seeing.

Solution
‎02-20-2018 07:27 AM
Trusted Advisor
Posts: 1,737

Re: proc sql statement continues to run in db server

[ Edited ]
Posted in reply to putteringpluie

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.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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