Informatica - Repository Queries

Informatica - Repository Queries

Index

1. SQL to get all workflows with Suspend on error checkbox not checked.
2. Informatica Workflows with No email tasks
3. SQL query to list all workflows which haven't run in last 24 hours.
4. All Workflows and tasks associated with it.
5. All Objects checked-in in last 24 hours
6. Average Run Stats vs Last Run Stats

1. SQL to get all workflows with Suspend on error checkbox not checked.

WITH LatestVersion AS
  (SELECT WORKFLOW_ID WORKFLOW_ID,
          MAX(VERSION_NUMBER) mVERSION_NUMBER
   FROM OPB_WORKFLOW
   WHERE BIT_OPTIONS =0
   GROUP BY WORKFLOW_ID),
     LatestTaskVersion AS
  (SELECT task_id task_id,
          max(version_number) mv
   FROM OPB_TASK
   GROUP BY TASK_ID)
SELECT rat.SUBJECT_AREA,
       rat.TASK_NAME,
       rat.LAST_SAVED
FROM OPB_WORKFLOW ow,
     LatestVersion lv,
     LatestTaskVersion ot,
     OPB_TASK ot1,
     REP_ALL_TASKS rat
WHERE lv.WORKFLOW_ID=ow.WORKFLOW_ID
  AND lv.mVERSION_NUMBER=ow.VERSION_NUMBER
  AND ot.TASK_ID=ow.WORKFLOW_ID
  AND (ot.task_id= ot1.TASK_ID
       AND ot.mv= ot1.VERSION_NUMBER)
  AND rat.TASK_ID=ot.task_id
  AND rat.TASK_TYPE=71

where BIT_OPTION represents the if the checkbox is checked or not.

2. Informatica Workflows with No email tasks

WITH latestWFID AS
  (SELECT WORKFLOW_ID WORKFLOW_ID,
          MAX(VERSION_NUMBER) VERSION_NUMBER
   FROM [OPB_WORKFLOW]
GROUP BY WORKFLOW_ID)
SELECT rw.SUBJECT_AREA,
       rw.WORKFLOW_NAME,
       rw.WORKFLOW_LAST_SAVED,
       'Email Not Set' AS NOTES
FROM latestWFID lw,
     OPB_WORKFLOW ow,
     REP_WORKFLOWS rw
WHERE lw.WORKFLOW_ID= ow.WORKFLOW_ID
  AND lw.VERSION_NUMBER= ow.VERSION_NUMBER
  AND EMAIL_ID = 0
  AND rw.WORKFLOW_ID=ow.WORKFLOW_ID

3.  SQL query to list all workflows which haven't run in last 24 hours.

WITH lastrunOfWF AS
  (SELECT WORKFLOW_ID WORKFLOW_ID,
          MAX(WORKFLOW_RUN_ID) WORKFLOW_RUN_ID
   FROM [PC_REP].[dbo].[REP_WFLOW_RUN]
   GROUP BY WORKFLOW_ID)
SELECT rwr.SUBJECT_AREA,
       rwr.WORKFLOW_NAME,
       rwr.START_TIME,
       rwr.END_TIME,
       CASE rwr.RUN_TYPE
           WHEN 1 THEN 'Scheduler Run'
           WHEN 2 THEN 'User request Run'
           ELSE convert(varchar, rwr.RUN_TYPE)
       END AS hello
FROM lastrunOfWF lrow,
     REP_WFLOW_RUN rwr
WHERE lrow.WORKFLOW_ID=rwr.WORKFLOW_ID
  AND lrow.WORKFLOW_RUN_ID= rwr.WORKFLOW_RUN_ID
  AND START_TIME < DATEADD(DAY, -1, GETDATE())
ORDER BY START_TIME

4) All Workflows and tasks associated with it.

SELECT *
FROM [REP_TASK_INST] rti,
     [REP_WORKFLOW_DEP] rwd
WHERE rti.WORKFLOW_ID=rwd.WORKFLOW_ID

5). All Objects checked-in in last 24 hours

SELECT rs.SUBJECT_AREA,
       rvp.OBJECT_NAME,
       rvp.LAST_SAVED,
       ru.USER_NAME,
       rvp.COMMENTS
FROM REP_VERSION_PROPS rvp,
     REP_USERS ru,
     REP_SUBJECT rs
WHERE rvp.USER_ID=ru.USER_ID
  AND rvp.SUBJECT_ID = rs.SUBJECT_ID





  AND LAST_SAVED > GETDATE() -1

6). Average Run Stats vs Last Run Stats  (i.e. No of records pulled from Source in the last run against average of all runs.

WITH MaxRunID AS
  (SELECT ostl.WORKFLOW_ID,
          ostl.INSTANCE_ID,
          max(ostl.WORKFLOW_RUN_ID) WORKFLOW_RUN_ID
   FROM [OPB_SESS_TASK_LOG] ostl,
        [OPB_TASK_INST_RUN] otir
   WHERE 1=1
     AND ostl.WORKFLOW_RUN_ID=otir.WORKFLOW_RUN_ID
     AND otir.START_TIME >= GETDATE()-1
   GROUP BY ostl.WORKFLOW_ID,
            ostl.INSTANCE_ID),
     latestRunData AS
  (SELECT ostl.WORKFLOW_ID,
          ostl.WORKFLOW_RUN_ID,
          ostl.INSTANCE_ID,
          ostl.SRC_SUCCESS_ROWS,
          ostl.SRC_FAILED_ROWS,
          ostl.TARG_SUCCESS_ROWS,
          ostl.TARG_FAILED_ROWS
   FROM MaxRunID mri,
        OPB_SESS_TASK_LOG ostl
   WHERE ostl.WORKFLOW_ID= mri.WORKFLOW_ID
     AND mri.INSTANCE_ID= ostl.INSTANCE_ID
     AND ostl.WORKFLOW_RUN_ID = mri.WORKFLOW_RUN_ID),
     avgAllSessionRunData AS
  (SELECT ostl.WORKFLOW_ID,
          ostl.INSTANCE_ID,
          avg(CAST (ostl.SRC_SUCCESS_ROWS AS bigint)) AVG_SRC_SUCCESS_ROWS,
          avg(CAST (ostl.SRC_FAILED_ROWS AS bigint)) AVG_SRC_FAILED_ROWS,
          avg(CAST (ostl.TARG_SUCCESS_ROWS AS bigint)) AVG_TARG_SUCCESS_ROWS,
          avg(CAST (ostl.TARG_FAILED_ROWS AS bigint)) AVG_TARG_FAILED_ROWS
   FROM MaxRunID mri,
        OPB_SESS_TASK_LOG ostl --,[dbo].[OPB_TASK_INST_RUN] otir

   WHERE ostl.WORKFLOW_ID= mri.WORKFLOW_ID
     AND mri.INSTANCE_ID= ostl.INSTANCE_ID
     AND ostl.WORKFLOW_RUN_ID !> mri.WORKFLOW_RUN_ID --and ostl.WORKFLOW_RUN_ID = otir.WORKFLOW_RUN_ID
 --and otir.START_TIME > DATEADD(Year,-1,GETDATE())

   GROUP BY ostl.WORKFLOW_ID,
            ostl.INSTANCE_ID)
SELECT aasrd.WORKFLOW_ID,
       lrd.WORKFLOW_RUN_ID,
       lrd.INSTANCE_ID,
       aasrd.AVG_SRC_FAILED_ROWS,
       lrd.SRC_FAILED_ROWS,
       aasrd.AVG_SRC_SUCCESS_ROWS,
       lrd.SRC_SUCCESS_ROWS,
       aasrd.AVG_TARG_FAILED_ROWS,
       lrd.TARG_FAILED_ROWS,
       aasrd.AVG_TARG_SUCCESS_ROWS,
       lrd.TARG_SUCCESS_ROWS
FROM avgAllSessionRunData aasrd,
     latestRunData lrd
WHERE aasrd.WORKFLOW_ID=lrd.WORKFLOW_ID
  AND aasrd.INSTANCE_ID = lrd.INSTANCE_ID
ORDER BY aasrd.WORKFLOW_ID,
         aasrd.INSTANCE_ID,
         lrd.WORKFLOW_RUN_ID

---