Limitations of Pushdown Optimization - PDO

Limitations of Pushdown optimization.

• Source and Target and Lookups must be in same database server for full Pushdown.
• Expressions which are not supported by databases like data_diff () cannot be used in Expression transformations, the solution is to used SQL override.
• Variable Ports in Expression transformation is not allowed, the solution is to use SQL override.
• When Using SQL override, you need to Allow temporary view for pushdown in session properties.
• When Using Sequencer you need to Allow temporary sequence for pushdown in session properties.
• Sorter and Sequence cannot be used in the same pipeline.
• The unconnected lookup should have return port else you will get otplkp.cpp error while doing a preview of data.
• If you use pushdown optimization, you cannot override the ORDER BY clause or suppress the generated ORDER BY clause with a comment notation.

pmrep - infacmd - pmcmd commands

pmrep command : pmrep is used to perform repository metadata administration tasks, such as listing repository objects.

infacmd command : infacmd is used to perform service-related functions, such as creating or removing a Repository Service.

infasetup command : infasetup to back up, restore, define, and delete domains, and to define and update nodes

pmcmd command : pmcmd is used to communicate with PowerCenter Integration Services in order to manage the Informatica workflows (start, stop, recover, …) 

Informatica ETL : Performance Tips

While doing Insert and Update on Target table, Update Strategy is poor in performance. Without using update strategy, how do you perform Insert and Updates? How do you design the mapping?

We can create two mappings. One for inserting the new records and, another one is for updating the existing record,
In the Update mapping, we have to connect the key column and columns which have to get updated of the target table. In the session only update the target rows have to check.

Informatica ETL : Convert Any date to end of day i.e. 23:59:59 or 11:59:59

SET_DATE_PART(
        SET_DATE_PART(
           SET_DATE_PART(v_Your_date_Port, 'HH', 23)
                                      , 'MI', 59)
                            , 'SS', 59)

Environment Variables now working - Command Task

If you are struggling to get a script (which is referring to some environment variable) working vai command task but the same script will just work fine on the command line try restarting the Informatica server, as for newly created environment Informatica server will not be aware of unless restarted.


Please subscribe on my YouTube Channel.

Update of record failing using Update Strategy

Check following

Go to Session Properties -> ensure we have set DataDriven

Also to further Debug, you can set Tracing Level to Verbose Data on the updateStartegy.

Please subscribe on my YouTube Channel.

Informatica variable not getting set - using setvariable

Please subscribe on my YouTube Channel.



There could multiple reasons as to why the Variable is not getting set as expected.

In my scenario, My SQL override didn't pick any data to be loaded to the target table.

The SETVARIABLE function executes only if a row is marked as insert or update. SETVARIABLE ignores all other row types and the current value remains unchanged.

At the end of a successful session, the PowerCenter Integration Service compares the final current value of the variable to the start value of the variable.

The PowerCenter Integration Service does not save the final value of a mapping variable to the repository when any of the following conditions are true:

• The session fails to complete.
• The session is configured for a test load.
• The session is a debug session.
• The session runs in debug mode and is configured to discard session output. 

If you always want your variable to be set after every run then the workaround I used it to do a union of your SQL override with dummy data row, implies you will always have at least one row to be inserted in the target table.
you later can delete this extra row using post-session SQL.


select col1, col2 from yourTargetTable where conditionThatDoesntReturnAnyRow=0
union
select 'dummy1','dummy2'