Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Decomposing a database's structure

by talexb (Chancellor)
on Dec 08, 2008 at 02:47 UTC ( [id://728840] : perlquestion . print w/replies, xml ) Need Help??

talexb has asked for the wisdom of the Perl Monks concerning the following question:

I'm really interested in extracting information about a database structure from a production database in order to determine which tables are currently being used and/or are linked to other tables, and which tables are not being used and/or are isolated. Before I joyfully dive in and start writing code, is there a tool or module that does this already? (It's a rather large DB2 database.)

Something that draws pretty pictures based on the resulting information would be even cooler, but that certainly isn't a requirement.

Alex / talexb / Toronto

"Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds

Replies are listed 'Best First'.
Re: Decomposing a database's structure
by CountZero (Bishop) on Dec 08, 2008 at 06:07 UTC
    One of the many sub-modules of SQL::Translator?

    CountZero

    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

      I had a look at the documentation but couldn't see anything that suggested it could start with a database and come up with any sort of structure information. Did you have a specific module call in mind?

      Friend monarch has kindly suggested Schema Spy, a Java tool, so I may try that out.

      Alex / talexb / Toronto

      "Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds

Re: Decomposing a database's structure
by jplindstrom (Monsignor) on Dec 08, 2008 at 11:44 UTC
    Are all the relations specified properly with FK declarations in the DDL, or are you talking about dumping SQL queries during runtime to see what tables are actually being used and joined?

    I've written a rather nice table and SP graphing tool that parses DDL. It supports Sybase/SQL Server and MySQL currently, I don't know how close that is to DB2 syntax.

    It's not actually released yet, but I could throw a tarball your way if you like.

    /J