Shell,Oracle,SQL/PLSQL,Informatica | 用来检查批量ma...

用来检查批量mappings，sessions，workflows 相关属性的语句。

nattyneat | 28 六月, 2006 18:08

中间陆续写了几个关于informatica知识库的查询检查语句；到了今天，已经有了比较多的累积。这里是一个到目前为止的完整版本：

version:powercenter 7.1.1
用来检查批量某个参数缺省值，长度等属性的脚本：

select t2.subj_name,t1.mapping_name,t0.pv_name,t0.pv_default from opb_map_parmvar t0,opb_mapping t1,opb_subject t2 WHERE
--t0.pv_name='$$LEGAL_ENTITY_CODE' AND
t0.mapping_id=t1.mapping_id AND
t0.subject_id=t2.subj_id
AND t0.pv_default IS NOT NULL
AND ( lower(t2.subj_name) LIKE '%xxxx%' OR t2.subj_name='folder_name')
开发大量的mappings（例如100个）的过程中经常要检查是否mappings的参数字段的长度符合需求，有没有清除缺省值。对于mappings的实现过程有一些是相同的，这些部分在每个mappings中是否发生变化（这些变化可能是你无意造成的）。如果一个一个mappings的检查也是可以，但是效率不高，且人工检查容易漏掉一些部分。下面的一些脚本是team在开发过程中总结出来的一些脚本，以后会陆续补充。

1. 用来批量检查是否含有某个fileter等条件的脚本：
SELECT OPB_SUBJECT.SUBJ_NAME, OPB_MAPPING.MAPPING_NAME
FROM OPB_MAPPING, OPB_SUBJECT
WHERE OPB_SUBJECT.SUBJ_ID = OPB_MAPPING.SUBJECT_ID
AND UPPER(OPB_MAPPING.MAPPING_NAME) LIKE 'M/_xxx/_%' ESCAPE
'/'
AND MAPPING_NAME NOT IN
(SELECT T3.MAPPING_NAME
FROM (SELECT WIDGET_ID, ATTR_VALUE
FROM OPB_WIDGET_ATTR
WHERE UPPER(ATTR_VALUE(+)) LIKE '%$$LEGAL_ENTITY_CODE%') T1,
OPB_MAPPING T3,
OPB_SWIDGET_INST T4,
OPB_SUBJECT T5
WHERE T4.WIDGET_ID = T1.WIDGET_ID
AND T4.MAPPING_ID = T3.MAPPING_ID
AND T5.SUBJ_ID = T3.SUBJECT_ID
AND UPPER(T3.MAPPING_NAME) LIKE 'M/_HIST/_%' ESCAPE '/')
ORDER BY OPB_SUBJECT.SUBJ_NAME

2, Session 的连接信息错误。workflow都没问题。下面的session有问题。
(select出来的都是直接指定连接值，如果是通过变量的话，这个查询是选择不出的)
select * from rep_session_cnxs T
WHERE UPPER(T.SESSION_NAME) LIKE 'S_M_P2%'
AND UPPER(CONNECTION_NAME) LIKE '%_V1%'

3. Truncate Table Option 未选中。workflow都没问题。下面的session有问题。
select T.WORKFLOW_ID, B.TASK_NAME, T.ATTR_VALUE, T.ATTR_VALUE from opb_extn_attr t , rep_all_tasks B
WHERE ATTR_ID = 9
AND SESSION_ID =B.TASK_ID
AND UPPER(B.TASK_NAME) LIKE 'S_M_P2%'

4. Config Name 没有设置成新创建的“xxx_session_config”
/*查看是否所有的sessions的config name 属性已经更改！*/

SELECT P.TASK_NAME,
--T.TASK_ID,
--T.REF_OBJ_ID,
--T.OBJECT_TYPE,
--S.OBJECT_TYPE_NAME,
R.CONFIG_NAME,
Q.SUBJ_NAME
FROM OPB_COMPONENT T,
OPB_OBJECT_TYPE S,
OPB_SESSION_CONFIG R,
OPB_SUBJECT Q,
OPB_TASK P
WHERE OBJECT_TYPE_ID = T.OBJECT_TYPE
AND S.OBJECT_TYPE_NAME = 'SessionConfig'
AND T.REF_OBJ_ID = R.CONFIG_ID
AND Q.SUBJ_NAME IN
('xxxx_xxx', 'xxxx_CN', 'xxxx_xxx', 'xxx_xxxxx')
AND R.CONFIG_NAME <> 'xxx_session_config'
AND Q.SUBJ_ID = R.SUBJECT_ID
AND P.TASK_ID = T.TASK_ID

5.Target name 不符合标准：
Select op.mapping_name, owi.instance_name
From Opb_Mapping Op,
Opb_Widget_Inst Owi,
Opb_Subject Os
Where Owi.Mapping_Id = Op.Mapping_Id
And Op.Subject_Id = Os.Subj_Id
and owi.widget_type = 2
and Op.Mapping_name like 'm_P2%'
and subj_name = xxx_13_TO_15'
and Owi.instance_name not like 't_%_Ins'

6.检查workflow 的sesssion之间有无连接status检查的sql
select a.task_name,
b.instance_name as From_session,
c.instance_name as To_session,
t.CONDITION
from rep_workflow_dep t, opb_task a, opb_task_inst b, opb_task_inst c
where t.WORKFLOW_ID = a.task_id
and a.subject_id = 38
and a.task_type in (70, 71)
and t.FROM_INSTANCE_ID = b.instance_id
and b.task_type = 68 --only session
and t.TO_INSTANCE_ID = c.instance_id
and a.is_visible = 1
and a.version_number = t.VERSION_NUMBER
/* and t.CONDITION is null*/
Order by a.task_name
=======================================
针对上面语句调整后的，可以较快执行的语句：
SELECT *
FROM (SELECT (SELECT S.SUBJECT_AREA
FROM REP_WORKFLOWS S
WHERE S.WORKFLOW_ID = A.WORKFLOW_ID) AS SUJ_AREA,
A.WORKFLOW_ID,
(SELECT S.WORKFLOW_NAME
FROM REP_WORKFLOWS S
WHERE S.WORKFLOW_ID = A.WORKFLOW_ID),
A.FROM_INSTANCE_ID,
(SELECT INSTANCE_NAME
FROM OPB_TASK_INST S
WHERE INSTANCE_ID = A.FROM_INSTANCE_ID) AS FROM_INSTANCE_NAME,
A.TO_INSTANCE_ID,
(SELECT INSTANCE_NAME
FROM OPB_TASK_INST S
WHERE INSTANCE_ID = A.TO_INSTANCE_ID) AS TO_INSTANCE_NAME,
A.CONDITION
FROM REP_WORKFLOW_DEP A)

WHERE FROM_INSTANCE_NAME <> 'Start'
AND CONDITION IS NULL

7,检查有没有用参数来指定source file 的名称：
select * from rep_session_files t
WHERE file_name<>'$InputFile_SOURCEFILE'

8.检查session的连接参数是否指定正确的连接变量（也可用来检查那些session没有用连接参数而是直接指定连接常量）。
SELECT b.SUBJECT_AREA,b.TASK_NAME,a.INSTANCE_NAME,a.CNX_NAME
FROM REP_SESS_WIDGET_CNXS_RICHEL A, REP_ALL_TASKS B
WHERE A.SESSION_ID = B.TASK_ID
AND B.SUBJECT_AREA IN
( 'xxx','xxxxx')
AND (A.CNX_NAME NOT LIKE '$DBConnection%' OR A.CNX_NAME IS NULL)

9.查找某列都出现在那些mapping（ACC_REF）中：

select mapping_name from rep_mapping_unconn_ports t WHERE T.FIELD_NAME LIKE '%ACC_REF%'
AND SUBJECT_AREA = 'xxxxc'
UNION
SELECT DISTINCT T.MAPPING_NAME
FROM REP_MAPPING_CONN_PORTS T
WHERE (T.FROM_OBJECT_FIELD_NAME LIKE '%ACC_REF%' OR
T.TO_OBJECT_FIELD_NAME LIKE '%ACC_REF%')
AND SUBJECT_AREA = 'xxxxx_xxx'

10.查看某列（ACC_REF）都会出现在那些表达式中。
SELECT B.MAPPING_NAME,C.FIELD_NAME,C.EXPRESSION FROM
REP_WIDGET_INST A,REP_ALL_MAPPINGS B,rep_widget_field C
WHERE A.MAPPING_ID=B.MAPPING_ID
AND A.WIDGET_ID=C.WIDGET_ID
AND B.SUBJECT_AREA='xxx_xxx_xxx'
AND B.PARENT_SUBJECT_AREA='xxxx_xxx'
AND C.EXPRESSION LIKE '%ACC_REF%'

11.查看某列的长度是否都是一致的。（这里ACC_REF列被修改为20－>40,该语句用来查询是否有遗漏未修改的部分。）
select * from rep_all_transforms s
WHERE s.WIDGET_ID IN (select t.widget_id from opb_widget_field t --WHERE WIDGET_ID=47
WHERE T.FIELD_NAME LIKE '%ACC_REF%' AND t.wgt_prec=20)

11.1(例子：查找UPD_DATE_PREVIOUS字段不为日期类型的mapping名字）
SELECT *
FROM REP_WIDGET_INST A,
(SELECT *
FROM REP_ALL_TRANSFORMS s
WHERE S.WIDGET_ID IN (SELECT WIDGET_ID/*,WGT_PREC*/
FROM OPB_WIDGET_FIELD --WHERE WIDGET_ID=47
WHERE FIELD_NAME LIKE '%UPD_DATE_PREVIOUS%'
AND WGT_PREC <> 19)) b
WHERE A.WIDGET_ID = B.WIDGET_ID
AND A.INSTANCE_NAME = B.WIDGET_NAME

12.检查 Fail parent if this task failed;Fail parent if this task does not run;
option checked or not.

SELECT /***/ W.WORKFLOW_NAME,I.INSTANCE_NAME,I.BIT_OPTIONS
FROM OPB_TASK_INST I,REP_WORKFLOWS W
WHERE I.WORKFLOW_ID = W.WORKFLOW_ID
AND W.SUBJECT_AREA = 'xxxx_xxx'
AND I.IS_ENABLED = 1
AND I.TASK_TYPE <>62
AND ( I.BIT_OPTIONS != 49 AND I.BIT_OPTIONS != 113)

13.检查log文件名是否和session名一致：
SELECT C.*, INSTR(ATTR_VALUE, TASK_NAME, 1)
FROM (SELECT /*b.subject_id,*/
(SELECT SUBJ_NAME FROM OPB_SUBJECT WHERE SUBJ_ID = B.SUBJECT_ID) AS SUBJ,
A.WORKFLOW_ID,
A.TASK_ID,
B.TASK_NAME,
A.ATTR_VALUE
FROM OPB_TASK_ATTR A, OPB_TASK B
WHERE A.ATTR_ID = 2
AND A.TASK_ID = B.TASK_ID
AND (A.ATTR_VALUE <> B.TASK_NAME || '.log')) C

WHERE SUBJ LIKE 'xxx_xxxxx_xxx'
AND INSTR(ATTR_VALUE, TASK_NAME, 1) <> 1；

14,用来检查是否session的edit tasks->Components->On Failure E-Mail (Reusable em_session_fail)已经设定。
SELECT S.SUBJECT_AREA, S.TASK_NAME, S.TASK_ID, COUNT(*)
FROM REP_COMPONENT T, OPB_OBJECT_TYPE O, REP_ALL_TASKS S
WHERE T.OBJECT_TYPE = O.OBJECT_TYPE
AND T.TASK_ID = S.TASK_ID
AND S.SUBJECT_AREA IN
( 'xxxx')
GROUP BY S.SUBJECT_AREA, S.TASK_NAME, S.TASK_ID
HAVING COUNT(*) > 4