Informatica Repository Query (part 2)

Continue to post a few more queries for Informatica PowerCenter Repository. Part 1 can be found at http://it.toolbox.com/blogs/golden-orbit-blog/dig-into-informatica-powercenter-repository-29027

How to find Teradata MLoad session against a particular table

select U.SUBJ_NAME, F.TASK_NAME WORKFLOW_NAME, T.Instance_name session_name, S.INSTANCE_NAME target_name,
F.subject_id, F.TASK_ID WORKFLOW_ID, s.session_id, s.MAPPING_ID
from OPB_TASK_INST T, OPB_TASK F, OPB_SUBJECT U,
(
  select session_id, sess_widg_inst_id, version_number,
    instance_name, mapping_id,
    rank() over (partition by session_id, sess_widg_inst_id order by version_number desc) sort_id
  from OPB_SWIDGET_INST
  where widget_type = 2
    and UPPER(INSTANCE_NAME) LIKE '%MY_TABLE_NAME%'
) S
where (s.session_id, s.sess_widg_inst_id, s.version_number) in
(
  select session_id, sess_widg_inst_id, max(VERSION_NUMBER) version_number
  from OPB_EXTN_ATTR e
  where object_type = 79 and object_subtype = 315000 -- MLoad Extension
  group by SESSION_ID, sess_widg_inst_id
)
 and s.SORT_ID = 1 -- only the current version
 and s.SESSION_ID = T.task_id
 and s.VERSION_NUMBER = T.VERSION_NUMBER
 and T.WORKFLOW_ID = F.TASK_ID
 and T.VERSION_NUMBER = F.VERSION_NUMBER
 and F.subject_id = U.SUBJ_ID
order by 1,2,3;

-- It will be nice to see workflow name in View Point
update OPB_EXTN_ATTR
set ATTR_VALUE = 'SubjectArea=$PMFolderName; Workflow=$PMWorkflowName; Session=$PMSessionName; WorkflowRunId=$PMWorkflowRunId;'
where attr_id = 26 -- Query Band Expression
and object_type = 79  -- Target
and OBJECT_SUBTYPE = 315000 -- Teradata Plug-in
and (SESSION_ID, SESS_WIDG_INST_ID, VERSION_NUMBER) in 
(
    select session_id, sess_widg_inst_id, max(VERSION_NUMBER) version_number
    from OPB_EXTN_ATTR e
    where object_type = 79 and object_subtype = 315000
    group by SESSION_ID, sess_widg_inst_id
);

update OPB_EXTN_ATTR
set ATTR_VALUE = '1' 
where attr_id = 10 -- Check "Drop Log/Error Tables" option
and object_type = 79  -- Target
and OBJECT_SUBTYPE = 315000 -- Teradata Plug-in
and ATTR_VALUE = '0';  -- Option is not set yet

How to view all the expressions in a transformation

select wf.WIDGET_ID, wf.FIELD_ID, wf.FIELD_NAME, e.EXPR_ID, e.LINE_NO, e.EXPRESSION
from OPB_EXPRESSION e, OPB_WIDGET_FIELD wf, OPB_WIDGET_EXPR we
where wf.WIDGET_ID = e.WIDGET_ID
  and wf.WIDGET_ID = we.WIDGET_ID
  and wf.FIELD_ID = we.OUTPUT_FIELD_ID
  and we.EXPR_ID = e.EXPR_ID
  and wf.VERSION_NUMBER = we.VERSION_NUMBER
  and we.VERSION_NUMBER = e.VERSION_NUMBER 
  and (wf.WIDGET_ID, wf.VERSION_NUMBER) =  (
    select w.widget_id, max(w.version_number) version_number --, w.widget_type, m.mapping_id
    from OPB_SUBJECT s, OPB_MAPPING m, OPB_WIDGET w, OPB_WIDGET_INST wi
    where m.subject_id = s.subj_id
      and m.mapping_id = wi.mapping_id
      and w.widget_id = wi.WIDGET_ID
      and w.subject_id = m.subject_id
      and s.SUBJ_NAME = 'folder_name1' 
      and m.MAPPING_NAME = 'm_mapping_name1'
      and w.WIDGET_NAME = 'transformation_name1'
    group by w.widget_id
  )

Make sure “Insert Else Update” and “Update Else Insert” are checked for Dynamic Lookup

update OPB_WIDGET_ATTR
set ATTR_VALUE = '1'
where (WIDGET_ID, WIDGET_TYPE, VERSION_NUMBER) in
( select w.WIDGET_ID, w.WIDGET_TYPE, max(w.VERSION_NUMBER) VERSION_NUMBER
  from opb_widget w, opb_widget_attr wa
  where wa.ATTR_ID = 15
    and wa.ATTR_VALUE = '1' -- Dynamic Lookup Cache
    and w.WIDGET_ID = wa.WIDGET_ID
    and w.WIDGET_TYPE = wa.WIDGET_TYPE
    and w.VERSION_NUMBER = wa.VERSION_NUMBER
    and w.WIDGET_TYPE = 11 -- Lookup
  group by w.WIDGET_ID, w.WIDGET_TYPE
)
and ATTR_ID in (18,19) -- Insert Else Update, Update Else Insert
and LINE_NO = 1
and ATTR_VALUE = '0';

select w.WIDGET_NAME, w.VERSION_ID, w.WIDGET_ID,
  wa.ATTR_ID, a.ATTR_NAME, wa.LINE_NO, wa.ATTR_VALUE 
from opb_widget w, opb_widget_attr wa, opb_attr a
where w.WIDGET_NAME like ??? -- put your filter here
  and w.WIDGET_ID = wa.WIDGET_ID
  and w.WIDGET_TYPE = wa.WIDGET_TYPE
  and w.VERSION_NUMBER = wa.VERSION_NUMBER
  and wa.ATTR_ID = a.attr_id
  and wa.WIDGET_TYPE = a.OBJECT_TYPE_ID 
order by w.WIDGET_NAME, w.VERSION_NUMBER desc, wa.ATTR_ID, wa.LINE_NO;
Advertisements

, ,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: