Repository Tables:
There around a couple of hundred OPB tables in 7.x version of PowerCenter, but in 8.x, this number crosses 400. Below are some of the tables where we can get details of the objects & object properties.
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.
We can Query against the Informatica repository Tables to identify the Long Running Sessions or Time Consuming Sessions by using the below Query
SELECT
RepTask.SUBJECT_AREA,RepTask.INSTANCE_NAME,
TRUNC(AVG(END_TIME-START_TIME)*24, 2) SESS_RUN_HOURS,
MIN(RepTask.START_TIME) START_TIME,
SUM(SessTask.SRC_SUCCESS_ROWS) SRC_ROWS,
SUM(SessTask.TARG_SUCCESS_ROWS) TGT_ROWS
FROM INFO.REP_TASK_INST_RUN RepTask, INFO.OPB_SESS_TASK_LOG SessTask
WHERE RepTask.run_err_code=0
AND (RepTask.END_TIME-RepTask.START_TIME)>= 1/24
AND RepTask.INSTANCE_ID = SessTask.INSTANCE_ID
AND rownum <100
GROUP BY RepTask.INSTANCE_NAME, RepTask.SUBJECT_AREA
Order By SESS_RUN_HOUR desc;