Troubleshoot Informatica Mapping Using Verbose Mode Session Log
Debugger is a great tool to troubleshoot your mapping logic, but there are instances where we need to go for a different troubleshooting approach for mappings. Session log file with verbose data gives much more details than the debugger tool, such as what data is stored in the cache files, how variables ports are evaluated. Such information helps in complex tricky troubleshooting.

For our discussion, lets consider a simple mapping. In this mapping we have one lookup transformation and an expression transformation. Below shown is the structure of the mapping. We will set up the session to debug these two transformations. 
Troubleshoot Informatica Mapping Using Session Log with Verbose Data

Setting Up the Session for Troubleshooting

Before we can run the workflow and debug, we need to setup the session to get log file with detailed verbose data. We can set the session to get verbose data from all the transformations in the mapping or specific transformations in the mapping.

For our demo, we are going to collect verbose data from the lookup and expression transformation.

We can set up the session to for debugging by changing the Tracing Level to Verbose Data as shown in below image. Here we are setting the tracing level for the lookup transformation.
Troubleshoot Informatica Mapping Using Session Log with Verbose Data
As we mentioned, we are setting up the Tracing Level to Verbose Data for the expression transformation as well as shown below.
Troubleshoot Informatica Mapping Using Session Log with Verbose Data
Note : We can override the tracing level for all the individual transformations at once from Configuration Object -> Override Tracing property.

Read and Understand the Log File

Once you open the session log file with verbose data, you going to notice a lot more information that we normally see in a log file.

Since we have are interested in the data transformation details, we can scroll down through the session log and look for transformation thread.     

Below shown part of the log file; details what data is stored in the lookup cache file. The highlighted section shows the data is read from the lookup source LKP_T_DIM_CUST{{DSQ}} and is build into  LKP_T_DIM_CUST{{BLD}} cache. Further you can see the values stored in the cache file.
Troubleshoot Informatica Mapping Using Session Log with Verbose Data
Further down through the transformation thread, you can see three records are passed on to LKP_T_DIM_CUST from the source qualifier SQ_CUST_STAGE. You can see the Rowid in the log file. 
Troubleshoot Informatica Mapping Using Session Log with Verbose Data
Lookup transformation out put is send out to next transformation EXP_INSERT_UPDATE. 
Troubleshoot Informatica Mapping Using Session Log with Verbose Data
You can see what data is received by EXP_INSERT_UPDATE from the Lookup transformation. Rowid is helpful to track the rows between the transformations.
Troubleshoot Informatica Mapping Using Session Log with Verbose Data
Since we have enabled verbose data for the expression transformation as well, additional to the above details, you will see how data is passed into and out of the expression transformation. But skipped from this demo.

Pros and Cons

Both the debugger tool and debugging using verbose data got its own plus and minuses.

Pros

  • Faster : One you get the hang of verbose data, it is faster debugging using session log file than the debugger tool. You do not have to patiently wait to get info from each transformation like debugger tool.
  • Detailed info : Verbose data gives much more details than the debugger, such as what data is stored in the cache files, how variables ports are evaluated and much more, which is useful in detailed debugging.
  • All in one place : We get all the detailed debugger info in one place, which help you go through how rows are transformed from source to target. You can see only one row at a time in debugger tool. 

Cons

  • Difficult to understand : Unlike debugger tool, it requires an extra bit of effort to understand the verbose data in session log file.
  • No user interface : No user interface is available, all the debugging info is provided in text format, which might not be a proffered way for some.
  • Lot of info : Session log file with verbose data gives much more details than the debugger tool, which is some times irrelevant for your troubleshooting.
Hope you enjoyed this tutorial, Please let us know if you have any difficulties in trying out this debugging approach or share us if you use any different methods for your debugging.

Share with your friends

Readers Comments