topic proc sql statement continues to run in db server in Administration and Deployment

proc sql statement continues to run in db server

putteringpluie — Mon, 19 Feb 2018 16:47:06 GMT

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,

Re: proc sql statement continues to run in db server

boemskats — Mon, 19 Feb 2018 17:17:35 GMT

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

Re: proc sql statement continues to run in db server

putteringpluie — Mon, 19 Feb 2018 17:37:33 GMT

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.

Re: proc sql statement continues to run in db server

SASKiwi — Mon, 19 Feb 2018 19:11:42 GMT

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.

Re: proc sql statement continues to run in db server

JuanS_OCS — Tue, 20 Feb 2018 09:03:57 GMT

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#p1janotyd8q0qrn1jhkin21462in.htm)

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&docsetVersion=9.4&locale=en

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.