Repository
tables
|
All objects that we create in
Informatica PowerCenter (sources, targets,
transformations, mappings,
sessions, workflows, command tasks etc) get stored in a
set of database tables. These
database tables are known as either Repository tables
or metadata tables or OPB tables.
List of some important
Repository tables
There around a couple of hundred
OPB tables in 7.x version of PowerCenter, but in
8.x, this number crosses 400.
OPB_SUBJECT - PowerCenter folders table
This table stores the name of each
PowerCenter repository folder.
Usage: Join any of the repository tables that have SUBJECT_ID
as column with that
of SUBJ_ID in this table to know
the folder name.
OPB_MAPPING - Mappings table
This table stores the name and ID
of each mapping and its corresponding folder.
Usage: Join any of the repository tables that have MAPPING_ID
as column with that
of MAPPING_ID in this table to
know the mapping name.
OPB_TASK - Tasks table like sessions, workflow etc
This table stores the name and ID
of each task like session, workflow and its
corresponding folder.
Usage: Join any of the repository tables that have TASK_ID as
column with that of
TASK_ID/SESSION_ID in this table
to know the task name. Observe that the session
and also workflow are stored as
tasks in the repository. TASK_TYPE for session is 68
and that of the workflow is 71.
OPB_SESSION - Session & Mapping linkage table
This table stores the linkage
between the session and the corresponding mapping. As
informed in the earlier paragraph,
you can use the SESSION_ID in this table to join
with TASK_ID of OPB_TASK table.
OPB_TASK_ATTR - Task attributes tables
This is the table that stores the
attribute values (like Session log name etc) for tasks.
Usage: Use the ATTR_ID of this table to that of the ATTR_ID of
OPB_ATTR table to
find what each attribute in this
table means. You can know more about OPB_ATTR
table in the next paragraphs.
OPB_WIDGET - Transformations table
This table stores the names and
IDs of all the transformations with their folder
details.
Usage: Use WIDGET_ID from this table to that of the WIDGET_ID
of any of the
tables to know the transformation
name and the folder details. Use this table in
conjunction with OPB_WIDGET_ATTR
or OPB_WIDGET_EXPR to know more about
each transformation etc.
OPB_WIDGET_FIELD - Transformation ports table
This table stores the names and
IDs of all the transformation fields for each of the
transformations.
Usage: Take the FIELD_ID from this table and match it against
the FIELD_ID of any
of the tables like OPB_WIDGET_DEP
and you can get the corresponding information.
OPB_WIDGET_ATTR - Transformation properties table
This table stores all the
properties details about each of the transformations.
Usage: Use the ATTR_ID of this table to that of the ATTR_ID of
OPB_ATTR table to
find what each attribute in this
transformation means.
OPB_EXPRESSION - Expressions table
This table stores the details of
the expressions used anywhere in PowerCenter.
Usage: Use this table in conjunction with
OPB_WIDGET/OPB_WIDGET_INST and
OPB_WIDGET_EXPR to get the
expressions in the Expression transformation for a
particular, mapping or a set.
OPB_ATTR - Attributes
This table has a list of
attributes and their default values if any. You can get the
ATTR_ID from this table and look
it up against any of the tables where you can get
the attribute value. You should
also make a note of the ATTR_TYPE,
OBJECT_TYPE_ID before you pick up
the ATTR_ID. You can find the same ATTR_ID
in the table, but with different
ATTR_TYPE or OBJECT_TYPE_ID.
OPB_COMPONENT - Session Component
This table stores the component
details like Post-Session-Success-Email, commands
in Post-Session/pre-Session etc.
Usage: Match the TASK_ID with that of the SESSION_ID in
OPB_SESSION table to
get the SESSION_NAME and to get
the shell command or batch command that is
there for the session, join this
table with OPB_TASK_VAL_LIST table on TASK_ID.
OPB_CFG_ATTR - Session Configuration Attributes
This table stores the attribute
values for Session Object configuration like "Save
Session log by", Session log
path etc.
Happy Learning
|