INFORMATICA 8.1 中mapping中所有的变量都存在opb_map_parmvar 中.为每一个mapping设置变量是一个很痛苦的事情.而对于workflow的 persistent value 控制更是麻烦.原先是通过update workflow的persistent value 来控制.但是若是workflow没有执行过就没有persistent value.就无法update.通过研究informatica的知识库.现在可以手工设置每一个workflow .每一个session的persistent value 了
–获得目前知识库所存的所有的persistent value变量信息
select g.subj_name as folder_name,
a.task_name as workflow_name,
a.task_id as workflow_id,
b.instance_name as session_name,
b.task_id as sessionid,
b.instance_id as sess_inst_id,
c.mapping_id as mapping_id,
d.mapping_name as mapping_name,
e.pv_name as pv_name,
e.pv_default as pv_default,
f.pv_value as pv_value
from opb_task a,
opb_task_inst b,
opb_session c,
opb_mapping d,
opb_map_parmvar e,
opb_map_persisval f,
opb_subject g
where a.task_type = 71
and a.task_id = b.workflow_id
and b.task_type = 68
and c.session_id = b.task_id
and d.mapping_id = c.mapping_id
and e.mapping_id = c.mapping_id
and f.mapping_id = c.mapping_id
and f.session_inst_id = b.instance_id
and f.pv_id = e.pv_id
and g.subj_id = a.subject_id
–手工设置opb_map_persisval表.为每一workflow,session设置变量
insert into opb_map_persisval
select 0 as workflow_id,
b.instance_id as session_inst_id,
g.subj_id as subject_id,
0 as version_id,
c.mapping_id as mapping_id,
0 as mapplet_id,
0 as mapplet_inst_id,
e.pv_id as pv_id,
”’888888”’ as pv_value, –需要设置的pv值
TO_CHAR(sysdate, ‘MM/DD/YYYY HH24:MI:SS’) as last_saved
from opb_task a,
opb_task_inst b,
opb_session c,
opb_mapping d,
opb_map_parmvar e,
opb_subject g
where a.task_type = 71
and a.task_id = b.workflow_id
and b.task_type = 68
and c.session_id = b.task_id
and d.mapping_id = c.mapping_id
and e.mapping_id = c.mapping_id
and g.subj_id = a.subject_id
and e.subject_id = d.subject_id
and e.pv_name = ” –变量名
and a.task_name = ” –workflow名