Mapping Debugger to Troubleshoot your Informatica PowerCenter ETL Logic
Debugger is an integral part of Informatica PowerCenter mapping designer, which help you in troubleshooting the ETL logical error or data error conditions in an Informatica mapping. The debugger user interface shows the step by step execution path of a mapping and how the source data is transformed in the mapping. Features like "break points", "evaluate expression" makes the debugging process easy.
Understand Debugger Interface
The debugger user interface is integrated with Mapping Designer. Once you invoke the debugger, you get few additional windows to display the debugging information such as transformation instance window to show how the data is transformed at a transformation instance and target window to show what data is written to the target.
- Instance Window : View how data is transformed at a transformation instance, This window gets refreshed as the debugger progresses from one transformation to other. You can choose a specific transformation from the drop down list to see how data looks like at that particular transformation instance for a particular source row.
- Target Window : Shows what data is processed into the target instance. You can see, if the record is going to get inserted, updated, deleted or rejected. If there are multiple target instances, you can choose the target instance name from the drop down window, to see its data.
- Mapping Window : Mapping window shows the step by step execution path of a mapping. It highlights the transformation instance which is being processed and shows the breakpoints setup on different transformations.
- Debugger Log : This window shows messages from the Debugger.
Below shown is the windows in the Mapping Designer, that appears when you run the Debugger.
Above image shows the mapping with one breakpoint set on the expression transformation. Target instance window is showing first two records set for update. And the Instance window is showing how the third record from the source is transformed in the expression EXP_INSERT_UPDATE.
Configuring the Debugger
Before the debugger can run we need to setup the break points and configure the session, which is to be used by mapping being debugged. Setting up break point is optional to run the debugger. But this option helps to narrow down the issue faster, especially when the mapping is pretty big and complex.
Creating Breakpoints
When you are running a debugger session, you may not be interested to see the data transformations in all the transformations instances, but specific transformations where you expect a logical or data error.
For example, you might want to see what is going wrong in the expression transformation EXP_INSERT_UPDATE for a specific customer record, say CUST_ID = 1001.
By setting a break point, you can pause the Debugger on specific transformation or specific condition is satisfied. You can set two types of break points.
- Error Breakpoints : When you create an error break point, the Debugger pauses when the Integration Service encounters error conditions such as a transformation error. You also set the number of errors to skip for each break point before the Debugger pauses.
- Data Breakpoints : When you create a data break point, the Debugger pauses when the data break point condition evaluates to true. You can set the number of rows to skip or a data condition or both.
You can start the Break point Window from Mapping -> Debugger -> Edit Breakpoints (Alt+F9) as shown in below image.
Below shown is a Data Break point created on EXP_INSERT_UPDATE, with condition CUST_ID = 1001. With this setting the debugger pauses on the transformation EXP_INSERT_UPDATE, when processing the CUST_ID = 1001.
In the same way, we can create error breakpoints on any transformation. Setting up break point is optional to run the debugger But this option helps to narrow down the issue faster, especially when the mapping is pretty big and complex.Configuring the Debugger
In addition to setting breakpoints, you must also configure the Debugger. Use the Debugger Wizard in the Mapping Designer to configure the Debugger against a saved mapping. When you configure the Debugger, enter parameters such as the Integration Service, an existing non-reusable session, an existing reusable session, or create a debug session instance for the mapping you are going to debug.
You can start the Debugger Wizard from Mapping -> Debugger -> Start Debugger (F9) as shown in below image.
From below shown window you choose the integration service. You choose an existing non-reusable session, an existing reusable session, or create a debug session instance.
Next window will give an option to choose the sessions attached to the mapping which is being debugged.
You can choose to load or discard target data when you run the Debugger. If you discard target data, the Integration Service does not connect to the target. You can select the target instances you want to display in the Target window while you run a debug session.
With this settings the mapping is ready to be debugged.Running the Debugger
When you complete the Debugger Wizard shown in the configuration phase in the step above, the Integration Service starts the session and initializes the Debugger. After initialization, the Debugger moves in and out of running and paused states based on breakpoints and commands that you issue from the Mapping Designer.
When the Debugger is in paused state, you can see the transformation data in the Instance Window.
After you review or modify data, you can continue the Debugger in the following ways. Different commands to control the Debugger execution is shown in below image. This menu is available under Mapping -> Debugger.
- Continue to the next break : To continue to the next break, click Continue (F5). The Debugger continues running until it encounters the next break.
- Continue to the next instance : To continue to the next instance, click Next Instance (F10) option. The Debugger continues running until it reaches the next transformation or until it encounters a break. If the current instance has output going to more than one transformation instance, the Debugger stops at the first instance it processes.
- Step to a specified instance : To continue to a specified instance, select the transformation instance in the mapping, then click Step to Instance (Ctrl+F10) option. The Debugger continues running until it reaches the selected transformation in the mapping or until it encounters a break.
Evaluating Expression
When the Debugger pauses, you can use the Expression Editor to evaluate expressions using mapping variables and ports in a selected transformation. This option is helpful to evaluate and rewrite an expression, in cause if you find the expression result is erroneous.
You can access Evaluate Expression window from Mapping -> Debugger -> Evaluate Expression.
Modifying Data
When the Debugger pauses, the current instance displays in the Instance window. You can make the data modifications to the current instance when the Debugger pauses on a data break point.
You can modify the data from the Instance Window. This option is helpful, if you want to check what would be the result if the input was any different from the current value.
Hope you enjoyed this tutorial, Please let us know if you have any difficulties in trying out mapping debugger and subscribe to the mailing list to get the latest tutorials in your mail box.