일상(업무, 식사, 맛집, 취미 등...) 담은 블로그

--적재 결과 확인
SELECT D.SUBJ_NAME             AS FOLDER_NAME
     , X.WORKFLOW_NAME         AS WORKFLOW_NAME
     , X.INSTANCE_NAME         AS SESSION_NAME
     , X.TABLE_NAME            AS TABLE_ID
     , X.START_TIME
     , X.END_TIME
     , X.SRC_SUCCESS_ROWS      AS SRC_ROWS
     , X.TARG_SUCCESS_ROWS     AS TARG_ROWS
     , X.TARG_FAILED_ROWS      AS FAILED_ROWS
     , TRUNC((X.END_TIME - X.START_TIME)*24*60*60) AS LOADTIME
     , DECODE(X.RUN_STATUS_CODE, 1, 'SUCEEDED', 2, 'DISABLED', 3, 'FAILED', 4, 'STOPPED', 5, 'ABORTED'
                                 , 6, 'RUNNING', 7, 'SUSPENDING', 8, 'SUSPENDED', 9, 'STOPPING', 10, 'ABORTING'
                                 , 11, 'WAITING', 12, 'SCHEDULED', 13, 'UNSCHEDULED', 14, 'UNKNOWN', 15, 'TERMINATED') AS RUN_STATUS
     , DECODE(FIRST_ERROR_CODE, 0, '', SUBSTR(REPLACE(REPLACE(FIRST_ERROR_MSG, CHR(10), ' '), CHR(13), ' '), 1, 200)) AS ERR_MSG
     , x.SUBJECT_ID
FROM ( SELECT C.SUBJECT_ID
            , C.WORKFLOW_NAME
            , A.INSTANCE_NAME
            , REGEXP_REPLACE(A.INSTANCE_NAME, 's_m_') TABLE_NAME
            , A.START_TIME
            , A.END_TIME
            , B.SRC_SUCCESS_ROWS
            , B.TARG_SUCCESS_ROWS
            , B.TARG_FAILED_ROWS
            , A.RUN_STATUS_CODE
            , B.FIRST_ERROR_MSG
            , B.FIRST_ERROR_CODE
            , ROW_NUMBER() OVER(PARTITION BY C.WORKFLOW_NAME, A.INSTANCE_NAME ORDER BY A.START_TIME DESC) RNK
       FROM OPB_TASK_INST_RUN A
          , OPB_SESS_TASK_LOG B
          , OPB_WFLOW_RUN C
      WHERE A.WORKFLOW_ID = B.WORKFLOW_ID
        AND A.WORKFLOW_RUN_ID = B.WORKFLOW_RUN_ID
        AND A.WORKLET_RUN_ID = B.WORKLET_RUN_ID
        AND A.INSTANCE_ID = B.INSTANCE_ID
        AND A.WORKFLOW_RUN_ID = C.WORKFLOW_RUN_ID
        AND C.WORKFLOW_NAME IN (SELECT TASK_NAME FROM OPB_TASK WHERE TASK_TYPE = '71')
        AND A.START_TIME > TO_DATE('201901010000', 'YYYYMMDDHH24MI')
      ) X
    , OPB_SUBJECT D
WHERE X.SUBJECT_ID = D.SUBJ_ID

 

 

 

--SQL 확인
SELECT T.FOLDER_NAME
     , T.MAPPING_NAME
     , REGEXP_REPLACE(T.MAPPING_NAME, 'map_|_01') AS TABLE_NAME
     , T.OBJECT_TYPE
     , T.ATTR_NAME
     , T.LINE_NO
     , T.SQL_TEXT
FROM (SELECT D.SUBJ_NAME     AS FOLDER_NAME
           , A.MAPPING_NAME  AS MAPPING_NAME
           , 'SOURCE'        AS OBJECT_TYPE
           , F.ATTR_NAME     AS ATTR_NAME
           , C.WIDGET_ID     AS OBJECT_ID
           , C.ATTR_ID       AS ATTR_ID
           , 0               AS LOAD_PLAN
           , C.LINE_NO       AS LINE_NO
           , C.ATTR_VALUE    AS SQL_TEXT
      FROM OPB_MAPPING A
         , OPB_WIDGET B
         , OPB_WIDGET_ATTR C
         , OPB_SUBJECT D
         , OPB_OBJECT_TYPE E
         , OPB_ATTR F
      WHERE A.SUBJECT_ID = D.SUBJ_ID
        AND A.MAPPING_ID = B.RU_PARENT_ID
        AND B.WIDGET_ID = C.WIDGET_ID
        AND B.WIDGET_TYPE = C.WIDGET_TYPE
        AND B.WIDGET_TYPE = E.OBJECT_TYPE
        AND E.OBJECT_TYPE_ID = F.OBJECT_TYPE_ID
        AND C.ATTR_ID = F.ATTR_ID
        AND F.ATTR_NAME IN ('Sql Query', 'Source Filter', 'Pre SQL', 'Post SQL')
        AND C.ATTR_VALUE IS NOT NULL
      UNION ALL
      SELECT B.SUBJ_NAME
           , A.MAPPING_NAME
           , 'TARGET'
           , F.ATTR_NAME
           , C.WIDGET_ID
           , C.ATTR_ID
           , G.ORDER_VAL
           , C.LINE_NO
           , C.ATTR_VALUE
      FROM OPB_MAPPING A
         , OPB_SUBJECT B
         , OPB_WIDGET_ATTR C
         , OPB_OBJECT_TYPE E
         , OPB_ATTR F
         , OPB_TDS G
      WHERE A.SUBJECT_ID = B.SUBJ_ID
        AND A.MAPPING_ID = C.MAPPING_ID
        AND C.WIDGET_TYPE = E.OBJECT_TYPE
        AND E.OBJECT_TYPE_ID = F.OBJECT_TYPE_ID
        AND C.ATTR_ID = F.ATTR_ID
        AND C.MAPPING_ID = G.MAPPING_ID
        AND C.INSTANCE_ID = G.TARGET_INSTANCE_ID
        AND C.ATTR_ID IN (6, 7)
        AND C.ATTR_VALUE IS NOT NULL
        AND F.ATTR_NAME IN ('Sql Query', 'Source Filter', 'Pre SQL', 'Post SQL')
) T
ORDER BY 1, 2, 5, 6

1. 인파 서버에서 워크플로우 실행

 

pmcmd startworkflow -sv $ISSRV -d $DOMAIN -u $USERID -p $USERPW -f $FORLDER -wait $WFNAME

 

 

 

2. 실행했던 워크플로우 정보 확인

 

pmcmd getworkflowdetails -sv $ISSRV -d $DOMAIN -u $USERID -p $USERPW -f $FORLDER $WFNAME

 

A라는 DB에서 B테이블을 Select 해보니 데이터가 0건,

 

하지만 분명히 ETL(Informatca) 프로그램 에서 AS-IS는 A라는 DB의 B 테이블 이었다.

 

DB에서 직접 Select 할때에는 0건이던 데이터가, ETL 프로그램을 거치면 데이터가 생긴다???

 

말도 안되는 현상이다...

 

 

한참을 헤매다가 인포매티카 커넥션 정보의

 

Connectiion Environment SQL 에서 호출하는 프로시져가 있는것이 아닌가!!!

 

해당 프로시져의 내용을 살펴보니

 

apps.mo_global.set_policy_context('S', 108)

 

이 명령어가 전부였다.

 

 

DB에서도 해당 프로시져를 호출하고 데이터를 Select 해보니

 

데이터가 잘 보인다...

 

 

apps.mo_global.set_policy_context('S', 108) 명령어는

 

평소에는 보안상의 이유로 데이터를 안보이게 했다가,

 

필요할 때만 볼 수있게 하는 보안 기능인 것 같다...