3 Replies Latest reply on Jul 11, 2019 4:04 AM by 3988035

    ODI: SNP Tables related queries: Fetching ODI Metadata Information

    3988035

      Hi All,

       

      Do you have sql query which will provide below information from SNP tables:

       

      1) List out all mappings/interface and their execution time, start time, end time, total number of records loaded in target, total number of records fetched from sources.

       

      2) List out package names and total execution time for package , start time and end time.

       

      3) List out mapping name, package name, target table name associated in that mapping and source table names in that mapping.

       

      Thanks,

      Rajneesh

        • 1. Re: ODI: SNP Tables related queries: Fetching ODI Metadata Information
          Adrian_Popescu-Oracle

          Hi Rajneesh,

           

          This note is the best starting point for such scripts:

          1903225.1 Oracle Data Integrator 11g and 12c Repository Description

           

          For example, SNP_MAPPING contains the mappings.

           

          Adrian

          • 2. Re: ODI: SNP Tables related queries: Fetching ODI Metadata Information
            Rodrigo Radtke Souza

            I use the following SQLs (ODI 12) to get run time and mapping info. Maybe you may use them as a basis for your own analyzes:

             

            -- RUNTIME

            SELECT SS.SESS_NO

                 , SS.SCEN_NAME

                 , SS.SCEN_VERSION

                 , SS.SESS_NAME

                 , SS.PARENT_SESS_NO

                 , SS.SESS_BEG

                 , SS.SESS_END

                 , SS.SESS_STATUS

                 , DECODE(SS.SESS_STATUS, 'D', 'Done', 'E', 'Error', 'M', 'Warning', 'Q', 'Queued', 'R', 'Running', 'W', 'Waiting', SS.SESS_STATUS) AS SESS_STATUS_DESC

                 , SSL.NNO

                 , SSTL.NB_RUN

                 , SST.TASK_TYPE

                 , DECODE(SST.TASK_TYPE, 'C', 'Loading', 'J', 'Mapping', 'S', 'Procedure', 'V', 'Variable', SST.TASK_TYPE) AS TASK_TYPE_DESC

                 , SST.EXE_CHANNEL

                 , DECODE(SST.EXE_CHANNEL, 'B', 'Oracle Data Integrator Scripting', 'C', 'Oracle Data Integrator Connector', 'J', 'JDBC', 'O', 'Operating System', 'Q', 'Queue', 'S', 'Oracle Data Integrator Command', 'T', 'Topic', 'U', 'XML Topic', SST.EXE_CHANNEL) AS EXE_CHANNEL_DESC

                 , SSTL.SCEN_TASK_NO

                 , SST.PAR_SCEN_TASK_NO

                 , SST.TASK_NAME1

                 , SST.TASK_NAME2

                 , SST.TASK_NAME3

                 , SSTL.TASK_DUR

                 , SSTL.NB_ROW

                 , SSTL.NB_INS

                 , SSTL.NB_UPD

                 , SSTL.NB_DEL

                 , SSTL.NB_ERR

                 , SSS.LSCHEMA_NAME || '.' || SSS.RES_NAME AS TARGET_TABLE

                 , CASE WHEN SST.COL_TECH_INT_NAME IS NOT NULL AND SST.COL_LSCHEMA_NAME IS NOT NULL THEN SST.COL_TECH_INT_NAME || '.' || SST.COL_LSCHEMA_NAME

                        ELSE NULL

                   END AS TARGET_SCHEMA

                 , SSTL.DEF_TXT AS TARGET_COMMAND

                 , CASE WHEN SST.DEF_TECH_INT_NAME IS NOT NULL AND SST.DEF_LSCHEMA_NAME IS NOT NULL THEN SST.DEF_TECH_INT_NAME || '.' || SST.DEF_LSCHEMA_NAME

                        ELSE NULL

                   END AS SOURCE_SCHEMA

                 , SSTL.COL_TXT AS SOURCE_COMMAND

              FROM LONGSCX_ODI_REPO.SNP_SESSION SS

                INNER JOIN LONGSCX_ODI_REPO.SNP_STEP_LOG SSL

                    ON SS.SESS_NO = SSL.SESS_NO

                INNER JOIN LONGSCX_ODI_REPO.SNP_SESS_TASK_LOG SSTL

                    ON SS.SESS_NO = SSTL.SESS_NO

                INNER JOIN LONGSCX_ODI_REPO.SNP_SB_TASK SST

                    ON SSTL.SB_NO = SST.SB_NO

                    AND SSTL.SCEN_TASK_NO = SST.SCEN_TASK_NO

                    AND SSL.NNO = SSTL.NNO

                    AND SSTL.NNO = SST.NNO

                    AND SSL.NB_RUN = SSTL.NB_RUN

                LEFT JOIN LONGSCX_ODI_REPO.SNP_SB_STEP SSS

                    ON SST.SB_NO = SSS.SB_NO

                    AND SST.NNO = SSS.NNO

            WHERE 1 = 1

               AND SS.SESS_NO = 123542

               AND SS.SESS_STATUS  IN ('D', 'R')

               AND SST.EXE_CHANNEL IN ('B', 'C', 'J', 'S')

               AND (UPPER(SSTL.DEF_TXT) LIKE '%SELECT%' OR UPPER(SSTL.DEF_TXT) LIKE '%MERGE%' OR UPPER(SSTL.DEF_TXT) LIKE '%INSERT%')

               AND SST.TASK_TYPE NOT IN ('V') --VARIABLE

               AND SSTL.NB_ROW > 0

               AND LENGTH(SS.SCEN_VERSION) > 3

            ORDER BY SS.SESS_NO DESC, SSL.NNO, SSL.NB_RUN, SSTL.SCEN_TASK_NO;

             

            -- MAPPING

            WITH

            MOD_FOLDERS__ AS (

            SELECT F.I_MOD_FOLDER, F.MOD_FOLDER_NAME, LEVEL AS LEVEL_

                 , SYS_CONNECT_BY_PATH(MOD_FOLDER_NAME, ' -> ') AS MOD_FOLDER_PATH

              FROM DEV_ODI_REPO.SNP_MOD_FOLDER F

              CONNECT BY PRIOR F.I_MOD_FOLDER = F.PAR_I_MOD_FOLDER

            )

            , MOD_FOLDERS_MAX__ AS (

            SELECT I_MOD_FOLDER, MAX(LEVEL_) AS LEVEL_

              FROM MOD_FOLDERS__ F

            GROUP BY I_MOD_FOLDER

            , MOD_FOLDERS_ AS (

            SELECT SUBSTR(MF.MOD_FOLDER_PATH, 5) AS MOD_FOLDER_PATH, MF.I_MOD_FOLDER, MF.MOD_FOLDER_NAME, MF.LEVEL_

              FROM MOD_FOLDERS__ MF

                INNER JOIN MOD_FOLDERS_MAX__ M

                    ON MF.I_MOD_FOLDER = M.I_MOD_FOLDER

                    AND MF.LEVEL_ = M.LEVEL_

            )

            , FOLDERS__ AS (

            SELECT F.I_FOLDER, F.FOLDER_NAME, F.I_PROJECT, LEVEL AS LEVEL_

                 , SYS_CONNECT_BY_PATH(FOLDER_NAME, ' -> ') AS FOLDER_PATH

              FROM DEV_ODI_REPO.SNP_FOLDER F

              WHERE F.I_PROJECT = 1

              CONNECT BY PRIOR F.I_FOLDER = F.PAR_I_FOLDER

            )

            , FOLDERS_MAX__ AS (

            SELECT I_FOLDER, MAX(LEVEL_) AS LEVEL_

              FROM FOLDERS__ F

            GROUP BY I_FOLDER

            , FOLDERS_ AS (

            SELECT SUBSTR(F.FOLDER_PATH, 5) AS FOLDER_PATH, F.I_FOLDER, F.FOLDER_NAME, F.I_PROJECT, F.LEVEL_

              FROM FOLDERS__ F

                INNER JOIN FOLDERS_MAX__ M

                    ON F.I_FOLDER = M.I_FOLDER

                    AND F.LEVEL_ = M.LEVEL_

            )

            , TABLES_ AS (

            SELECT MF.MOD_FOLDER_PATH, T.*

              FROM DEV_ODI_REPO.SNP_TABLE T

                INNER JOIN DEV_ODI_REPO.SNP_MODEL SM

                    ON T.I_MOD = SM.I_MOD

                LEFT JOIN MOD_FOLDERS_ MF

                    ON MF.I_MOD_FOLDER = SM.I_MOD_FOLDER

            )

            , MAPPINGS_ AS (

            SELECT F.FOLDER_PATH, SM.*

              FROM DEV_ODI_REPO.SNP_MAPPING SM

                INNER JOIN FOLDERS_ F

                    ON F.I_FOLDER = SM.I_FOLDER

            WHERE 1 = 1

            )

            , COMPLETE_ AS (

            SELECT DISTINCT SM.FOLDER_PATH

                 , SM.NAME AS MAPPING_NAME

                 , MC.NAME AS OBJECT_ALIAS

                 , MR.QUALIFIED_NAME AS OBJECT_NAME

                 , T.I_TABLE

                 , T.MOD_FOLDER_PATH

                 , SM.I_MAPPING

                 , CP.I_MAP_CP

                 , MR.I_REF_ID

                 , CASE WHEN T.I_TABLE IS NULL THEN M.I_MAPPING ELSE NULL END AS REUSABLE_I_MAPPING

              FROM DEV_ODI_REPO.SNP_MAP_COMP MC

                INNER JOIN MAPPINGS_ SM

                    ON MC.I_OWNER_MAPPING = SM.I_MAPPING

                INNER JOIN DEV_ODI_REPO.SNP_MAP_CP CP

                    ON MC.I_MAP_COMP = CP.I_OWNER_MAP_COMP

                INNER JOIN DEV_ODI_REPO.SNP_MAP_REF MR

                    ON MC.I_MAP_REF = MR.I_MAP_REF

                LEFT JOIN TABLES_ T

                    ON MR.I_REF_ID = T.I_TABLE

                LEFT JOIN DEV_ODI_REPO.SNP_MAPPING M

                    ON MR.I_REF_ID = M.I_MAPPING

            WHERE 1 = 1

               AND CP.DIRECTION = 'O' --output

            ORDER BY 1, 3

            )

            , SOURCES_ AS (

            SELECT

                   C.OBJECT_NAME AS SOURCE_OBJECT

                 , C.*

              FROM COMPLETE_ C

            WHERE C.I_MAP_CP IN (SELECT I_START_MAP_CP FROM DEV_ODI_REPO.SNP_MAP_CONN) --takes the target

            )

            , TARGETS_ AS (

            SELECT C.OBJECT_NAME AS TARGET_TABLE

                 , C.I_MAPPING

              FROM COMPLETE_ C

            WHERE C.I_MAP_CP NOT IN (SELECT I_START_MAP_CP FROM DEV_ODI_REPO.SNP_MAP_CONN) --takes the target

            )

            , ALL_ AS (

            SELECT T.TARGET_TABLE, S.*

              FROM SOURCES_ S

                INNER JOIN TARGETS_ T

                    ON S.I_MAPPING = T.I_MAPPING

            )

            SELECT DISTINCT A.FOLDER_PATH, A.MAPPING_NAME, A.TARGET_TABLE

                  , A.SOURCE_OBJECT, A.OBJECT_ALIAS, A.I_MAPPING, A.I_TABLE, A.REUSABLE_I_MAPPING, A.MOD_FOLDER_PATH

               FROM ALL_ A

              WHERE 1 = 1

               AND A.MAPPING_NAME = 'TEST2'

              ORDER BY 1, 2, 3, 4, 5;

             

            Hope it helps!

            • 3. Re: ODI: SNP Tables related queries: Fetching ODI Metadata Information
              3988035

              Thanks Rodrigo,

               

              Seconds sql query provides mapping names, however need some analysis to enhance it to get mapping start time , end time, total execution duration columns in it.

              Do you have such script handy (though you provided run duration in first query, wanted to get it with mapping names).

               

              Thanks,

              Rajneesh