Debugging with Informatica's ETL can be confusing. It does take a bit of practice and could present with a big learning curve if you're just starting out.
Thanks Christine for putting this together.
Informatica Debugger Tips & Tricks
Debugger Toolbar Menu:
Starting the debugger:
Clicking on
Start Debugger from the toolbar will bring up the Debug Mapping wizard. Always select DWH2IntegrationServ from the drop down list, and “Use an existing session” when you want to debug a mapping.
Navigating in the debugger:
Once the debugger session is launched, there are many ways to navigate to the data that you want to see:
i) Next instance button: Walks through each transformation in the order that Informatica processes them.
ii) Step to Instance button: click on the transformation that you want to view and then click this button. The debugger will skip all transformations in between. Note that you can select any previous transformation from the drop down list in the Debugger Data Display window if you want to check what the data was at an earlier step in the mapping for the current row of data.
iii) Using breakpoints
Breakpoints allow you to set criteria to determine when you want the debugger to stop and display data
- click on the transformation where you want the debugger to stop, and then on the Edit Breakpoints button on the toolbar
- click
Add in the Edit Breakpoints window – notice that a breakpoint is displayed in the bottom window. If you click OK at this point, the debugger will stop by default on the selected transformation for each row of data in the mapping.
If you want to stop at a particular row of data, add a Condition by clicking on the Add button above the condition window:
Define your condition, select OK, and then use the green arrow Continue button in the debugger toolbar. You can add multiple conditions.
NOTE: if you really just want to look at one row of data and you’re selecting it from a large source table, it is more efficient to put a filter on the session in thecorresponding workflow rather than setting a breakpoint
Checking the data
i) Eyeballing:
Often it is enough to check the values as they show in the Debugger Data Display window for the different transformations to identify what the problem is in a mapping
ii) Evaluating expressions:
If eyeballing isn’t enough, you can right-click in the top-left corner of a transformation and select Evaluate Expression:
Enter the expression you want to evaluate in the Formula window, and then select Evaluate:
Informatica will validate the expression and then display the result:
Tips for evaluating expressions:
- You can open the transformation by double-clicking on it in the mapping window, copy an expression from your mapping, and then paste it into the Evaluate Expression formula window, rather than recreating it
- You can test whether an expression is true or false. For example, you could test if the expression above generates a date that is later than 1996 by changing the formula to:
TO_DATE(ZZRCRTDATE,
'YYYYMMDD') >
TO_DATE('19970101',
'YYYYMMDD')
Value will be 0 if the expression is false, or 1 if it’s true
- The evaluate expression option is available in Expression, Filter, Rank, Router and Update strategy transformations
- You can use Evaluate Expressions to display mapping variables or parameter variables in
Viewing target data
The target data is displayed in the Debugger Target Data Display window. The ROW-TYPE column tells you if a row is Inserted, Deleted, or Filtered from the target table.
Stopping the debugger
To shut down the debugger when it has paused on a transformation, click on the Stop the Debugger toobar button.
To stop the debugger when it is processing data, click on the Break Now button to pause the debugger and then you will be able to stop it.
Debugger “Gotchas”
- sometimes the debugger stops working and an error comes up saying that it failed to initialize. We have not found a work-around for this, other than restarting the Informatica service on the server.
- In our experience, it is very difficult to debug mappings that use joiners. Even with breakpoints the debugger seems to skip over transformations that come downstream from the join.
