The DataBee Create/Edit Load Manager Rule Form
The rules in an extraction set identify the rows in the source schema which will need to be copied to the target schema. The actual transfer of the rows from the source schema to the target is handled by special rules called loader rules. There is one loader rule for every table to be loaded. It would be inconvenient and visually difficult, to have a loader set with thousands of rules for schemas which contain thousands of tables. Therefore, to increase the usability of the loader set, all of the load rules are collected together into a manager rule which schedules their execution, monitors their progress and reports errors (if any).
The Create/Edit Load Manager Rule form is used to create and edit DataBee Load Manager rules. Load Manager rules are designed to manage the process of the loading the target tables with copies of the rows identifed by a completed extraction set. A Load Manager can load data in one of two ways: by pulling the data through a database link or by direct schema to schema copy. In the example screen shot above, the form is editing an existing Load Manager rule configured to load the data via a database link named DTBLink.
Load Manager rules are only present in loader sets and only a single Load Manager rule is usually implemented. Typically the Load Manager rule is created automatically by the Set Designer application when a new loader set is created. Load Manager rules can be created manually, if required, using the options on the New Loader Rule form.
After the extraction set has successfully completed, a list of ROWIDs will have been stored in the extraction set temporary tables located in the source schema. These ROWIDs are accessed by the loader rule and used to identify the rows in the source schema which will need to be moved to the target. It is important to realize that the loader set connects to the target schema and its rules execute there. This means that the extraction set temporary tables in the source schema must be visible to SQL statements running in the target schema. There are two ways this can be done: via a DBLink which runs from the target schema into the source schema or via direct schema to schema copy. Both of these methods will be discussed in detail below.
Important Note: The Set Loader application, like all components of the DataBee software, is installed on a PC and connects to the target schema via TNSNames and SQL*Net. The role of the DataBee software on the PC is to manage the process, at no time does it move the data being loaded travel to the PC. In other words, the data is transferred directly between the source and target schema it does not travel from the source schema down to the installation PC and then back up to the target schema. Even the ROWID values identified by the Set Extractor application never leave the source schema.
Load by DBLink: The extracted rows are loaded into the target via a database link. This link runs from the target schema into the source schema. The link must be created by a DBA, or other user, in the target schema - the DataBee software cannot automatically create this link. Normally the database link connects to the schema containing the extraction set tables. Usually this is the source schema but can be a proxy schema if the extraction set used one. If the database link method of data transfer is used, the source and target schemas can be in the same or different databases.
Load by Direct Schema Copy: The process of loading the data via a database link is limited by the speed of the link. In other words, even though the load process is multi-threaded, there are limits to how fast data can pulled from the source to the target via a database link. The actual transfer rate varies depending on the network implementation but it is usually quite a bit slower than a direct copy of the data between tables within the same database. If the source and target schemas are in the same database then the loader set can directly copy the data from the source to the target. The subset target schema can then be used in place, exported and imported elsewhere or moved quickly by a mechanism such as transportable tablespaces. Note that the Set Loader application still connects to the target schema. This means that when using direct copy mode the target schema must, at minimum, be configured at the Oracle level with read permission on the tables in the source schema.
It can happen that there are differences between the column structure of a table in the source schema and its equivalent in the target schema. By default, the Load Manager rule assumes that the structure of the tables in the source and target schemas are identical. It is assumed that the columns in both tables have the same name and also have the same datatype. If this is not the case, the data being transferred must be adjusted during the copy process otherwise errors will be generated.
The transformation engine is a tool which can manipulate the column information as necessary. The transformation engine modifies the data stream as it is read from the source table and inserted into the target table. It can remove data columns, add data items and perform function based modifications such as TO_STRING or TO_DATE on the existing values. Detailed information on this topic can be found in the transformation engine help file.
In certain circumstances it is not desirable to load a table with rows. Tables can be removed from the load process by removing the check mark in the box next to the table name. It is important to be aware that if a table is not loaded then any foreign key relationships in which that table is involved as a parent will probably not enable. DataBee always extracts a referentially correct set of rows - if it extracted rows for that table then they need to be present in the target schema according to the defined rules in the extraction set.
A Load Manager rule is provided with the tables known to its Rule Controller when the rule is created. To refresh the list of tables known to the Load Manager rule, first refresh the tables in the Rule Controller using the Refresh Tables and Indexes button on Options tab of the edit Rule Controller form. This retrieves an up-to-date list of tables from the target schema. Once the Rule Controller has been updated, use the Refresh Table List button to refresh the list inside the Load Manager rule. If required, the checked or unchecked state of the tables, along with any transformation engine configurations, can be saved and then restored after the refresh using the Export Current Configuration and Import Current Configuration buttons.
The Number of Errors Permitted field configures the Load Manager to ignore a certain number (or all) of the errors received while processing the table operations. This enables the Load Manager to continue operating even if errors occur. Be aware that ignoring the errors in the Load Manager just permits the Load Manager to complete as many operations as possible before returning an error and stopping the execution of the masking set. In other words, even if errors are ignored, any errors which occur during the table load operations will mark the Load Manager rule as having failed and the error state will be reported to the DataBee software for handling once the actions have completed.
Important Note: Remember that the DataBee software is multi-threaded and that properly configured Rule Blocks are required to ensure the Load Manager rule runs in the appropriate order. Usually a disable mode Load Manager rule is configured with a rule block that runs it as the very first rule in the loader set and a second Load Manager rule is configured to run in enable mode as the very last rule.
Existing Load Manager rules can be edited by double clicking on them with the mouse. Load Manager rules are created by launching the New Load Manager form using the New Rule button located on the lower right hand corner of the main form in the Set Designer application.
How to Create a New Load Manager Rule