Repository tables

What are 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.