Debugger in Informatica: Session, Breakpoint, Verbose Data & Mapping

As we discussed in previous topics, a mapping is a collection of source and target objects linked together by a set of transformations. These transformations consist of a set of rules, which define the data flow and how the data is loaded into the targets.

What is Debugger?

Debugger is a very handy utility in Informatica. When we execute the mapping, Informatica executes the mapping based on the transformation logic defined.

Its execution is similar to batch mode execution where we cannot see the intermediate results, or how the data is getting modified from transformation to transformation.

Many a times we get the data in the target which is not as we expected. With the help of debugger, we can analyze how the data is moving within the mapping. We can analyze the data row by row in a debugger.

In this tutorial - you will learn

• Steps to use debugger in a mapping
• Using breakpoint in debugger
• Tracing in Informatica
• How to set tracing level in a transformation

The use of debugger requires a session instance, we can either reuse an existing session which was created earlier to run the mapping, or we can create a new debug session instance. Since during debugging the data will be fetched from the source, so we have the option of what to do with that data.

• Either we can discard the data or
• The data can be loaded into the target

These options can be set during the configuration of the debugger.

To monitor the debugger, there are two windows in Informatica designer

• Target window
• Instance window

In target window, the target record which is going to be inserted into the target table is shown. In instance window, you can select any of the transformation instances of the mapping. After that, all the ports of that transformation are visible in this window, and you can analyze the data, how it is getting transformed.

Steps to use debugger in a mapping

Informatica designer provides the feature to use the debugger to debug mappings. It means we can debug and analyze any existing mapping. The requirement for the debugger is that the mapping should be a valid mapping.

In the exercise, we will debug our existing mapping "m_emp_emp_target" which we created earlier. In our source data, we will check for a particular record of employee_name ='SCOTT'. For Scott, we will verify the values are fetched in Informatica and whether the record is pushed to the target correctly or not.

Our source data for the "emp" table looks like this.

For the ename "SCOTT", using debugger we will check that what value for the "sal" (salary) column is fetched in the Informatica.

Step 1 – Open Informatica designer and in designer open the mapping "m_emp_emp_target" by double clicking on it. It will open source qualifier and target definition tables showing mapping inside Mapping Designer.

Step 2 – Now start the debugger

1. Click on the mappings menu option
2. Select Debugger from the options
3. Select "start debugger" option

Step 3 – Select next on debug mapping window

Step 4 – In the next window, you will get an option to select session type. You can use existing session, existing reusable session, or you can create a debug session instance.

When you use the existing session, Informatica will use the earlier session which you created for this mapping. When you select the create debug session instance, a new session instance will be created for debugging.

In this example, we will select existing reusable session instance. The integration service will be the default.

1. Select "use an existing session instance" option
2. Click next

Step 5 – In the new window, Informatica will prompt you to select the existing session for the debugging.

Select the session "s_m_emp_emp_target" and click next button

Step 6 - In the next window there will be an option "Discard target data", we select this option as during debugging we don't want to load any data in our target table.

1. Select option "Discard target data."
2. Select target table "emp_target"
3. Click finish button.

With the above steps, we have configured and started the debugger. Now we can debug the mapping.

Step 7 - The debugger has started, but it hasn't fetched any rows from the source. During debugging it fetches data from source row by row. To start fetch first row from the source

1. Select mappings option from the menu
2. Select debugger option
3. Select next instance option

Step 8 - In the instance window you will see the first fetched record from the source qualifier.

If you analyze the instance window, it will be showing the first record – ename "SMITH" row. This isn't the record which we are looking for. So we can move to the next instance.

Step 9 - To go to next instance (to move to next row)

• Press F10 button (shortcut for going to next instance, instead of going to mapping option then debugger option)

After moving to next instance, the data in instance window will change to next record.

Step 10 - Keep pressing F10 button, till you reach to the required record of "SCOTT" employee. Once you reach that point, the instance window will be like as shown below

This is the instance, which we were looking for. So in the instance window, we can analyze the data. Whether it is coming correctly, or any value is missing.

In the source data, the details of the "SCOTT" employee were like job = "ANALYST", manager id = "7566", sal "3000."

And in the debugger, for the employee "SCOTT" we have analyzed all these details, and they are matching with the source data, so we are assured that the records were fetched correctly by source qualifier.

Using breakpoint in debugger

When we are looking for a particular row of data like in the previous example, then instead of going to every instance of source data we can define breakpoints.

A breakpoint is a condition or a set of a condition defined during configuration of debugging such that when that condition becomes true the debugger halts to that particular instance.

For example if we define deptno=20 in a breakpoint, then whenever for a record if this condition is true, then the debugger will stop to that instance of data.

Step 1- To configure the breakpoint

1. Go to mappings menu
2. Select debugger option
3. Select "edit breakpoints" option

Step 2 – You will see an "edit breakpoint" window as shown below

Step 3 – In the breakpoint window, click on the add button

Step 4 – In the new window

1. Select the SQ_EMP as instance
2. Select OK button

Step 5 – The previous window will look as shown below

Step 6 – You have a breakpoint, now you have to define the breakpoint condition. To define breakpoint condition

1. Click on "add new condition" icon

A new row under condition tab will appear. Here we can configure the condition.

Step 7 - In the condition table

1. In port drop down, select "ENAME" option ( as we are debugging for record ename='SCOTT')
2. In the value box, enter value "SCOTT."
3. Select OK button

Step 8 – Now start the debugger as mentioned in the previous topic

Go to mapping menu -> select debugger option -> select start debugger -> select session instance

Step 9 – Once debugger is started

1. Select mappings menu
2. Select debugger option
3. Select continue option

The debugger will go to each instance of row itself and will check for the condition "ename=SCOTT". The record for which this condition is true, the debugger will halt on that instance. So when the data volume is high, then instead of going for each instance of source record you can configure a breakpoint, and you can directly jump to that record.

Once the Informatica is done with going through all the instances, the debugger is stopped automatically. If you want to analyze the data again, you have to restart the debugger again.

Tracing in Informatica

Whenever you execute a session for a mapping, a session log is generated. It consists of the details of the execution like

• Timing of the events
• Error details
• Individual transformation statistics etc.

Inside the transformations of a mapping, you can configure and set how much details Informatica writes inside the session log.

There are four levels which can be set for tracing inside the transformations.

Note: By default the tracing level is set to normal, it is recommended to use verbose data mode only when you are debugging the mapping. The terse mode can be used to improve the performance, as it writes the minimum details. So the overhead of writing log will be minimum.

How to set tracing level in a transformation

Step 1 – Open the mapping in Informatica designer, for which you want to set the tracing level

Step 2 – Double click on the transformation (Source Qualifier transformation "SQ_EMP")

It will open edit transformation window.

Step 3 – In the edit transformation window

1. Click the properties tab
2. Select the Tracing level option
3. From the drop down select Verbose data
4. Select OK button

Step 4 – Save the mapping and execute the mapping ( use ctrl+s keyboard shortcut to save the mapping)

Step 5- To view the log

1. Open workflow monitor and select the session which was executed in the last step
2. Click on "session log" option to open the session log for the mapping

This will open session log window for the mapping

The session log provides the detail about how your session got executed. It provides the timing details when execution started and stopped. It provides the basic idea about the performance. It mentions which database connection you are using, what parameter file you are using. It also summarizes about the source and targets by mentioning how many source records were fetched, how many records were loaded into the target, etc.

Step 6 – Scroll down in the log, and you can see additional log details captured including the data records.

In this way, you can set the tracing level in mappings to capture the additional details for debugging.

In this tutorial, you have learned how to set, configure and execute the debugger and how to set tracing level in mappings. These options provide you the ability to debug the mappings.