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

--적재 결과 확인
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