Using the Bulk Import and Export Utilities
Page 2 of 12

Overview

The COM.cloudscape.tools package contains two classes for importing data from flat ASCII files and exporting data to flat ASCII files: COM.cloudscape.tools.FileImport and COM.cloudscape.tools.FileExport.

You can perform an Import or Export operation from Cloudview, ij, or a java program, or from within an SQL-J statement.

Options for Running Import and Export

These 100%-Java utilities are most easily run from Cloudview. You can also run them on the command line or invoke them from another java application. When run in any of these ways, the import utility automatically tries to import data in fast mode.

The design of these utilities also allows you to "run" them within an SQL-J statement using ij or any Java application. When run in this way, the utility runs by default in slow mode, unless you explicitly set a property.

Import/Export reads and writes only ASCII text files. If you export non-ASCII data, only the first byte of each character is written to the file. If you import non-ASCII data, each byte is read as a character. In addition, Import does not support read-once streams (live data feeds), because it reads the first line of the file to determine the number of columns, then reads it again to import the data.

NOTE: These server-side utilities exhibit different behavior in client/server mode. Typically, you use them to import data into and export data from a locally running Cloudscape. However, you can use the language-based version of the commands when Cloudscape is running in a server framework if you specify import and export files that are accessible to the server.

Preparation, Requirements, and Use

  • Table must exist.

    For you to import data into a table, the table must already exist in Cloudscape. The table does not have to be empty. If the table is not empty, bulk import runs in slow mode, unless you explicitly replace existing data with the new data.

  • Create indexes and primary key, foreign key, and unique constraints first.

    Create the table's indexes and primary keys first (before importing data) to avoid a separate create index step. However, if your memory and disk spaces resources are limited, build the indexes and primary keys after importing data.

  • Disable check constraints.

    You can disable check constraints with the SET CONSTRAINTS command. Re-enable constraints after the data are loaded. When replacing existing data, disable circular foreign key constraints.

  • Format of data file.

    The data must be in a flat ASCII text file. There is a default file format (see Default Input or Output File Format). If the format of the file is different, you can use Cloudview to help you create a control file, which describes the alternate format of the file to the import and export utilities.

  • Data types.

    You can import and export only data of the non-binary, built-in data types. For example, you can export objects of type INTEGER but not byte arrays or objects of type JBMSTours.serializabletypes.City. COM.cloudscape.tools.FileImport sends all valid data types to Cloudscape as strings. Cloudscape 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 binary data types: BIT, BIT VARYING, LONG VARBINARY, LONG BIT VARYING, or LONG BINARY.

  • Autoincrement column default

    You cannot import into a column which has a default of AUTOINCREMENT; you must let Cloudscape generate the value for you. If a table has an autoincrement default, you must first drop the default, then import the data and (if applicable) re-add the default.

    NOTE: You can preserve the values in the autoincrement default column by dropping the default before importing the data and then re-adding the default when you are done importing.

  • Table locking.

    During import, the entire table is exclusively locked. During export, the entire table is locked with a read lock.

Slow and Fast Modes for Import

Bulk import can be run in slow or fast mode.

  • Slow mode

    In slow mode, bulk import does not work much faster than a series of insert statements, but it provides the convenience of allowing you to work with files that are produced or read by other applications, such as spreadsheets or other desktop databases.

  • Fast mode

    In fast mode, bulk import works much faster than a series of insert statements because Cloudscape minimizes logging and makes some under-the-covers performance enhancements. Fast mode is available in specific circumstances; see Fast Mode Requirements.

Both slow- and fast-mode bulk import are transactional. If an error occurs during bulk import, all the changes made are rolled back.

Fast Mode Requirements

To make a bulk-import operation run in fast mode, the import must fulfill these requirements:

  • The table must not be in a target database.
  • The table must not be a published table in a source database if you are replacing existing data.
  • From within the context of an SQL-J statement, you must explicitly specify fast import mode (insertMode=bulkInsert or insertMode=replace). See Import Properties.
  • For non-published non-empty tables, you must explicitly specify that you are replacing existing data. In Cloudview, you select a checkbox; otherwise, you set the property insertMode to replace. See Import Properties. Otherwise, slow import mode is used.

NOTE: When using fast import mode and replacing existing data in a table that has a primary or unique constraint referenced by foreign keys in other tables, any foreign key constraints that have not been turned off are checked when Cloudscape completes the operation. If any of the foreign key constraints are not satisfied, an exception is thrown, and the statement is rolled back. Any constraints which have been turned off are not checked until the user explicitly turns them back on.

Using the Utilities

There are three ways to Import and Export data.