Hi Eeveryone,
Informatica repository is organized as database
tables. Each and every Informatica objects (Sources, Targets, Mappings,
Sessions, Workflows, Worklets etc) that we creates are stored in set of database tables
as records. These are called OPB Tables or Meta Tables or Repository Tables.
Before we start over, I would like to tell you that
any manual changes (Insert, Delete or Update operations) to the Repository /OPB
/Meta Tables might corrupt the repository. So DO NOT ISSUE UPDATE, DELETE OR INSERT
COMMANDS AGAINST THESE TABLES. I WOULD
STRONGLY RECOMMEND YOU TO USE ONLY SELECT COMMAND TO RESEARCH ON THE META
TABLES.
Let’s start discussing about it. Here are the Meta
Tables and what it contents are?
OPB_SUBJECT - PowerCenter Folders Table.
This table stores the name of each PowerCenter repository folder. SUBJ_ID is the Primary Key in this table.
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. MAPPING_ID is the Primary Key in this table.
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. SESSION_ID is the Primary Key of this 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.
This table stores the name of each PowerCenter repository folder. SUBJ_ID is the Primary Key in this table.
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. MAPPING_ID is the Primary Key in this table.
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. SESSION_ID is the Primary Key of this 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.
I will be posting more details about each of the above mentioned tables in the succeeding posts.
Thanks for reading my Blog !
0 comments:
Post a Comment