I have read that you can determine who has a lock on metadata using the Open Metadata Interface and then use the OMI_UNLOCK_FORCE flag to unlock it. I am firstly trying to see who has the lock but cannot work out how to get this information.
I have tried using proc metadata as below
<PhysicalTable Id="A57OBXEE.BE000MIP" Name="" Desc="" LockedBy="">
which then returns:
<GetMetadata><Metadata><PhysicalTable Id="A57OBXEE.BE000MIP" Name="EDWPORT.PORTFOLIO" Desc=""
I have the table open in e-guide using the sasdemo user so the table should definitely be showing as locked.
I have also tried using the metadata_getnatr function which also returns blank for LockedBy.
length attr $256 value $256;
if (rc>0) then put n= attr= value=;
n=1 attr=UsageVersion value=1000000
n=2 attr=TableName value=PORTFOLIO
n=3 attr=SASTableName value=PORTFOLIO
n=4 attr=PublicType value=Table
n=5 attr=NumRows value=-1
n=6 attr=Name value=EDWPORT.PORTFOLIO
n=7 attr=MetadataUpdated value=15Aug2016:23:20:31
n=8 attr=MetadataCreated value=27Jan2016:05:05:46
n=9 attr=MemberType value=VIEW
n=10 attr=LockedBy value=
n=11 attr=IsHidden value=0
n=12 attr=IsEncrypted value=0
n=13 attr=IsDBMSView value=1
n=14 attr=IsCompressed value=0
n=15 attr=Desc value=
n=16 attr=DBMSType value=
n=17 attr=ChangeState value=
n=18 attr=Id value=A57OBXEE.BE000MIP
What am I doing wrong?
I have then tried using the OMI_UNLOCK_FORCE flag to unlock the table
<PhysicalTable Id="A57OBXEE.BE000MIP" Name="" Desc="">
but get ERROR: The user does not have permission to perform this action.
I have administrator privileges.
Any help would be greatly appreciated.
In that situation I might keep asking IT to tell me who has a file locked in the hope that they might eventually decide to delegate access so I can find out for myself 😉
Something else that comes to mind is to use SAS library access logging to give you an idea of who has been accessing the table most recently. Gerry Nelson wrote a blog post about it: Auditing data access: who did what and when? There's some more info in the Audit Messages for SAS Library Access section of the SAS 9.4 Logging: Configuration and Programming Reference.
Once you have the logging enabled you would write a stored process to report on recent table access.
This is an interesting question. Let me try if I am able to put my point
Through metadata function you can only see whatever gets updated in metadata. When a table is locked by SAS EG, there is no change in metadata as such. The lock is put on the physical table not the metadata. Thats why you can't view using Open metadata Interface or SAS metadata_getattr function.
Now question would arise what about the "LockedBy" attribute in metadata. When we check out any table using SAS DI then this attribute is updated. Value of Lockedby would be the URI of the user who has checked out the object. You can try this by making a check out of any table then find the Locked by value.
Using metadata function it is not possible to find locked table and to unlock it.
Ah, thank you Rahul. I have misunderstood the type of lock those methods and functions are for.
We have production jobs trying to update tables but they are falling over as there is a lock. It turns out that a user has the table in their e-guide project and even though they don't have it open it is still causing issues as the metadata is in use in the active e-guide session.
I think the lock is actually on the metadata as when we had the job running with the library as pre-assigned we didn't have this problem but now every second day or more we have several users having this core table in their e-guide project and it's causing a lock even though they aren't actually using the table at the time. (they leave jobs running on their pc's overnight)
A lock on the table does not happen in metadata, but on the OS level.
So if a table is locked and can't be overwritten, you can only try to find the offending processes and terminate them.
If your SAS system runs on UNIX, I have a (IMO nice) workaround where I simply delete the dataset first with the operating system's rm command, and then create it. On UNIX, this is possible because file and directory information is kept in separate places. The locking process still has its handle on the physical file, but the directory reference is removed, and a new file can be created. The locking process will see the old file until the handle is released (file closed), at which point the OS also deletes the physical data.
Please post your actual SAS error message. It is easy for users to cause this type of problem by opening a view data window in EG and just leaving it.
We deal with this type of problem by making our Production data libraries read-only via OS folder permissions and AD user groups for all SAS users except for SAS administrators allowed to run Production updates.
Our users have told us they haven't left the view data window open. Obviously someone is telling fibs as I think this is the issue as well!! 🙂
I have read only for the users in metadata and also on the file system (Windows).
This is the error message:
631 proc datasets lib = EDWPORT nolist nowarn memtype = (data view);
632 delete PORTFOLIO;
NOTE: Deleting EDWPORT.PORTFOLIO (memtype=VIEW).
ERROR: A lock is not available for EDWPORT.PORTFOLIO.VIEW.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: SAS set option OBS=0 and will continue to check statements. This might cause NOTE: No observations in data set.
Well, there might be "dead" processes lying around (workspace servers that have lost their client connection, but don't act properly by shutting themselves down). One can only try to use operating system tools to locate such processes. Sometimes locks might be caused by backup software or malware scanners (which are only needed on the Windows *@x?!#, one of the gazillion reasons Windows should never be used on a server).
Be also aware that a lock on a SAS view might be caused by a lock on one of the datasets referenced in the view.
So you might have some detective/computer forensics work coming up.
Actually, the problem is not that the file is locked, but that a lock (which is necessary for writing) can not be obtained, because some process(es) has/have an open handle on that file.
And therefore setting the file, directory or filesystem to RO for the other users (that don't need write access) won't remedy the problem.
There's no way for me to determine which process it is that has the lock on Windows however. I don't want to have to kill all processes.
If you want to run SAS as a more-or-less administrator or at least as a power user, access to certain operating system features and knowledge how to use them is essential. Either you run the system, or the system runs you.
On top of that, it is my personal belief that Windows is NOT suited as a server platform. Certain multi-user issues are much more easily dealt with in a UNIX system. Like the problem with open SAS datasets.
The SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment.
Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.
Find more tutorials on the SAS Users YouTube channel.