Informatica help

Informatica help and metadata report query

Failed Session Statistics(Started Yesterday)

Failed Session Statistics(Started Yesterday)

This query will displays the total number of sessions that started yesterday and resulted in failed statuses by repository by folder. A session can fail due to read login failure or custom select statement error.


Click on the text to copy the query report to your clipboard.


select "ACTUAL_START",
"RUN_STATUS_CODE",
"REPOSITORY_NAME",
"INSTANCE_ID",
"SUBJECT_AREA",
"WORKFLOW_NAME"
from (SELECT  SESSION_REPOSIT_INFO.REPOSITORY_NAME,  TO_CHAR(SESSION_LOG.ACTUAL_START,'DD-MON-YYYY')    AS "ACTUAL_START",  ALL_SESSIONS.SUBJECT_AREA,  SESSION_LOG.WORKFLOW_NAME,  CASE    WHEN SESSION_LOG.RUN_STATUS_CODE <= 9    THEN    CASE     WHEN SESSION_LOG.RUN_STATUS_CODE = 1     THEN 'Succeeded'     ELSE     CASE      WHEN SESSION_LOG.RUN_STATUS_CODE = 2      THEN 'Disabled'      ELSE      CASE       WHEN SESSION_LOG.RUN_STATUS_CODE = 3       THEN 'Failed'       ELSE       CASE        WHEN SESSION_LOG.RUN_STATUS_CODE = 4        THEN 'Stopped'        ELSE        CASE         WHEN SESSION_LOG.RUN_STATUS_CODE = 5         THEN 'Aborted'         ELSE         CASE          WHEN SESSION_LOG.RUN_STATUS_CODE = 6          THEN 'Running'          ELSE          CASE           WHEN SESSION_LOG.RUN_STATUS_CODE = 7           THEN 'Suspending'           ELSE           CASE            WHEN SESSION_LOG.RUN_STATUS_CODE = 8            THEN 'Suspended'            ELSE            CASE             WHEN SESSION_LOG.RUN_STATUS_CODE = 9             THEN 'Stopping'            END           END          END         END        END       END      END     END    END    ELSE    CASE     WHEN SESSION_LOG.RUN_STATUS_CODE = 10     THEN 'Aborting'     ELSE     CASE      WHEN SESSION_LOG.RUN_STATUS_CODE = 11      THEN 'Waiting'      ELSE      CASE       WHEN SESSION_LOG.RUN_STATUS_CODE = 12       THEN 'Scheduled'       ELSE       CASE        WHEN SESSION_LOG.RUN_STATUS_CODE = 13        THEN 'UnScheduled'        ELSE        CASE         WHEN SESSION_LOG.RUN_STATUS_CODE = 14         THEN 'Unknown'         ELSE         CASE          WHEN SESSION_LOG.RUN_STATUS_CODE = 15          THEN 'Terminated'         END        END       END      END     END    END   END                   AS RUN_STATUS_CODE,  (COUNT(   CASE    WHEN SESSION_LOG.RUN_STATUS_CODE = 3    THEN SESSION_LOG.INSTANCE_ID   END)) AS INSTANCE_ID  FROM  REP_LOAD_SESSIONS ALL_SESSIONS,  REP_SESS_LOG SESSION_LOG,  REP_REPOSIT_INFO SESSION_REPOSIT_INFO  WHERE  (ALL_SESSIONS.SESSION_ID = SESSION_LOG.SESSION_ID AND  ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_LOG.TASK_VERSION_NUMBER AND  ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID) AND  (TO_CHAR(SESSION_LOG.ACTUAL_START,'DD-MON-YYYY') = TO_CHAR(SYSDATE-1, 'DD-MON-YYYY') AND  CASE    WHEN SESSION_LOG.RUN_STATUS_CODE <= 9    THEN    CASE     WHEN SESSION_LOG.RUN_STATUS_CODE = 1     THEN 'Succeeded'     ELSE     CASE      WHEN SESSION_LOG.RUN_STATUS_CODE = 2      THEN 'Disabled'      ELSE      CASE       WHEN SESSION_LOG.RUN_STATUS_CODE = 3       THEN 'Failed'       ELSE       CASE        WHEN SESSION_LOG.RUN_STATUS_CODE = 4        THEN 'Stopped'        ELSE        CASE         WHEN SESSION_LOG.RUN_STATUS_CODE = 5         THEN 'Aborted'         ELSE         CASE          WHEN SESSION_LOG.RUN_STATUS_CODE = 6          THEN 'Running'          ELSE          CASE           WHEN SESSION_LOG.RUN_STATUS_CODE = 7           THEN 'Suspending'           ELSE           CASE            WHEN SESSION_LOG.RUN_STATUS_CODE = 8            THEN 'Suspended'            ELSE            CASE             WHEN SESSION_LOG.RUN_STATUS_CODE = 9             THEN 'Stopping'            END           END          END         END        END       END      END     END    END    ELSE    CASE     WHEN SESSION_LOG.RUN_STATUS_CODE = 10     THEN 'Aborting'     ELSE     CASE      WHEN SESSION_LOG.RUN_STATUS_CODE = 11      THEN 'Waiting'      ELSE      CASE       WHEN SESSION_LOG.RUN_STATUS_CODE = 12       THEN 'Scheduled'       ELSE       CASE        WHEN SESSION_LOG.RUN_STATUS_CODE = 13        THEN 'UnScheduled'        ELSE        CASE         WHEN SESSION_LOG.RUN_STATUS_CODE = 14         THEN 'Unknown'         ELSE         CASE          WHEN SESSION_LOG.RUN_STATUS_CODE = 15          THEN 'Terminated'         END        END       END      END     END    END   END = 'Failed' )  GROUP BY  SESSION_REPOSIT_INFO.REPOSITORY_NAME,  TO_CHAR(SESSION_LOG.ACTUAL_START,'DD-MON-YYYY'),  ALL_SESSIONS.SUBJECT_AREA,  SESSION_LOG.WORKFLOW_NAME,  CASE    WHEN SESSION_LOG.RUN_STATUS_CODE <= 9    THEN    CASE     WHEN SESSION_LOG.RUN_STATUS_CODE = 1     THEN 'Succeeded'     ELSE     CASE      WHEN SESSION_LOG.RUN_STATUS_CODE = 2      THEN 'Disabled'      ELSE      CASE       WHEN SESSION_LOG.RUN_STATUS_CODE = 3       THEN 'Failed'       ELSE       CASE        WHEN SESSION_LOG.RUN_STATUS_CODE = 4        THEN 'Stopped'        ELSE        CASE         WHEN SESSION_LOG.RUN_STATUS_CODE = 5         THEN 'Aborted'         ELSE         CASE          WHEN SESSION_LOG.RUN_STATUS_CODE = 6          THEN 'Running'          ELSE          CASE           WHEN SESSION_LOG.RUN_STATUS_CODE = 7           THEN 'Suspending'           ELSE           CASE            WHEN SESSION_LOG.RUN_STATUS_CODE = 8            THEN 'Suspended'            ELSE            CASE             WHEN SESSION_LOG.RUN_STATUS_CODE = 9             THEN 'Stopping'            END           END          END         END        END       END      END     END    END    ELSE    CASE     WHEN SESSION_LOG.RUN_STATUS_CODE = 10     THEN 'Aborting'     ELSE     CASE      WHEN SESSION_LOG.RUN_STATUS_CODE = 11      THEN 'Waiting'      ELSE      CASE       WHEN SESSION_LOG.RUN_STATUS_CODE = 12       THEN 'Scheduled'       ELSE       CASE        WHEN SESSION_LOG.RUN_STATUS_CODE = 13        THEN 'UnScheduled'        ELSE        CASE         WHEN SESSION_LOG.RUN_STATUS_CODE = 14         THEN 'Unknown'         ELSE         CASE          WHEN SESSION_LOG.RUN_STATUS_CODE = 15          THEN 'Terminated'         END        END       END      END     END    END   END) "query"
order by "REPOSITORY_NAME", "ACTUAL_START"
>/code>

Sponsored Content


Canon EOS 5D Mark IV Full Frame Digital SLR Camera Body


Canon EF 24-70mm f/2.8L II USM Standard Zoom Lens

GoPro HERO5 Black

Canon EF 100-400mm f/4.5-5.6L IS II USM Lens

Firefield FF16001 NVRS 3x 42mm Gen 1 Night Vision Riflescope, Black

Visitors


Quantcast