Recently I was debugging user renaming procedure (install/admin/userrename.ebs) and realised that EMC’s implementation smells a lot – weird logic related to locked and immutable objects:
' Unlock (or only report) sysobjects which are locked by the Old User. Call DmPrint(" ", "information") Call DmPrint("====== Sysobjects that are locked and have references to user '" & OldUserName & _ "' =====", "information") If(UnlockLckObj = true) then Call DmPrint("(all the objects in this list will be unlocked)", _ "information") Else Call DmPrint("(all the objects in this list will remain locked)", _ "information") End If Call DmPrint(" ", "information") QueryStr$ = "query,c,select r_object_id from " & _ "dm_sysobject (all) where " & _ "(owner_name = '" & DqlOldUserName & "' or " & _ "r_creator_name = '" & DqlOldUserName & "' or " & _ "r_modifier = '" & DqlOldUserName & "' or " & _ "acl_domain = '" & DqlOldUserName & "' or " & _ "r_lock_owner = '" & DqlOldUserName & "') and " & _ "r_lock_owner <> ' '" Call DmUpdateSysobj("unlock", QueryStr) Call DmRenameDmUserObj() ... ' Update (or only report) sysobjects which are not locked. Call DmPrint(" ", "information") Call DmPrint("====== Sysobjects referencing user '" & OldUserName & _ "', which are not locked =====", "information") Call DmPrint(" ", "information") QueryStr$ = "query,c,select r_object_id from " & _ "dm_sysobject (all) where " & _ "(owner_name = '" & DqlOldUserName & "' or " & _ "r_creator_name = '" & DqlOldUserName & "' or " & _ "r_modifier = '" & DqlOldUserName & "' or " & _ "acl_domain = '" & DqlOldUserName & "' or " & _ "r_lock_owner = '" & DqlOldUserName & "') and " & _ "r_lock_owner = ' '" Call DmUpdateSysobj("", QueryStr) ... Sub DmUpdateSysobj(unlockOnly As String, QueryStr As String) Dim DmQuery As String, ObjectId As String, theModifier As String Dim mutable As String, DmObjectType As String, DmOwnerName As String, AttrList As String, DmModifierName As String, DmLockOwner As String Dim DmAclDomain As String, DmCreatorName As String Dim NumOfObj As Long, ret As Integer Dim lockOwner As String DmQuery = dmAPIGet(QueryStr) If DmQuery = "" then Call DmPrint("Could not query sysobjects referencing user " & _ OldUserName, "fatal") End If NumOfObj = 0 While dmAPIExec("next,c," & DmQuery) NumOfObj = NumOfObj + 1 ObjectId = dmAPIGet("get,c," & DmQuery & ",r_object_id") ret = dmAPIExec("fetch,c," & ObjectId) If ret = 0 then Call DmPrint("Could not fetch object with Id " & ObjectId & _ ".", "fatal") End If If unlockOnly = "unlock" Then Call UnlockObject(ObjectId) Else DmObjectType = dmAPIGet("get,c,l,r_object_type") lockOwner = dmAPIGet("get,c,l,r_lock_owner") If ReportOnly = false Then ' save latest modifier, check immutability ret theModifier = dmAPIGet("get,c,l,r_modifier") mutable = dmAPIGet("get,c,l,r_immutable_flag") If mutable = "1" Or mutable$ = "T" Then ret = dmAPISet("set,c,l,r_immutable_flag", "F") End If End If AttrList = "" DmOwnerName = dmAPIGet("get,c," & ObjectId & ",owner_name") If(DmOwnerName = OldUserName) then AttrList = "owner_name" End If DmAclDomain = dmAPIGet("get,c," & ObjectId & ",acl_domain") If(DmAclDomain = OldUserName) then If(AttrList <> "") then AttrList = AttrList & "," End If AttrList = AttrList & "acl_domain" End If DmCreatorName = dmAPIGet("get,c," & ObjectId & ",r_creator_name") If(DmCreatorName = OldUserName) then If(AttrList <> "") then AttrList = AttrList & "," End If AttrList = AttrList & "r_creator_name" End If DmModifierName = dmAPIGet("get,c," & ObjectId & ",r_modifier") If(DmModifierName = OldUserName) then If(AttrList <> "") then AttrList = AttrList & "," End If AttrList = AttrList & "r_modifier" End If DmLockOwner = dmAPIGet("get,c," & ObjectId & ",r_lock_owner") If(DmLockOwner = OldUserName) then If(AttrList <> "") then AttrList = AttrList & "," End If AttrList = AttrList & "r_lock_owner" End If If(AttrList <> "") then Call CaExecute(DmObjectType, ObjectId, "object_name", AttrList, _ "Could not save sysobject", "error", "dm_sysobject", "") End If If ReportOnly = false Then ' set immutability back, if necessary If mutable$ = "1" Or mutable$ = "T" Then ret = dmAPISet("set,c,l,r_immutable_flag", "T") ret = dmAPIExec("save,c,l") End If ' set modifier back to original, or newname if last modified by renamed user If theModifier$ = OldUserName Then theModifier$ = NewUserName ' Checking for ReportOnly above, don't need to call ExecuteUpdateSql ret = dmAPIExec("execsql,c,update dm_sysobject_s set r_modifier = '" & theModifier$ & _ "' where r_object_id = '" & ObjectId$ & "'") End If End If Wend ret = dmAPIExec("close,c," & DmQuery) Call DmPrint("**** Number of sysobjects affected: " & NumOfObj, _ "information") End Sub
Below is my extremely clear and straightforward implementation:
Call DmUpdateUsrDefACL() ' Update sysobjects. Call DmUpdateSysobj() ... Sub DmUpdateSysobj() Dim query As String Dim ret As Integer ' todo: add logic for MSSQL query = "update dm_sysobject_s " & _ "set i_vstamp=i_vstamp+1, " & _ "r_modifier=DECODE(r_modifier, '" & DqlOldUserName & "','" & DqlNewUserName & "', r_modifier), " & _ "r_lock_owner=DECODE(r_lock_owner, '" & DqlOldUserName & "','" & DqlNewUserName & "', r_lock_owner), " & _ "owner_name=DECODE(owner_name, '" & DqlOldUserName & "','" & DqlNewUserName & "', owner_name), " & _ "acl_domain=DECODE(acl_domain, '" & DqlOldUserName & "','" & DqlNewUserName & "', acl_domain), " & _ "r_creator_name=DECODE(r_creator_name, '" & DqlOldUserName & "','" & DqlNewUserName & "', r_creator_name) " & _ "where (owner_name = '" & DqlOldUserName & "' or " & _ "r_creator_name = '" & DqlOldUserName & "' or " & _ "r_modifier = '" & DqlOldUserName & "' or " & _ "acl_domain = '" & DqlOldUserName & "' or " & _ "r_lock_owner = '" & DqlOldUserName & "')" Call DmPrint("Updating sysobjects using query: " & query, "information") ret = ExecuteUpdateSql(query, "Update sysobject attributes for user " & OldUserName) If ret <> 1 Then Call DmPrint("Could not update sysobject attributes for user " & OldUserName,"fatal") Exit Sub End If End Sub
Enjoy 🙂
Hum, doing a OR in query, for performance issue on large docbase….
In previous actions, I did change the full script to remove all OR. Instead of one day, it took 1 hour to do the job 😉
LikeLike
And what performance issues does OR introduce? When we are renaming user (actually it is extremely stupid idea to rename users in Documentum, but dumb LDAP sync job does it sometimes) we need to change values of at least following attributes: acl_domain, owner_name and r_lock_owner – so, you need to either use OR in WHERE clause or issue three queries. If you concern about performance just add FULL and PARALLEL hints to the query.
LikeLike
When I had to do this, it did introduce multiple queries with an UNION.
It was really must faster like this. And yes, we were concerned by performance issue, because the ob run each day, but did not complete in 24h.
It did not use your trick about keywords, did not know them, but performances were really better.
By the way, I had a similar issue with large docbase and the component MyFiles in Webtop, but this is another subject.
LikeLike