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.

Differences between Advanced External Procedure and External Procedure Transformations

Advanced External Procedure Transformation

Advanced External Procedure transformation is an Active and Connected transformation. It operates in conjunction with procedures, which are created outside of the Designer interface to extend PowerCenter/PowerMart functionality. It is useful in creating external transformation applications, such as sorting and aggregation, which require all input rows to be processed before emitting any output rows.

External Procedure Transformation

External Procedure transformation is an Active and Connected/UnConnected transformations. Sometimes, the standard transformations such as Expression transformation may not provide the functionality that you want. In such cases External procedure is useful to develop complex functions within a dynamic link library (DLL) or UNIX shared library, instead of creating the necessary Expression transformations in a mapping.

Differences between Advanced External Procedure and External Procedure Transformations:

External Procedure returns single value,
whereas Advanced External Procedure returns multiple values.

External Procedure supports COM and Informatica procedures
whereas AEP supports only Informatica Procedures.

Difference between Connected and UnConnected Lookup Transformation

Difference between Connected and UnConnected Lookup Transformation:

Connected lookup receives input values directly from mapping pipeline
whereas UnConnected lookup receives values from: LKP expression from another transformation.

Connected lookup returns multiple columns from the same row
whereas UnConnected lookup has one return port and returns one column from each row.

Connected lookup supports user-defined default values
whereas UnConnected lookup does not support user defined values.

Informatica 7 vs 8 (architecture level)

The architecture of Power Center 8 has changed alot;
PC8 isservice-oriented for modularity, scalability and flexibility.

The Repository Service and Integration Service (as replacementfor Rep Server and Informatica Server) can be run on different computers in a network (so called nodes), even redundantly.

Management is centralized, that means services can be startedand stopped on nodes via a central web interface.

Client Tools access the repository via that centralized machine,resources are distributed dynamically.

Running all services on one machine is still possible, ofcourse.

Differences between informatica 6,7,8 versions (application level)

6 to 7
union transformation
lookup on flat files
we will connect workflow through designer itself

7 to 8
java transformation
sql transformation
HTML transformation

8 version contains integration services
many source files are added like
webservices,
tibco,
webmethods
user defined functions ...

Unable to connect to SQLServer 2005 on repository

Change the authentication of the SQL Server to "SQL Server and Windows" (also known as "mixed mode").

OR

Use a different network library.

Go to SQL Server Configuration Manager > Protocols for MSSQLSERVER > enable TCP/IP

and restart SQL server services

Bad File in informatica

Informatica badfiles will have the row indicator columns which can take values between 0 to 9 and Please find the details as below:

RowIndicator Meaning Rejected By
0 Insert Writer or target
1 Update Writer or target
2 Delete Writer or target
3 Reject Writer
4 Rolled-back insert Writer
5 Rolled-back update Writer
6 Rolled-back delete Writer
7 Committed insert Writer
8 Committed update Writer
9 Committed delete Writer

Also you can focus on column indicators, and the details are as below:
Indicator Type of data Writer Treats As

D Valid data. Good data. Writer passes it to the target database. The target accepts it unless a database error occurs, such as finding a duplicate key.

O Overflow Numeric data exceeded the specified precision or scale for the column. Bad data, if you configured the mapping target to reject overflow or truncated data.

N Null The column contains a null value. Good data. Writer passes it to the target, which rejects it if the target database does not accept null values.

T Truncated String data exceeded a specified precision for the column, so the PowerCenter Server truncated it. Bad data, if you configured the mapping target to reject overflow or truncated data.

Administration Console hangs when logging in using host name

Administration Console hangs when logging in using host name

Problem Description

The Administration Console is displayed however after entering the user name and password, the page appears to hang. Domain will be running and accessible by command line tools such as infatest . Domain may be accessed using localhost and not the machine host name is entered into Administration Console URL.

Cause

The fact that the Administration Console can be accessed via the localhost string indicates there is a fault with DNS. PowerCenter will use DNS to check back on host names and IP addresses.

Solution

To resolve this issue identify which machines on the network are acting as DNS servers and check their configuration ensuring that entries for machines used by PowerCenter (database server, client machines, PowerCenter nodes) are correct.

Applies To

PowerCenter 8.1.x PowerCenter 8.5

HOW TO: Set the PowerCenter service Custom Properties
Solution
To add an undocumented PowerCenter parameter (Integration Service or Repository Service Custom Property ) do the following in the Informatica PowerCenter (8.1.x or 8.5.x) Administration Console:
Stop the Integration Service (or Repository Service).
Select the Integration Service (or Repository Service).
Under the Properties tab, click Edit in the Custom Properties section.
Under Name enter the name of the undocumented parameter.
Example:
XMLinUTF8
Enter the value for the parameter under Value .
Example:
AggSupprtWithNoPartLic = Yes
Click OK .
Start the Integration Service (or Repository Service

Partitioning option license required to run sessions with user-defined partition points.

Quote: TM_6281: Partitioning option license required to run sessions with user-defined partition points.

When I de-select the "Sorted Input" property, it runs fine but it spends needless time caching my entire table to file. Does anyone know why this might be happening and/or a possible resolution? " " To run a PowerCenter session with a sorted Aggregator transformation set the server parameter AggSupprtWithNoPartLic to "Yes" as follows:

Unix Using a text editor open the PowerCenter server configuration (pmserver.cfg) file.
Add the following entry to the end of the file:
AggSupprtWithNoPartLic = Yes
Re-start the PowerCenter server (pmserver).

Windows To set this parameter on Windows refer to article 11486
(article 11486) To add a PowerCenter Server parameter (that is not in the server configuration dialog) on Windows enter it in the registry as follows:

Click Start, click Run, type regedit, click OK. Go to the following registry key: HKEY_LOCAL_MACHINE\ SYSTEM\CurrentControlSet\Services\PowerMart\Paramet ers\Configuration
On the Edit menu, point to New, and then click String Value. Enter the String Value "SERVER_PARAMETER". Go to Edit > Modify. Enter "VALUE" and then click OK Re-start the PowerCenter server (Informatica Service). Where SERVER_PARAMETER is the name of the parameter and VALUE is the setting of the parameter."