Informatica Metadata

Informatica maintains metedata regarding the mappings and its tranformations, sessions, workflows and their statistics. These details are maintained in a set of tables called OPB tables and REP tables.

The widget refers to the types of transformation details stored in these tables.

Widget Ids and transformation types
widget_type Type of transformation
1 Source
2 Target
3 Source Qualifier
4 Update Strategy
5 expression
6 Stored Procedures
7 Sequence Generator
8 External Procedures
9 Aggregator
10 Filter
11 Lookup
12 Joiner
14 Normalizer
15 Router
26 Rank
44 mapplet
46 mapplet input
47 mapplet output
55 XML source Qualifier
80 Sorter
97 Custom Transformation

select z.widget_id, decode(z.porttype, 1, 'INPUT', 3, 'IN-OUT', 2, 'OUT', 32, 'VARIABLE', 8, 'LOOKUP', 10, 'OUT-LOOKUP', to_char(z.porttype)) Port_Type from opb_widget_field z;

If you want to know the mapping name, then match the widget_id against the widget_id of opb_widget_inst and then pull the mapping_id which can be mapped against mapping_id in opb_mappings table. If you want to know the Folder name, then map the subject_id from opb_mappings to that of subj_id in OPB_SUBJECTS table to get the subject_name.

OPB_EXPRESSION is the table that stores all the expressions in metadata. To associate an expression to a field in a transformation, OPB_WIDG_EXPR is the table to be used.

select g.expression from opb_widget_expr f, opb_expression g where f.expr_id = g.expr_id;

SQL overrides can be in Source Qualifiers and Lookup transformations.
To get the SQL Override from metadata, check REP_WIDGET_ATTR.ATTR_VALUE column.


select * from opb_widget_field where wgt_datatype=11

select g.expression from opb_widget_expr f, opb_expression g where f.expr_id = g.expr_id;

select * from REP_WIDGET_ATTR

select * from opb_mapping -- to check for mapping,mapplet, its stauts of valid/invalid etc

select * from opb_server_info -- to get server hostname,ip,etc

select src_id from opb_src where source_name ='W_DAY_D' -- source table , owner info

select * from opb_src_fld where src_id=1753 -- to get table column/field list info

select * from opb_targ where target_name like 'W_DAY_D' -- target table info

select * from opb_targ_fld where target_id =1835 -- to get target field info