User Defined Functions

Informatica User Defined Functions are similar to Built-in Functions, where these functions need to be created once and execute multiple times. Transformation logics that are common across the ports are the ideal candidate for User Defined Functions.

Transformation Logic implemented without User Defined Functions

Validation “IIF( ISNULL(LTRIM(RTRIM(INPUT))),’TRUE’,’FALSE’)” is being performed in multiple ports.

The disadvantage with this approach is any changes to this validation need to be done in all the ports.

This can be addressed by creating a User Defined function and have the logic incorporated there.

Steps to Create User Defined Functions

Step 1 : Right-click on the User-Defined Functions folder in a repository folder in the Designer.

Click on “New”

Step 2: In Editor add the transformation logic / validation that needs to be performed.

Click ok and validate the UDF.

User Defined Function – Type:

Public if the function is callable from any expression. Private if the function is only callable from another user-defined function.

To Call User Defined functions from Port:

Informatica Error Logging

Informatica Inbuilt Error Logging feature can be leveraged to implement Row Error logging in a central location. When a row error occurs, the Integration service logs error information which can be used to determine the cause and source of the error.

These errors can be logged either in a relational table or in a flat file. When the error logging is enabled, the Integration service creates the error table or error log file the first time when it runs the session. If the error table or error log file exists already, then the error data will be appended.

Following are the activities that need to be performed to implement the Informatica Row Error Logging:

1. In the “Config object” tab of “Error Handling” option , set the “Error Log type” attribute to “Relational database” or “Flat File”. By Default error logging is disabled.

2. SET Stop On Errors = 1

3. If the Error Log Type is set to “Relational”, specify the Database connection & Table Name Prefix

Following are the tables which will be created by Integration service and which will be populated as and when the error occurs.

PMERR_DATA

Stores data and metadata about a transformation row error and its corresponding source row.

PMERR_MSG

Stores metadata about an error and the error message.

PMERR_SESS

Stores metadata about the session.

PMERR_TRANS

Stores metadata about the source and transformation ports, such as name and datatype, when a transformation error occurs.

4. If the Error Log Type is set to “Flatfile”, specify the “Error log file directory” and “Error log file name”

Database Error Messages and the Error messages that Integration service writes to Bad File/ Reject file can also be captured and stored in the Error log tables / Flat files.

Following are the few database error messages which will be logged in the Error Log Tables / Flat files.

Error Messages

Cannot Insert the value NULL into column ‘<>’, table ‘<>’

Violation of PRIMARY KEY constraint ‘<>’

Violation of UNIQUE KEY constraint ‘<>’

Cannot Insert Duplicate key in object ‘<>’

Row Error Logging Implementation

Advantages

Since the Informatica Inbuilt feature is leveraged, the Error log information would be very accurate with very minimal development effort.

Pitfall

Enabling Error logging will have an impact to performance, since the integration service processes one row at a time instead of block of rows.

Key components-Informatica PowerCenter 8.x Suite

The fundamental components of the Informatica PowerCenter 8.x Suite, the key components are

1. PowerCenter Domain

2. PowerCenter Repository

3. Administration Console

4. PowerCenter Client

5. Repository Service

6. Integration Service

PowerCenter Domain

A domain is the primary unit for management and administration of services in PowerCenter. Node, Service Manager and Application Services are components of a domain.

Node

Node is the logical representation of a machine in a domain. The machine in which the PowerCenter is installed acts as a Domain and also as a primary node. We can add other machines as nodes in the domain and configure the nodes to run application services such as the Integration Service or Repository Service. All service requests from other nodes in the domain go through the primary node also called as ‘master gateway’.

The Service Manager

The Service Manager runs on each node within a domain and is responsible for starting and running the application services. The Service Manager performs the following functions,

• #Alerts. Provides notifications of events like shutdowns, restart
• #Authentication. Authenticates user requests from the Administration Console, PowerCenter #Client, Metadata Manager, and Data AnalyzerDomain configuration. Manages configuration details of the domain like machine name, port
• #Node configuration. Manages configuration details of a node metadata like machine name, port
• #Licensing. When an application service connects to the domain for the first time the licensing registration is performed and for subsequent connections the licensing information is verified
• #Logging. Manages the event logs from each service, the messages could be ‘Fatal’, ‘Error’, ‘Warning’, ‘Info’
• #User management. Manages users, groups, roles, and privileges

Application services

The services that essentially perform data movement, connect to different data sources and manage data are called Application services, they are namely Repository Service, Integration Service, Web Services Hub, SAPBW Service, Reporting Service and Metadata Manager Service. The application services run on each node based on the way we configure the node and the application service

Domain Configuration

Some of the configurations for a domain involves assigning host name, port numbers to the nodes, setting up Resilience Timeout values, providing connection information of metadata Database, SMTP details etc. All the Configuration information for a domain is stored in a set of relational database tables within the repository. Some of the global properties that are applicable for Application Services like ‘Maximum Restart Attempts’, ‘Dispatch Mode’ as ‘Round Robin’/’Metric Based’/’Adaptive’ etc are configured under Domain Configuration

Infa- Architecture Power Center 8