topic Re: SAS DI jobs trying to update the same dataset by multiple SAS jobs in SAS Programming

SAS DI jobs trying to update the same dataset by multiple SAS jobs

JJP1 — Tue, 28 May 2019 11:39:46 GMT

Hi,

We running libname statement which will validate oracle database source connection step in SAS monthly and weekly jobs(parallely job s runs) and updating the job control dataset
but what is happening is there is some contention issue when parallelely running SAS weekly and monthly jobs and validating oracle database source connection step fails due to contention issues and update status fails for job control status dataset.

please suggest to avoid this issue please

Re: SAS DI jobs trying to update the same dataset by multiple SAS jobs

Kurt_Bremser — Tue, 28 May 2019 11:46:08 GMT

Make one job dependent on the other in the scheduler, so they run in succession.

Re: SAS DI jobs trying to update the same dataset by multiple SAS jobs

JJP1 — Tue, 28 May 2019 11:51:56 GMT

Would you please explain more on this as all existing monthly and weekly jobs are running parallely

Re: SAS DI jobs trying to update the same dataset by multiple SAS jobs

Kurt_Bremser — Tue, 28 May 2019 12:05:22 GMT

@JJP1 wrote:

Would you please explain more on this as all existing monthly and weekly jobs are running parallely

Which they obviously should not. So you need to force them to run in succession, and you best achieve that by making one a logical prerequisite for the other.

Re: SAS DI jobs trying to update the same dataset by multiple SAS jobs

JJP1 — Wed, 29 May 2019 08:00:38 GMT

Hi,
we have below jobs running under flow manager.

Data_Weekly_3 - Mon May 27 03:30:25
Data_Weekly_2 - Mon May 27 03:30:25
Data_Weekly_1 - Mon May 27 03:30:25

Data_Monthly_3 - Wed May 01 05:42:31
Data_Monthly_1 - Wed May 01 05:42:02

Data_Monthly_3- ValidateLibname.sas(one of the node in job flow)

Data_Monthly_1- ValidateLibname.sas

Data_Weekly_3 : ValidateLibname.sas
Data_Weekly_2 : ValidateLibname.sas
Data_Weekly_1 : ValidateLibname.sas

So,here the Data_Weekly_3,Data_Weekly_2 and Data_Weekly_1 are running at same time and excuting the first node 'ValidateLibname'
and causing contention issue when it is updating the job control dataset(which is same for) and finally this fails.

same with Data_Monthly_1 and Data_Monthly_1 jobs also.would you please help on practically whther i can mention sas options/sascode
to resolve this contention issue please.

Re: SAS DI jobs trying to update the same dataset by multiple SAS jobs

ScottBass — Wed, 29 May 2019 08:17:13 GMT

I don't even use the SAS Flow Manager...

But I typed "SAS flow manager dependencies" into Google, and got "About 626,000 results".

Perhaps you should read the first few hits?

Re: SAS DI jobs trying to update the same dataset by multiple SAS jobs

Patrick — Wed, 29 May 2019 09:57:53 GMT

@JJP1

What I understand you've got:

- some SAS programs executed in parallel (using LSF)

- a single job control table (a SAS table)

- each job inserts and/or updates rows in this common job control table

- a table locking issue due to multiple jobs trying to write access the control table at the same time

If above is correct then what you need/can do:

1. Ensure that the code inserting/updating rows in the job control table performs really well (=within a second or less)

- Use SQL INSERT/UPDATE or data step MODIFY/UPDATE

- IF also UPDATE used then have an INDEX on the column used for row selection

2. Use libname option FILELOCKWAIT with a value like 30 (for 30 seconds). This will make a job wait up to 30 seconds in case of the control table being locked by another job (...which should only lock the table for max a second).

Alternative to 2.

Migrate the common control table to Oracle and maintain it there. Oracle supports concurrent read/write access and though there won't be any table locking issues (you still should write well performing code though).

Re: SAS DI jobs trying to update the same dataset by multiple SAS jobs

JJP1 — Wed, 29 May 2019 09:07:38 GMT

Thanks @Kurt_Bremser .

Actually i have below jobs running under flow manager.so how can i create logical prerequisite :
would you please help practically (as i am new to this please

)to resolve the contention issue

Data_Weekly_5
Data_Weekly_4
Data_Weekly_3
Data_Weekly_2
Data_Weekly_1

Data_Monthly_8
Data_Monthly_3
Data_Monthly_2
Data_Monthly_1

Re: SAS DI jobs trying to update the same dataset by multiple SAS jobs

Patrick — Wed, 29 May 2019 10:03:46 GMT

@JJP1

"logical prerequisites" = job dependencies

It's all fully documented here:

https://go.documentation.sas.com/api/docsets/scheduleug/9.4/content/scheduleug.pdf (page 55 and later)

But: If it's only a problem of control table locking and not about real dependencies like Job A creates a table which is required as input for Job B then don't define dependencies but solve the issue of table locking and write access concurrency.

Re: SAS DI jobs trying to update the same dataset by multiple SAS jobs

JJP1 — Wed, 29 May 2019 12:19:22 GMT

Thanks @Patrick .

Yes there is no job dependencies. but multiple jobs trying to run the program "validate libname connection" and job_status_table (not control table sorry please )and it is getting failed as there are multiple jobs tried to update the same dataset simultaneously.

Actually job_status_table we are creating under status handling tab (job status where the dataset is creted) in job properties.so this table is getting failed due to contention issue.

because "validate libname connection" job is being used in mentioned weekly and monthly jobs.and the job_status_table is getting failed please.

please help how can we resolve this issue please

Re: SAS DI jobs trying to update the same dataset by multiple SAS jobs

JJP1 — Wed, 29 May 2019 12:20:59 GMT

Thanks @Patrick

Actually job_status_table we are creating under status handling tab (job status where the dataset is creted) in job properties.so this table is getting failed due to contention issue.

because "validate libname connection" job is being used in mentioned weekly and monthly jobs.and the job_status_table is getting failed please.

please help how can we resolve this issue please.i am extremely sorry for wrongly conveying the issue please.

Re: SAS DI jobs trying to update the same dataset by multiple SAS jobs

Patrick — Wed, 29 May 2019 23:38:53 GMT

@JJP1

I assume you've got somewhere a metadata library definition for the permanent status table. Let's assume this library has a libref called sref and the status table under this library is called status

You then have in your DIS job properties under status handling something as below:

If you look into the generated code of your DIS job you'll see that there is now a SAS macro called %etls_sendJobStatus

In this macro the code first creates a SAS WORK table with the status and then inserts the new row to the permanent table using a Proc Append. That's a very fast process and how it needs to be done.

When running jobs in parallel then it's of course possible that more than one job executes a Proc Append to the status table at exactly the same time. SAS tables don't allow concurrent write access and though the 2nd job will fail.

There are two ways to resolve this:

A) Add Libname option FILELOCKWAIT to the SAS metadata library definition (sref). This option makes the 2nd job wait until the status table becomes accessible for write access. https://go.documentation.sas.com/?docsetId=lesysoptsref&docsetTarget=n07w6un7zadsb4n1mw59odismosv.htm&docsetVersion=3.2&locale=en

B) Use a libref which points to Oracle (or change the library metadata definition for sref so that it points to Oracle). Oracle allows for concurrent read/write access and though the problem will just go away.

I don't know what job validate libname connection does. This must be something site specific. For your problem at hand: A SAS table gets also locked for write access if another job reads the data (write needs exclusive access to the table). So, if this job also accesses the job status table then:

A) If the status table is a SAS table implement code that minimizes the time the status table gets accessed (FILELOCKWAIT still allows for some overlaps).

B) If the status table is in Oracle then nothing further needs to be done as Oracle allows for concurrency.

Re: SAS DI jobs trying to update the same dataset by multiple SAS jobs

JJP1 — Thu, 30 May 2019 09:33:26 GMT

Thanks @Patrick for understanding my issue.even though i was not clear.

please find below :

There are two ways to resolve this:

A) Add Libname option FILELOCKWAIT to the SAS metadata library definition (sref). This option makes the 2nd job wait until the status table becomes accessible for write access.

Actually if i use FILELOCKWAIT option it will affect all the datasets present in library at library level and i guess team manager would not prefer.
any option only for that particular dataset itself please

B) Use a libref which points to Oracle (or change the library metadata definition for sref so that it points to Oracle). Oracle allows for concurrent read/write access and though the problem will just go away.

Actually we are following all SAS DI Jobs like this process only.for single job changing pointing to oracle and storing under oracle
datasource for single job i guess is not prefered by our team .

I don't know what job validate libname connection does. This must be something site specific.

As part of validate libname connection job we will ensure that external database is ok it is in correct state or not before running jobs that are using this library

Thanks for your time.Would you please suggest any way please

Re: SAS DI jobs trying to update the same dataset by multiple SAS jobs

Patrick — Thu, 30 May 2019 22:59:30 GMT

"Actually if i use FILELOCKWAIT option it will affect all the datasets present in library at library level and i guess team manager would not prefer.any option only for that particular dataset itself please"

Yes, you've got it. ...BUT: I've used FILELOCKWAIT already many times. It doesn't have any negative impact whatsoever except that a locked table doesn't immediately lead to job failure. For that reason I wouldn't set the value for waiting too high so that if locking is not caused by expected behavior a job still fails within reasonable time if there is a permanent lock and doesn't just "hang around waiting".

I'd make my case and propose FILELOCKWAIT to your manager as it's really a very simple and robust solution to your problem.

It is something I've used for exactly your use case already many times for major implementations. I almost dare to call this "best practice".

As a personal opinion: Status and control tables should anyway have their own library and with these tables defining FILELOCKWAIT is the right thing to do.

"Actually we are following all SAS DI Jobs like this process only.for single job changing pointing to oracle and storing under oracle
datasource for single job i guess is not prefered by our team ."

I've just given you the options. You don't need to go down this road. This was my preferred option before FILOCKWAIT had been implemented in SAS. I would eventually still consider it for Oracle heavy implementations where also the DIS processes happen mostly in-database.

"As part of validate libname connection job we will ensure that external database is ok it is in correct state or not before running jobs that are using this library"

Ah, o.k., so that means that this job is highly likely not involved in the issue we're talking about here.

"Would you please suggest any way please"

Can't think of any other option that would be as simple and robust as the two I've already proposed.

Re: SAS DI jobs trying to update the same dataset by multiple SAS jobs

JJP1 — Thu, 30 May 2019 12:46:30 GMT

Thanks @Patrick for guidance.

Would you please suggest based on experience . how much time we can give on FILELOCKWAIT option for job status datasets.

i guess if we give more time the it will wait for more time .Also the job status libarary is pre-assigned.so do we need to mention this option in SAS SMC or SAS DI please

Re: SAS DI jobs trying to update the same dataset by multiple SAS jobs

Patrick — Thu, 30 May 2019 22:53:24 GMT

@JJP1

I would assume the actual Append operation locks the table significantly less than a second. So even in a worst case scenario 5 seconds should be more than good enough. But then to be on the overly safe side: Does it hurt if a job could potentially wait for 30 seconds for a scenario I can't even think about? If not then that's the value I'd choose: 30 seconds.

You can use SMC or DIS to change a SAS Metadata Library definition. Using DIS adding FILELOCKWAIT would be done here:

Re: SAS DI jobs trying to update the same dataset by multiple SAS jobs

JJP1 — Thu, 30 May 2019 13:43:27 GMT

Thanks @Patrick .