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(

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)