Tuesday, February 04, 2014

Reverse engineering a sybase database

The world seems to have decided on Oracle. But every now and then as a freelancer I come across a system that uses sybase. Usually it is an ancient legacy system that cannot be turned off for some reason, so sybase soldiers on. In such cases it is often useful to be able to reverse engineer the schema. This is where SchemaSpy comes in.

SchemaSpy requires graphviz, aka dot. The final generated output is a set of web pages and a set of dot files. I suspect that the dot is used to generate the image files used on the web pages, so dot is needed even if you never want to look at the dot files directly. Dot can be installed on windoze by merely unpacking the zip and adjusting PATH.

Some sybase installations have internationalisation set up in such a way that the URL needs to end in ?charset=iso_1. schemaSpy forms the URL based on a config it reads so the config must be extracted from the schemaSpy jar (a zip).

I first came across the charset issue when setting up database connections in DbVisualizer. I found
this web page which has the solution. This is also the solution for the same problem in schemaSpy.

Edit a copy of this config, appending ?charset=iso_1 to the name of the URL, then invoke schemaSpy with this command line (the sybase config filename is sybase.properties):

Note that the schema dbo must be specified, otherwise it tries to use the username as the schema name.

java -jar schemaSpy_5.0.0.jar -t sybase.properties -db <dbName> -u <userid> -p  <password> 
  -o <outputDir> -host <stringFromSybaseInterfaceFile> -port <portNumber>
  -dp <pathnameTojconn3.jar> -s dbo

No comments: