db2xml - Import Schema to XML


Return to main dbmstools documentation page.


db2xml imports a schema directly from a database to an XML description. It uses Java's JDBC API to do this, so it'll run only under Jython, and you have to provide a suitable JDBC driver for your DBMS.

There are 2 main reasons why you'd want to use this tool:

  1. To reverse-engineer an existing schema into XML, so that from then on you'll maintain it in XML
  2. To verify that the changes made to a database (perhaps from scripts generated by deltaddl) match what you expected.


Because this script needs to get at metadata for the database, it uses the dbmsjy.py wrapper and Jython. See the Jython scripts page for details.


You use the tool like this:

dbmsjy.py db2xml.py [dbOptions] [generalOptions]

or, specifying JDBC details directly:

dbmsjy.py db2xml.py [jdbcOptions] [generalOptions]

The XML result will be written to stdout, or to a path of your choice with the -o option.

Database Options:

-D dbName Name of the database to connect to. Required
-H dbHost Name or IP address of the database host to connect to. Optional, defaults to localhost
-P dbPort Port of the database host to connect to. Optional, defaults to the default port for the DBMS

JDBC Options:

-c driverClass Fully-qualified name of the JDBC driver class, e.g. org.postgresql.Driver. If omitted, will be defaulted based on URL. Must be present in classpath, usually by placing in the jdbc-drivers directory
-j JDBC_URL JDBC URL for connecting to the database, in the format expected by the JDBC driver, e.g. jdbc:postgresql://localhost:5432/PS30 Required

General Options:

-d dbms Name of the DBMS to generate schema files for. Mandatory. Values recognised are: postgres7, postgres8, mssql, oracle, mysql, hsql
-g Include grants for each object (default is to omit)
-h Shows this screen
-o outputPath Path to output schema file to (defaults to stdout)
-p password Password to use to connect to DB (if required)
-s schema Schema name to export (defaults to all schemas)
-u username Username to use to connect to DB (if required)
-v Enable verbose logging (to stderr)

Long Options

 Don't omit indexes that have the same name as a primary key (default is to omit these).
--lower-case Convert all object names to lower case
--storage-line text
 Include this text as a storage line for each table (one or more). Pass "" as text to omit any default storage line
--upper-case Convert all object names to upper case
--version Print version number and exit

See the dbmsjy page for details of default JDBC drivers and URLs etc.


In this example, we're using Postgres (under Unix), and a local database called 'jcr':

$ mkdir ~/tmp/schema-output                    # generate schema to this directory
$ dbmsjy.py db2xml.py -d postgres8 -D jcr \
    -u johnd -o ~/tmp/output/importedSchema.xml

Hints and Tips

Because every DBMS does things in a slightly different way, db2xml probably won't produce the same schema output for the same database in each DBMS. However, there are a number of options that can improve the quality of the XML schema.


db2xml does what it can, but different DBMSs interpret the JDBC metadata API in different ways.

In particular:

Other Notes