Desktop productivity for business analysts and programmers

How do I get Enterprise Guide to stop when I click...stop?

Reply
Super Contributor
Posts: 399

How do I get Enterprise Guide to stop when I click...stop?

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?

 

Moderator
Posts: 329

Re: How do I get Enterprise Guide to stop when I click...stop?

Posted in reply to ScottBass

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?

Super Contributor
Posts: 399

Re: How do I get Enterprise Guide to stop when I click...stop?

Posted in reply to AndrewHowell

Hi Andrew, the SAS Workspace Session is on a server somewhere

Super User
Posts: 10,850

Re: How do I get Enterprise Guide to stop when I click...stop?

Posted in reply to ScottBass
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.

Super Contributor
Posts: 399

Re: How do I get Enterprise Guide to stop when I click...stop?

[ Edited ]

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)

Super User
Posts: 4,028

Re: How do I get Enterprise Guide to stop when I click...stop?

Posted in reply to ScottBass

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. 

Super Contributor
Posts: 399

Re: How do I get Enterprise Guide to stop when I click...stop?

The 2 hours work was:

 

proc copy in=odbc_lib out=work;

   select my_two_tables;

run;

 

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.

PROC Star
Posts: 1,334

Re: How do I get Enterprise Guide to stop when I click...stop?

Hi, @SASKiwi

 

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?

 

Tom

PROC Star
Posts: 1,472

Re: How do I get Enterprise Guide to stop when I click...stop?

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. 

Community Manager
Posts: 3,463

Re: How do I get Enterprise Guide to stop when I click...stop?

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.

 

Chris

Super User
Posts: 4,028

Re: How do I get Enterprise Guide to stop when I click...stop?

Hi @TomKari - unfortunately we don't use DM any more so I can't confirm that behaviour. My guess is it would behave similarly to EG. 

 

@ChrisHemedinger - My experience with SAS 9.3 or later is consistent with your comments. The STOP button works pretty well except when querying remote databases.

Occasional Learner
Posts: 1

Re: How do I get Enterprise Guide to stop when I click...stop?

we have been experiencing the same issues. When we hit stop, it doesnt actually stop executing and creates what we are calling a "ghost query" on our hadoop cluster. However, if we close EG, it also terminates the query on the cluster (removing the "ghost query").
Frequent Contributor
Posts: 142

Re: How do I get Enterprise Guide to stop when I click...stop?

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. 

Super Contributor
Posts: 399

Re: How do I get Enterprise Guide to stop when I click...stop?

Posted in reply to ScottBass

Hi All,

 

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.

PROC Star
Posts: 1,334

Re: How do I get Enterprise Guide to stop when I click...stop?

Posted in reply to ScottBass

Very interesting!

 

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.

 

Tom

Ask a Question
Discussion stats
  • 17 replies
  • 4425 views
  • 12 likes
  • 10 in conversation