infa资料库的使用

--需求：WMS需要被替换，相应的BI系统数据源也需要替换，故需要查找替换的那些表
1：通过infa资料库找到wms数据源
select * from OPB_DBD where dbdnam='OC_WMS_ILS';
2：通过数据源找到对应的workflow
select * from rep_session_instances where connection_name='C_WMS_ILS';
3：通过workflow查找有哪些表
其他的脚本参考：

--根据目标表查询mapping
SELECT DISTINCT m.mapping_id,m.mapping_name,SUBJECT_ID
   FROM opb_widget_inst w, opb_mapping m, opb_targ t
  WHERE m.mapping_id = w.mapping_id
    AND t.target_id = w.widget_id
    AND t.target_name = 'IAGENT_OLDCONTACT_CHAIN'
    AND w.widget_type = 2 --限定为目标表

select * from opb_subject where subj_id=4
/*******INFA_某个文件夹下所有的mapping-session-workflow***************/
SELECT f.subj_name AS folder_name,
        wf.task_name AS workflow_name,
        se.instance_name AS session_name,
        m.mapping_name,
        src.instance_name AS source_name,
        tgt.instance_name AS target_name
   FROM opb_subject f,
        opb_task wf,
        (SELECT workflow_id,
                instance_id,
                task_id,
                task_type,
                instance_name,
                MAX(version_number)
           FROM opb_task_inst sess
          WHERE sess.task_type = 68
          GROUP BY workflow_id,
                   instance_id,
                   task_id,
                   task_type,
                   instance_name) se,--- 所有的session
        (SELECT session_id, mapping_id, MAX(version_number)
           FROM opb_session
          GROUP BY session_id, mapping_id) s,---session,mapping关联关系
        opb_mapping m,
        (SELECT mapping_id, instance_name, widget_type, MAX(version_number)
           FROM opb_widget_inst
          GROUP BY mapping_id, instance_name, widget_type) src,
        (SELECT mapping_id, instance_name, widget_type, MAX(version_number)
           FROM opb_widget_inst
          GROUP BY mapping_id, instance_name, widget_type) tgt
  WHERE   wf.subject_id = f.subj_id
  AND se.workflow_id = wf.task_id
  AND wf.task_type = 71
  AND se.task_id = s.session_id
  AND s.mapping_id = m.mapping_id
  AND src.mapping_id = m.mapping_id
  AND src.widget_type = 1
  AND tgt.mapping_id = m.mapping_id
  AND tgt.widget_type = 2
  AND f.subj_id = 4;
/*********************/
SELECT *
           FROM opb_widget_inst
where widget_type=1
and instance_name='ORDERDET'-----1:源表 2：目标表
OPB_SRC--这个表是源表的数据源
OPB_SRC_FLD--源表中的字段
opb_targ--目标表
opb_targ_fld--目标表中的字段
/**********************/
create view opb_src_field as
select a.source_name,b.src_id,c.src_name as field_name from (
SELECT distinct instance_name as source_name FROM opb_widget_inst
where widget_type=1
) a left join OPB_SRC b on a.source_name=b.source_name
left join OPB_SRC_FLD c on b.src_id=c.src_id

/**********************************************/
For example:
1,lookup attribute value:
1.1

1.2,on sdrfat_uni/ broccoli

In folder: SDER_FIXES ‘s
subject_id is 71
mapping is SDER_BASE_TO_SUPPORT_CASE_TXN_STG
Lookup transformer’s widget type is 11
Lookup condition is 5
The sql for query the lookup condition as below:

select map.mapping_name,wid.instance_name,wid.widget_id,ow.widget_name,owa.attr_value
from opb_mapping map, opb_widget_inst wid,opb_widget ow,opb_widget_attr owa
where map.mapping_id=wid.mapping_id and ow.widget_id=wid.widget_id and owa.widget_id=ow.widget_id
and owa.attr_id=5 and  map.mapping_name='SDER_BASE_TO_SUPPORT_CASE_TXN_STG'
and  ow.widget_type=11 and ow.subject_id=71

the result:

1.3 lookup portattribute:
select map.mapping_name,wid.widget_id,ow.widget_name,wf.field_name,wf.field_id,wf.porttype,owa.attr_value
from opb_mapping map, opb_widget_inst wid,opb_widget ow,opb_widget_attr owa,opb_widget_field wf
where map.mapping_id=wid.mapping_id and ow.widget_id=wid.widget_id and owa.widget_id=ow.widget_id
and ow.widget_id=wf.widget_id(+)
and owa.attr_id=5 and  map.mapping_name='SDER_BASE_TO_SUPPORT_CASE_TXN_STG'
and  ow.widget_type=11 and ow.subject_id=71 and wf.porttype in (4,5,7,12,13,14,15)

1.4 lookup table
select map.mapping_name,wid.instance_name,ow.widget_name,owa.attr_value
from opb_mapping map, opb_widget_inst wid,opb_widget ow,opb_widget_attr owa
where map.mapping_id=wid.mapping_id and ow.widget_id=wid.widget_id and owa.widget_id=ow.widget_id
and owa.attr_id=2 and map.mapping_name='SDER_BASE_TO_SUPPORT_CASE_TXN_STG'
and  ow.widget_type=11 and ow.subject_id=71

1.5 lookup all attribute value
select om.mapping_name, owi.instance_name, owi.comments, ow.widget_name, ow.comments, owa.attr_value
from opb_widget_inst owi, opb_widget ow, opb_widget_attr owa, opb_mapping om
where owa.widget_type = 11 and ow.subject_id = 71
and owi.mapping_id = owa.mapping_id and
owa.widget_id = ow.widget_id and
owa.mapping_id = om.mapping_id;

Wonder to query other lookup attribute value like the above script.

2,the relation of resource and mapping:
SELECT      SOURCE_NAME,
     OPB_SRC.BUSINESS_NAME SRC_BUSNAME,
     SUBJ_NAME SUBJECT_AREA,
     DBDNAM,
     MAPPING_NAME,
     OPB_SCHEMA.VERSIONID VERSION_ID,
     SCH_VERSION VERSION_NAME,
     OPB_MAPPING.COMMENTS MAPPING_COMMENT,
     OPB_MAPPING.LAST_SAVED MAPPING_LAST_SAVED
FROM     OPB_SRC,
OPB_SUBJECT,/* informatic's folder information*/
OPB_MAPPING,
OPB_DBDS,
OPB_SCHEMA,
OPB_DBD
  WHERE     OPB_SUBJECT.SUBJ_ID = OPB_MAPPING.SUBJECT_ID    
AND OPB_MAPPING.MAPPING_ID = OPB_DBDS.MAPPING_ID    
AND OPB_DBD.DBDID = OPB_DBDS.DBD_ID    
AND OPB_DBD.ROOTID = OPB_SRC.SRC_ID    
AND OPB_MAPPING.VERSION_ID = OPB_SCHEMA.VERSIONID    
AND OPB_MAPPING.REF_WIDGET_ID = 0
and OPB_SUBJECT.Subj_Id = 71 /* 71 means sder_fixes*/

3, the relation of session and dbconntion
select * from rep_session_instances where subject_area='SDER_FIXES' and is_target=1

4:this script select the relation of workflow, worklet and taskinstance(when the session is not loaded)

select distinct ob.subj_name, wf.workflow_name,ts.task_name worklet,sess.instance_name--,map.mapping_name
from OPB_SUBJECT ob,opb_wflow_run wf, opb_wflow_cache sess,opb_task ts--,opb_load_session si--,opb_mapping map
where  sess.instance_name not like 'wl_%'
   and ob.subj_id = wf.subject_id
and wf.workflow_id=sess.workflow_id
and sess.worklet_id=ts.task_id(+)
  and wf.subject_id=71
  and sess.instance_name <>'Start'

5,the session runtime:

SELECT
ols.sessname,
TO_CHAR(osl.actual_start,'YYYY/MM/DD') actual_start_yr_mo_dt,
TO_CHAR(osl.actual_start,'HH24:MI:SS') actual_start_timestamp,
TO_CHAR(osl.session_timestamp,'YYYY/MM/DD') session_yr_mo_dt,
TO_CHAR(osl.session_timestamp,'HH24:MI:SS') session_timestamp,
((osl.session_timestamp - osl.actual_start) * 24 * 60 * 60) session_run_time_in_sec,
osl.last_errcode,
osl.SUCC_ROWS,
osl.FAIL_ROWS
FROM
OPB_LOAD_SESSION ols,
OPB_SESSION_LOG osl
WHERE
ols.session_id = osl.session_id
AND osl.actual_start BETWEEN to_date('2005-08-31 06:00:00', 'yyyy-mm-dd hh34:mi:ss') and
       to_date('2005-08-31 21:00:00', 'yyyy-mm-dd hh34:mi:ss')
order by osl.actual_start
;

6,query the log options in task(attr_id=103 means “log runs number” in config object/task)

SELECT * FROM OPB_CFG_ATTR a, rep_load_sessions s
   where a.attr_id=103
   and a.attr_value<>5
   and a.session_id=s.session_id
   and subject_area ='WFMIM'