Tuesday, September 23, 2008

Using SchemaSpy with multiple schema

A happy coincidence occurred last week.

One of my current projects has a database with foreign key relationships spanning across two schemas. I needed to create some database documentation.

And SchemaSpy v4 was in late beta. One of the great new features of v4 is support for multiple schema. John Currier was short of a "real" database to test these changes on.

John gave me great support in working through a few issues and fixing up a couple of bugs, that have made it into the SchemaSpy 4.0.0 release.

The result is a new main page showing all of the selected schema:



Drilling down, the relationship graph now shows related tables in foreign schema:



SchemaSpy does an excellent job of hyperlinking all of the tables, columns and relationships, allowing you to easily navigate around your schema.

To use the multiple schema feature, add a -all parameter to your SchemaSpy command line. With this parameter, SchemaSpy will include all schema except for system tables (which are determined by a schemaSpec pattern in the properties file for the specified database type). To further restrict the schema, add a -schemaSpec command line parameter with a regular expression to match the required schema.

The following ant task instructs SchemaSpy to document the PMH and SHO schema of our DB2 database.
<java jar="lib/schemaSpy_4.0.0.jar" fork="true"/>
<arg line="-t udbt4"/>
<arg line="-host ${db.host}"/>
<arg line="-port ${db.port}"/>
<arg line="-db ${database}"/>
<arg line="-u ${db.userid}"/>
<arg line="-p ${db.password}"/>
<arg line="-all"/>
<arg line="-schemaSpec (PMH)|(SHO)"/>
<arg line="-dp ${DB2_LIB}/db2jcc.jar:${DB2_LIB}/db2jcc_license_cu.jar"/>
<arg line="-o ${reports.data.model.dir}"/>
</java>

No comments: