db documentation using schemaspy in linux

SchemaSpy in Linux

Introduction:

            SchemaSpy is a Java-based tool that analyzes the metadata of a schema in a database and generates a visual representation of it in a browser-displayable format. It lets you click through the hierarchy of database tables via child and parent table relationships as represented by both HTML links and entity-relationship diagrams.

           

Release:

RedHat Enterprise Linux

MSSQL 2005

Problem:

Need to create database documentation using SchemaSpy utility.

Dependencies:

1)    JVM 1.5 or above

2)    Graphviz (graph visualization software)

3)    JTDS 1.2.5

4)    HTTP Server (Optional)

Solution:

1)    Download the JVM from the below URL

                        http://www.oracle.com/technetwork/java/javase/downloads/index.html

2)    Give the executable permission and install the downloaded java

            # chmod +x jdk-1_5_0_22-linux-amd64.bin

            # ./jdk-1_5_0_22-linux-amd64.bin

3)    Then download the graphviz package from the below URL

            http://www.graphviz.org/Download_linux_rhel.php

4)    Install the downloaded the rpm using rpm or yum

                        # yum install graphviz

5)    Downlad the JTDS from the below link

                        http://sourceforge.net/projects/jtds/files/

6)    JTDS is used to connect the MSSQL database. JTDS does not need any special installation.

7)    Download the SchemaSpy from the below link

                        http://sourceforge.net/projects/schemaspy/files/

8)    Before running the schemaspy make sure that, the “dot” program should be in your PATH. If not present then we need to mention the path of that command using “-gv” option.

9)    Now execute the below command to create the relationship diagrams

                        # /opt/jdk1.5.0_22/bin/java -jar /asp/data/schemaSpy_5.0.0.jar -t mssql05-jtds -u user -p password -db database  -host IPAddress -port 1433 -o /opt/test_schema/ -s schemaname -dp /opt/jtds/jtds-1.2.5.jar

            Where,

            -t → Type of database (e.g. ora, db2, etc.). Defaults is ora

            -u → Valid database user id with read access.

            -p → Password associated with that user. Defaults to no password.

            -db → Name of database to connect to

            -host → database server ip or hostname

            -port → tcp port used to connect the database

            -o → Directory to write the generated HTML/graphs

            -s → Database schema

            -dp → Looks for drivers here before looking in driverPath

10)  The above command takes some time that depends upon your schema size and generate the graphs in the given output directory (in my case /opt/test_schema/).

11)  In that output directory “index.html” file will be there using that we can browse everything.

12)  If we have http server installed in the same server means we can include this directory to the “Document Root” by creating on symbolic link.

                        # ln -s /opt/test_schema/ /var/www/html/test_schema

Note: Here “/var/www/html” is my “Document Root” in apache web server.

13)  Now we can access the schemaspy graphs through the webserver.

                       

                        http://ServerIPAddress/test_schema