BookmarkSubscribeRSS Feed
ScottBass
Rhodochrosite | Level 12

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?

 


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
17 REPLIES 17
AndrewHowell
Moderator

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?

ScottBass
Rhodochrosite | Level 12

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


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
Ksharp
Super User
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.

ScottBass
Rhodochrosite | Level 12

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)


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
SASKiwi
PROC Star

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. 

ScottBass
Rhodochrosite | Level 12

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.


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
TomKari
Onyx | Level 15

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

Quentin
Super User

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. 

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
SASKiwi
PROC Star

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.

dettmerc
Calcite | Level 5
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").
boemskats
Lapis Lazuli | Level 10

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. 

ScottBass
Rhodochrosite | Level 12

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.


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
TomKari
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 17 replies
  • 15920 views
  • 12 likes
  • 10 in conversation