The default configuration should work fine with most databases. However, it
may be necessary to change some settings to optimize the performance of the
copy operation. Below is a screenshot of the Global Preferences dialog that
is used to configure the DBCopy plugin. To access this dialog, choose
File -> Global Preferences. Depending upon which plugins you have installed,
you may have more or less tabs than shown in the screenshot below.
The following is a description of what each configuration item does:
- Transfer Options
- Use truncate command instead of delete where possible - if checked, and records exist
in the destination table, an attempt will be made to issue a "truncate" command instead of
"delete". This may only be of use when the destination database is Oracle and there are a
large number of records to be removed from existing tables in the database. The truncate
command avoids the use of rollback segments in Oracle and runs substantially faster. In
any if truncate fails, then delete will be issued.
- Copy the records from source to destination table - If checked, records from the source
database table will be copied to the destination database. If unchecked, records will be
skipped, but tables will be created. This is useful for testing the validity of the table
definitions created by DBCopy without waiting for all the records to be copied.
- Copy foreign key definitions - if checked, foreign keys for tables being copied
will be created in the destination database. It is important that the set of tables
being copied do not make reference by foreign key to tables that aren't being copied
and don't exist in the destination database.
- Ignore index defs for columns already indexed - some databases don't allow
multiple indexes to be created using the same column. If this is enabled,
an index will be skipped if it uses a column that is covered by another index
- Use a file buffer instead of memory for copying BLOBs -
- Copy buffer size - This option is only available when not using a file buffer to
copy BLOBs. This is the number of bytes to read from the database stream and
write to the temporary file at one time.
- Auto-Commit - If checked every SQL statement is committed in it's own transaction.
- Commit after creating table - This option is available when Auto-Commit is unchecked. If checked
a commit will be performed after each table is created, prior to inserting any records.
Some databases (like Firebird) don't allow record insertion to occur in the same transaction that
the table the records are inserted into is being created.
- Commit count - This option is available when Auto-Commit is unchecked.
How many records to insert before committing the transaction.
- Write script file containing SQL statements executed - a script file approximating the
SQL statements issued during the copy operation will be created if this is checked. Only
statements that actually create database objects or insert records will be added to the file.
The file can be located in the <user.home>/.squirrel-sql/plugins/dbcopy/scripts directory.
The file will be called <source_db_user>_to_<dest_db_user>.sql. Binary columns are
problematic as there isn't a portable way to insert BLOBs into a database from a script.
- Column Type Mapping
- Always prompt to choose the Hibernate Dialect for the destination database - If checked, DBCopy plugin
will not attempt to detect the dialect to use for the destination database. The user will be prompted to
choose the dialect.
- Check column names in copied tables for keywords in destination database - If checked, the list of keywords
from the JDBC driver for the destination database will be consulted to ensure that column names of the source
tables to be copied are legal column names in the destination database. All tables in the copy set will be
checked before any changes are made to the destination database. The copy operation will be halted if a keyword
is detected. This checking is too aggressive for some databases that report valid column names as keywords (e.g. DB2)
- Test column names in copied tables to see if they are valid for the destination database - If checked, this
will cause a test table to be created in the destination database for every column of every table in the copy set
of the source database. This will slow the copy operation quite a bit, but can be useful for determining why a
copy operation is failing - sometimes the database error gives no hint that an invalid column name appeared in
the create table statement.