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:
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.
| -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 |
| -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 |
| -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) |
| --include-pk-indexes | |
| 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
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.
Depending on your preference for object names, and the way your DBMS works, you may want to use --lower-case or --upper-case to get the names you want. Oracle always outputs in upper case, Postgres in lower, MSSQL and MySQL preserve case
Important: Oracle users should use the -s (schema) option as a matter of course.
While dbmstools tries to avoid exporting system tables and the like (by excluding anything from the SYS schema, anything with a $ sign in its name, and anything with a name starting with SYS_), you'll still end up with a lot of unwanted rubbish if you don't specify the schema. And worse than that, the export may fail completely, depending on the version of Oracle you use, and your access rights.
Note that, for Oracle, the schema name is case-sensitive and is almost always upper case, e.g. -s MYSCHEMA. If no schema is specifed when objects are created, they go into the user's default schema, which normally has the same name as the user.
The XML encoding is set to UTF-8, as is the output when writing to a file. However, the default console encoding is used if you don't specify an output file, so this may fail if your console does not default to unicode. If you have character encoding problems, just specify an output file.
db2xml does what it can, but different DBMSs interpret the JDBC metadata API in different ways.
In particular: