Export, Alter, and Import a Database Schema Using DdlUtils

Use DdlUtils to export, modify, and import data.

Procedure
  1. Download and install Apache Ant if it is not already available on your system.
  2. Ensure that your CLASSPATH includes the JAR file that contains the third-party database's JDBC driver.
  3. Read the available DdlUtils documentation to understand the various Ant tasks. Documentation is installed in the ddlutils/DdlUtils-1.0-doc.zip file and is also available at the DdlUtils project site. This step is not required if you only want to export DDL from a schema and then apply it to SQLFire.
  4. Make sure that you can connect to the external database and to SQLFire servers. You will need to supply the connection URL and properties for both databases when using DdlUtils.
  5. Edit the build.xml file in the ddlutils/example directory to specify the JDBC connection information for both SQLFire and the database from which you are importing data. The example build file contains sample entries for SQLFire and MySQL. You will need to change each occurrence of the JDBC URLs. The build.xml contents are shown in the Example build.xml File.
  6. By default, the usedelimitedsqlidentifiers property is set to false, which means that tables, columns, and other object names are simple references with no need to place the names in quotation marks. However, if your schema contains table or column names with embedded spaces, change this property to "true" and use single quotation marks around the table and column names each time you reference them.
  7. Databases such as Oracle 11g contain system tables with data types that are incompatible with the DdlUtils 1.1 API. To export schemas or data from these databases, you must use the schemapattern property to exclude schemas that contain incompatible data types.
  8. In the ddlutils/example directory, run the following command to run the three Ant tasks that generate a SQL DDL script that is compatible with SQLFire syntax:
    ant writeDDLToXML createDBFromXML writeDDLToSQL
  9. Edit the CREATE TABLE statements in the resulting script to use SQLFire-specific clauses. For example use syntax to use colocation for partitioning, persist tables, associate tables with gateways, and so forth.
  10. Use the sqlf utility to run the modified script in SQLFire. For example:
    sqlf
    connect client 'localhost:1527';
    run 'db-schema1.sql';
    exit; 
  11. To import data from the database, use the ImportDataToDB target in the example build.xml file.
  12. If necessary, manually add triggers, views, and Java stored procedures in the SQLFire database.