topic Re: BATCH Job Gives a Lock ERROR While Enterprise Guide Gives No Error for Same Code in SAS Programming

BATCH Job Gives a Lock ERROR While Enterprise Guide Gives No Error for Same Code

turcay — Thu, 01 Mar 2018 09:28:34 GMT

Hello everyone,

I try to automatize the whole process in customer environment so the code should be scheduled and works a batch job. The following BATCH data set is updated everyday, but it is unclear when the data set will be updated so we need to check every half an hour. For this reason, I prepared the following code and scheduled by the help of WindowsTask scheduler for every half an hour.

Data BATCH;
Length FINAL_DATE 8 ERROR 8;
Infile Datalines Missover;
Input FINAL_DATE ERROR ;
FORMAT FINAL_DATE  DATE9.;
Datalines;
21244 0
21244 0
21244 0
21244 0
21244 0
21244 0
21244 0
21244 0
21244 0
21244 0
21244 0
;
LIBNAME PORT "C:\Users\...\Desktop";
Data PORT.STATUS;
Start_Date="Y";
Complete_Date="01MAR2018"D;
Format Complete_Date DATE9.;
Run;
Options dbidirectexec bufno=100 bufsize=128k;
LIBNAME SQL ODBC DATAsrc=proddb SCHEMA=sas USER=user PASSWORD="*******" INSERT_SQL=YES INSERTBUFF=32767 readbuff=10000 bulkload=yes dbcommit=10000;
%MACRO main_loop;
PROC SQL;
SELECT MAX(RESULT) INTO:errcheck FROM(SELECT *,
CASE WHEN FINAL_DATE="&SYSDATE9."D AND ERROR=0 THEN 0 ELSE 1 END AS RESULT
FROM BATCH);
QUIT;
%IF &errcheck %THEN %DO;      
DATA _NULL_;
ABORT CANCEL;
RUN;
%END;
%ELSE %DO;
DATA _NULL_;
SET PORT.STATUS;
IF Start_Date="Y" OR Complete_Date="&sysdate9."d THEN ABORT CANCEL;
ELSE Call system("...Batch.bat");
RUN;
%END;
%MEND;

%main_loop

When the above code executes the bat file the following code is triggered but I need to update PORT.STATUS data set because the code run time can take an hour so when the Windows Task Scheduler trigger the code it shouldn't re-execute the following code if it is already running.

The problem is if I execute the following code on Enterprise Guide, it works without an error but when it is triggered by Batch job it gives an LOCK error for PORT.STATUS. Actually, both Batch and EG session running on the same exact environment and I am trying to access the exact same data set and I am also pretty sure the PORT.STATUS data set does not open anywhere beacuse I'm the only who knows the PORT.STATUS data set.

LIBNAME PORT "C:\Users\...\Desktop";/**/
DATA PORT.STATUS;/*It gives the LOCK error here*/
SET PORT.STATUS;
Start_Date="Y";
Run;
/*MAIN CODE IS HERE BUT I DO NOT TYPE IT BECAUSE IT CAN TAKE UP A LOT OF SPACE*/
DATA PORT.STATUS;
SET PORT.STATUS;
Complete_Date="&sysdate9."d;
format Complete_Date date9.;
Start_Date="N";
Run;

Firstly, do you have an idea what can be the reason for?

Secondly, do you have an alternative solution to prevent the lock error?

Thank you,

Re: BATCH Job Gives a Lock ERROR While Enterprise Guide Gives No Error for Same Code

Tom — Thu, 01 Mar 2018 13:51:25 GMT

Is it a timing issue?

Looks like you have a data _NULL_ step that is reading the data set (so has a lock) and it is generating batch jobs (other SAS programs?). If those programs start and try to write to that data set and the current data step has not finished closing it will be locked.

Why are you read/writing to the same dataset in multiple jobs?

Re: BATCH Job Gives a Lock ERROR While Enterprise Guide Gives No Error for Same Code

SuryaKiran — Thu, 01 Mar 2018 20:01:11 GMT

Before running the code check if the Dataset is locked or not. If it is locked then sleep for some time and again check the Lock on it until a specific timeout period.

%macro trylock(member=, timeout=);
%local starttime;
%let starttime = %sysfunc(datetime());
%DO %UNTIL (&syslckrc = 0 or %sysevalf(%sysfunc(datetime()) > (&starttime + &timeout)));
 lock &member NOMSG;
%if &syslckrc ne 0 %then %do;
			filename outbox email;
			DATA _NULL_;
			file outbox to=("suryakiran@abc.com") 
						cc=("suryakiran@abc.com")    
                        subject="Dataset Locked" ;
  
			PUT "Thanks";
			RUN;
			%end;
%if &syslckrc ne 0 %then %let rc=%sysfunc(sleep(1000*60*1));/* One Minute */
%END;
%mend trylock;
%trylock(member=MYWORK.TEST, timeout=3550);

<My code>
LOCK MYWORK.TEST CLEAR;

This will check if there is a lock on the dataset. If there is a look then it will sleep for some time (1 min in above code) and send you a mail and loop until lock is released or timeout (1hr in above code 3550). If there is no lock then the dataset will be locked.

Don't forget to clear the lock after your code is done.

LOCK MYWORK.TEST CLEAR;

Re: BATCH Job Gives a Lock ERROR While Enterprise Guide Gives No Error for Same Code

turcay — Thu, 01 Mar 2018 22:08:33 GMT

I also try it by not using Data _NULL_ step but it still gives same error. Also I change the some part of trigger code as below;

Instead of calling another bat file, I prefer to call SAS code but it still gives same error.

DATA _NULL_;
SET PORT.STATUS;
IF Start_Date="Y" OR Complete_Date="&sysdate9."d THEN ABORT CANCEL;
ELSE %Include "...Main_Code.sas";
RUN;

You told me this -> Why are you read/writing to the same dataset in multiple jobs?

As I told, Tthe BATCH data set is updated everyday, but it is unclear when the data set will be updated so we need to check every half an hour and the process should work just one time in a day.

Actually, I am open to listen your suggestions. Can you share me another possible structure for my process?

Thank you

Re: BATCH Job Gives a Lock ERROR While Enterprise Guide Gives No Error for Same Code

turcay — Thu, 01 Mar 2018 22:11:01 GMT

Hello SuryaKiran,

Thank you for your code, actually, it is little bit complicated is it possible for you to show me in a sample data set?

On the other hand, is it possible to kill the data set which is already open to prevent to Lock error? Any code for this?

Thank you

Re: BATCH Job Gives a Lock ERROR While Enterprise Guide Gives No Error for Same Code

SuryaKiran — Thu, 01 Mar 2018 22:35:47 GMT

You can try LOCK <Dataset> CLEAR; before your update query to the table. This will release the lock only if it is locked by you.

Re: BATCH Job Gives a Lock ERROR While Enterprise Guide Gives No Error for Same Code

SuryaKiran — Thu, 01 Mar 2018 23:04:18 GMT

Adding comments for your understanding.

%PUT &SYSLCKRC.;
/* Unlock if the dataset need to be updated is locked by me previously */
LOCK MYWORK.TEST CLEAR;

%macro trylock(member=, timeout=);
%local starttime;
%let starttime = %sysfunc(datetime());/* Current time */
/* Loop until lock is released */
/* Initially &syslckrc value is 0 */
%DO %UNTIL (&syslckrc = 0 or %sysevalf(%sysfunc(datetime()) > (&starttime + &timeout)));
/* Try to lock the Dataset, If it is previously locked then &syslckrc value will be >0, Lets assume Its locked*/
lock &member NOMSG;
 /*Since &syslckrc >0 you will get email saying its locked. You can ignore this if you don't wish for email */
%if &syslckrc ne 0 %then %do;
			filename outbox email;
			DATA _NULL_;
			file outbox to=("suryakiran@abc.com") 
						cc=("suryakiran@abc.com")    
                        subject="Dataset Locked" ;
  
			PUT "Thanks";
			RUN;
			%end;
/* *Since &syslckrc >0 it will sleep for 1 min */
%if &syslckrc ne 0 %then %let rc=%sysfunc(sleep(1000*60*1));/* One Minute */ /* change 1 to 10 for 10 min sleep */
/* Since the &syslckrc value is >0 it will loop after sleep */
%END;
%mend trylock;

%trylock(member=MYWORK.TEST, timeout=3550);

Re: BATCH Job Gives a Lock ERROR While Enterprise Guide Gives No Error for Same Code

turcay — Fri, 02 Mar 2018 07:57:48 GMT

Hello,

Thank you for your help.

Why the following statement gave an error in log? -> ERROR: MYWORK.TEST.DATA is not locked by you. Shouldn't it better if it would give a warning?

LOCK MYWORK.TEST CLEAR;

And what exactly &SYSLCKRC. macro do?

And why timeout=3550?

This code will work for just one data set? -> member=MYWORK.TEST

I don't want to send an email to myself so I think I can ignore that part. If I put the following code at the begining of my code, it won't be locked, right?

%PUT &SYSLCKRC.;
/* Unlock if the dataset need to be updated is locked by me previously */
LOCK MYWORK.TEST CLEAR;
%macro trylock(member=, timeout=);
%local starttime;
%let starttime = %sysfunc(datetime());/* Current time */
/* Loop until lock is released */
/* Initially &syslckrc value is 0 */
%DO %UNTIL (&syslckrc = 0 or %sysevalf(%sysfunc(datetime()) > (&starttime + &timeout)));
/* Try to lock the Dataset, If it is previously locked then &syslckrc value will be >0, Lets assume Its locked*/
lock &member NOMSG;
/* *Since &syslckrc >0 it will sleep for 1 min */
%if &syslckrc ne 0 %then %let rc=%sysfunc(sleep(1000*60*1));/* One Minute */ /* change 1 to 10 for 10 min sleep */
/* Since the &syslckrc value is >0 it will loop after sleep */
%END;
%mend trylock;
%trylock(member=MYWORK.TEST, timeout=3550);

Thank you

Re: BATCH Job Gives a Lock ERROR While Enterprise Guide Gives No Error for Same Code

turcay — Fri, 02 Mar 2018 15:28:28 GMT

Does anyone have a different opinion?

Thanks

Re: BATCH Job Gives a Lock ERROR While Enterprise Guide Gives No Error for Same Code

SuryaKiran — Fri, 02 Mar 2018 15:55:49 GMT

Place the LOCK PORT.STATUS CLEAR; in the 1st code that you run.

Data BATCH;
Length FINAL_DATE 8 ERROR 8;
Infile Datalines Missover;
Input FINAL_DATE ERROR ;
FORMAT FINAL_DATE  DATE9.;
Datalines;
21244 0
21244 0
21244 0
21244 0
21244 0
21244 0
21244 0
21244 0
21244 0
21244 0
21244 0
;
LIBNAME PORT "C:\Users\...\Desktop";
Data PORT.STATUS;
Start_Date="Y";
Complete_Date="01MAR2018"D;
Format Complete_Date DATE9.;
Run;
Options dbidirectexec bufno=100 bufsize=128k;
LIBNAME SQL ODBC DATAsrc=proddb SCHEMA=sas USER=user PASSWORD="*******" INSERT_SQL=YES INSERTBUFF=32767 readbuff=10000 bulkload=yes dbcommit=10000;
%MACRO main_loop;
PROC SQL;
SELECT MAX(RESULT) INTO:errcheck FROM(SELECT *,
CASE WHEN FINAL_DATE="&SYSDATE9."D AND ERROR=0 THEN 0 ELSE 1 END AS RESULT
FROM BATCH);
QUIT;
%IF &errcheck %THEN %DO;      
DATA _NULL_;
ABORT CANCEL;
RUN;
%END;
%ELSE %DO;
DATA _NULL_;
SET PORT.STATUS;
IF Start_Date="Y" OR Complete_Date="&sysdate9."d THEN ABORT CANCEL;
ELSE Call system("...Batch.bat");
RUN;
%END;
%MEND;

%main_loop;
LOCK PORT.STATUS CLEAR;

Now when the above program running your .bat file your triggering the next program (Try triggering after the .bat run completed).

So before the next program runs you need to check whether the lock is released or not. If the above program still running then the dataset is locked.

%PUT &SYSLCKRC.;
/* Unlock if the dataset need to be updated is locked by me previously */

%macro trylock(member=, timeout=);
%local starttime;
%let starttime = %sysfunc(datetime());/* Current time */
/* Loop until lock is released */
/* Initially &syslckrc value is 0 */
%DO %UNTIL (&syslckrc = 0 or %sysevalf(%sysfunc(datetime()) > (&starttime + &timeout)));
/* Try to lock the Dataset, If it is previously locked then &syslckrc value will be >0, Lets assume Its locked*/
lock &member NOMSG;
/* *Since &syslckrc >0 it will sleep for 10 min */
%if &syslckrc ne 0 %then %let rc=%sysfunc(sleep(1000*60*10/*Minutes*/));
/* Since the &syslckrc value is >0 it will loop after sleep */
%END;
%mend trylock;

%trylock(member=PORT.STATUS, timeout=3600);
/* If you have another dataset and need to run the rest of the query only if the lock is released. */
%trylock(member=PORT.STATUS2, timeout=3600);

LIBNAME PORT "C:\Users\...\Desktop";/**/
DATA PORT.STATUS;/*It gives the LOCK error here*/
SET PORT.STATUS;
Start_Date="Y";
Run;
/*MAIN CODE IS HERE BUT I DO NOT TYPE IT BECAUSE IT CAN TAKE UP A LOT OF SPACE*/
DATA PORT.STATUS;
SET PORT.STATUS;
Complete_Date="&sysdate9."d;
format Complete_Date date9.;
Start_Date="N";
Run;
/* Release lock on all datasets */
LOCK PORT.STATUS CLEAR;
LOCK PORT.STATUS2 CLEAR;

SYSLCKRC Automatic macro variable value will be 0 if the last lock statement is successful. >0 if the lock statement is not successful and <0 if warnings or note was written to log.

3550 Sec~1hr because I want to try for checking the lock for 1 hr ie. every 10 min until 1 hr. This is not a fixed number it's up to you how long you want to check for lock on the dataset depending on the previous job run time.

Note: If running in windows then remove 1000 in sleep sunction

i.e %if &syslckrc ne 0 %then %let rc=%sysfunc(sleep(60*10/*Minutes*/));

You can run the %trylock on any number of datasets you wish. It's a macro.

%trylock(member=PORT.STATUS, timeout=3600);

%trylock(member=PORT.STATUS2, timeout=3600);

%trylock(member=PORT.STATUS3, timeout=3600); ........................so on