Stop user access while batch is running

[NikosStratis]

Hello team!

I run into a difficult situation that I describe below:

We have a SAS 9.4 installation on a Windows Server 2019. There are a few folders on the server containing SAS tables and a few end users who use these SAS tables through SAS Enterprise Guide (that has been installed on their desktops).

Also, there are 4 SAS batch daily runs, that update all SAS tables. We would like to make sure that no users have any SAS tables opened through E.G. while the batch runs are executing, to avoid table is locks. Since there many dependencies on other systems, the batch runs do not always complete before users start working in the morning.

Is there a way to cut all E.G. users' SAS connections before the batch run starts, keep them out during the run, and then allow them to reconnect when the batch run has completed?

Thank you in advance.

[jimbarbour]

Well, if you shut down the Workspace server, I would think that would shut out all EG access.  I'm not sure how the SAS admin team at your shop would feel about that, and I'm not sure if there might not be other impacts.  Worth investigating at least I would think.

Alternatively, there is the LOCK command in SAS.  At the start of a run the LOCK command may be run against a dataset and the &SYSLCKRC macro variable queried thereafter to see if in fact a lock has been obtained.  A zero value in &SYSLCKRC indicates that a lock has been successfully obtained.  Non-zero indicates failure.

At the end of the run, the LOCK CLEAR command should be run to release the lock.  Again, the &SYSLCKRC macro variable should be queried thereafter to see that the lock has in fact been released.  Zero indicates success.  Non-zero indicates failure.

You could set up a SAS program that runs first before your daily batch jobs that locks all necessary datasets.

You could then set up a second SAS job that clears all locks after a successful run.

Jim

[NikosStratis]

Hello Jim,

Thank you very much for your reply, I will follow your suggestion on setting up a SAS program that runs before the daily batch and locks all necessary datasets and release the lock at the end of the batch run. The word ‘lock’ worries me though, isn’t going to affect the jobs that create or update these datasets?

Also, since I have never done something like that, would it be easy to send me a quick example?

Thanks again!

Nick.

[jimbarbour · Accepted solution]

@NikosStratis, 

There are definitely some tricks here.  For one, the lock has to be applied in the same session that any updates occur in.  If you have four batch jobs, each batch job would need to have a little section added to the front end of the job that locks the datasets and also a little section added to the back of the job that clears the locks.  It can't be a completely separate program in other words (but it can be brought in by %INCLUDE or a macro).

Troy Martin Hughes has written a paper that explains how a lot of this works:  https://www.lexjansen.com/wuss/2014/69_Final_Paper_PDF.pdf

I would definitely test this in whatever staging environment you have before implementing it in production.  I should be relatively straightforward, but better safe than sorry.

One potential problem would be parallel processing.  If one of your batch jobs launches concurrent sub-processes, those sub-processes would not be able to use any of the locked data sets.  In such a case, this technique may not be workable.

A second potential problem would be if any two or more of your batch jobs a) run at the same time and b) use the same datasets.  Using a lock would not work in this circumstance because one or the other of the the jobs would not be able to get a lock on the datasets.  However, since locking is also done by SAS automatically when certain types of data access occur, this may not be a problem.  Obviously, the four batch jobs that you have must have some means of avoiding dataset update conflicts now or they'd be having problems.

Jim

[NikosStratis]

The batch jobs that run simultaneously do not use the same datasets, therefore, it’s worth a try on a few datasets to start and see the behavior. The document you recommended is very helpful!

Thank you very much!