Export and Import documentation

You can use Export and Import tool to migrate the database from one version to another.

Export

The database is exported as a ZIP file that contains the required information and data that enables you to Import it later.

Export Working Details

The database details are saved in a ZIP file as follows:

For all the tables in the database, SQL Create statements are created and will be stored in createstmts.sql under each schema.

The table database are saved in CSV (Comma Separated Values) file format in the file table_name.csv

For all the constraints and index on the table corresponding SQL Alter statements are created and stored in alterstmts.sql and indexstmts.sql

All the check constraints are save in non-sql format (a limitation) in the file called constraint_name.check, list of such filenames are written into checkfiles.list

The information about the views are stored in the file viewinfo.viw

For the foreign key constraints (or referential constraints) SQL Alter statements are created and saved in the file foreign.sql which appears at the catalog level.

The table previleges and column previleges are saved in the file table.prv and column.prv files respectively at the catalog level.

The data dictionary (table, column description) are saved in the files table.dcr and column.dcr respectively at the schema level.

The CLOB and BLOB type of table data are stored are as separate files which ends with the name ".clob" and ".blob" respectively.

There is a limitation on each entry of the ZipFile. To overcome this, if the zip entry crosses the minimum size which can be safely handled, new entries are added with the name ending with zippart_[number]. This helps us to have large size ZipFile.

Export Options

You can export the required catalogs/schemas/tables of the database. There is a option available to even export SQL Query on the database. This is useful when exporting the part of the table.

Export Limitations

These are some of the known limitations.
  • Check constraints statements exported depends on the database version.
  • Exporting of text searches enabled on the database, for table etc, are not done.

Export Tools integration

Exporting database can be done through
  • Console
  • WebAdmin
  • Command line tool available in DbUtils, com.applibase.dbutils.dbexport.Export

Import

Import the database from the ZIP file created using Export tool. Only Administrators can use the import tool.

Import Working Details

The path information that needs to be created in the database are mapped from the ZIP file. The ZIP file entries spans at max to 3 levels (sub-directories). [The structure assumed is, Zip file root entry will have catalog entry, which can have schema entry and which contains file entries.]

The following are the sequence of steps during import of the database.

First the required catalog and schema is created (if not present). Then createstmts.sql file is read and tables are created (if not present). Next the indexstmts.sql file is read and indexes are created (if not present).

The table data are now imported.

The check constraints are applied after the table data is imported. Some times imporing check constratins might not work as it is tied to database version. In such cases, user might set option for Import tool to avoid importing the check constraints.

The data dictionary (table and column descriptions) are imported.

The alterstmts.sql file is read and unique constratints are applied (if not present).

After schema import is finished, foreign.sql file is read and the statements are executed.

Next importing the views are started. There may be situation like,

View A depends on View B that depends on View C and so on, in such cases there is some linear dependecy. So a queue is maintained and serveral times the queue is cycled through so that all the views are imported but in some cases it might end up in a infinite wait, to avoid this there is a limit on trying the view import that is in the queue (pending for imported). It is usually set to 5. You can set this limit through the options supported by the import tools.

When the ZipFile size is more than 2GB, it fails to open. In such cases, a wrapper around the ZipOutputStream is used that will help reading the large ZipFile. This might impact the performace of the application.

Import Limitations

These are some of the known limitations.
  • As only administrator has the previlege to import, he should make sure to grant the permissions to other users for them to use the tables/views after the import.

Import Tools integration

Importing database can be done through
  • Console
  • WebAdmin
  • Command line tool available in DbUtils, com.applibase.dbutils.dbimport.Import