Informatica Metadata Repository tables

While browsing through the Informatica rep and opb tables, we are usually stuck up as we do not understand what the widget_id and widget_types are for.

Below is the list of widget_types. This data would help us ease the handling of metadata.
Widget Ids and transformation types

widget_type

Type of transformation

1Source

2Target

3Source Qualifier

4Update Strategy

5expression

6Stored Procedures

7Sequence Generator

8External Procedures

9Aggregator

10Filter

11Lookup

12Joiner

14Normalizer

15Router

26Rank

44mapplet

46mapplet input

47mapplet output

55XML source Qualifier

80Sorter

97Custom Transformation

What are widget IDs and why do we require them?
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.

Port types in a transformation

As the above section details, widget is a transformation in metadata tables. To get the port type from repository table, below is the SQL snippet to use

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

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.

Expressions and SQL overrides in a transformation
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.