-- Get number of session count by hour within last 24 hours
select hour_range.start_time,hour_range.end_time, count(1) from
(select trunc(sysdate,'HH') - (level+1)/24 start_time, trunc(sysdate,'HH') - level/24 end_time from dual
connect by level <= 3*24) hour_range
left join ((select DISTINCT
--Workflow
S.SUBJ_NAME as FOLDER_NAME
,WR.WORKFLOW_NAME as WF_NAME
,WR.START_TIME as WF_START
,TRUNC(WR.START_TIME) as RUN_DATE
--Session
,OTIR.INSTANCE_NAME as SES_NAME
,OTIR.START_TIME as SES_STIME
/*
,OSTL.MAPPING_NAME
,OSTL.SRC_SUCCESS_ROWS
,OSTL.TARG_SUCCESS_ROWS
,OTIR.START_TIME
,TO_CHAR(TRUNC(MOD((OTIR.END_TIME - OTIR.START_TIME) * 24 ,24)),'00') || ':' ||
TO_CHAR(TRUNC(MOD((OTIR.END_TIME - OTIR.START_TIME) * 24 * 60 , 60)),'00') || ':' ||
TO_CHAR(TRUNC(MOD(TO_CHAR(OTIR.END_TIME, 'SSSSS') - TO_CHAR(OTIR.START_TIME, 'SSSSS') , 60)),'00') AS SES_ELAPSED_TIME,
TO_CHAR(OTIR.END_TIME, 'SSSSS') - TO_CHAR(OTIR.START_TIME, 'SSSSS') AS SES_ELAPSED_TIME_IN_SEC
*/
FROM DWPC95.OPB_WFLOW_RUN WR
INNER JOIN
DWPC95.OPB_SUBJECT S ON WR.SUBJECT_ID = S.SUBJ_ID
INNER JOIN
DWPC95.OPB_SESS_TASK_LOG OSTL ON WR.WORKFLOW_ID = OSTL.WORKFLOW_ID
AND WR.WORKFLOW_RUN_ID = OSTl.WORKFLOW_RUN_ID
INNER JOIN
DWPC95.OPB_TASK_INST_RUN OTIR ON WR.SUBJECT_ID = OTIR.SUBJECT_ID
AND OSTL.INSTANCE_ID = OTIR.INSTANCE_ID
AND OSTL.WORKFLOW_ID = OTIR.WORKFLOW_ID
AND OSTL.WORKFLOW_RUN_ID = OTIR.WORKFLOW_RUN_ID
AND OTIR.TASK_TYPE = 68
WHERE
WR.START_TIME between sysdate-3 and sysdate)
) session_run
on session_run.SES_STIME BETWEEN hour_range.start_time and hour_range.end_time
group by hour_range.start_time, hour_range.end_time
-- Get QC Number and deployment time FROM TEST to VCT
select substr(dep_group_name,14,instr(dep_group_name,'_',1,3)-14) "QC", to_date(creation_time,'MM-DD-YYYY hh24:mi:ss') "DeploymentTime" from DWPC.REP_DEPLOY_GROUP
where query_id = 0
order by to_date(creation_time,'MM-DD-YYYY hh24:mi:ss') desc
/
-- Get QC Number and deployment time FROM VCT to UAT
select substr(dep_group_name,14,instr(dep_group_name,'_',1,3)-14) "QC", to_date(creation_time,'MM-DD-YYYY hh24:mi:ss') "DeploymentTime" from DWPC_VC.REP_DEPLOY_GROUP
where query_id = 0
order by to_date(creation_time,'MM-DD-YYYY hh24:mi:ss') desc
/
-- Get QC Number and deployment time FROM UAT to PROD
select substr(dep_group_name,14,instr(dep_group_name,'_',1,3)-14) "QC", to_date(creation_time,'MM-DD-YYYY hh24:mi:ss') "DeploymentTime" from DWPC_UAT.REP_DEPLOY_GROUP
where query_id = 0
order by to_date(creation_time,'MM-DD-YYYY hh24:mi:ss') desc
/
-- Failed rows within specific time period
select DISTINCT
--Workflow
S.SUBJ_NAME as FOLDER_NAME
,WR.WORKFLOW_NAME as WF_NAME
,WR.START_TIME as WF_START
--Session
,OTIR.INSTANCE_NAME as SES_NAME
,OSTL.MAPPING_NAME
,OSTL.SRC_SUCCESS_ROWS
,OSTL.TARG_SUCCESS_ROWS
,OSTL.SRC_FAILED_ROWS
,OSTL.TARG_FAILED_ROWS
,TO_CHAR(TRUNC(MOD((OTIR.END_TIME - OTIR.START_TIME) * 24 ,24)),'00') || ':' ||
TO_CHAR(TRUNC(MOD((OTIR.END_TIME - OTIR.START_TIME) * 24 * 60 , 60)),'00') || ':' ||
TO_CHAR(TRUNC(MOD(TO_CHAR(OTIR.END_TIME, 'SSSSS') - TO_CHAR(OTIR.START_TIME, 'SSSSS') , 60)),'00') AS SES_ELAPSED_TIME
FROM DWPC95_QA.OPB_WFLOW_RUN WR
INNER JOIN
DWPC95_QA.OPB_SUBJECT S ON WR.SUBJECT_ID = S.SUBJ_ID
INNER JOIN
DWPC95_QA.OPB_SESS_TASK_LOG OSTL ON WR.WORKFLOW_ID = OSTL.WORKFLOW_ID
AND WR.WORKFLOW_RUN_ID = OSTl.WORKFLOW_RUN_ID
INNER JOIN
DWPC95_QA.OPB_TASK_INST_RUN OTIR ON WR.SUBJECT_ID = OTIR.SUBJECT_ID
AND OSTL.INSTANCE_ID = OTIR.INSTANCE_ID
AND OSTL.WORKFLOW_ID = OTIR.WORKFLOW_ID
AND OSTL.WORKFLOW_RUN_ID = OTIR.WORKFLOW_RUN_ID
AND OTIR.TASK_TYPE = 68
AND S.SUBJ_NAME IN ('DW ODS', 'DW Staging')
AND WR.START_TIME BETWEEN TO_DATE('03/23/2015 06:00 AM','MM/DD/YYYY HH:MI AM') AND TO_DATE('03/23/2015 09:00 AM','MM/DD/YYYY HH:MI AM')
AND (OSTL.SRC_FAILED_ROWS > 0 OR OSTL.TARG_FAILED_ROWS > 0 );
-- Query to get session statistics for Enterprise DW Dimension folder
select DISTINCT
--Workflow
S.SUBJ_NAME as FOLDER_NAME
,WR.WORKFLOW_NAME as WF_NAME
,WR.START_TIME as WF_START
--Session
,OTIR.INSTANCE_NAME as SES_NAME
,OSTL.MAPPING_NAME
,OSTL.SRC_SUCCESS_ROWS
,OSTL.TARG_SUCCESS_ROWS
,TO_CHAR(TRUNC(MOD((OTIR.END_TIME - OTIR.START_TIME) * 24 ,24)),'00') || ':' ||
TO_CHAR(TRUNC(MOD((OTIR.END_TIME - OTIR.START_TIME) * 24 * 60 , 60)),'00') || ':' ||
TO_CHAR(TRUNC(MOD(TO_CHAR(OTIR.END_TIME, 'SSSSS') - TO_CHAR(OTIR.START_TIME, 'SSSSS') , 60)),'00') AS SES_ELAPSED_TIME FROM DWPC.OPB_WFLOW_RUN WR
INNER JOIN
DWPC.OPB_SUBJECT S ON WR.SUBJECT_ID = S.SUBJ_ID
INNER JOIN
DWPC.OPB_SESS_TASK_LOG OSTL ON WR.WORKFLOW_ID = OSTL.WORKFLOW_ID
AND WR.WORKFLOW_RUN_ID = OSTl.WORKFLOW_RUN_ID
INNER JOIN
DWPC.OPB_TASK_INST_RUN OTIR ON WR.SUBJECT_ID = OTIR.SUBJECT_ID
AND OSTL.INSTANCE_ID = OTIR.INSTANCE_ID
AND OSTL.WORKFLOW_ID = OTIR.WORKFLOW_ID
AND OSTL.WORKFLOW_RUN_ID = OTIR.WORKFLOW_RUN_ID
AND OTIR.TASK_TYPE = 68
AND S.SUBJ_NAME = 'Enterprise DW ODS'
-- Query to get Workflow ran during specific time period
SELECT S.SUBJ_NAME AS FOLDER_NAME
,WR.WORKFLOW_NAME AS WF_NAME
,WR.START_TIME AS WF_START
,WR.END_TIME AS WF_END,
TO_CHAR(TRUNC(MOD((WR.END_TIME - WR.START_TIME) * 24 ,24)),'00') || ':' ||
TO_CHAR(TRUNC(MOD((WR.END_TIME - WR.START_TIME) * 24 * 60 , 60)),'00') || ':' ||
TO_CHAR(TRUNC(MOD(TO_CHAR(WR.END_TIME, 'SSSSS') - TO_CHAR(WR.START_TIME, 'SSSSS') , 60)),'00') AS WF_TIME
FROM DWPC95_DEV.OPB_WFLOW_RUN WR
INNER JOIN
DWPC95_DEV.OPB_SUBJECT S ON WR.SUBJECT_ID = S.SUBJ_ID
WHERE WR.START_TIME BETWEEN to_date('09/25/2014 03:10:00 PM','MM/DD/YYYY HH:MI:SS AM') AND to_date('09/25/2014 03:35:00 PM','MM/DD/YYYY HH:MI:SS AM')
ORDER BY WR.START_TIME
-- Query to get workflow run time during perticular day in specific folders
SELECT S.SUBJ_NAME AS FOLDER_NAME
,WR.WORKFLOW_NAME AS WF_NAME
,WR.START_TIME AS WF_START
,WR.END_TIME AS WF_END,
TO_CHAR(TRUNC(MOD((WR.END_TIME - WR.START_TIME) * 24 ,24)),'00') || ':' ||
TO_CHAR(TRUNC(MOD((WR.END_TIME - WR.START_TIME) * 24 * 60 , 60)),'00') || ':' ||
TO_CHAR(TRUNC(MOD(TO_CHAR(WR.END_TIME, 'SSSSS') - TO_CHAR(WR.START_TIME, 'SSSSS') , 60)),'00') AS WF_TIME
FROM DWPC.OPB_WFLOW_RUN WR
INNER JOIN
DWPC.OPB_SUBJECT S ON WR.SUBJECT_ID = S.SUBJ_ID
WHERE S.SUBJ_NAME IN ('Enterprise DW Dimension',
'Enterprise DW ODS',
'Enterprise DW Staging','Elvis Datamart')
AND WR.START_TIME BETWEEN TRUNC(SYSDATE-1) AND TRUNC(SYSDATE)
ORDER BY WR.START_TIME
-- connections being used at VCI
SELECT
DISTINCT
WF.SUBJECT_AREA AS FOLDER_NAME,
WF.WORKFLOW_NAME AS WORKFLOW_NAME,
T.INSTANCE_NAME AS SESSION_NAME,
T.TASK_TYPE_NAME,
C.CNX_NAME AS VARIABLE_NAME,
V.CONNECTION_NAME,
V.CONNECTION_SUBTYPE,
V.HOST_NAME,
V.USER_NAME,
--C.INSTANCE_NAME,
C.READER_WRITER_TYPE--,
--C.SESS_EXTN_OBJECT_TYPE
FROM
dwpc95.REP_TASK_INST T
INNER JOIN dwpc95.REP_SESS_WIDGET_CNXS C ON C.SESSION_ID = T.TASK_ID
INNER JOIN dwpc95.REP_WORKFLOWS WF ON WF.WORKFLOW_ID = T.WORKFLOW_ID
LEFT JOIN dwpc95.V_IME_CONNECTION V ON UPPER(CASE WHEN C.CNX_NAME = '$DBConnection_MDR_REPO_READER' THEN 'REPO_READER'
WHEN C.CNX_NAME = '$DBConnection_MSQL_FISERV_BF' THEN 'USVCITST031_cf_50_backfill'
WHEN C.CNX_NAME = '$DBConnection_ORA_DW_STAGING' THEN 'ODS_DW_STAGING_ETL'
WHEN C.CNX_NAME = '$DBConnection_ORA_DW_ODS' THEN 'ODS_DW_ODS_ETL'
WHEN C.CNX_NAME = '$DBConnection_ORA_DW_MART' THEN 'DIM_DW_MART_ETL'
WHEN C.CNX_NAME = '$DBConnection_SYB_DW_EXTRACT1' THEN 'lp_prod_BCV_DW_STAGING'
WHEN C.CNX_NAME = '$DBConnection_SYB_DW_EXTRACT' THEN 'lp_prod_DW_STAGING'
WHEN C.CNX_NAME = '$DBConnection_ORA_ELVIS' THEN 'Elvis_DW_USER_Daily'
WHEN C.CNX_NAME = '$DBConnection_SQLS_FISERV' THEN 'FISERV_DW_USER'
WHEN C.CNX_NAME = '$DBConnection_ORA_CROWE' THEN 'WLSP_CROWE'
WHEN C.CNX_NAME = '$DBConnection_ORA_ELVIS_FS' THEN 'Elvis_Elvis_FS'
WHEN C.CNX_NAME = '$DBConnection_ORA_VDWP_ODS' THEN 'VDWP_DW_DATA'
WHEN C.CNX_NAME = '$DBConnection_MSQL_FISERV' THEN 'USVCILVS008_cf'
WHEN C.CNX_NAME = '$DBConnection_ORA_DW_ODS_AUTODEPLOY' THEN 'ODS_DEPLOYMENT'
WHEN C.CNX_NAME = '$DBConnection_ORA_DW_DIM_AUTODEPLOY' THEN 'DIM_DEPLOYMENT'
WHEN C.CNX_NAME = '$DBConnection_ORA_DIM_DW_DF_ETL' THEN 'DIM_DW_DF_ETL'
WHEN C.CNX_NAME = '$DBConnection_ORA_ODS_DW_DF_ETL' THEN 'ODS_DW_DF_ETL'
WHEN C.CNX_NAME = '$DBConnection_ORA_ABS' THEN 'ABS_ETL'
WHEN C.CNX_NAME = '$DBConnection_ORA_DE' THEN 'FSPORTALAPP_ETL'
WHEN C.CNX_NAME = '$AppConnection_OMNI_Local' THEN 'OmnitureLocalService'
WHEN C.CNX_NAME = '$DBConnection_ORA_FSP' THEN 'FSP_ETL'
WHEN C.CNX_NAME = '$DBConnection_ORA_DW_MART_BACKFILL' THEN 'DIM_DW_MART_ETL_BACKFILL'
WHEN C.CNX_NAME = '$DBConnection_ORA_DW_ODS_BACKFILL' THEN 'ODS_DW_ODS_ETL_BACKFILL'
WHEN C.CNX_NAME = '$DBConnection_ORA_ELVIS_BACKFILL' THEN 'Elvis_Elvis_BACKFILL'
WHEN C.CNX_NAME = '$DBConnection_ORA_MAPS' THEN 'MAPP_DWUSER'
WHEN C.CNX_NAME = '$DBConnection_ORA_DW_EXCEPTION' THEN 'DIM_DW_EXCEPTION_ETL'
WHEN C.CNX_NAME = '$DBConnection_ORA_IVR' THEN 'IVR_DWUSER'
WHEN C.CNX_NAME = '$DBConnection_ORA_ELVIS_ALT' THEN 'Elvis_Elvis_BACKFILL'
WHEN C.CNX_NAME = '$DBConnection_MSQL_FISERV_BACKFILL' THEN 'USVCITST031_cf_50_backfill'
WHEN C.CNX_NAME = '$AppConnection_Vehicle_Info' THEN 'MM_AutoData_Web_Service_VehicleInfo'
WHEN C.CNX_NAME = '$DBConnection_ORA_VC_BATCH' THEN 'RMK_DW_ETL' END) = UPPER(V.CONNECTION_NAME)
-- Query to get connection details for sessions
SELECT WF.SUBJECT_AREA AS FOLDER_NAME, WF.WORKFLOW_NAME AS WORKFLOW_NAME,
T.INSTANCE_NAME AS SESSION_NAME, T.TASK_TYPE_NAME,
C.CNX_NAME AS CONNECTION_NAME, V.CONNECTION_SUBTYPE, V.HOST_NAME,
V.USER_NAME, C.INSTANCE_NAME, C.READER_WRITER_TYPE,
C.SESS_EXTN_OBJECT_TYPE
FROM DWPC95_TEST.REP_TASK_INST T,
DWPC95_TEST.REP_SESS_WIDGET_CNXS C,
DWPC95_TEST.REP_WORKFLOWS WF,
DWPC95_TEST.V_IME_CONNECTION V
WHERE T.TASK_ID = C.SESSION_ID
AND WF.WORKFLOW_ID = T.WORKFLOW_ID
AND C.CNX_NAME = V.CONNECTION_NAME
AND WF.SUBJECT_AREA = 'Cloverleaf Extractions'
/* Get Details of all connections stings in VCI Prod.
*/
SELECT
cnx.object_id AS "OBJECT_ID",
cnx.object_name AS "CONNECTION_NAME",
case cnx.object_subtype
WHEN 101 THEN 'Oracle'
WHEN 102 THEN 'Sybase'
WHEN 106 THEN 'ODBC'
WHEN 304000 THEN 'AS400/8.6'
ELSE 'Other'
END AS "DATABASE_TYPE",
cnx.connect_string AS "CONNECT_STRING",
cnx.user_name AS "USER_NAME",
attribute1.attribute AS "AS400_SERVER",
attribute9.attribute AS "AS400_DATABASE",
attribute10.attribute AS "ADDITIONAL_INFORMATION",
attribute11.attribute AS "SERVER_NAME"
FROM dwpc.opb_cnx cnx
LEFT JOIN (
SELECT object_id, attr_value AS attribute
FROM dwpc.opb_cnx_attr
WHERE attr_id = 1
) attribute1
ON cnx.object_id = attribute1.object_id
LEFT JOIN (
SELECT object_id, attr_value AS attribute
FROM dwpc.opb_cnx_attr
WHERE attr_id = 9
) attribute9
ON cnx.object_id = attribute9.object_id
LEFT JOIN (
SELECT object_id, attr_value AS attribute
FROM dwpc.opb_cnx_attr
WHERE attr_id = 10
) attribute10
ON cnx.object_id = attribute10.object_id
LEFT JOIN (
SELECT object_id, attr_value AS attribute
FROM dwpc.opb_cnx_attr
WHERE attr_id = 11
) attribute11
ON cnx.object_id = attribute11.object_id
/*********************************************************/
-- ALL OBJECT TYPES IN INFORMATICA
SELECT * FROM DWPC.OPB_OBJECT_TYPE
-- Query for Rich to get mapping using batch date - logic
select
RAM.subject_area AS FOLDER_NAME,
RAM.mapping_name,
RWI.WIDGET_TYPE_NAME AS TRANSFORMATION_TYPE,
RWI.INSTANCE_NAME AS TRANSFORMATION_NAME,
RWA.ATTR_NAME,
RWA.ATTR_VALUE
from
dwpc_uat.rep_all_mappings RAM,
dwpc_uat.rep_widget_attr RWA,
dwpc_uat.rep_widget_inst RWI
where
RWI.MAPPING_ID=RAM.MAPPING_ID
AND
RWA.WIDGET_ID = RWI.WIDGET_ID
AND
(RWA.attr_name='Sql Query'
or
RWA.attr_name='Source Filter' or 1=1)
and
(
RWA.attr_value like '%$$BatchControlDtId-%'
or
RWA.attr_value like '%$$BatchControlDtId -%'
or
RWA.attr_value like '%$$BatchControlDtId -%'
)
ORDER BY RAM.SUBJECT_AREA,RAM.MAPPING_NAME,RWI.INSTANCE_NAME
--This query returns all mappings that write to a particular table and column (Query from Alex)
SELECT
RMCP.subject_area as folder_name,
RMCP.mapping_name,
RMCP.to_object_name as target_instance_name
FROM
DWPC_UAT.rep_mapping_conn_ports RMCP,
dwpc_uat.rep_all_targets RAL
WHERE
--BEGIN PARAMETERS
RAL.TARGET_NAME='ASSET'
AND
RMCP.to_object_field_name='MODEL_YEAR_NBR'
--END PARAMETERS
AND
RAL.PARENT_TARGET_ID=RMCP.TO_OBJECT_ID
AND
RMCP.to_object_type_name='Target Definition'
ORDER BY
RMCP.SUBJECT_AREA,RMCP.MAPPING_NAME,RMCP.TO_OBJECT_NAME
-- Query to get workflow and session statistics
select DISTINCT
--Workflow
S.SUBJ_NAME as FOLDER_NAME
,WR.WORKFLOW_NAME as WF_NAME
,WR.START_TIME as WF_START
,WR.END_TIME as WF_END
,WR.RUN_ERR_CODE as WF_ERR_CODE
,WR.RUN_ERR_MSG as WF_ERR_MSG
,TO_CHAR(TRUNC(MOD((WR.END_TIME - WR.START_TIME) * 24 ,24)),'00') || ':' ||
TO_CHAR(TRUNC(MOD((WR.END_TIME - WR.START_TIME) * 24 * 60 , 60)),'00') || ':' ||
TO_CHAR(TRUNC(MOD(TO_CHAR(WR.END_TIME, 'SSSSS') - TO_CHAR(WR.START_TIME, 'SSSSS') , 60)),'00') AS WF_ELAPSED_TIME
--Session
,OTIR.INSTANCE_NAME as SES_NAME
,OSTL.MAPPING_NAME
,OSTL.SRC_FAILED_ROWS
,OSTL.SRC_SUCCESS_ROWS
,OSTL.TARG_FAILED_ROWS
,OSTL.TARG_SUCCESS_ROWS
,OTIR.START_TIME as SES_START
,OTIR.END_TIME as SES_END
,OSTL.FIRST_ERROR_CODE as SES_ERR_CODE
,OTIR.RUN_ERR_MSG as SES_ERR_MSG
,DECODE(OTIR.RUN_STATUS_CODE, 1,'SUCCEEDED',3,'FAILED') AS SES_STATUS_CODE
,TO_CHAR(TRUNC(MOD((OTIR.END_TIME - OTIR.START_TIME) * 24 ,24)),'00') || ':' ||
TO_CHAR(TRUNC(MOD((OTIR.END_TIME - OTIR.START_TIME) * 24 * 60 , 60)),'00') || ':' ||
TO_CHAR(TRUNC(MOD(TO_CHAR(OTIR.END_TIME, 'SSSSS') - TO_CHAR(OTIR.START_TIME, 'SSSSS') , 60)),'00') AS SES_ELAPSED_TIME FROM DWPC_UAT.OPB_WFLOW_RUN WR
INNER JOIN
DWPC_UAT.OPB_SUBJECT S ON WR.SUBJECT_ID = S.SUBJ_ID
INNER JOIN
DWPC_UAT.OPB_SESS_TASK_LOG OSTL ON WR.WORKFLOW_ID = OSTL.WORKFLOW_ID
AND WR.WORKFLOW_RUN_ID = OSTl.WORKFLOW_RUN_ID
INNER JOIN
DWPC_UAT.OPB_TASK_INST_RUN OTIR ON WR.SUBJECT_ID = OTIR.SUBJECT_ID
AND OSTL.INSTANCE_ID = OTIR.INSTANCE_ID
AND OSTL.WORKFLOW_ID = OTIR.WORKFLOW_ID
AND OSTL.WORKFLOW_RUN_ID = OTIR.WORKFLOW_RUN_ID
AND OTIR.TASK_TYPE = 68
AND S.SUBJ_NAME IN ('Elvis Datamart UAT')
AND OTIR.INSTANCE_NAME = 's_m_WRITE_AccountAssetUsageAddress'
ORDER BY S.SUBJ_NAME,WR.WORKFLOW_NAME, OTIR.START_TIME desc
SELECT
SUBJECT_AREA AS FOLDER_NAME,
WORKFLOW_NAME,
SESSION_NAME,
SUCCESSFUL_SOURCE_ROWS AS SOURCE_ROWS,
FAILED_ROWS,
SUCCESSFUL_ROWS AS TARGET_ROWS,
ACTUAL_START AS START_TIME,
SESSION_TIMESTAMP
FROM DWPC_UAT.REP_SESS_LOG
WHERE RUN_STATUS_CODE = 1
--AND TRUNC(ACTUAL_START) BETWEEN TRUNC(SYSDATE -6) AND TRUNC(SYSDATE)
--AND (SESSION_TIMESTAMP – ACTUAL_START) > (10/24*60))
AND WORKFLOW_NAME = 'wf_PROD_DAILY'
and SESSION_NAME = 's_m_WRITE_AccountAssetUsageAddress'
ORDER BY trunc(SESSION_TIMESTAMP) desc,SUBJECT_AREA,
WORKFLOW_NAME,
SESSION_NAME
--
SELECT DISTINCT
--Workflow
S.SUBJ_NAME as FOLDER_NAME
,WR.WORKFLOW_NAME as WF_NAME
,WR.START_TIME as WF_START
,WR.END_TIME as WF_END
,WR.RUN_ERR_CODE as WF_ERR_CODE
,WR.RUN_ERR_MSG as WF_ERR_MSG
,TO_CHAR(TRUNC(MOD((WR.END_TIME - WR.START_TIME) * 24 ,24)),'00') || ':' ||
TO_CHAR(TRUNC(MOD((WR.END_TIME - WR.START_TIME) * 24 * 60 , 60)),'00') || ':' ||
TO_CHAR(TRUNC(MOD(TO_CHAR(WR.END_TIME, 'SSSSS') - TO_CHAR(WR.START_TIME, 'SSSSS') , 60)),'00') AS WF_ELAPSED_TIME
--Session
,OTIR.INSTANCE_NAME as SES_NAME
,OSTL.MAPPING_NAME
,OSTL.SRC_FAILED_ROWS
,OSTL.SRC_SUCCESS_ROWS
,OSTL.TARG_FAILED_ROWS
,OSTL.TARG_SUCCESS_ROWS
,OTIR.START_TIME as SES_START
,OTIR.END_TIME as SES_END
,OSTL.FIRST_ERROR_CODE as SES_ERR_CODE
,OTIR.RUN_ERR_MSG as SES_ERR_MSG
,DECODE(OTIR.RUN_STATUS_CODE, 1,'SUCCEEDED',3,'FAILED') AS SES_STATUS_CODE
,TO_CHAR(TRUNC(MOD((OTIR.END_TIME - OTIR.START_TIME) * 24 ,24)),'00') || ':' ||
TO_CHAR(TRUNC(MOD((OTIR.END_TIME - OTIR.START_TIME) * 24 * 60 , 60)),'00') || ':' ||
TO_CHAR(TRUNC(MOD(TO_CHAR(OTIR.END_TIME, 'SSSSS') - TO_CHAR(OTIR.START_TIME, 'SSSSS') , 60)),'00') AS SES_ELAPSED_TIME
FROM DWPC_UAT.OPB_WFLOW_RUN WR
INNER JOIN
DWPC_UAT.OPB_SUBJECT S ON WR.SUBJECT_ID = S.SUBJ_ID
INNER JOIN
DWPC_UAT.OPB_SESS_TASK_LOG OSTL ON WR.WORKFLOW_ID = OSTL.WORKFLOW_ID
AND WR.WORKFLOW_RUN_ID = OSTl.WORKFLOW_RUN_ID
INNER JOIN
DWPC_UAT.OPB_TASK_INST_RUN OTIR ON WR.SUBJECT_ID = OTIR.SUBJECT_ID
AND OSTL.INSTANCE_ID = OTIR.INSTANCE_ID
AND OSTL.WORKFLOW_ID = OTIR.WORKFLOW_ID
AND OSTL.WORKFLOW_RUN_ID = OTIR.WORKFLOW_RUN_ID
AND OTIR.TASK_TYPE = 68
WHERE 1=1
AND TO_NUMBER(TO_CHAR(WR.START_TIME,'YYYYMMDD')) = 20100305
--AND (TO_NUMBER(TO_CHAR(WR.START_TIME,'HH')) BETWEEN 9 AND 10 OR TO_NUMBER(TO_CHAR(WR.END_TIME,'HH')) BETWEEN 9 AND 10)
AND S.SUBJ_NAME IN ('Enterprise DW Dimension','Enterprise DW ODS','Enterprise DW Staging')
--AND (OSTL.SRC_FAILED_ROWS > 0 OR OSTL.TARG_FAILED_ROWS > 0)
--AND WR.WORKFLOW_NAME LIKE '%WF_CUSTOMER%'
ORDER BY WR.START_TIME
,OTIR.START_TIME
;
/*
SELECT S.SUBJ_NAME
,T.TASK_ID
,TSK_TYPE.OBJECT_TYPE_ID as TASK_TYPE_ID
,TSK_TYPE.OBJECT_TYPE_NAME as TASK_TYPE_NM
,T.TASK_NAME
,TSK_ATTR.ATTR_ID as TASK_ATTR_TYPE_ID
,TSK_ATTR_TYPE.OBJECT_TYPE_NAME as TASK_ATTR_TYPE_NAME
,TSK_ATTR.ATTR_NAME as TASK_ATTR_NAME
,TA.ATTR_VALUE as TASK_ATTR_VALUE
FROM DWPC_UAT.OPB_SUBJECT S
INNER JOIN
DWPC_UAT.OPB_TASK T ON S.SUBJ_ID = T.SUBJECT_ID
INNER JOIN
DWPC_UAT.OPB_TASK_ATTR TA ON T.TASK_ID = TA.TASK_ID
INNER JOIN
DWPC_UAT.OPB_OBJECT_TYPE TSK_TYPE ON TA.TASK_TYPE = TSK_TYPE.OBJECT_TYPE_ID
INNER JOIN
DWPC_UAT.OPB_MMD_TASK_ATTR TSK_ATTR ON TA.TASK_TYPE = TSK_ATTR.OBJECT_TYPE_ID
AND TA.ATTR_ID = TSK_ATTR.ATTR_ID
INNER JOIN
DWPC_UAT.OPB_OBJECT_TYPE TSK_ATTR_TYPE ON TSK_ATTR.ATTR_TYPE = TSK_ATTR_TYPE.OBJECT_TYPE_ID
WHERE 1=1
--AND S.SUBJ_NAME IN ('Enterprise DW Dimension','Enterprise DW ODS','Enterprise DW Staging')
--AND T.TASK_ID = 341
AND TSK_ATTR.ATTR_ID = 17 --Write Backward Compatible Session Log File
AND TSK_TYPE.OBJECT_TYPE_ID = 68 --Session
ORDER BY S.SUBJ_NAME
,T.TASK_NAME
,TSK_TYPE.OBJECT_TYPE_NAME
,TSK_ATTR_TYPE.OBJECT_TYPE_NAME
;
*/
--How to find all the mappings and sessions which have PARALLEL hints in the SQL Override
SELECT S.SUBJ_NAME, M.MAPPING_NAME, W.WIDGET_NAME, A.WIDGET_ID, W.VERSION_NUMBER,
SUBSTR(A.ATTR_VALUE, 1, 60) ATTR_VALUE
FROM OPB_WIDGET_ATTR A, OPB_WIDGET W, OPB_SUBJECT S, OPB_WIDGET_INST I, OPB_MAPPING M
WHERE A.WIDGET_ID = W.WIDGET_ID
AND W.IS_VISIBLE = 1
AND A.VERSION_NUMBER = W.VERSION_NUMBER
AND A.WIDGET_TYPE IN(2, 3, 11) --Limit to Src/Tgt/Lkp Transformations
AND W.WIDGET_ID = I.WIDGET_ID
AND W.VERSION_NUMBER = I.VERSION_NUMBER
AND I.MAPPING_ID = M.MAPPING_ID
AND I.VERSION_NUMBER = M.VERSION_NUMBER
AND W.SUBJECT_ID = S.SUBJ_ID
AND UPPER(A.ATTR_VALUE) LIKE '%PARALLEL%'
UNION
SELECT S.SUBJ_NAME, W.TASK_NAME||'.'||TI.INSTANCE_NAME TASK_NAME, 'SQL Override' WIDGET_NAME,
A.SESS_WIDG_INST_ID, TI.VERSION_NUMBER, SUBSTR(A.ATTR_VALUE, 1, 60) ATTR_VALUE
FROM OPB_SWIDGET_ATTR A, OPB_TASK_INST TI, OPB_TASK W, OPB_SUBJECT S
WHERE A.SESSION_ID = TI.TASK_ID
AND A.VERSION_NUMBER = TI.VERSION_NUMBER
AND TI.WORKFLOW_ID = W.TASK_ID
AND TI.VERSION_NUMBER = W.VERSION_NUMBER
AND W.IS_VISIBLE = 1
AND W.SUBJECT_ID = S.SUBJ_ID
AND UPPER(A.ATTR_VALUE) LIKE '%PARALLEL%'
ORDER BY 1, 2, 3, 4;
--How to turn on Write Backward Compatible Session Log File for all session tasks in a folder
UPDATE OPB_TASK_ATTR A SET A.ATTR_VALUE=1
WHERE A.ATTR_ID=17 -- WBCSLF
-- 102 Write performance data to repository
-- 108 Collect performance data
-- 105 Enable high precision
AND EXISTS (
SELECT 0 FROM OPB_TASK T, OPB_SUBJECT S
WHERE T.TASK_ID = A.TASK_ID
AND T.VERSION_NUMBER = A.VERSION_NUMBER
AND T.IS_VISIBLE = 1
AND T.SUBJECT_ID = S.SUBJ_ID
AND S.SUBJ_NAME LIKE '%Sample%'
)
-- AND A.INSTANCE_ID = 0 -- Reusable Session Only
;
--What are today's long-running tasks
select -- the SRC_ROWS may look big if joiner is used
T.SUBJECT_AREA, T.INSTANCE_NAME, TRUNC(AVG(END_TIME-START_TIME)*24, 2) RUN_HOUR,
MIN(T.START_TIME) START_TIME,
SUM(L.SRC_SUCCESS_ROWS) SRC_ROWS, SUM(L.TARG_SUCCESS_ROWS) TGT_ROWS
from REP_TASK_INST_RUN T, OPB_SESS_TASK_LOG L
where T.run_err_code=0 and (T.END_TIME-T.START_TIME)>= 1/24
and T.START_TIME >= TRUNC(SYSDATE)-2/24
and T.INSTANCE_ID = L.INSTANCE_ID
GROUP BY T.SUBJECT_AREA, T.INSTANCE_NAME
Order By RUN_HOUR desc;
--Inside the Expression Transformation
select S.SUBJ_NAME, W.WIDGET_NAME, F.WIDGET_ID, F.FIELD_NAME, F.FIELD_ID, E.EXPR_ID,
E.VERSION_NUMBER, E.EXPRESSION
from OPB_WIDGET W, OPB_SUBJECT S, OPB_WIDGET_FIELD F,
OPB_WIDGET_EXPR R, OPB_EXPRESSION E
where W.SUBJECT_ID=S.SUBJ_ID and W.WIDGET_ID=F.WIDGET_ID
and W.WIDGET_ID=R.WIDGET_ID AND F.FIELD_ID=R.OUTPUT_FIELD_ID
and W.WIDGET_ID=E.WIDGET_ID AND R.EXPR_ID=E.EXPR_ID
and W.VERSION_NUMBER = F.VERSION_NUMBER
and F.VERSION_NUMBER = R.VERSION_NUMBER
and R.VERSION_NUMBER = E.VERSION_NUMBER
and W.IS_VISIBLE = 1
and w.WIDGET_NAME like 'EXP_SAMPLE%'
order by S.SUBJ_ID, W.WIDGET_ID, F.FIELD_ID;
--Which session populates the specific target table
select SUBJECT_AREA, SESSION_NAME,TARGET_NAME,
MAX(SUCC_ROWS) AS ROWS#, TYPE_ID, COUNT(1) SAMPLE#
from (
select SUBJECT_AREA, SESSION_INSTANCE_NAME SESSION_NAME,
TYPE_ID, SUCCESSFUL_AFFECTED_ROWS SUCC_ROWS,
TABLE_NAME TARGET_NAME
from PM_REPO.REP_SESS_TBL_LOG
WHERE TYPE_ID in (2) -- Target Only
and END_TIME >= TRUNC(SYSDATE-40)
and SUCCESSFUL_AFFECTED_ROWS > 0
and TABLE_NAME like :TGT_NAME||'%' ESCAPE '\'
)
Group By SUBJECT_AREA, SESSION_NAME, TARGET_NAME, TYPE_ID
Order By SUBJECT_AREA, TARGET_NAME, SESSION_NAME
;
--How to avoid re-generating & re-transporting ABAP program after slightly changing the mapping
CR 29233 and 63134 have been opened to have fix this problem.
-- Find the current LAST_SAVED and UTC_LAST_SAVED
select m.Mapping_ID, m.VERSION_NUMBER, m.Last_Saved, m.UTC_Last_Saved, v.User_ID,
v.LAST_SAVED, v.UTC_LAST_SAVED, v.COMMENTS, p.PROGRAM_NAME,
p.INSTALL_TIME, p.VERSION_NUMBER ABAP_VERSION_NUM
from opb_mapping m, OPB_VERSION_PROPS v, opb_program_info p
where m.MAPPING_ID = v.OBJECT_ID and v.OBJECT_TYPE = 21 and
m.SUBJECT_ID = v.SUBJECT_ID and m.VERSION_NUMBER = v.VERSION_NUMBER and
m.MAPPING_ID = p.MAPPING_ID(+) and m.SUBJECT_ID = p.SUBJECT_ID(+) and
m.VERSION_NUMBER = p.VERSION_NUMBER(+) and
m.MAPPING_NAME = :MAP_NAME
order by m.version_number;
--Then modify the LAST_SAVED, UTC_LAST_SAVED in OPB_VERSION_PROPS and OPB_MAPPING first; then modify the VERSION_NUMBER in OPB_PROGRAM_INFO if necessary.
--The timsstamp below is just a sample
update OPB_VERSION_PROPS v
set last_saved='12/05/2008 09:10:11' ,
UTC_LAST_SAVED=1228363499
where v.OBJECT_ID = :MAP_ID and version_number = :VER_NUM and object_type = 21;
update opb_mapping m
set last_saved='12/05/2008 09:10:11',
UTC_LAST_SAVED=1228363499
where MAPPING_ID = :MAP_ID and version_number = :VER_NUM;
update opb_program_info set VERSION_NUMBER = :VER_NUM
where mapping_id = :MAP_ID and subject_id = :SUB_ID;
--Link from EXP_FROM.PORT_NAME to ???
select S.SUBJ_NAME, WF.INSTANCE_NAME ||'.'|| F.FIELD_NAME FROM_NAME, F.WIDGET_ID, F.FIELD_ORDER, F.FIELD_ID,
WT.INSTANCE_NAME ||'.'|| T.FIELD_NAME TO_NAME, T.WIDGET_ID, T.FIELD_ORDER, T.FIELD_ID
from OPB_WIDGET Z, OPB_WIDGET_INST WF, OPB_WIDGET_INST WT, OPB_WIDGET_FIELD F,
OPB_WIDGET_FIELD T, OPB_WIDGET_DEP D, OPB_SUBJECT S
where Z.SUBJECT_ID = S.SUBJ_ID and Z.IS_VISIBLE = 1
and Z.WIDGET_ID = F.WIDGET_ID
and Z.WIDGET_ID = WF.WIDGET_ID
and Z.RU_VERSION_NUMBER = WF.VERSION_NUMBER
and WF.REF_VERSION_NUMBER = F.VERSION_NUMBER
and WF.VERSION_NUMBER = D.VERSION_NUMBER
and WF.MAPPING_ID = D.MAPPING_ID
and WF.INSTANCE_ID = D.FROM_INSTANCE_ID and F.FIELD_ID = D.FROM_FIELD_ID
and D.TO_INSTANCE_ID = WT.INSTANCE_ID and D.TO_FIELD_ID = T.FIELD_ID
and D.MAPPING_ID = WT.MAPPING_ID
and D.VERSION_NUMBER = WT.VERSION_NUMBER
and WT.WIDGET_ID = T.WIDGET_ID
and WT.REF_VERSION_NUMBER = T.VERSION_NUMBER
and Z.WIDGET_NAME like 'EXP_FROM%'
and F.FIELD_NAME like 'PORT_NAME%'
order by F.FIELD_ID;
--How the connection values is set at session level
select x.WORKFLOW_ID , t.TASK_ID, t.TASK_NAME, b.INSTANCE_ID, b.INSTANCE_NAME,
b.SESS_WIDG_INST_ID, x.CONNECTION_NUM, x.CONNECTION_TYPE, x.CONNECTION_ID,
x.CONNECTION_VALUE, c.OBJECT_NAME
from opb_session s, opb_task t, OPB_SWIDGET_INST b, OPB_SESS_CNX_VALS x, opb_cnx c
where c.OBJECT_ID(+) = x.CONNECTION_ID
and s.MAPPING_ID = b.MAPPING_ID
and s.SESSION_ID = b.SESSION_ID
and s.SESSION_ID = t.TASK_ID
and s.SESSION_ID = x.SESSION_ID
and b.SESS_WIDG_INST_ID = x.SESS_WIDG_INST_ID
and t.SUBJECT_ID = :SUBJ_ID
and b.INSTANCE_NAME like :WIDGET_NAME -- Source/Target
and t.TASK_NAME like :SESS_NAME
order by t.task_name, b.SESS_WIDG_INST_ID;
--Find Transformation Attribute Override at Session Level
select f.SUBJ_NAME Folder, t.task_name SESSION_NAME, i.INSTANCE_NAME,
o.OBJECT_TYPE_NAME, a.*
from opb_swidget_attr a, OPB_SWIDGET_INST i,
OPB_WIDGET w, OPB_OBJECT_TYPE o,
opb_task t, opb_subject f
where a.attr_value in (:VALUE1, :VALUE2, :VALUE3)
and i.SESSION_ID = a.SESSION_ID
and i.SESS_WIDG_INST_ID = a.SESS_WIDG_INST_ID
and i.WIDGET_ID = w.WIDGET_ID
and i.WIDGET_TYPE = w.WIDGET_TYPE
and i.WIDGET_TYPE = o.OBJECT_TYPE_ID
and i.SESSION_ID= t.task_id
and t.SUBJECT_ID= f.subj_id;
--List all the Transformations in a given Mapping
--Including the transformations within the mapplet
WITH M AS (
select M.SUBJECT_ID, M.MAPPING_ID
from OPB_MAPPING M, OPB_SUBJECT S
where S.SUBJ_ID = M.SUBJECT_ID
and S.SUBJ_NAME LIKE :Folder_Name
and M.MAPPING_NAME like :Mapping_Name
)
select O.OBJECT_TYPE_NAME, W.INSTANCE_NAME, W.COMMENTS
from OPB_WIDGET_INST W, OPB_OBJECT_TYPE O, M
where O.OBJECT_TYPE_ID = W.WIDGET_TYPE
and O.OBJECT_TYPE_NAME NOT IN ('Mapplet')
and W.MAPPING_ID = M.MAPPING_ID
union
select O.OBJECT_TYPE_NAME, W.INSTANCE_NAME, W.COMMENTS
from OPB_WIDGET_INST W, OPB_OBJECT_TYPE O, M
where O.OBJECT_TYPE_ID = W.WIDGET_TYPE
and O.OBJECT_TYPE_NAME NOT IN ('Mapplet', 'Output Transformation', 'Input Transformation')
and W.MAPPING_ID in (
select X.MAPPING_ID
from OPB_WIDGET_INST W, OPB_OBJECT_TYPE O, M, OPB_MAPPING X
where W.MAPPING_ID = M.MAPPING_ID
and O.OBJECT_TYPE_ID = W.WIDGET_TYPE
and O.OBJECT_TYPE_NAME = 'Mapplet'
and X.REF_WIDGET_ID = W.WIDGET_ID
)
order by 1,2 ;
-- Query to identify session that are not checked for property "Fail parent if session failed". Query provied by Rich
SELECT --Workflow
DISTiNCT
S.SUBJ_NAME as FOLDER_NAME
,WR.WORKFLOW_NAME as WF_NAME
,TI.INSTANCE_NAME as SES_NAME
,TI.BIT_OPTIONS
FROM DWPC.OPB_WFLOW_RUN WR
INNER JOIN
DWPC.OPB_SUBJECT S ON WR.SUBJECT_ID = S.SUBJ_ID
INNER JOIN
DWPC.OPB_TASK_INST TI ON WR.WORKFLOW_ID = TI.WORKFLOW_ID
INNER JOIN
(
SELECT MAX(VERSION_NUMBER) as VERSION_NUMBER
,TASK_ID
,WORKFLOW_ID
,INSTANCE_ID
FROM DWPC.OPB_TASK_INST
GROUP BY TASK_ID
,WORKFLOW_ID
,INSTANCE_ID
) Z ON TI.VERSION_NUMBER = Z.VERSION_NUMBER
AND TI.TASK_ID = Z.TASK_ID
AND TI.WORKFLOW_ID = Z.WORKFLOW_ID
AND Ti.INSTANCE_ID = Z.INSTANCE_ID
WHERE S.SUBJ_NAME IN ('Enterprise DW Staging','Enterprise DW ODS','Enterprise DW Dimension')
AND TI.BIT_OPTIONS = 1
AND TO_NUMBER(TO_CHAR(WR.START_TIME,'YYYYMMDD')) = TO_CHAR(SYSDATE - 1,'YYYYMMDD')
AND TI.INSTANCE_NAME != 'Start'
AND Ti.IS_ENABLED = 1
ORDER BY S.SUBJ_NAME
,WR.WORKFLOW_NAME
,TI.INSTANCE_NAME
------ Following queries are from Website: http://www.dwbiconcepts.com/etl/14-etl-informatica/94-useful-informatica-metadata-repository-queries.html
--Search for Truncate Table Option
--Suppose you want to find out on which of your sessions, truncate target table option is set on. Instead of manually opening each task and checking if the truncate table option is on, you may use the below query:
select
task_name,
'Truncate Target Table' ATTR,
decode(attr_value,1,'Yes','No') Value
from OPB_EXTN_ATTR OEA,
REP_ALL_TASKS RAT
where
OEA.SESSION_ID=rat.TASK_ID
and attr_id=9
--Find Mappings where SQL Override is used
--Below query will give you count of mapping instance where SQL Override has been used. The count is presented folder by folder.
WITH detail AS
(SELECT
c.subject_area,
c.mapping_name,
d.instance_name source_qualifier_name,
CASE WHEN a.attr_value IS NOT NULL
THEN 1 ELSE 0
END as OVR_OK
FROM
rep_all_mappings c,
opb_widget_inst d,
opb_widget_attr a
WHERE
c.mapping_id = d.mapping_id
AND c.mapping_version_number = d.version_number
AND d.widget_type = 3
AND d.widget_id = a.widget_id
AND a.widget_type = d.widget_type
AND a.attr_id = 1
)
SELECT
subject_area,
'SQ_OVERIDE' STATUS,
COUNT (DISTINCT mapping_name) NO_OF_Mapping,
COUNT (DISTINCT (mapping_name || source_qualifier_name)) NO_OF_SQ_IN_MAPPING,
COUNT (DISTINCT (source_qualifier_name)) NO_OF_DISTINCT_SQ
FROM detail
WHERE OVR_OK =1
GROUP BY subject_area
UNION
SELECT
subject_area, 'SQ_NON_OVERIDE',
COUNT (DISTINCT mapping_name) nb_mapping,
COUNT (DISTINCT (mapping_name || source_qualifier_name)) nb_map_inst,
COUNT (DISTINCT (source_qualifier_name)) nb_inst
FROM detail
WHERE OVR_OK =0
GROUP BY subject_area
--Find Tracing Levels for Informatica Sessions
--Sessions can have different tracing levels (Terse to Verbose). Often in Development environment we test our mappings with verbose tracing levels and then forget to reduce the level while promoting to Production environments. This creates issues like abnormal "SessLogs" growth, slower mapping performance etc.
--This query will give tracing information along with session names so that you can quickly identify unintended tracing levels without opening each sessions manually.
select
task_name,
decode (attr_value,
0,'None',
1,'Terse',
2,'Normal',
3,'Verbose Initialisation',
4,'Verbose Data','') Tracing_Level
from
REP_SESS_CONFIG_PARM CFG,
opb_task TSK
WHERE
CFG.SESSION_ID=TSK.TASK_ID
and tsk.TASK_TYPE=68
and attr_id=204
and attr_type=6
--Find name of all stored procedure used in stored procedure transformation
--This query is helpful when you require to know name of all stored procedure being used in informatica stored procedure transformation
select
attr_value
from
OPB_WIDGET_ATTR
where
widget_type=6
and attr_id=1
--Find who modified (saved) a mapping last time
--This information is available under mapping properties. But the below query lists down this information for all the mappings in one place.
SELECT
substr(rpl.event_time,7,4) || substr(rpl.event_time,6,1) ||
substr(rpl.event_time,1,5) || ' ' substr(rpl.event_time,12,11) "EventTimestamp" ,
usr.user_name "Username",
DECODE(rpl.object_type_id,21,s21.subj_name,('('rpl.object_type_id')')) "Folder",
obt.object_type_name "Type",
DECODE(rpl.object_type_id,21,map.mapping_name,('('rpl.object_type_id')')) "Object"
FROM
opb_reposit_log rpl,
opb_object_type obt,
opb_subject fld,
opb_mapping map,
opb_users usr,
opb_subject s21
WHERE
obt.object_type_name = 'Mapping'
AND rpl.object_type_id = obt.object_type_id
AND rpl.object_id = map.mapping_id(+)
AND rpl.object_id = fld.subj_id(+)
AND rpl.event_uid = usr.user_id
AND map.subject_id = s21.subj_id(+)
ORDER BY
1 DESC
--Find Lookup Information from Repository
--This query will give information about lookup transformations like lookup name,Tablename ,Mapping name etc.
Select distinct
wid.WIDGET_ID,
all_map.mapping_name,
wid.INSTANCE_NAME Lkp_name,
Decode(widat.attr_id,2,widat.attr_value) Table_name,
decode (widat.attr_id,6,widat.attr_value) src_tgt
FROM
rep_all_mappings ALL_MAP,
rep_widget_inst wid,
OPB_WIDGET_ATTR widat
where
all_map.mapping_id=wid.mapping_id
and wid.WIDGET_ID=widat.WIDGET_ID
and wid.WIDGET_TYPE=11
and widat.WIDGET_TYPE=11
and widat.ATTR_ID in (2,6)
--Find all Invalid workflows from Metadata repository
--This query will list of all invalid workflows under the given subject area (folder)
select
opb_subject.subj_name,
opb_task.task_name
from
opb_task,
opb_subject
where
task_type = 71
and is_valid = 0
and opb_subject.subj_id = opb_task.subject_id
and UPPER(opb_subject.SUBJ_NAME) like UPPER('YOUR_FOLDER_NAME')
--Generate a list of failed sessions from the repository
--Here is a query that will display list of failed / aborted / terminated sessions from each of the folders in the repository
SELECT
RSL.SUBJECT_AREA AS FOLDER,
RW.WORKFLOW_NAME AS WORKFLOW,
RSL.SESSION_NAME AS SESSION_NAME,
DECODE(RSL.RUN_STATUS_CODE,
3,'FAILED',
4,'STOPPED',
5,'ABORTED',
15,'TERMINATED','UNKNOWN') AS STATUS,
RSL.FIRST_ERROR_CODE AS FIRST_ERROR,
RSL.FIRST_ERROR_MSG AS ERROR_MSG,
RSL.ACTUAL_START AS START_TIME,
RSL.SESSION_TIMESTAMP AS END_TIME
FROM
REP_SESS_LOG RSL,
REP_WORKFLOWS RW
WHERE
RSL.RUN_STATUS_CODE IN (3,4,5,14,15)
AND RW.WORKFLOW_ID = RSL.WORKFLOW_ID
AND RW.SUBJECT_ID = RSL.SUBJECT_ID AND
RSL.SUBJECT_AREA ='CDW_FDR2_SERVICEQUALITY'
ORDER BY
RSL.SESSION_TIMESTAMP DESC
--- Queries from website: http://informaticadoubts.blogspot.com/2013/08/informatica-repository-queries-to-get.html
--Query1:
--Query to get folder name, workflow name, session names and all the connections used in sessions.
SELECT WF.SUBJECT_AREA AS FOLDER_NAME, WF.WORKFLOW_NAME AS WORKFLOW_NAME,
T.INSTANCE_NAME AS SESSION_NAME, T.TASK_TYPE_NAME,
C.CNX_NAME AS CONNECTION_NAME, V.CONNECTION_SUBTYPE, V.HOST_NAME,
V.USER_NAME, C.INSTANCE_NAME, C.READER_WRITER_TYPE,
C.SESS_EXTN_OBJECT_TYPE
FROM REP_TASK_INST T,
REP_SESS_WIDGET_CNXS C,
REP_WORKFLOWS WF,
V_IME_CONNECTION V
WHERE T.TASK_ID = C.SESSION_ID
AND WF.WORKFLOW_ID = T.WORKFLOW_ID
AND C.CNX_NAME = V.CONNECTION_NAME
AND WF.SUBJECT_AREA = <FOLDER NAME>
-- Note: If SESS_EXTN_OBJECT_TYPE=78 then its reader, If 79 then its writer
--Query2:
--Query to get commands used in command tasks including the commands used as pre/post session commands.
SELECT DISTINCT T.SUBJECT_ID, F.SUBJECT_AREA AS FOLDER_NAME,
W.TASK_NAME AS WORKFLOW_NAME, T.TASK_NAME AS CMD_TASK_NAME,
CMD.PM_VALUE AS CMD_NAME, CMD.EXEC_ORDER,
CMD.VAL_NAME AS CMD_NUMBER, T.TASK_ID, T.TASK_TYPE,
T.RU_PARENT_ID
FROM OPB_TASK_VAL_LIST CMD, OPB_TASK T, OPB_TASK W, REP_SUBJECT F
WHERE T.TASK_ID = CMD.TASK_ID
AND T.SUBJECT_ID = F.SUBJECT_ID
AND T.TASK_TYPE = 58
AND T.RU_PARENT_ID = W.TASK_ID
AND F.SUBJECT_AREA = <FOLDER NAME>
ORDER BY F.SUBJECT_AREA, W.TASK_NAME, T.TASK_NAME, CMD.EXEC_ORDER
--Query3:
--Query to get the event wait file names used in event wait tasks.
SELECT DISTINCT F.SUBJECT_AREA AS FOLDER_NAME, W.TASK_NAME AS WORKFLOW_NAME,
T.TASK_ID, T.TASK_TYPE, T.TASK_NAME AS EVENTWAIT_NAME,
T.RU_PARENT_ID, W.TASK_ID
FROM OPB_TASK T, OPB_TASK W, REP_SUBJECT F
WHERE T.RU_PARENT_ID = W.TASK_ID
AND T.TASK_TYPE = 60
AND T.SUBJECT_ID = F.SUBJECT_ID
AND F.SUBJECT_AREA = <FOLDER NAME>
-- Losts of Metadata queries from website: http://aambarish.blogspot.com/2012/01/normal-0-false-false-false-en-us-x-none.html
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment