08-02-2016 02:06 AM
I have an EG project where it took about 2 hours to copy data from an RDBMS into SAS.
I then submitted PROC SQL to join the various tables. I then noticed a bug in the submitted code that would return incorrect results from the join.
So I clicked Stop. And waited...and waited...and waited.
Killing EG via Task Manager seems a little extreme. Plus I then lose the 2 hours of previous processing.
When I click Stop in SQL Server Management Studio, it always instantly stops. Sure would be great if EG worked that way...
Are there any tricks to getting EG to stop in this scenario, or is it just the way it is?
08-02-2016 02:45 AM
Hey, Scott - your EG is presumably on your workstation, but where is the underlying SAS that's doing the work - also on the workstation, or on a server somewhere?
08-02-2016 03:31 AM
The most convenient way is to use OS command KILL . But that need OS Admin right. You can resort to your Admin to help you kill that sas process.
08-02-2016 04:38 AM - edited 08-02-2016 04:46 AM
I don't want to kill the process, otherwise I lose my 2 hours work. I want EG to stop when I click stop.
Like most of other client-server products out there (ok, my sample size is only SQL Server Management Studio and the Oracle client, both of which allow me to stop a query)
08-02-2016 03:33 AM
In my experience EG only stops SAS server processing reliably and quickly. If your project is querying an external database from your SAS server then EG doesn't appear to be able to forward a stop signal to the external database. This is at least the case with SAS/ACCESS to ODBC which is what we use to query external databases. I'd be interested to hear if users of other SAS/ACCESS products have different experiences.
BTW if you end up killing EG via Task Manager, you will still probably have a SAS Workspace session continuing on the server with the database connection still active. These sessions can be killed if you have SAS server admin access.
08-02-2016 04:44 AM
The 2 hours work was:
proc copy in=odbc_lib out=work;
The SQL join was then against 3 work datasets.
I suspect EG can only work with what SAS Foundation gives it. I just wish SAS would update the interrupt processing in the workspace server so its products work better.
I'd love for this suspicion to be incorrect, and there is a reliable way to get EG to stop when I click stop.
I don't have SAS server admin access. Hopefully my SAS Admin runs some utility on a nightly basis to kill the zombie SAS workspace server processes, or else they otherwise die of their own accord since the connection to EG is dropped when I (usually but not in this case) nuke it via Task Manager.
08-02-2016 08:13 AM
I can confirm that when Enterprise Guide is running a long query against a database, using the STOP button won't cause SAS to interrupt the database query. I've had a lot of experience using EG against different database products, and have seen this behaviour quite a few times.
Out of curiousity, have you ever checked with SAS DM if you close the SAS session, does the database query keep running?
08-02-2016 09:53 AM
I haven't checked, but it often feels to me like there is nothing I can do to kill a query to a remote database. I don't think killing EG or even killing the workspace server session would necessarily do it (BTW, I don't think you need to be root or admin to kill the server session, as long as it is running as you, I suppose maybe a pooled workspace server doesn't run jobs as you). I think you end up with a zombie query running on the database. Unless the database itself can detect that nothing is listening anymore.
So I agree with @ScottBass, would be nice to have a good way to interrupt a query. And agree, it's probably more of a SAS/ACCESS thing than an EG thing, as SAS/ACCESS would need a way to send that interupt signal to the database server, at the same time as it is just sitting there, waiting for a response.
08-02-2016 10:42 AM
How about this custom task to list/stop SAS sessions? Or PROC IOMOPERATE? Both of these do require connection information for the SAS Object Spawner. You can try these options from a second EG session that should be able to see the process started by your busy EG session.
I'm sorry that you're experiencing a delay/hang when trying to Stop a job. This was a big problem before SAS 9.2 -- the "cancel" button really did nothing because a busy SAS process would never yield long enough to receive a Cancel message. However, in SAS 9.2 the Cancel was "fixed" to behave basically the same as Ctrl-Break in SAS Display Manager, "Terminate all submitted statements". Even with that though, a job that has been farmed out to a database might keep running.
08-02-2016 03:33 PM
2 weeks ago
2 weeks ago
This behaviour is why we initially built the interface in ESM that allows admins to delegate the OS-level session management to power users, while auditing any OS-level intervention. It can often unfreeze the UI while keeping the appserver clean of zombies and old sasworks, especially with less recent versions of EG.
Another feature we put in on a whim, but our EG-heavy clients love is the ability to terminate a disconnected session while keeping its SASWork intact, so that they can copy their old SASWork over to the SASWork for their new session and resume their EG projects from an intermediate task rather than starting again.
08-02-2016 07:38 PM
Thanks for the replies so far. To wrap up this thread...
Sorry, I think mentioning an external database was a red herring and opened a can of worms. I only mentioned it since it was the cause of the long running job.
Here is my code, edited slightly for privacy:
* fact table ; data workspde.main_fact_table; set odbclib.vw_record; where hospital_type eq 2 and record_status ne 4 ; run; * lookup tables - this took about 2 hours ;
* there is a reason I wasn't using pass through ; proc copy in=odbclib out=workspde; select lookup1 lookup2; run; * join ; proc sql _method; create table workspde.final as select * from workspde.main_fact_table a left join workspde.lookup1 b on a.key1=b.key1 left join workspde.lookup2 c on a.key2=c.key2 ; quit;
About 2 mins into the join, I clicked stop. I think EG stopped about 10 mins later; I don't know for sure as I was in other windows at the time.
So at the time I clicked stop I wasn't running a query against an external database. I was using a SPDE library if that matters?
Like I said, I think EG can only work with what SAS Foundation provides. I don't have DMS available to test whether interrupting a similar join would immediately stop or not.
What I can say is that, speaking objectively and without bias (I love SAS software!) is that, for this situation, my user experience is better when I undertake a similar action in SSMS or Oracle SQL Developer. Clicking stop immediately stops the query.
I'll see if Chris' suggestions help.
08-02-2016 08:12 PM
I've always cut SAS a pass on the "keeps on running" behaviour with external databases, because to be realistic how COULD SAS stop a query to a database over which it realistically has no control? Whereas when you're running in SSMS or Oracle SQL Developer, the total stack is under the control of Microsoft or Oracle, therefore there's a lot more flexibility.
However, the fact that you are running against SPDE changes everything, in my opinion. I have no experience with SPDE, but SAS-developed software should absolutely have the necessary hooks into SPDE to stop a query immediately.
If I were in this situation, and nothing develops from discussing it on the communities, I would certainly raise it with SAS Tech Support. Not necessarily a bug, but definitely room for improvement.