Date: Fri, 13 Jan 2006 08:47:14 -0700
Reply-To: Alan Churchill <SASL001@SAVIAN.NET>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Alan Churchill <SASL001@SAVIAN.NET>
Subject: Re: SAS table locked - concurrent access by ETL Informatica and
In-Reply-To: <1137149302.358804.267300@f14g2000cwb.googlegroups.com>
Content-Type: text/plain; charset="us-ascii"
Cyril,
Have you considered looking at the file and then searching the process
threads to see if it is owned by anyone before launching the AF code? I
don't know how this is coded in SCL but it is certainly doable in C# (if you
are on Windows). If it is Unix, David can probably tell you how to do it
using perl.
Alan
Alan Churchill
Savian "Bridging SAS and Microsoft Technologies"
www.savian.net
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
cyril.faulconnier@capgemini.fr
Sent: Friday, January 13, 2006 3:48 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: SAS table locked - concurrent access by ETL Informatica and
Thank you all for your answers,
Actually, the client is relunctant to buy an another SAS module
(SAS/SHARE, SAS/SPDS, ...).
To answer to Clint, the AF program performs these tasks :
1/ Try to open the SAS table with this statement open('mylib.toto','u')
2/ If this operation is successful, then I read the data set for new
records and update a status field to tell to other processes that I'm
proceeding news records, and of course, I don't forget to close the
data set with a close statement when the task is done.
3/ If this operation is unsuccessful, then I increment a counter
3a/ if this counter is greater than a constant (max attempts to open
the dataset), then I notify it and stop the process.
3b/ if this counter is lower than a constant (max attempts to open the
dataset), then I wait 15 seconds and try again in the next loop
This works perfectly right in most of cases.
Unfortunately, when the SAS program launched by ETL Informatica try to
execute a proc append, and, in the meantime, the AF program try to open
the data set that is used by ETL Informatica (this a short window, less
than a second), then the SAS program launched by ETL Informatica
craches and leaves a lock file, which prevent the AF program to open
the data set.
My problem is that the operations performed by the ETL are not atomic.
I saw in the SAS documentation online a statement that might help, the
lock statement. I never worked with this statement and I don't know if
it works correctly. Have anybody worked with it?
In the previous mails, I saw that there a lock file created by the ETL
program, I tried to locate it but unsuccessfully so far. Do you have
any idea where this file is?
I really appreciate your help on this topic.
Cyril
