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.