Friday, May 25, 2012

INFORMATICA REPOSITORY QUERIES - PART V



INFORMATICA REPOSITORY QUERIES - PART I
INFORMATICA REPOSITORY QUERIES - PART II
INFORMATICA REPOSITORY QUERIES - PART III
INFORMATICA REPOSITORY QUERIES - PART IV
 

16        SCHEDULER


 16.1           Check Scheduled workflows using pmcmd command
 

Command Prompt:

pmcmd getservicedetails -sv etltst222_iserv -d domain_ etltst222-u Administrator -p Administrator –scheduled



16.2           Check Scheduled workflows using queries



Query:

 SELECT DISTINCT subject_area, workflow_name FROM rep_workflows WHERE run_options = 8 AND end_options =2 ORDER BY 1;

 
select
SUBJECT_AREA,
WORKFLOW_NAME,
SCHEDULER_NAME,
START_TIME,
END_TIME,
Case when RUN_OPTIONS = 1 then 'Run on demand'
        when RUN_OPTIONS = 2 then 'Run once'
        when RUN_OPTIONS = 4 then 'Run every DELTA_VALUE seconds'
       when RUN_OPTIONS = 8 then  'Customized repeat'
       when RUN_OPTIONS = 16 then 'Run on IS int'
       when RUN_OPTIONS = 18 then 'Run on IS int and run once'
    when RUN_OPTIONS = 20 then 'Run on IS int and every DELTA_VALUE seconds'
    when RUN_OPTIONS = 24 then 'Run on IS int and customized repeat'
    when RUN_OPTIONS = 32 then 'Run continuously'
        else 'Dummy'
end as RUN_OPTIONS,SERVER_NAME
from rep_workflows
where
--SUBJECT_AREA = 'EABC_2_02'
and RUN_OPTIONS <> 1
order by 2,3


17        INTEGRATION SERVICES


 
17.1        Count of workflows running on each Integration Service

 select server_name,count(1) from(
select subject_id, workflow_name,server_name, trunc(start_time) as start_time,
rank() over (partition by subject_id, workflow_name order by start_time desc ) rnk
from  OPB_WFLOW_RUN
) where rnk=1
group by server_name
--order by 1,2,3


--  AS (SELECT Server_name, Workflow_name, start_time FROM OPB_WFLOW_RUN)
  SELECT (SELECT repository_name FROM OPB_REPOSIT_INFO) AS REPO,
  TRUNC (START_TIME),
         A.SERVER_NAME,
         COUNT (*)
    FROM OPB_WFLOW_RUN A
   WHERE TRUNC (START_TIME) = (  SELECT MAX (TRUNC (AA.START_TIME))
                                   FROM OPB_WFLOW_RUN AA
                                  WHERE AA.workflow_name = A.workflow_name
                               GROUP BY A.workflow_name)
GROUP BY SERVER_NAME,TRUNC (START_TIME)
--ORDER BY 1, 2, 3
 
 
17.2        List of workflows running on each Integration Service


select * from(
select subject_id, workflow_name,server_name, trunc(start_time) as start_time,
rank() over (partition by subject_id, workflow_name order by start_time desc ) rnk
from  OPB_WFLOW_RUN
) where rnk=1
order by 1,2,3

 

17.3        how to mass update connection


Incase if we have to update permission for multiple connections please use the below pmrep command, Adjust the parameters as needed and make it as a script under $INFA_HOME/server/bin.

 

AssignPermission -o [-t ] -n {-u | -g } [-s ] -p
 

Example:

pmrep connect -r Repository_EDW -d Domain_EDW -n abcd-s LDAP -x xxxx

pmrep assignPermission -o Connection -t Relational -n ADM_Src_d -g Informatica_grade -p RWX  (use updatepermission for modifying the currently assigned permission)