Friday, January 20, 2017

Informatica Metadata Queries - 14 USER & GROUPS




14.1         List Execute Locks on Objects in Folder Level


SELECT SUBJ_NAME FOLDER,
       CNX_ID,
       DECODE (LOCK_TYPE,
               1, 'NodeInUseLock',
               2, 'NodeWriteIntentLock',
               4, 'ExecuteLock',
               5, 'SharedLock',
               6, 'ExclusiveLock',
               7, 'SubTreeSharedLoc',
               8, 'SubTreeExclusiveLock',
               9, 'SubTreeISharedLock',
               10, 'SubTreeIExclusiveLock',
               11, 'SubTreeWriteIntentLock',
               12, 'SubTreeIWriteIntentLock')
          LOCK_TYPE
  FROM OPB_OBJECT_LOCKS L, OPB_SUBJECT S
WHERE L.SUBJECT_ID = SUBJ_ID
and lock_type = 4
--and SUBJ_NAME  = 'EBI_1_41'

14.2         List Execute Locks on Objects level by Users level



SELECT SUBJ_NAME FOLDER,U.USER_NAME,
       CNX_ID,
       DECODE (LOCK_TYPE,
               1, 'NodeInUseLock',
               2, 'NodeWriteIntentLock',
               4, 'ExecuteLock',
               5, 'SharedLock',
               6, 'ExclusiveLock',
               7, 'SubTreeSharedLoc',
               8, 'SubTreeExclusiveLock',
               9, 'SubTreeISharedLock',
               10, 'SubTreeIExclusiveLock',
               11, 'SubTreeWriteIntentLock',
               12, 'SubTreeIWriteIntentLock')
          LOCK_TYPE
  FROM OPB_OBJECT_LOCKS L, OPB_SUBJECT S, REP_USERS U
WHERE L.SUBJECT_ID = SUBJ_ID
and U.USER_ID=L.USER_ID
and lock_type = 4
order by 1

14.3         How to Kill a User with Execute Locks on Object Level


SELECT SUBJ_NAME FOLDER,
       CNX_ID,
       DECODE (LOCK_TYPE,
               1, 'NodeInUseLock',
               2, 'NodeWriteIntentLock',
               4, 'ExecuteLock',
               5, 'SharedLock',
               6, 'ExclusiveLock',
               7, 'SubTreeSharedLoc',
               8, 'SubTreeExclusiveLock',
               9, 'SubTreeISharedLock',
               10, 'SubTreeIExclusiveLock',
               11, 'SubTreeWriteIntentLock',
               12, 'SubTreeIWriteIntentLock')
          LOCK_TYPE
  FROM OPB_OBJECT_LOCKS L, OPB_SUBJECT S
WHERE L.SUBJECT_ID = SUBJ_ID
and lock_type = 4
--and SUBJ_NAME  = 'FolderName'


With the CNX_ID received in the result of the query search in admin console. And if the Delete user connection is enabled then you can delete the CNX_ID from admin console. Else check the properties for the connection ID and take the process ID and log in to the Host name (Node).

·         Execute Command ps –ef | grep “Process_ID”
·         Execute Kill -9 process_ID


14.4         List Group Names in a repository


SELECT
(SELECT REPOSITORY_NAME FROM OPB_REPOSIT_INFO) AS REPO_NAME,
GROUP_NAME, GROUP_DESC
FROM OPB_GROUPS
ORDER BY 1