Friday, January 20, 2017

Informatica Metadata Queries - 2 INFORMATICA FOLDERS




2.1         List folder details


SELECT SUBJ_NAME,SUBJ_DESC FROM OPB_SUBJECT ORDER BY 1,2

2.2         List of shared folders


SELECT SUBJ_NAME,SUBJ_DESC FROM OPB_SUBJECT 
WHERE IS_SHARED <>0
ORDER BY 1,2


2.3         List of Users and groups having privileges’ on Folders


SELECT  (select repository_name from OPB_REPOSIT_INFO) repo_name,subj.subj_name folder_name, user_group.NAME user_name,
    DECODE (obj_access.user_type, 1, 'USER', 2, 'GROUP') TYPE,
    CASE WHEN ((obj_access.permissions - (obj_access.user_id + 1)) IN (8, 16))THEN 'READ'        
   WHEN ((obj_access.permissions - (obj_access.user_id + 1)) IN (10, 20))THEN 'READ  EXECUTE'
         WHEN ((obj_access.permissions - (obj_access.user_id + 1)) IN (12, 24))THEN 'READ  WRITE'
         WHEN ((obj_access.permissions - (obj_access.user_id + 1)) IN (14, 28))THEN 'READ, WRITE  EXECUTE'
    ELSE 'NO PERMISSIONS'
    END permissions
    FROM opb_object_access obj_access,opb_subject subj,opb_user_group user_group
     WHERE obj_access.object_type = 29
       AND obj_access.object_id = subj.subj_id
       AND obj_access.user_id = user_group.ID
       AND obj_access.user_type = user_group.TYPE
     --  and user_group.NAME not in ('Admin','READ_ONLY','Administrator','Administrators')
order by 1,2,3


2.4                      List of Folder Owners


select (select repository_name from OPB_REPOSIT_INFO) repo_name,
a.SUBJ_NAME,b.NAME
from opb_subject a, opb_user_group b
where a.owner_id = b.id
order by 1,2

2.5            Drop list of folders using Script



pmrep connect -d Domain_NAME -n USERNAME -x PASSWORD -r RS_NAME
while read p; do
echo $p
echo "DELETING" $p
pmrep deletefolder -n $p
done < Filewithlistoffolderstobedeleted.txt

Step 1 : Enter the folder names in a file Filewithlistoffolderstobedeleted.txt
Step 2: Enter the details to connect to repository in pmrepconnect command
Then execute the script.
Let me know if this helps or if you need some other info for the same. 


No comments:

Post a Comment