The DataBee Set Designer Misc. Setup Tab

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

Normally the Set Extractor application will still extract and collect the ROWID's of Full Pull tables in the normal manner. This leaves you with the option of not using a Full Pull to load them if there is insufficient rollback segment space on the target database. The extract of these ROWID's takes time. If you are sure you will always want to load the tables via the Full Pull method you can check the Skip Extract of Full Pull Tables option and tables marked as Full Pulls will not be extracted. This speeds up the extract process considerably - but such tables must be loaded via Full Pulls.

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.

Use Explicit Conversion Mode There is a rare bug in certain versions of Oracle 9i that causes the Set Loader to load only a small percentage of the rows in the table - even though they were successfully extracted. If this problem exists on your system then the DataBee customer support desk will advise you to enable this option. It should normally be left disabled.

Extract Options

Use Read-Only Extract Mode: The Set Extractor can (optionally) perform extracts on read only databases. In READ-ONLY mode the DTB_RS_TMP temporary table is no longer required on the source database. Read-Only extracts are typically used in situations where the source database cannot be written to for some reason. An example of this may be when the source is an Oracle Stand-By database.

Enabling Read-Only mode does cause an increase in extract times. Tests indicate that extracts in READ-ONLY mode will typically take about three times longer than the same extract which uses the writeable temporary table. It is advised to leave READ-ONLY mode disabled unless you really need it.

The READ-ONLY option can be applied within the Set Designer and Set Extractor. If enabled within the Set Designer tool the READ-ONLY mode will be saved with the Extraction Set and passed to the Set Extractor as part of the Extraction Set configuration. Setting the READ-ONLY mode in the Set Extractor tool will cause it to activate only for the duration of the current extract. The Set Extractor cannot save the READ-ONLY mode state back to the Extraction Set.

SQL Hints

Data Dictionary Optimizer Hint: Certain operations, such as the download of schema information and foreign key constraints, access the Oracle Data Dictionary through the use of the USER_* views. These operations use the default optimizer mode currently in force on the database. If the download of schema and table information is slow then setting an appropriate hint in this section may speed up the process considerably.

Note: This optimizer mode does not affect the retrieval data rows in the Set Extractor application - it only affects statements which access the Oracle Data Dictionary.

Global Extraction SQL Hint: This Hint is applied to all SQL statements that extract rows in the Set Extractor application. It is simply an easy way to apply a hint to every individual rule without having to edit each rule. Hints applied specifically to a rule will override this global SQL hint.

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 overridden (or set) in the Set Loader application itself.

Directory for Extraction Database: The Set Extractor application will use the rules defined in the Extraction Set to identify and download the appropriate and relevant rows from each table in the source schema. It will place this row information in a binary file called an Extracted Set File. This field lets you specify the directory into which the Extracted Set File should be written. This information is not required - you can enter it when you run the Set Extractor application. However, any information written here will be stored with the Extraction Set and will become the default in the Set Extractor application.

Directory for Log Files: All DataBee applications write out a log of their progress as they are used. A new log file is created each time the application starts and this field is the directory in which the log files will be placed. The Log File Directory setting in the Set Designer application provides the default log directory for all other DataBee applications.