Configuration

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. Delete will be used if truncate command fails, so it is safe to leave this checked.
• 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.

• ResultSet fetch size - the number of records to be fetched in one trip to the database for selects. A small number should conserve memory and reduce performance. A large number should increase performance and memory consumption.
• 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.
• Delay time in between copying database objects(ms) - When enabled, this option allows the user to insert a time delay into the copy operation at two different points. The delay is taken to be in milliseconds and the points are 1) in between copying tables 2) in between copying records. A delay of 0 or less is effectively the same as disabling delay time for the insertion point. This option can be used to conserve CPU and I/O on the client which is running SQuirreL, and the database server(s).
• Tables - delay is inserted between tables.
• Records - delay is inserted between tables. Care should be taken as this will have a greater impact for many records.
• 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.