24 Mappings and Process Flows Reference

Analyze Table Sample Percentage

When you select the "Analyze Table Statements" option, Oracle Warehouse Builder estimates when gathering statistics on the target tables. After data is loaded into the target tables, statistics used for cost-based optimization are gathered on each target table. You can set this parameter to the percentage of rows in each target table used for this analysis.

Bulk Size

Use Bulk Size to specify the number of rows in each bulk for PL/SQL Bulk Processing. Oracle Warehouse Builder uses the Bulk Size parameter only when the "Bulk Processing Code" option is selected and the operating mode is set to row-based. For more information, see Oracle PL/SQL Reference Guide.

Chunk Execute Resume Task

This parameter specifies whether the parallel chunking task must be resumed. Select False to run a new parallel chunking task. Select True to resume the parallel chunking task. At run time, you can override the value set for this parameter by using the Input Parameters dialog box.

Chunk Force Resume

This parameter specifies whether the parallel chunking task must be resumed if it has been stopped due to errors. Set this parameter to True to force Oracle Warehouse Builder to resume the parallel chunking task. At run time, you can override the value set for this parameter by using the Input Parameters dialog box.

Chunk Number of Times to Retry

This parameter specifies the number of times that Oracle Warehouse Builder attempts to resume a failed parallel chunking task. Enter a numeric value that specifies the number of attempts. At run time, you can override the value set for this parameter by using the Input Parameters dialog box.

Chunk Parallel Level

This parameter specifies the number of threads that must be used to process the chunks in parallel. Enter a numeric value for this parameter. Setting this parameter to NULL or zero causes the mapping to be run serially.

At run time, you can override the value set for this parameter by using the Input Parameters dialog box.

Commit Frequency

Commit frequency applies only to non-bulk mode mappings. Bulk mode mappings commit according to the bulk size.

If you set the Default Operating Mode to row-based and deselect "Bulk Processing Code", then use the Commit Frequency parameter to determine the number of rows to be processed before a commit operation. Oracle Warehouse Builder commits data to the database after processing the number of rows specified in this parameter.

If you select the "Bulk Processing Code" option, set the Commit Frequency equal to the "Bulk Size". If the two values differ, then Bulk Size overrides Commit Frequency and a commit operation is implicitly performed for every bulk size.

Default Audit Level

Use Default Audit Level to indicate the audit level used when executing the package. Audit levels dictate the amount of audit information captured in the run time schema when the package is run. The audit level settings are:

• None: No auditing information is recorded in run time.

• Statistics: Statistical auditing information is recorded in run time.

• Error Details: Error information and statistical auditing information is recorded in run time.

• Complete: All auditing information is recorded in run time. Running a mapping with the audit level set to Complete generates a large amount of diagnostic data, which may quickly fill the allocated tablespace.

Default Operating Mode

For mappings with a PL/SQL implementation, select a default operating mode. The operating mode, you selected can greatly affect mapping performance. For details on how operating modes affect performance, see "Set-Based Versus Row-Based Operating Modes". You can select one of the following operating modes:

• Set based: A single SQL statement that inserts all data and performs all operations on the data is generated. This increases the speed of Data Manipulation Language (DML) operations. Set based mode offers optimal performance but minimal auditing details.

• Row based: Statements that process data row by row are generated. The select statement is a SQL cursor. All subsequent statements are PL/SQL. Because data is processed row by row, the row-based operating mode has the slowest performance but offers exhaustive auditing details.

• Row based (Target Only): A cursor select statement is generated and attempts are made to include as many operations as possible in the cursor. For each target, Oracle Warehouse Builder generates a PL/SQL insert statement and inserts each row into the target separately.

• Set based fail over row based: The mapping is run in set based mode. If an error occurs, then the execution fails and the mapping is started over again in the row-based mode. This mode is recommended for use only in test environments and is not recommended for use in production environments.

• Set based fail over row based (Target Only): The mapping is first run in set based mode. If an error occurs, then the execution fails over to Row based (Target Only) mode. This mode is recommended for use only in test environments and is not recommended for use in production environments.

Default Purge Group

Default Purge Group is used when executing the package. Each audit record in the run time schema is assigned to the purge group specified.

Maximum Number of Errors

Use Maximum Number of Errors to indicate the maximum number of errors enabled while executing the package. Execution of the package terminates when the number of errors exceeds the maximum number of errors value.

The Maximum Number of Errors parameter applies to the count of errors for the entire mapping run, whether run in set-based, row-based, or failover modes. Consider the following cases:

• Maximum number of errors is set to 50 and the mapping is run in set-based mode. The data did not load successfully. One error resulted from failure of the set-based load DML statement. The mapping return status is WARNING.

• Maximum number of errors is set to 50, the mapping is run in set-based mode, and Enable Constraint parameter is set to false. The data is loaded successfully, but 60 constraint violation errors occurred during reenabling of the constraint. The mapping return status is ERROR.

• Max number of errors is set to 50 and the mapping is run in row-based mode. Some of the data loaded successfully, but with many errors. The mapping terminates after reaching the 50th error. The mapping return status is ERROR.

• Max number of errors is set to 50 and the mapping is run in set-based failover to row-based mode. The data did not load successfully in the set-based mode. One error resulted from the failure of the set-based load DML statement. Some of the data loaded successfully in the row-based mode, but with many errors. The mapping terminates after reaching the 49th error in the row-based mode because there was one error counted in set-based mode. The mapping return status is ERROR.

Number of Threads to Process Chunks

The Number of Threads to Process Chunks parameter represents the number of threads used to process the chunks of source data. Setting this parameter to zero means that the mapping is not run in parallel.

ANSI SQL Syntax

If you select this option, ANSI SQL syntax is generated. Otherwise, Oracle SQL syntax is generated.

Commit Control

Automatic: This is the default setting. Oracle Warehouse Builder loads and then automatically commits data based on the mapping design. This setting is valid for all mapping types. For multiple targets in a single mapping, data is committed based on target by target processing (insert, update, delete).

Automatic Correlated: Automatic correlated commit is a specialized type of automatic commit that applies only to PL/SQL mappings with multiple targets. Oracle Warehouse Builder considers all targets collectively and commits or rolls back data uniformly across all targets.

The mapping behavior varies according to the operating mode that you select. For more information about automatic correlated commit, see "Committing Data from a Single Source to Multiple Targets".

Manual: Select manual commit control for PL/SQL mappings when you want to interject complex business logic or perform validations before committing data.

You have the following options for specifying manual commits:

• You can define the commit logic within the mapping as described in "Embedding Commit Logic into the Mapping".

• You can commit data in a process flow or from a SQL*Plus session as described in "Committing Data Independently of Mapping Design".

No Commit: If you set this option, then Oracle Warehouse Builder mapping does not issue a commit while the mapping run.

Analyze Table Statements

If you select this option, code is generated for analyzing the target table after the target is loaded, if the resultant target table is double or half its original size.

If the target table is not in the same schema as the mapping and you want to analyze the table, then you must grant ANALYZE ANY to the schema owning the mapping.

Enable Parallel DML

If you select this option, parallel DML is enabled at run time. Executing DML statements in parallel improves the response time of data-intensive operations in large databases that are present in a data warehouse.

Optimized Code

Select this option to improve performance for mappings that include the Splitter operator and inserts into multiple target tables. When this option is selected and the mapping is run by Oracle9i or later, a single SQL statement is generated (multi_table_insert) that inserts data into multiple tables based on the same set of source data.

The multiple table insert is performed only if this option is selected and the Oracle target module database is Oracle9i or later. The multiple table insert is performed only for mappings in set-based mode that include a Splitter operator, and does not include active operators, such as an Aggregator or Joiner operator, between the Splitter and the target. In addition, the multiple insert is available only for tables. It is not available for views, materialized views, dimensions, or cubes. Each target table must have fewer than 999 columns. For detailed instructions on how to create a mapping with multiple targets, see "Example: Creating Mappings with Multiple Targets".

Do not select this option for mappings run in row-based mode or for mappings run by Oracle8i server. Also, do not select this option when auditing information for individual targets is required.

When this option is selected, one total SELECT and INSERT count is returned for all targets.

Authid

Specifies the AUTHID option to be used while generating the code. The options that you can select are Current_User, Definer, or None.

Use Target Load Ordering

For PL/SQL mappings with multiple targets, you can generate code that defines an order for loading the targets. This is important when a parent-child relationship exists between two or more targets in a mapping. The option is selected by default.

ERROR TRIGGER

Specify the name of the error trigger procedure in this field.

Bulk Processing Code

If this configuration parameter is selected and the operating mode is set to row-based, Oracle Warehouse Builder generates PL/SQL bulk processing code. PL/SQL bulk processing improves row-based ETL performance by collecting, processing, and writing rows in bulk, instead of doing it row by row. The size of each bulk is determined by the configuration parameter Bulk Size. Set-based mode offers optimal performance, followed by bulk processing, and finally by row-based mode. For more information, see Oracle PL/SQL Reference Guide.

Generation Mode

By default, when code is generated for a mapping, the code for all possible operating modes is generated. That is, if you set the "Default Operating Mode" to Set based, Oracle Warehouse Builder still generates code for all possible operating modes when Generation Mode is set to All Operating Modes. It enables you to switch the operating modes for testing purposes at run time.

Chunking Method

Use this parameter to select the method to be used for performing parallel chunking. Following are the options you can select for this parameter:

• None: Does not perform parallel chunking.

• Chunk By Number Column: Uses a numeric column in the source data as a basis for dividing the source data into chunks. When you select this option, you must set the parameters listed under the Chunk By Number Column node.

• Chunk by ROWID: Divides the source rows into chunks depending on the ROWID of the column in the source data. When you select this option, you must set the parameters listed under the Chunk By ROWID node.

• Chunk by SQL Statement: Uses a SQL statement to divide the source data into chunks. When you select this option, you must set the parameters listed under the Chunk By SQL Statement node.

Chunk Table

Use Chunk Table to select the source table on which parallel data chunking must be performed.

Chunk Column

The Chunking Column parameter is applicable when you perform parallel chunking using a numeric column in the source table. Use this parameter to select the source column based on which parallel chunking is performed.

Chunk Size

Use Chunk Size to specify the range of each chunk.

Chunk Type

Use chunk Type to specify how source data must be divided into chunks based on the ROWID of the source columns. The options you can select are By data blocks and By rows.

SQL Statement

Specify the SQL statement using which the source data is divided into smaller chunks.

SQL Statement Chunk Type

Use this parameter to specify how the SQL statement, specified using "SQL Statement", chunks the source data. Select one of the following options:

• NUMBER_COLUMN: Data is chunked using a numeric column in the source data.

• ROWID: Data is chunked using the ROWID of columns.

Chunking Strategy

The Chunking Strategy parameter enables to perform serial chunking. Select Serial to perform serial chunking on the source data. Select None to disable serial chunking for the PL/SQL mapping.

Use LCR APIs

By default, this setting is enabled and DML is performed using LCR APIs if available. If no LCR APIs are available, then the standard DML is used.

Database Link

This parameter is maintained for backward compatibility only.

In previous releases, you could select a database link by name from the list. Source operators can be configured for schemas and database links, but targets can be configured for schemas only. Sources and targets can reside in different schemas, but they must reside in the same database instance.

Location

This setting specifies the location that is used to access the source or target operator.

Conflict Resolution

Enable this setting to detect and resolve any conflicts that may arise during DML operations using the LCR APIs.

Schema

This parameter is maintained for backward compatibility only.

In previous releases, you could link the mapping to a particular schema by clicking on the Schema field and typing a name.

Partition Exchange Loading

Use the settings in this section to enable Partition Exchange Loading (PEL) into a target table. For specific information about each of these settings and additional information about how to design mappings for PEL, see "Improved Performance through Partition Exchange Loading".

Hints

Define loading or extraction hints. Application developers often develop insights into their data. For example, they know that a query runs much faster if a set of tables is joined in one order rather than another. Oracle Warehouse Builder can incorporate these insights into the generated SQL code packages as SQL Optimizer Hints.

When you select a hint from the Hints dialog box, the hint appears in the Existing Hints field. Enter additional text as appropriate in the Extra Text column. The editor includes the hint in the mapping definition as is. There is no validation or checking on this text.

You can define loading hints for mappings that load data in INSERT or UPDATE mode. By default, commonly used hints such as APPEND and PARALLEL are added. For all loading modes other than INSERT, the APPEND hint causes no effect and you can choose to remove it.

Hint is available during mapping configuration. To configure a hint:

1. In the Projects Navigator, expand the Databases folder, and then the required module.

2. In the module, expand the Mappings node.

3. Right-click the required mapping and select Configure.

   The Configuration tab displays the configuration parameters of the mapping.

4. In the Configuration tab, expand the required operator type and then expand the required operator.

5. Expand the Hints node and click the Ellipsis button to the right of a hint type to enter a hint.

For information about optimizer hints and how to use them, see Oracle Database Performance Tuning Guide.

Constraint Management

Configure the following Constraint Management parameters:

• Exceptions Table Name: All rows that violate their foreign key constraints during reenabling are logged into the specified exceptions table. No automatic truncation of this table is performed either before or after the load. Constraint violations are also loaded into the run time audit error tables.

  For SQL and PL/SQL loading, if you do not specify an exceptions table, invalid rows are loaded into a temporary table located in the default tablespace and then loaded into the Runtime Audit error table. The table is dropped after the load.

  If you are using SQL*Loader direct path loading, you must specify an exception table. Consult the SQL*Loader documentation for more information.

• Enable Constraints: If you set this option to False, Oracle Warehouse Builder disables constraints on the target tables, loads data, and then reenables the constraints. Constraint violations found during reenable are identified in the run time audit error table and, if specified, loaded into an exceptions table. If you set this option to True, then Oracle Warehouse Builder does not manage constraints and the data from the source is loaded into the target table.

  When you disable constraints, loading is quicker because a constraint check is not performed. However, if exceptions occur for any rows during reenabling, the constraints for those rows remain in a nonvalidated state. These rows are logged in the run time audit error table by their ROWID. You must manually inspect the error rows to take any necessary corrective action.

  The disabling and enabling of constraints happens on the target table. When the Enable Constraints parameter is set to True, the constraints on the target table is disabled before the loading of data, and is reenabled after the loading of data. When the constraints are reenabled, the entire table is scanned and rows that violate the constraints are logged in the exceptions table. These rows are reported as constraint violation errors in the audit browser.

  Consider a scenario where the target table is empty and the Enable Constraints parameter is set to True. Initially suppose that the source table has 10 rows, of which 2 rows violate the constraint on the target table. When the mapping is run, the constraints on the target table are first disabled, then data is loaded (all 10 rows), and then constraints on the target table are reenabled. When the constraints are reenabled, the 2 rows that violate the constraints are logged into the exceptions table. The audit browser reports that there are 2 constraint violation errors.

  Later, the mapping is again run with a new source table containing 20 rows, of which 5 rows violate the constraint on the target table. After the data is loaded into the target table (all 20 rows), the target table has 30 rows. When the constraints on the target table are reenabled, 7 rows are logged in to the exceptions table and reported as constraint violation errors in the audit browser. These include the 5 rows reported newly and the 2 rows reported initially. This is because Oracle Warehouse Builder scans the entire target table, which means that all 30 rows are checked and therefore the 2 rows with violations from the first data load are included. Oracle Warehouse Builder cannot identify only the new rows added when the mapping was run the second time. Therefore, unless you truncate the target table before each data load, you always see the constraint violations from the previous data loads reported each time.

  Setting the Enable Constraints option to True is subject to the following restrictions:

  • For set-based operating mode, the foreign key constraints on the targets are disabled before loading, and then reenabled after loading. This parameter has no effect on foreign key constraints on other tables referencing the target table. If the load is done using SQL*Loader instead of a SQL or PL/SQL package, then a reenable clause is added to the .ctl file.

  • For set-based fail over to row-based and set-based fail over to row-based (target only) operating modes, the deselect setting disables the foreign key constraints on the targets before loading and then reenables them if the load succeeds in set-based mode. This setting has no effect on foreign keys referencing other tables. If the load fails over to row-based, then loading repeats in row-based mode and all constraints remain enabled.

    Note:

    Constraint violations created during reenabling does not cause the load to fail from set-based mode over to row-based mode.

  • For row-based or row-based (target only) operating modes, all foreign key constraints remain enabled even if the option is not selected.

  • For the TRUNCATE/INSERT DML type, the deselect setting disables foreign key constraints on other tables referencing the target table before loading, and then reenables the constraints after loading, regardless of the default operating mode.

SQL*Loader Parameters

When you have a Table operator that contains inputs from a flat file, you must configure the following SQL*Loader Parameters properties:

• Partition Name: Indicates that the load is a partition-level load. Partition-level loading enables you to load one or more specified partitions or subpartitions in a table. Full database, user, and transportable tablespace mode loading does not support partition-level loading. Because incremental loading (incremental, cumulative, and complete) can be done only in full database mode, partition-level loading cannot be specified for incremental loads. In all modes, partitioned data is loaded in a format such that partitions or subpartitions can be selectively loaded.

• Sorted Indexes Clause: Identifies the indexes on which the data is presorted. This clause is enabled only for direct path loads. Because the data sorted for one index is not usually in the right order for another index, you specify only one index in the SORTED INDEXES clause. When the data is in the same order for multiple indexes, all indexes can be specified. All indexes listed in the SORTED INDEXES clause must be created before you start the direct path load.

• Singlerow: Intended for use during a direct path load with APPEND on systems with limited memory, or when loading a small number of records into a large table. This option inserts each index entry directly into the index, one record at a time. By default, SQL*Loader does not use SINGLEROW to append records to a table. Index entries are stored in a temporary area and merged with the original index after the load. Although this method achieves better performance and produces an optimal index, it requires extra storage space. During the merge, the original index, the new index, and the space for new entries all simultaneously occupy storage space. With the SINGLEROW option, storage space is not required for new index entries or for a new index. Although the resulting index may not be as optimal as a freshly sorted one, it takes less space to produce. It also takes more time, because additional UNDO information is generated for each index insert. This option is recommended when the available storage is limited. It is also recommended when the number of records to be loaded is small compared to the size of the table. A ratio of 1:20 or less is considered small.

• Trailing Nullcols: Sets SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.

• Records To Skip: Invokes the SKIP command in SQL*Loader. SKIP specifies the number of logical records from the beginning of the file that should not be loaded. By default, no records are skipped. This parameter continues loads that have been interrupted for some reason. It is used for all conventional loads, for single-table direct loads, and for multiple-table direct loads when the same number of records is loaded into each table. It is not used for multiple-table direct loads when a different number of records is loaded into each table.

• Database File Name: Specifies the names of the export files to import. The default extension is .dmp. Because you can export multiple export files, you may must specify multiple file names to be imported. You must have read access to the imported files. You must also have the IMP_FULL_DATABASE role.

Use Enclosure Character

Select this parameter to enclose SQL identifiers in the generated code of CT mappings. Oracle Warehouse Builder supports delimited identifiers in CT mapping across heterogeneous database systems.

For example, if the Use Enclosure Character parameter is selected, and the enclosure character is set to square brackets, the SQL statement generated is as follows:

SELECT [My Empno], [My Emp Name] FROM [My Employees]

By default, the enclosure character used for Oracle Database, IBM DB2 UDB, and SQL Server is the double quotation marks. You can set the enclosure character that is necessary for each platform by using OMB*Plus. For example, to change the enclosure character for the SQL Server platform to square brackets, use the following OMB*Plus command.

OMBALTER PLATFORM 'SQLSERVER' SET PROPERTIES (ENCLOSURE_CHAR) VALUES ('/[/]');

For a CT mapping that has the Use Enclosure Character parameter selected to work correctly, you must set up the correct enclosure character for any new platform that you create. For example, you create a new platform called MYSQL that references a MySQL database. Your CT mapping uses a table from the MySQL database and you want to generate code using the double quotation mark as the enclosure character. Before you generate code for the CT mapping, you must set the enclosure character for the MYSQL platform using the following statement:

OMBALTER PLATFORM 'MYSQL' SET PROPERTIES (ENCLOSURE_CHAR) VALUES ('"')

SQL Loader Data Files

Use the SQL Loader Data Files section to set the "Source File Operator" parameter. For CT mappings that contain one or more Flat File operators, you can set parameters for the associated data files.

Source File Operator

Use this property to specify the Flat File operator that must be associated with the current data file.

If your CT mapping contains only one Flat File operator, setting the Source File Operator parameter is optional. However, if your CT mapping contains multiple Flat File operator, you must set this parameter. This parameter enables Oracle Warehouse Builder to understand the relationship between the data file and the Flat File operator.

This property is available starting with Oracle Warehouse Builder 11g Release 2 (11.2.0.2).