Informatica Tips 

Informatica General Notes

HOW TO Use a Joiner transformation instead of Lookup Transformation

HOW TO De-normalize data/pivot rows into columns


General Notes

Almost all the transformation functions have been rewritten to be faster and more scalable. There are new type promotion and compatibility rules; strict data type conversion rules.
Functions in mapping transformations are affected, as in Expression and Aggregator.
In addition Workflow expressions, like link conditions, Assignment tasks, Decision tasks. There is no change in SQL Override, Lookup override, Target Update Overrides.
Functionally there is no change, output of each function is as expected (for example, SQRT(4) will return 2, not something else).


This section applies when upgrading from PowerCenter or PowerMart 5.0, 5.1 and 6.0 only.

5.2. Using NULL values in boolean expressions

In the Designer and Workflow Manager, you may pass a null value to an expression that contains logical operators. Effective with version 6.1, in expressions that contain the logical AND operator, combining a null value with a Boolean expression produces the following results:

NULL AND TRUE = NULL - This expression no longer returns FALSE .

NULL AND FALSE = FALSE - This expression no longer returns NULL .

5.3. Using mixed datatypes in expressions

The following changes have been made to expressions with mixed datatypes:

·Using strings in numeric functions is no longer allowed.

a = 1 + ‘1’ is no longer allowed

·Use of string values as numeric arguments to functions is no longer allowed.

SUBSTR( STRING , ‘1’, 10) is no longer allowed

·Mixed datatypes with comparison operator are no longer allowed

123 = ‘123’ is invalid

·Handling of Integer and Decimal overflow conditions has been improved.

·Scientific functions allow only numeric operands.

5.4. Datatype handling with IIF/DECODE statements

IIF and DECODE have always assumed the TRUE result datatype for the FALSE result. This limitation is removed. Now it will take widest of both results.

IIF(1 = 2, 0, 1.7)

This expression will result in 2 in previous releases, whereas it results into 1.7 in PowerCenter 7.1, which is correct. 0 is an integer and 1.7 (decimal) can not fit into integer, so previous release rounds it to 2. In case of 7.1, it will use decimal data type for FALSE result in this case.

5.5. TO_NUMBER has been replaced with TO_FLOAT

The TO_NUMBER function has been replaced with the TO_FLOAT() in new mappings.

TO_FLOAT(date) is no longer allowed, parser error “Operand datatypes not Compatible”.

5.6. IS_DATE , IS_NUMBER , and IS_SPACES accept only string values as input. They no longer accept non-string values.

When the upgrade process encounters one of these functions that evaluates numeric data instead of string data, it adds TO_CHAR to the value.
For example, you have the following expression, where
ORDER_NUM is a numeric port:


The upgrade process produces the following expression:


5.7. TO_DATE accepts only string values as input. It no longer accepts datetime values.

When the upgrade process encounters a TO_DATE expression that evaluates datetime data instead of string data, it adds TO_CHAR to the value.
For example, you have the following expression, where
ORDER_DATE is a datetime port:


The upgrade process produces the following expression:


5.8. TO_DECIMAL and TO_FLOAT accept only string or numeric values as input. In previous versions of PowerCenter, they accepted any datatype except binary.

When the upgrade process encounters a TO_DECIMAL or TO_FLOAT expression that evaluates datetime data instead of numeric or string data, it adds TO_CHAR to the value.
For example, you have the following expression, where
ORDER_DATE is a datetime port:


The upgrade process produces the following expression:


5.9. TO_INTEGER accepts only string or numeric values as input.

For example, you have the following expression, where ORDER_DATE is a datetime port:


The upgrade process produces the following expression:


5.10. IIF Expression Changes during the repository upgrade process

To maintain backward compatibility, a PowerCenter 5.x repository upgrade will make all necessary changes to mappings to comply with the new expression rules. Appropriate functions will be added to expressions as needed.


·An integer port with expression of 1 + ‘1’ will be modified to 1 + TO_INTEGER(‘1’) during the upgrade.

·The expression IIF(ISNULL(FIELD1),0,FIELD1) will be modified to IIF(ISNULL(FIELD1),0,TO_INTEGER(FIELD1)) during the upgrade.


If you want new expression behavior, remove the TO_INTEGER call.
During upgrade an Activity Log is created. It will have a list of invalided mappings during upgrade. It also lists different category of changes and objects in each category, Mappings, Mapplets, Worklets and Workflows.

5.11. Mixing single and nested aggregator functions in one Aggregator is no longer valid.

In previous versions of PowerCenter, you could create a single Aggregator transformation that contained both single-level and nested aggregate functions.In PowerCenter 7.1, you cannot combine nested and single-level aggregate functions in a single Aggregator transformation. When the upgrade process encounters a mapping that contains an Aggregator transformation with both single-level and nested aggregate functions, it marks the mapping invalid.


6.1. General Notes

Upgrading from 6.x there are improvements to the interface for each of the clients.
Upgrading from PowerCenter 5.1.x there are many changes including three new client tools - the Workflow Manager, the Workflow Monitor and the Repository Server Administration Console.
The PowerCenter 5.x Server Manager client tool has been replaced with two new client tools, the Workflow Manager and the Workflow Monitor. Workflow Manager is analogous to Server Manager and Workflow Monitor is analogous to the Monitor pane in Server Manager.
Immediately after upgrading a PowerCenter 5.x repository you will not see any Session objects under the Session node of the Workflow Manager and there is no longer a batches node. Sessions and Batches have all been converted to Workflows during the upgrade. If you expand the Workflows node you will see Workflow objects with the same name as the original session or batch. To run your upgraded Workflows right mouse click and select Run, or click the Run button on the toolbar, the same as in the PowerCenter 5.x Server Manager.
The execution order of Batches (now called Workflows) is now represented graphically. Go to the Tools menu, Workflow Designer, then drag a Workflow into the workspace. You will see your sessions laid out with links between showing the execution order.

6.2. WORKFLOW MANAGER: Session must now be re-usable in order to export it stand-alone.

In order to export a standalone session (without the Workflow) in 7.1 it is necessary to first make the session re-usable.
A session task that is part of a Workflow and is not re-usable will not export.

6.3. REPOSITORY SERVER ADMINISTRATION CONSOLE: Microsoft Management Console (MMC) version must be at least 1.2

The new Repository Server Administration Console client tool requires the Microsoft Management console which is a core component of Microsoft. To make sure that the version of MMC being used is correct (1.2) go to Start | Run | MMC and then Help | About.

6.4. WORKFLOW MONITOR: Workflow log not created by default on upgraded 5.x sessions

After upgrading a 5.x session the resulting workflow does not contain an entry for the workflow log file name. The workflow messages will be in the server log.

6.5. Designer will not import XML source and target definitions if Internet Explorer 5.x is installed

It is necessary to have Internet Explorer 6.x installed on the client machine in order to import and export objects in Designer.

7. Server

7.1. General Notes

With the new functionality of Workflows there are new things to consider on the server side.
Also, with the new Repository Server functionality there are new things to consider when connecting to a repository.

7.2. New "Server Name" configuration parameter must be set before starting the server.

When configuring the upgraded server make sure you add the server name entry. This entry must match the name of the server as entered in Workflow Manager. If this is not entered the following error will occur when starting the server:

LM_36022: The server name was not found in the repository

7.3. Upgrade process modifies server names with certain characters

In previous versions of PowerCenter, you could use the following special characters in session, batch, connection, and server names: ! @ # $ % ^ & ( ) { } [ ]<> . , ? | \ " ; : ~ ' * / -

You cannot use these special characters for session, connection, or server names in PowerCenter 7.1. For sessions and connections, the upgrade process replaces these special characters with an underscore (_). You may need to update pmrep and pmcmd scripts to reflect the upgraded names.
If you have server names that use these characters, you can continue to use them. However, if you edit a server, the Workflow Manager validates the server name and requires you to remove the special characters. You can use a dash (-) in a server name, but you cannot use it as the first character in the name.

7.4. Session Parameter information is no longer in the session log but is now in the workflow or server log

If you use parameter files look for this logging information in the workflow log if there is one defined for the workflow or, if not, look in the server log.

8. Mappings and Sessions

8.1. General Notes

Various mapping and session functionality has been updated and improved causing the changes in behavior noted here.

8.2. Sessions that have a sorted input aggregator and an Update Strategy will now fail if source data is not sorted

Sessions that are configured with sorted input in the aggregator and with an update strategy are now permitted, but now they will fail whereas before the sorted input option was ignored. Either remove the sorted input option or make sure the source data is sorted.

8.3. $Source and $Target will not resolve for partition sessions

Previously a session with only one partitioned target or source would always resolve $Source or $Target, now it will not resolve and the session will fail. Use the new $Source and $Target connection values for the session properties to resolve these.

8.4. Sessions with no target options selected default after upgrade

Sessions that have none of the SQL target options set (insert, delete, update as update, update as insert, update else insert) will be defaulted to the settings insert, update as update, and delete.

8.5. Session run history is not upgraded.

After upgrading, session execution history in the OPB_SESSION_LOG and OPB_SESS_TARG_LOG tables is not upgraded to the new workflow tables. The data remains in the old tables.

8.6. The PowerCenter Server fails to match a lookup value using ROUND(TO_DECIMAL( STRING )) in a string lookup port expression

The upgrade process replaces TO_INTEGER() with ROUND(TO_DECIMAL()) . When you have ROUND(TO_DECIMAL()) in a lookup port with a String datatype, the PowerCenter Server does not match the port value with a string integer value in the lookup cache or table in low precision mode.

For example, suppose you have the following expression in a previous version of PowerCenter or PowerMart:


The upgrade process changes this expression to:


ROUND(TO_DECIMAL()) returns values as a Double with trailing zeros in low precision mode.


Manually change expressions in string lookup ports of a Lookup transformations. Change ROUND(TO_DECIMAL()) to TO_INTEGER() .

8.7. Rows may be rejected if using a Target Update Override and the SET clause contains numeric datatypes

Rows may be rejected when using a Target Update Override and the SET clause contains numeric datatypes for Microsoft SQL Server, DB2, Sybase and Teradata targets.

8.8. Partitioned sessions with a stored procedure running in Unicode may drop rows.

Rows may be rejected with a Numeric Overflow error when using a Stored Procedure transformation in the mapping. This only occurs when the PowerCenter Server is installed on Unix and the DataMovementMode=UNICODE with multiple partitions in the session.


Either set the DataMovementMode=ASCII or reduce the number of partitions to one.

8.9. Concatenation of passive and active transformations no longer valid.

In the prior versions you could create a mapping that concatenates a passive transformation with an active transformation. The Designer no longer allows you to create this type of mapping, but up to PowerCenter version 7.0, you could continue to upgrade the mapping, and run sessions against it. Now that the PowerCenter Server validates mappings at initialization, it fails the session with the following error:

MAPPING> TT_11152 < transformation_name >: Concatenation error 

8.10. Input transformation errors counted towards error threshold.

Prior to version 7.1, the PowerCenter Server failed to count input transformation errors in the error threshold. The PowerCenter Server now counts input transformation errors toward the error threshold. As a result, sessions may fail sooner than in previous versions.

8.11. Sequence Generators

Effective with version 7.1, the PowerCenter Server now pulls a block of sequence numbers at a time from the Sequence Generator transformation. As a result, sessions with Sequence Generator transformations may have different output in PowerCenter 7.1.


When you connect the CURRVAL port in a Sequence Generator transformation, the PowerCenter Server processes one row in each block.

8.12. Conversion errors

<>Prior to version 7.1, when the PowerCenter Server encountered a conversion error passing data from a Joiner transformation, it dropped the row from all downstream targets.

<> <>Effective with version 7.1, when the PowerCenter Server encounters a conversion error passing data from a Joiner transformation, it drops the row from that pipeline branch. It does not drop the row from subsequent branches if no conversion error occurs.


8.13. Upgrading Incremental Aggregation Files

If you want to retain incremental aggregation files, you must first upgrade the repository to a version that has btree format changes, and then run a session that uses the incremental aggregation. Informatica recommends running a session with empty input.
When you upgrade from a version before 5.0, Informatica recommends that you upgrade to 5.1.2.
When you upgrade from a version between 5.1 and 6.0, you can upgrade to 6.1 or 7.x.
For example, use the following steps to upgrade from PowerCenter 4.7.3 / PowerMart 1.7.3:
1. Upgrade to PowerCenter 5.1.2.
2. Run a session that will update the incremental aggregation files you want to retain. Use empty input.
3. Upgrade to PowerCenter 6.1 or 7.x.
4. Run a session that will update the incremental aggregation files you want to retain. Use empty input.

8.14. Maintaining Sequential Processing for Mappings With Joiner Transformations

In releases prior to version 7.0, the PowerCenter Server read sources sequentially. Effective with PowerCenter 7.0, the PowerCenter Server can read sources concurrently. PowerCenter 7.1.2 maintains full backward compatibility for sequential processing. When you enable the server flag Pmserver6.xjoinersourcecompatibility , the PowerCenter Server processes all sources sequentially.

8.15. Partitioning Sessions with External Loaders

Some external loaders cannot load from multiple output files. As a result, in previous releases, if you created multiple partitions in sessions that used an external loader that could not load from multiple output files, the session might fail.
PowerCenter 7.1.2 introduced the use of round-robin partitioning to route the data to a single target file. You choose an external loader for the first partition only.


This also means that the PowerCenter Server flag SupportNonPartitionedLoaders is no longer needed in 7.1.2.

8.16. External Loaders load to named pipes

When you upgrade to PowerCenter 7.x from PowerCenter version 5.x or 6.x, all external loaders load to named pipes. If you want to load data to staged flat files, configure your external loader settings.

8.17. Sessions with Target-Based Commit Reach Error Threshold Sooner

In a previous release, the PowerCenter Server did not check the error threshold when it ran a target-based commit session. It did not fail the session when it reached the error threshold.
Now, the PowerCenter checks the error threshold and fails a session when it reaches the error threshold. As a result, sessions that succeeded before may now fail.

8.18. Flat file sourced that use non-staged FTP may not read all rows.

A session that reads from a flat file using the FTP option without staging the file may stop reading the rows before the end of the file. To avoid this issue use staged FTP.

9. Repository

9.1. General Notes

Repository access has changed, as with PowerCenter 6.x the repository is accessed using native database client drivers instead of through ODBC. The client tools connect to a Repository Server over the network which in turns connects to the Repository database natively just as in PowerCenter 6.x.

9.2. Upgrade: DB2 repository fails to upgrade (CR 85539)

During a repository upgrade, an error occurs when the upgrade runs the script file db2cc-ci.sq_, located in the repository server installation directory. An error message similar to the following log entry appears in the activity log for the upgrade:

[Error while executing script file C:\Informatica\v712\RepositoryServer\bin\sql\db2cc-ci.sq_]
Repository upgrade failed.
An error occurred while upgrading the repository.

Edit the file db2cc-ci.sq_, changing the order of two lines. Reverse the order of the following two lines:


The new code should look like the following example:


Then run the upgrade again.

9.3. Upgrade: Upgraded Teradata repository generates database constraint violations or duplicate key errors when you try to save (CR59390, 51723)

When you save a Teradata repository, it fails with database constraint violations or duplicate key errors.
Workaround :
After you upgrade a Teradata repository from 6.x to 7.x, back up the repository, delete it, and restore the repository from the backup before you use it. The upgrade adds new columns to primary keys. To change the keys, back up and restore the repository.

9.4. Upgrade: Upgraded Teradata repository fails to start (CR 51723)

The Repository Server fails to start an upgraded Teradata repository if the repository database does not have the correct primary indexes.Workaround: After you upgrade a Teradata repository, back it up and then restore the backup file. This causes the Repository Server to create new primary indexes for the repository tables. Back up and restore the repository before you can start it.

9.5. METADATA: Informatica Metadata Reporter (IMR) and Runtime Metadata Reporter (RMR) have been replaced by PowerCenter Metadata Reporter (PCMR)

IMR and RMR have been replaced with PCMR which runs on PowerAnalyzer 4.1.1 and the JBoss web server. Please refer to the PCMR documentation for further details on installing PCMR.

9.6. METADATA: "Folder Versions" do not exist anymore.

Previously "versioned" folders will be upgraded as separate folders. This has been replaced by the "Team-based Development" option.

9.7. METADATA: After upgrading the repository extra tables from previous version and possibly temporary tables will not be deleted

Certain tables used no longer used or only used during upgrade may be left behind after upgrading the repository since no tables are deleted when upgrading. This has no side effects and these table's will not be backed up when backing up the repository in 7.1.

9.8. REPOSITORY SERVER: The Repository Server on UNIX will not start if the soft limit for the number of files a process can have open simultaneously is greater than the hard limit.

Configure the hard limit for the number of files a process can have open simultaneously to a value greater than or equal to the soft limit. PowerCenter also recommends configuring the maximum number of semaphores per ID to a value greater than both limits. Configure the following kernel parameters with the specified values:


The soft limit for the number of files a process can have open simultaneously. Configure this parameter for 1024 files.


The hard limit for the number of files a process can have open simultaneously. The hard limit should be greater than or equal to the value specified for maxfiles. Configure this parameter for 1024 files.


The maximum number of semaphores per ID. You can configure this parameter in /usr/include/sys/sem.h. Configure this parameter for 2048 semaphores.

9.9. PMREP: Updatedbconfig option no longer supported with pmrep

To achieve the same functionality in PowerCenter 7.1 use the Createconnection command to create a new database connection. You can then use the Switchconnection command to replace the existing database connection with the new database connection in all sessions that use the existing database connection.

9.10. PMREP: The pmrep Restore and Delete commands have been moved to pmrepagent.

The command syntax and functionality are the same as in previous versions. If you use the Restore and Delete commands in a pmrep script, edit the script to replace pmrep with pmrepagent.

9.11. SYBASE/MS SQL SERVER: Not Null option must be turned on when upgrading repository

Select the "Allow NULLS by default" option in the database properties so that all these columns will be treated as NULL. Upgrade the repository after checking this option.

9.12. MAINTENANCE: "Update Statistics" should be run as often as possible

In previous versions it was recommended to run "Update Statistics" now in 7.1 it has become necessary to do this on a routine basis. This can now be done using the new UpdateStatistics pmrep Repository Command. Refer to chapter 15 of the Repository Guide for further details regarding this feature.

9.13. POWERCENTER.e: Sample Repository and the "PowerCenter.e" features have been discontinued.

If you are currently using any of the PowerCenter.e/Sample Repository features (weblog reader, Perl parser, etc.) most of this functionality has been incorporated into PowerCenter 7.1. For other functionality refer to Devnet (