28 Troubleshooting and Error Handling for ETL Designs

This chapter contains information about error logs and using DML error logging in Warehouse Builder. The chapter includes the following sections:

Inspecting Error Logs in Warehouse Builder

Scenario

While working with Warehouse Builder, the designers need to access log files and check on different types of errors. This case study outlines all the different types of error messages that are logged by Warehouse Builder and how to access them.

Solution

Warehouse Builder logs the following types of errors when you perform different operations:

Case Study

This case study shows you how to retrieve error logs after performing different operations in Warehouse Builder.

Validation Errors

In Warehouse Builder, you can validate all objects by selecting the objects from the console tree and then selecting Validate from the Object menu. After the validation is complete, the validation messages are displayed in the Validation Results window, as shown in Figure 28–1.

Figure 28-1 Validation Error Messages

Description of Figure 28-1 follows
Description of "Figure 28-1 Validation Error Messages"

You can also validate mappings from the Mapping Editor by selecting Mapping, then Validate. The validation messages and errors are displayed in the Validation Results window.

On the Validation tab of the Validation Results window, double-click an object name in the Object column to display the editor for that object. You can fix errors in the editor. Double-click a message in the Message column to display the detailed error message in a message editor window. To save the message to your local system, select Code in the menu bar, then select Save as File.

Warehouse Builder saves the last validation messages for each previously validated objects. You can access these messages at any time by selecting the object from the console tree in the Project Explorer, select View from the menu bar, and then click Validation Messages. The messages are displayed in the Validation Results window.

Generation Errors

After you generate scripts for Warehouse Builder objects, the Generation Results window displays the generation results and errors, as shown in Figure 28–2. Double-click an error under the Messages column on the Validation tab to display a message editor that enables you to save the errors to your local system.

Figure 28-2 Generation Results Window

Description of Figure 28-2 follows
Description of "Figure 28-2 Generation Results Window"

Deployment and Execution Errors

You can store execution or deployment error and warning message logs on your local system by specifying a location for them. In the Project Explorer, select the project. Then from the Tools menu, select Preferences. In the Preferences dialog box, click the Logging option in the object tree to the left. In the list box on the right, you can set the log file path, file name and maximum file size. You can also select the types of logs you want to store.

You can view this log of deployment and error messages from the Warehouse Builder console by selecting View from the menu bar, and then Messages Log. This Message Log dialog box is read-only.

Runtime Audit Browser

If an error occurs while transforming or loading data, the audit routines report the errors into the runtime tables. You can easily access these error reports using the Runtime Audit Browser (RAB). The RAB provides detailed information about past deployments and executions. These reports are generated from data stored in the runtime repositories. Click the Execution tab in the Execution reports to view error messages and audit details.

Name and Address Server Errors

If you are using the Name and Address cleansing service provided by Warehouse Builder, you can encounter related errors.

Name and address server start up and execution errors can be located at:

OWB_ORACLE_HOME\owb\bin\admin\NASver.log

If your Name and Address server is enabled in:

OWB_ORACLE_HOME\owb\bin\admin\NameAddr.properties:TraceLevel=1,

then it produces the log file NASvrTrace.log.

Using DML Error Logging

Error logging enables the processing of DML statements to continue despite errors being encountered during the statement execution. The details of the error such as the error code and the associated error message are stored in an error table. After the DML operation completes, you can check the error table to correct rows with errors. DML error logging is supported for SQL statements such as INSERT, UPDATE, MERGE, and multi-table insert. It is useful in long-running, bulk DML statements.

Warehouse Builder provides error logging for the tables, views, and materialized views used in set-based PL/SQL mappings. To enable error logging, you set the Shadow table name property of the table, view, or materialized view. DML error logging is supported only for target schemas created on Oracle Database 10g Release 2 or later versions.

About Error Tables

Error tables store error details. You can define error tables for tables, views, and materialized views only.

Error tables are used for the following purposes:

  • DML error logging (including physical errors)

  • Capturing logical errors when data rules are applied to tables, views, or materialized views

An error table is generated and deployed along with the base table, view, or materialized view. When you drop a data object, the shadow table associated with it is automatically dropped.

Error Tables and DML Error Logging

When DML error logging is enabled for a data object by setting the Shadow table name property for the object, the error table contains the following:

  • DML error columns, as described in Table 28-1.

  • All columns from the data object with which the shadow table is associated.

Table 28-1 DML Error Columns in Error Tables

Column Name Description

ORA_ERR_NUMBER$

Oracle error number

ORA_ERR_MESG$

Oracle error message text

ORA_ERR_ROWID$

Rowid of the row in error (for update and delete)

ORA_ERR_OPTYPE$

Type of operation: insert (I), update (U), delete (D)

ORA_ERR_TAG$

Step or detail audit ID from the runtime audit data. This is the STEP_ID column in the runtime view ALL_RT_AUDIT_STEP_RUNS.


For scalar data types in the source data object, if no data rules are applied to the data object, the columns in the error table are of data type VARCHAR2(4000). This allows physical data errors such as ORA-12899: value too large for column, to be captured. If data rules are applied, the columns in the error table are of the same data type as the source columns.

For example, the table TEST has the two columns C1, of data type NUMBER, and C2, of data type VARCHAR2(10). The error table generated for TEST will contain the DML error columns, C1, and C2. If no data rules are applied to TEST, the data type for both C1 and C2 will be VARCHAR2(4000). If data rules are applied to TEST, C1 will be NUMBER and C2 will be of data type VARCHAR2(10).

Error Tables and Data Rules

When one or more data rules are defined for a data object, the error table for this data object contains the following:

  • Columns from the data object

    These columns are of the same data type and precision as the ones in the data object.

  • DML error columns, as described in Table 28-1

  • Data rule columns

    The data rule columns store details such as the operator that caused the data rule violation, the cause of the error, severity, and the audit run details.

Using Error Tables for DML Error Logging and Data Rules

When you define data rules on a data object for which DML error logging is enabled, the error table generated by Warehouse Builder contains the columns from the data object, the data rules columns, and the DML error columns. The data type and precision of the columns from the data object are the same as the ones in the base data object. This could result in the failed inserts into the error table when errors occur during DML operations. For example, some errors, such as value too small, may cause error table insert failure.

Thus, if you want to perform DML error logging for a data object that has data rules applied, it is recommended that you create your own error tables. Ensure that the error table that you create contains the columns required for data rules and the DML error logging columns.

Enabling DML Error Logging

DML error logging is generated for set-based PL/SQL mappings if the following conditions are satisfied:

  • The Error table name property is set for the Table, View, or Materialized View operator.

  • The PL/SQL Generated Mode configuration property of the module that contains the mapping is set to Oracle 10gR2, Oracle 11gR1, or Default.

    If the value is set to Default, ensure that location associated with this module has the Version property set to 10.2 or 11.1.

To enable error logging for a data object:

  1. In the Project Explorer, right-click the data object for which DML error logging should be enabled, and select Open Editor.

    The Data Object Editor for the data object is displayed.

  2. On the canvas, select the data object.

  3. In the Properties panel, specify a value for the Shadow table name property.

If you do not specify a shadow table name for a data object, DML error logging is not enabled for that object. However, when a data object has data rules associated with it, if you do not specify a error table name for the object, Warehouse Builder creates an error table using a default name. For example, if the name of the table for which you specified data rules is EMP, the error table is called EMP_ERR.

When you use a data object in a mapping, the Error Table Name property for this data object is derived from the Shadow table name property of the data object.

Note:

If you modify the error table name for a data object (using the Shadow table name property), you must synchronize all the operators bound to this data object.

DML Error Logging and ETL

The execution of mappings that contain data objects for which DML error logging is enabled fails if any of the following conditions occur:

  • The number of errors generated exceeds the specified maximum number of errors for the mapping

    The default set for this value is 50. You can modify this value by setting the Maximum number of errors configuration property of the mapping. In the Project Explorer, right-click the mapping and select Configure. In the Maximum number of errors property, specify the maximum number of errors that can generated before the mapping execution is terminated.

  • Errors occur due to functionality that is not supported.

    See "DML Error Logging Limitations".

You can truncate the error table and delete error details generated during a previous load. This helps in housekeeping of the error tables. To truncate an error table before the map is executed, select the Truncate Error Table property of the operator bound to the data object that has DML error logging enabled.

The properties Roll up Errors and Select only errors from this property are not used for DML error logging.

The Error table name and Truncate error table properties of Table, View, or Materialized View operators are not used for row-based code.

DML Error Logging Limitations

DML error logging has certain limitations. DML error logging is not supported for non-scalar datatypes. In addition, each DML statement has specific limitations, which are listed in documentation related to that statement.

See Also:

Oracle Database SQL Language Reference for limitations on DML error logging for each DML statement

Depending on your error logging needs you can configure the table operator in a mapping to use the APPEND or NOAPPEND hint. For example, direct-path insert does not support error logging for unique key violations. To log unique key violations, use the NOAPPEND hint.

If you have an error table defined for a data object, you cannot upgrade the data object using the Upgrade option in the Control Center Manager. If you modify the Shadow table name property after the data object is deployed, you must first drop the data object and then redeploy it. If this data object was used in a mapping, ensure that you synchronize the mapping operator with the data object, drop the data object, redeploy the data object and the mapping.

Using Pseudocolumns ROWID and ROWNUM in Mappings

You can use the pseudocolumns ROWID and ROWNUM in mappings. The ROWNUM pseudocolumn returns a number indicating the order in which a row was selected from a table. The ROWID pseudocolumn returns the rowid (binary address) of a row in a database table.

You can use the ROWID and ROWNUM pseudocolumns in Table, View, and Materialized View operators in a mapping. These operators contain an additional column called COLUMN USAGE that is used to identify attributes used as ROWID or ROWNUM. For normal attributes, this column defaults to TABLE USAGE. To use an attribute for ROWID or ROWNUM values, set the COLUMN USAGE to ROWID or ROWNUM respectively.

You can map a ROWID column to any attribute of data type ROWID, UROWID, or VARCHAR2. You can map ROWNUM column to an attribute of data type NUMBER or to any other data type that allows implicit conversion from NUMBER.

Note that ROWID and ROWNUM pseudocolumns are not displayed in the Data Object Editor since they are not real columns.