Data Pump

The Data Pump is a tool used to transfer data from one database to another and can be reached via Tools | Data Pump. The Data Pump tool that comes with Database Workbench supports several types of connectivity:

By using ODBC or ADO connectivity, you can transfer data to and from any other database engine.

In this topic

User Interface Overview

As you can see in the above example, we have connected to a Firebird source database (1) and are connected to NexusDB database called "ADGSample" (2). In both sections, the tables are fetched (3 & 4) and a few table mappings have been made (4).

Loading & Saving a "pump project"

You can load and save your mappings and connection information to a "pump project"-file (extension ".pmp").

When you have saved a project, you can re-load it and the Data Pump will connect to both the source and destination database and displaying the table and column mappings. Any mappings before loading the file will be removed.

If you want to use a particular set of mappings for a different set of databases, you can connect to the source and/or destination databases first and then load the file, the Data Pump will then ask you if you want to keep the current (source or destination) connection.

Such a mapping is easily created by dragging the source table to the destination table and dropping it there. After such a drop, an attempt will be made to match column name - this matching process can be adjusted on the Options tab - it does a case insensitive match and filters any non-standard characters such as ' # $ _ etc...

Any columns that don't get matched will stay empty until you drag 'n drop a source column to the destination column.

The transfer order can be controlled via the UP/DOWN arrow buttons on the Data Pump toolbar. This can be very useful if your target database has foreign key constraint defined, additionally, you can let Database Workbench determine the order automatically by examining foreign key dependencies, use the Determine Table Order button for that.

You can also use the Auto-map button to automatically match table & column names for all available tables.

Besides source database values, you can also choose to use a constant or expression of some kind that results in a valid value and uses valid SQL for that particular database engine. Do a context-mouse click on a column to enter such an expression.

In the above example, you can see that while transferring data, the destination column ACCOUNTS.ACT_ID will be filled by using a generator. Any expression that results in a valid SQL value is possible here.

For the source table, you can also add a custom SQL WHERE clause to filter data for the destination database. Do a right-mouse click to enter such a WHERE clause.

The above WHERE clause results in ENDBALL records being transferred with a value for SUMS above 10000.

Create new Source SQL

You can also use a custom SQL statement as the source of data to transfer. Click the Create New Source SQL button in the toolbar and a simplified SQL Editor will be displayed.

In this SQL Editor, you can create and test your SQL statement. If you're satisfied with the results, close the editor to accept the changes. The SQL has to be "named" and you can enter a name for it.

If you want to delete it, simply use the context menu in the treeview and select Delete from the popup menu. Using a double click or Edit SQL from the menu will open up the editor again.

You can expand the SQL item just as you expand the tables and look at its columns.

Using an ODBC datasource requires a registered ODBC data source name. These can be created from the Windows Control Panel applet for ODBC datasources. This applet can also be reached via the 'Edit ODBC Datasources' button next to the ODBC Datasource drop down combo.

ADO Notes

Options

The Data Pump has options available to influence how the data transfer process, the name matching when mapping and some other options, these are defaulted as per application Preferences.

The options under Name Match are used when performing automatic matching only, that is, the automatic column matching when you drag a table from the source to the destination or when you choose to auto-match all tables.

Records per single transaction	This tells you how many records should be transferred before a database COMMIT is executed. Failed INSERT attempts do not count towards this limit. If you enter "0", all rows of the source will be transferred to the destination in a single transaction.
Transfer all data in a single transaction	This will wrap the transfer process of all source-to-destination items into a single transaction.
Do not COMMIT if an error occured	Don't commit the transfer of records when at least 1 record cannot be inserted.
Empty tables before data-transfer	Empties the target tables before starting the transfer. This will empty all the tables that are participating in the data transfer process.
Disable indices/triggers/constraints	If the target database engine allows it, these objects can be disabled before starting the data transfer, they will be re-enabled afterwards.

By default, the Data Pump will open an additional connection to your databases and optimize the running process for concurrency - that is, you will be able to continue working with Database Workbench. If you don't mind a stalled Database Workbench instance, click the arrow next to the Start Data Transfer Process and select Optimize for Speed from the menu.

The left hand side of that tab holds a list with the mapped data transfers (source to destination), the right hand side holds process messages and error results on a per transfer basis. Selecting [none] at the top left will list a summary of available messages, selecting a Transfer-item lists errors and messages for that particular transfer.

The Transfer-items can be in bold meaning that item is currently being processed and red indicating there were errors during processing.

Advanced Tips

1) If you need to convert data while transferring from one table to another, you can use a "custom SQL source" in a specific way.

Instead of using a simple (or complex) SQL statement to return results, you can also use a Stored Procedure that generates or converts data. Or you can use functions in your SQL to, for example, trim the contents of columns before doing the transfer.

2) Although you can use an ORDER BY clause on each table or view, this will still transfer all rows unless you specify a WHERE clause. But you cannot, for example, transfer the "first 100 rows". Again, you can easily use the "custom SQL source" to do this. Use a SELECT with a clause that trims the number of rows.