Accepted Solutions
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:
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.ht...
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.
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:
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
-
1941 views
-
0 likes
-
-
