There are requirements and limitations that you must consider before
you use the Derby import
and export procedures.
- Database transactions
- Derby issues a COMMIT or
a ROLLBACK statement after each import and export procedure is run (a COMMIT if
the procedure completes successfully, a ROLLBACK if it fails). For this reason,
you should issue either a COMMIT or ROLLBACK statement to complete all
transactions and release all table-level locks before you invoke an import or
export procedure. An error in an import or export procedure and the ensuing
ROLLBACK would throw away any changes performed before the procedure was called,
and vice versa: any unsound changes before the import or export procedure call
that should not be committed could be committed automatically.
- Database connections
- To invoke a Derby import
or export procedure, you must be connected to the database into which the
data is imported or from which the data is exported. Other user applications
that access the table with a separate connection do not need to disconnect.
- Classpath
- You must have the derbytools.jar file in your classpath
before you can use the import or export procedures from
ij.
- The table must exist
- To import data into a table, the table must already exist in
Derby. The table does not
have to be empty. If the table is not empty, bulk import performs single row
inserts, which result in slower performance.
- Create indexes, keys, and unique constraints before you import
- To avoid a separate step, create the indexes, keys (primary and foreign),
and unique constraints on tables before you import data. However, if your
memory and disk space resources are limited, you can build the indexes and
primary keys after importing data.
- Data types
- Derby implicitly converts
the strings to the data type of the receiving column. If any of the implicit
conversions fail, the whole import is aborted. For example, "3+7" cannot be
converted into an integer. An export that encounters a runtime error stops.
Note: You cannot import or export the XML data type.
- Locking during import
- Import procedures use the same isolation level as the connection in which
they are executed to insert data into tables. During import, the entire table
is exclusively locked irrespective of the isolation level.
- Locking during export
- Export procedures use the same isolation level as the connection in which
they are executed to fetch data from tables.
- Import behavior on tables with triggers
- The import procedures enable INSERT triggers when data is appended to the
table. The REPLACE parameter is not allowed when triggers are enabled on the
table.
- Restrictions on the REPLACE parameter
- If you import data into a table that already contains data, you can either
replace or append to the existing data. You can use the REPLACE parameter
on tables that have dependent tables. The replaced data must maintain
referential integrity; otherwise, the import operation will be rolled back. You
cannot use the REPLACE parameter if the table has triggers enabled.
- Restrictions on tables
- You cannot use import procedures to import data into a system table or a
declared temporary table.