The section on the top is entitled Tools and contains the buttons that launch tools that test load functionality, enable or disable constraints and trigger or perform the data load. The section on the bottom entitled Load Options provides a number of useful fields to modify the load method for certain types of table.

The DataBee Set Loader Tools & Info Tab

Tools Section: The buttons in this section are the individual dialog boxes of the Load Wizard separated out for individual access. Pressing the buttons in turn starting at the Test User button and continuing to the Enable Constraints button performs each step of the Load Wizard in the appropriate sequence. It is probably best to use the Load Wizard to perform loads - however, if the individual components are required they are available here. All of the buttons launch dialog boxes and the schema user they will operate on must be chosen from the drop down list box in the upper left of the panel.

What the buttons do

Test User

This button brings up a dialog box that tests the SQL*Net connectivity to the destination user.

Test Link

The Set Loader uses the row identifiers stored in the Extracted Set File to pull data from the source schema onto the destination schema. This information is retrieved via an Oracle database link. This button launches a dialog box that tests the connectivity over the database link.

Test Temporary Table

The Set Loader application uses a small table (DTB_RS_TMP) on the destination schema for temporary storage. This button starts a dialog box that checks for the existence of this table and offers to create it if it is not present.

Disable Constraints

The Oracle database constraints on the destination database schema must be disabled during the actual load. This is because any extracted set of data will not be referentially correct until the entire set is loaded. If the constraints are enabled during the load, referential integrity errors will occur.

Disable Triggers

This button launches a dialog box which offers to disable all triggers on the destination database schema. It may or may not be desirable to disable these triggers - it depends very much on the schema design.

Do the Load

This button launches a dialog box that performs the actual load. The load of the rows onto the destination schema can be stopped and re-started at any time.

Enable Triggers

If triggers were disabled earlier then the dialog box this button launches should be used to re-enable them.

Enable Constraints

This button launches a dialog box that re-enables the constraints in the destination database schema.

Load Options

Allow Full-Pulls: Full-Pulls are a load method that can, in certain specific, circumstances be used to greatly enhance load speeds. Normally the DataBee Set Loader application loads the target table by selecting the required rows from the source table. It does this by identifying the source ROWID's during the extract phase and then inserting each required ROWID into the target table during the load phase. In this way all of the required rows and none of the redundant rows are loaded into the target table.

The processing and request of each ROWID necessarily carries with it a certain amount of overhead at load time. In many cases a subset of the rows in the table is not required and the table is simply moved to the target in "Full". In DataBee this action is easily done using a simple WHERE Clause rule. If a table is loaded via a WHERE Clause rule that pulls every row in the table this fact will be noted by the Set Extractor application. If the Allow Full-Pulls option is enabled such tables will be loaded in their entirety and the processing of each ROWID for the table will be avoided. Such loads are much faster - but consume rollback on the target database since no commit can be performed until the end of the load.

Check the Allow Full-Pulls option if you wish to enable such fast loads in the Set Loader application. Since Full-Pulls involve consumption of rollback until the entire table is loaded it may be necessary to limit the maximum size of the tables allowed to be loaded via Full-Pulls. The Full-Pull Row Count Limit field provides the option of disabling Full-Pull mode on tables with an extracted Row Count greater than the specified limit

Enable Multi-Threaded Loads: The load procedure can be greatly speeded up by having the processing of multiple tables proceeding in parallel. If you wish to have more than one load thread processing simultaneously in the Set Loader application check the Enable Multi-Threaded Loads option and set the number of desired threads using the list box below.

Note: Usually it is best to set the number of load threads equal to the number of processors on the target hardware platform. Setting the number of load threads to a value greater than the number of CPUs rarely results in a performance increase.

Set Loader Commit Frequency: This option sets the maximum number of rows inserted into the target database in between commits. You might need to set this lower if you are loading rows which contain large nested tables. Tables loaded via a Full Pull ignore this option and only commit at the end of the load.

Enable Auto Load of Long Column Tables Oracle does not permit some types of data to traverse a database link. An example of this are tables that contain columns of type LONG, LONG RAW or data of user defined types. In the event that it is impossible to load a table via a database link, the DataBee Set Loader can invoke an internal load process. This option is slower because the data needs to travel down to the PC and then back up to the target schema. If you wish this feature to be enabled select this option. The other mechanism is to use the Set Loader Save ROWID's to File tool which can create a series of SQL COPY commands which can be manually run at the SQL command line to perform the load of tables with LONG columns.

Size of Buffer for LONG Column Loads: This "LONG Buffer Size" configuration item sets the size of the buffer to use for LONG or LONG RAW columns. If this value is smaller than the size of the largest LONG or LONG RAW data in an extracted row then an ORA-01406 error will occur. Unfortunately the larger the size, the slower the load will be so you are advised to adjust this setting until it is big enough and no larger. The value set on this screen is permanently associated with the extracted data but can be adjusted in the Set Loader if required.

Global Load SQL Hint: This Hint is applied to all SQL statements that load rows in the Set Loader application. Usually this hint is unnecessary - however for some RULE based optimizer mode systems you might sometimes need to apply a COST hint if the load process complains about the SGA filling up. This hint can be set here, and also associated with the Extraction Set on the Set Designer application Misc. Setup Tab.