Extraction and loader sets connect to remote source and target schemas in order to perform their extract and load operations. The connection information, schema structure and other schema related information needs to be stored for each source and target schema. A special type of rule called Rule Controller is designed to contain and maintain this information. Each extraction and loader set must contain at least one Rule Controller and may contain more than one. Every other type extraction and loader rule has a parent Rule Controller. The Rule Controller determines the schema in which each extraction and loader rule will execute.

The DataBee Create/Edit Rule Controller Form

The Set Designer application creates both extraction and loader sets and will modify its display appearance in order to offer options appropriate to the type of set currently open. The screen shot above shows the Create/Edit Rule Controller form for a Rule Controller involved in an extraction set. A Create/Edit Rule Controller form for a Rule Controller used in a loader set would look much the same - but would have fewer options.

The Rule Controller form has three tabs on the left hand side. The DB Login tab contains connection details for the Rule Controllers dependent rules. The Options tab contains additional configuration options and the Tools tab contains utilities which can refresh various components such as the schema structure or foreign keys.

What the items in the DB Login tab do

The login panel on the left hand side of the form is used to configure the Oracle login for the Rule Controller. Rule Controllers can be configured to connect via the standard Oracle SQL*Net using TNSNames or directly to the database via TCP/IP. If the TNSNames method is used, the PC must have Oracle SQL*Net version 9 (or higher) installed. In general if it is possible to connect to the target schema with a TNSName via an Oracle Utility such as SQL*Plus then the DataBee software should not have any difficulties connecting.

Login Name and Password

This is the login name of the schema to which the Rule Controllers dependent rules will connect and where they execute.

Connect via TNSNames and Connect via TCP/IP

These two options determine the method the rules will use when connecting to the target schema. The TNSNames mode requires a valid Oracle TNSName identical to that which would be used in an SQL*Plus session. The TCP/IP mode requires the host name, port and SID of the database.

Optional Proxy Configuration (extraction sets only)

Usually the Login Name field references the schema that contains the tables to be extracted - however the Login Name can be a proxy schema which only has select access to the tables real target schema. If the Login Name is a proxy schema, the name of the schema that actually owns the tables should be entered in this panel. The benefit to using a proxy schema is that it can contain the extraction set temporary tables leaving the real source schema entirely read-only. The read-only access permissions can be enforced at the Oracle level

The DataBee Create/Edit Rule Controller Form Options Tab

What the items in the Options tab do

Source/Target Database Version

This should be set to the version number of the Oracle database to which the controller will connect. The DataBee software will adjust its masking algorithms so as to be as efficient as possible. For some rules, higher Oracle database versions offer the opportunity for speed improvements.

Re-Analyze Temp Tables During Extract

In some cases it is useful to analyze the DTBTAB temporary table during the extraction process. The DTBTAB table contains one row for each row extracted and if it changes considerably in size during the extract the Oracle database can generate a sub-optimal execution plan based on erroneous assumptions about its rowcounts. This option enables a periodic statistics analyze operation to be performed on the DTBTAB. The table below the option checkbox enables the configuration of the levels at which the analyze statistics operations will be started. When the rowcount in the DTBTAB reaches a specified level the DTBTAB table is analyzed.

Temporary Table Configuration

Rule Controller for both extraction and loader sets require temporary tables to exist in the schemas to which they connect. One large table, named DTBTAB, is used in the source schema (or in the proxy login - see above) to store the extracted ROWIDs. Other, smaller tables, are used to record the actions of the rules. The DataBee Set Extractor and Set Loader software uses these tables to determine which rules need to be executed in the event of a re-start. More information on the temporary tables can be found in the extraction set temporary tables and loader set temporary table help files.

The DataBee Create/Edit Rule Controller Form Tools Tab

What the items in the Tools tab do

Refresh Tables and Indexes

The Rule Controller maintains the schema structure information for the connection schema as well as the login information. This button will activate a wizard (a series of walk through panels) which will refresh the schema structure held by the Rule Controller. It may be desirable to save the plan information (see below) before refreshing the table structure.

Refresh Foreign Key Rules (extraction sets only)

This button connects to the source schema and refreshes the foreign key information held by the Rule Controller and converts that foreign key information into Table-To-Table rules. These rules automatically replace the existing collection of foreign key derived Table-To-Table rules in the extraction set.

Add Cross Schema Foreign Key Rules (extraction sets only)

If multiple Rule Controllers are implemented in an extraction set, the foreign key relationships between them are not automatically implemented as Cross Schema Table-To-Table rules. This button activates a wizard which can build Cross Schema Table-To-Table rules which enforce foreign key relationships.

Refresh Foreign Keys (loader sets only)

This button connects to the schema and refreshes the foreign key information held by the Rule Controller.

Refresh Triggers

This button connects to the schema and refreshes the trigger information held by the Rule Controller.

Refresh Row Counts (extraction sets only)

The DataBee software does not need to know the number of rows in the schema tables in order to extract referentially correct subsets. However, it is very useful to have such information when developing extraction rules. This button offers the ability refresh the row count values held by the Rule Controller with the current schema table row counts. It is possible to use the estimated row counts provided by the Oracle statistics if desired.

Import/Export Plan CSV (extraction sets only)

Extraction set plans are optional information configured in the extraction set in order to determine how well the extracted output matches a desired state. This button enables the plan information to be saved and recovered while refreshing the table structure in an extraction set.