Date:         Thu, 12 Jan 2006 16:57:16 -0800
Reply-To:     David L Cassell <davidlcassell@MSN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         David L Cassell <davidlcassell@MSN.COM>
Subject:      Re: SAS table locked - concurrent access by ETL Informatica and
              SAS
In-Reply-To:  <200601121448.k0CESlZO031956@mailgw.cc.uga.edu>
Content-Type: text/plain; format=flowed

clinton.rickards@GE.COM replied:
>At one time or another either side could have a failure. The frequency of
>update and reading essentially boils down to simultaneous access and hence
>you need a product that supports multiple concurrent read/write access.
>That means something like SAS/Share, SPDS Server, or a RDBMS (e.g. Oracle,
>DB2, Sybase, SQL Server, even Access).
>
>If you don't want to go in that direction, then the only other solution I
>can think of is to put the AF application into a loop, which you already
>have. An David make a very good point. At the top of the loop, try to open
>the dataset. If you can't then 1) increment a counter; 2) if the counter
>exceeds a comfort threshold then perform a notification routine; 3) iterate
>the loop in other 10 seconds. If you can open the dataset then 1) set the
>counter to zero; 2) do the normal process; 3) close the data set.
>
>You may want to modify the open routine by looping it within the 10 second
>loop. that is, try opening the data set at the top of the loop up to 5
>times - if you can't open it then you increment the counter.

I agree.  SAS/Share may be the easiest to get the ETL Informatica program
cooperating.  But it's pretty hard to tell from here.

It seems to me that the AF program needs to do two sysadmin tasks which I
normally do outside of SAS:

[1]  Check system to see if the ETL program is running.
[2]  Check for lockfile and delete if needed, even if this requires
additional privileges.

And note that neither of these is 'atomic' and the whole process certainly
is not,
so the ETL program could start up in the middle of the checking for #2 and
maybe
create a race condition.

If the system can be adjusted so that the ETL program has write privileges
on the
data set but doesn't lock the file for readers, and the AF program has
read-only
privileges, then there could be no need for any of this file-wrestling.  But
that may
require being able to alter the ETL program.. which may not be allowed.

HTH,
David
--
David L. Cassell
mathematical statistician
Design Pathways
3115 NW Norwood Pl.
Corvallis OR 97330

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today - it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/