7
Debugging Mappings

Warehouse Builder provides debugging capabilities for data flows within the Mapping Editor. These debugging features, available from the toolbar and debug menu in the Mapping Editor, are only enabled after you begin a debug session and connect to a valid target schema. You can then run a mapping in debug mode using a defined set of test data to follow the flow of data as it is extracted, transformed, and loaded and ensure that the designed data flow is behaving as expected. If you find problems, you can correct them and restart the debug session to ensure that the problems have been fixed before proceeding to deployment.

The following topics explain the debug process and how it can be used in the Mapping Editor:

About Debug Mode in the Mapping Editor

After you start a debug session, the Mapping Editor displays additional panels intended to provide detailed information about the debug session. Additional debug buttons in the toolbar and the Debug menu are also enabled after the debug session begins.The status bar along the bottom of the Mapping Editor also contains information about the status of the debug session.

Figure 7-1 Mapping Editor in Debug Mode

Text description of initiali.gif follows.

Text description of the illustration initiali.gif

The left bottom panel contains the following tabs:

Messages: Displays all debugger operation messages. These messages let you know the status of the debug session. This includes any error messages that occur while running the mapping in debug mode.
Breakpoints: Displays a list of all breakpoints that you have set in the mapping. You can use the check boxes to activate and de-activate breakpoints.
Test Data: Displays a list of all data objects used in the mapping. The list also indicates which data objects have test data defined.

The right bottom panel includes Step Data and watch point tabs that contain input and output information for the operators being debugged. The Step Data tab contains information about the current step in the debug session. Additional tabs can be added for each watch you set. These watch tabs allow you to keep track and view data that has passed or will pass through an operator regardless of the currently active operator in the debug session. Operators that have more than one input group or more than one output group display an additional drop down list that enables you to select a specific group.

The debug buttons in the toolbar, as shown in Figure 7-2, and Debug menu options become enabled only after the debug session begins. Table 7-1 describes each of these buttons.

Figure 7-2 The Mapping Editor Debug Tools

Text description of mapedit1.gif follows.

Text description of the illustration mapedit1.gif

Table 7-1 Mapping Editor Debug Tools

Icon	Action	Description
Text description of the illustration tb_start.gif	Debug Start	Click this button to start the debug session.
Text description of the illustration tb_end.gif	Debug End	Click this button to end the debug session.
Text description of the illustration tb_reini.gif	Debug Re-initialize	Click this button to re-initialize the debug session. Re-initialize re-deploys the debug code and resets the debug session.
Text description of the illustration tb_resum.gif	Resume	Click this button to resume the debug session. The debug session will run until the next breakpoint. If there are no breakpoints it will finish debugging the mapping.
Text description of the illustration tb_pause.gif	Pause	Click this button to pause the mapping you are debugging. This functionality is not enabled in the current release.
Text description of the illustration tb_step.gif	Step	Click this button to step through an operator row by row.
Text description of the illustration tb_skip.gif	Skip	Click this button to skip through an operator. All rows for that operator will be processed.
Text description of the illustration tb_rset.gif	Reset	Click this button to reset the debug session and start debugging from the beginning.
Text description of the illustration tb_brkpt.gif	Set Breakpoint	Click this button to set a breakpoint on the selected operator. If a breakpoint already exists on the operator, clicking this button removes the breakpoint.
Text description of the illustration tb_wch.gif	Set Watch Point	Click this button to set a watch on the selected operator. If a watch point already exists on the operator, clicking this button removes the watch.

Starting a Debug Session

To start a debug session:

From the Mapping Editor, select Debug and then Start. You can also click the Start Debug button from the toolbar.

The Mapping Editor switches to debug mode with the debug panels appearing in the bottom of the editor and a Connection Information dialog displays.
Specify connection information for the target schema you are using for debug runs.

The generated code is deployed to this schema. You can connect to another target schema while you are in a debug session by selecting Re-connect from the Debug menu.

After the connection has been established, a message displays to indicate that you may want to define test data. If you have previously defined test data, then you will be asked if you want to continue with initialization. In order to debug a mapping, each source or target operator must be bound to a database object and test data must be defined for the database object. By default, the operators in the mapping are connected to local sources and targets based on matching object names.

Defining Test Data

Every source or target operator in the mapping is listed on the Test Data tab in the left bottom panel. It also contains the object type, the source, and a check mark that indicates that the database object has already been bound to the source or target operator. The object type listed on the tab is determined by whether or not the columns in the data source you select matches the columns in the mapping operators. There are two possible types: Direct Access and Deployed as View. If there is an exact match then the type is listed as Direct Access. If you choose a data source with columns that do not match the mapping operator columns, you can choose how you want the columns mapped. This object would then be deployed as a view when you run the mapping and the type will be listed as Deployed as View.

Use the Edit button to add or change the binding of an operator as well as the test data in the bound database objects. Before you can run the mapping in debug mode, each listed source or target operator must be bound and have a check mark. The need to have test data defined and available in the bound database object depends on what aspect of the data flow you are interested in focusing on when running the debug session. Typically, you will need test data for all source operators. Test data for target operators is usually necessary if you want to debug loading scenarios that involve updates or target constraints.

Figure 7-3 Test Data Tab

Text description of testdata.gif follows.

Text description of the illustration testdata.gif

To define or edit test data:

From the Test Data tab in the Mapping Editor, select an operator from the list and click the Edit button.

The Define Test Data dialog displays, as shown in Figure 7-5.
Specify a Access Name for the object.

To bind an existing database object, type the name into the Access Name field or click the Browse button to search for an existing database that you want to use. The Choose Entity dialog displays as shown in Figure 7-4. Use this dialog to create or select a database link and schema. Then select a database object from the list of entities and click OK.

Figure 7-4 Choose Entity Dialog

Text description of ora_ent.gif follows.

Text description of the illustration ora_ent.gif

To create and bind a new table and copy the existing set of test data shown in the test data spreadsheet, click the Create New Table button on the Define Test Data dialog. For more information, see "Creating New Tables".

To edit data, check the User Data Edit Mode check box.

This allows you to create specific test data necessary to debug particular scenarios for which no suitable data can be found in the bound database object. You can click on each of the cells in the test data panel and edit the values.

To restrict the number of rows selected to be used as test data, check the Row Count check box and set number of rows. By default, the row count is set to 100 rows.

This allows you to limit the amount of data in the debug session.

Figure 7-5 Test Data Definition Dialog

Text description of def_test.gif follows.

Text description of the illustration def_test.gif

Creating New Tables

If you create a new table using the Create New Table button on the Define Test Data dialog, the name of the table will be the name of the data operator prefixed by DBG_. If this name exists in the target, it will be suffixed by a sequence number to guarantee a unique object name. The table is created in the target schema you specified when you started the debug run. The debugger will not automatically drop that table so that you can always re-use it for other sessions. Constraints are not carried over for the new table.

You can edit test data at anytime using the Define Test Data dialog. If you change the binding of the operator to another database object, you must re-initialize the debug session to implement the change before running the mapping again in debug mode.

Note:

The data loaded in the target definitions will be implicitly committed. If you do not want the target objects updated, you should create copies of target objects using the Create New Table button.

Setting Breakpoints

If you are interested in how a specific operator is processing data, you can set a breakpoint on that operator which will cause a break in the debug session. This allows you to proceed quickly to a specific operator in the data flow without having to go through all the operators step by step. When the debug session gets to the breakpoint, you can run data through the operator step by step to ensure it is functioning as expected. Breakpoint settings are not stored when you close the mapping.

To set or remove a breakpoint:

From the Mapping Editor, click on an operator and then select Debug and then Set Breakpoint. You can also click the Breakpoint button on the toolbar to toggle the breakpoint on and off for the currently highlighted operator.

If you are setting the breakpoint, the name of the operator set as a breakpoint appears in the list on the Breakpoints tab on the left bottom panel. If you are removing the breakpoint the name is removed. Use the Clear button on the Breakpoint tab to remove breakpoints.
Uncheck or check the breakpoints on the Breakpoint tab in order to disable or enable them.

Figure 7-6 Breakpoints in Debug Mode

Text description of the illustration brkpt.gif

Setting Watches

The Step Data tab on the right bottom panel always shows the data for the current operator. If you want to keep track of data that has passed through any other operator irrespective of the active operator, you can set a watch.

Use watches to track data that has passed through an operator or in the case of sources and targets, the data that currently resides in the bound database objects. You can also set watch points on operators after the debug run has already passed the operator and look back to see how the data was processed by an operator in the data flow.

To set a watch:

From the Mapping Editor, click on an operator and then select Debug and then Set Watch. You can also click the Set Watch button on the toolbar to toggle the watch on and off.

The name of the operator will appear as an additional tab on the right bottom panel bottom containing the input and or output groups for the operator as shown in Figure 7-7.
To remove a watch, again select the operator and use the watch button on the toolbar, use set watch from the debug menu or use toggle debug watch from the right mouse button menu.

Figure 7-7 Watches as Separate Tabs on Right-bottom Panel

Text description of the illustration watch.gif

Running the Mapping

After you have defined the test data connections for each of the data operators, you can initially generate the debug code by selecting Re-initialize from the Debug menu, or by clicking the Re-initialize button on the toolbar. Warehouse Builder generates the debug code and deploys the package to the target schema you specified. Figure 7-8 shows an initialized mapping that is ready for debugging.

Figure 7-8 Initialized Mapping Ready for Debugging

Text description of the illustration initiali.gif

You can choose to run the debug session in one of the following modes:

Continue processing until the next breakpoint or until the debug run finishes by using the Resume button on the toolbar or the associated menu item.
Process row by row using the Step button on the toolbar or use the associated menu item.
Process all remaining rows for the current operator by using the Skip button on the toolbar or the associated menu item.
Reset the debug run and go back to the beginning by using the Reset button or the associated item from the debug menu.

Selecting a Leading Source and Path

A mapping may have more than one source and more than one path to debug. If a mapping has more than one source then Warehouse Builder will ask you what leading source to begin with. For example, when two tables are mapped to a joiner, you will have to select the leading source table.

There may be multiple paths that the debugger can walk through after it has finished one path. This is for example the case when you use a splitter. Having finished one path the debugger will ask you whether you would like to complete the other paths as well.

The mapping finishes if all target operators have been processed or if the maximum number of errors as configured for the mapping has been reached. The debug connection and test data definitions are stored when you commit changes to the Warehouse Builder repository. Breakpoint and watch settings are not stored and must be re-set each time you open a mapping.

As the debugger runs it generates debug messages whenever applicable. You can follow the data flow through the operators. The active operator is indicated by a red box surrounding the operator as shown in Figure 7-9.

Figure 7-9 The Mapping Debugger

Text description of the illustration debug.gif

Step Data Panel

If an operator has more than one input or output group then the debugger will have a drop down list in the upper right corner, above the input or output groups. Use this drop down list to select the group you are interested in. This applies both to the step data and to a watch. Figure 7-10 shows an example for the join operator.

Figure 7-10 Multiple Input Groups for a Join Operator

Text description of ingroups.gif follows.

Text description of the illustration ingroups.gif

Debugging Mappings with Correlated Commit

If you begin a debug session for a mapping that has the Correlated Commit parameter set to ON, the mapping is not debugged using paths. If Correlated Commit is set to OFF, the mapping is debugged using one path at a time. All other paths are left unexecuted and all other targets are not loaded unless the you reach the end of the original path and then chooses to go back and execute another path in the mapping. If Correlated Commit is set to ON, all paths are executed and all targets are loaded during the initial stepping through the mapping regardless of what path is chosen. Also, if one of the targets has a constraint violation for the step, then none of the targets are loaded for that step.

For example: You have a mapping that has a source S1, connected to a splitter that goes to two targets T1 and T2.

If Correlated Commit is OFF, the mapping is debugged starting with S1. You can then choose either the path going to T1 or the path going to T2. If you choose the path to T1, the data going to T1 is processed and displayed, and the target T1 is loaded. After T1 is completely loaded, you are given the option to go back and execute the other path and load target T2.

If Correlated Commit is ON, the mapping is also debugged staring with S1 and you are given the option of choosing a path however in this case, the path you choose only determines the path that gets displayed in the mapping editor as you step through the data. All paths are executed simultaneously. This is also how a mapping using Correlated Commit gets executed when the deployable code is run.

Re-Initializing a Debug Session

If you have made changes to the mapping, or have bound source or target operators to different database objects, then you must re-initialize the debug session in order to continue debugging the mapping with the new changes. The re-initialize button on the toolbar or the re-initialize menu item in the debug menu can be used to re-generate and re-deploy the debug code. The mapping debug session will start from the beginning after re-initialization.

Scalability

Scalability when debugging a mapping applies both to the amount of data that is passed as well as to the number of columns displayed in the step data panel. The define test data dialog provides a row limit that you can use to limit the amount of data that flows through the mapping. Also, you can define your own data set by creating your own table and manipulating the records manually.

To restrict the number of columns displayed on the step data window or on a watch tab you can use display sets. By default every operator has a display set ALL and a display set MAPPED to display only the mapped attributes. You can manually add display sets on sources by using the mapping editor directly. Select the use display set option under the right mouse button on an input or output group to select the display set.

Restrictions

These issues include functions currently included in the mapping debugger that have not yet been enabled as well as functions that intend to be added in future releases.

Mappings run using the debug mode in the Mapping Editor are intended to be used for debug purposes only. Mappings run from the Mapping Editor are not as performant as mappings that are run using the Deployment Manager. This is attributed to the set up of temporary objects necessary to support the debugging capabilities. Use the Deployment Manager to run mappings.
You cannot pause an active debug run using the pause button on the toolbar or the associated item in the debug menu.
Mapping statistics will be provided in a future release. The statistics will appear as an additional tab on the left bottom panel.
You cannot use the Runtime Audit Browser to view the results of a mapping run in debug mode.
Breakpoint and watch settings are not preserved between debug sessions.
Only mappings that would be implemented as a PL/SQL package can currently be run in debug mode.
The following mapping operators are not supported when running mappings in debug mode:
- Advanced Queue
- Flat File
- Source Table using from an ABAP application

7 Debugging Mappings