Search code examples
sqlinformatica-powercenter

How to export a Power Center mapping to SQL statements?

Is it always possible to export an Informatica PowerCenter mapping to a sequence of SQL statements? How would we do it? Plus points if we can do it programmatically.

My mapping is from a relational database to another relational database.

Solution

  • Well, Informatica actually makes it possible to convert the whole mapping to a INSERT-SELECT statement, as long as some conditions are met.

    • Most importantly: Pushdown Optimization option must be available (AFAIR it is licenced separately, hence not always available)
    • Session property Pushdow Optimization needs to be set to Full

    Then Pushdown Optimization Viewer (more info) allows you to take a sneak peek at the query being a result of complete mapping transformation into SQL statement.

    NOTE: This may also require enabling Allow Temporary View for Pushdown.

    NOTE2: This feature has a number of limitations (eg. it's not possible when local variables are used, etc.).