Bulk import and export requirements and considerations

There are requirements and limitations that you must consider before you use the Derby import and export procedures.

Database transactions
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. Derby issues a COMMIT or a ROLLBACK statement after each import and export procedure is run.
Note: Imports are transactional. If an error occurs during bulk import, all changes are rolled back.
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 results 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 spaces 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 enables 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.
Related concepts
Methods for running the import and export procedures
Bulk import and export of large objects
File format for input and output
Import and export procedures from JDBC
How the Import and export procedures process NULL values
Related tasks
Importing data using the built-in procedures
Exporting data using the built-in procedures
Related reference
Examples of bulk import and export
CODESET values for import and export procedures