Dbschema is a utility that extracts the schema of a database or entire Adaptive Server into one or more scripts. This article provides step-by-step instructions for installing the utility on a Windows machine for those without any prior experience of Perl.
There are a number of utilities that will reverse-engineer a database or Adaptive Server into SQL scripts. The dbschema utility is a particularly flexible option for automating schema extraction to a series of files for source control or database migration.
The utility requires Perl and the sybperl module to be installed on the local machine as a prerequisite. Many unix environments will have Perl as a standard installation, but the DBA may also wish to use the extraction utility on a Windows desktop. This document provides a step-by-step guide to installing Perl, the sybperl module and dbschema on a Windows machine.
No prior experience of Perl is required. Once the dbschema utility is installed, there is no further knowledge of perl required to use the utility.
The host computer must be configured for Sybase Open-Client connectivity i.e. you must be able to run isql against the target Sybase database server
Install a version of perl for Windows
Perl is a popular open source scripting language. A windows version, ActivePerl, is provided by ActiveState, and may be downloaded from http://www.activestate.com/Products/Download/Download.plex?id=ActivePerl
(Or go to the home page at http://www.activestate.com and follow the links for ActivePerl).
1. Run the installation package.
2. Accept the default features installation, or for a minimum installation the following features may be excluded:
* Perl subfeatures: Perl ISAPI, PerlEx, PerlScript
3.Accept the default setup options.
4.Test the installation and configuration by running a perl statement from the command prompt:
C:\>perl -e "print 'Hello World!';"
If the perl command isn’t recognized, try c:\perl\bin\perl -e "print 'Hello World!';"(or the correct path if you chose not to install to the default c:\perl directory). If you see the output, the path isn’t configured yet – fixing this may simply be a case of logging off and on.
Install the sybperl module
The sybperl module provides Sybase Open Client extensions to perl. The module was developed by Michael Peppler (http://www.peppler.org)
To avoid having to compile the extensions (which requires a C++ compiler), don’t download from the main downloads directory. Instead, download the latest zip file from http://www.peppler.org/downloads/ActiveState/.
Extract all files into a new subdirectory c:\perl\sybperl (or a directory of your choice). Check that one of the files is named sybperl.ppd. If not, then you’ve probably got the zip download that requires compilation: go back to the download site and get the ActiveState version.
Use the perl package manager (installed with the perl installation) to install the sybperl extensions. From the command line, issue the following command (providing the path of wherever you extracted the sybperl files).
ppm install c:\perl\sybperl\sybperl.ppd
The final output should be “Successfully installed sybperl version [version] in ActivePerl [version]”.
Install the dbschema.pl perl script
This sybperl script was originally developed by Michael Peppler, and is currently maintained by David Owen (http://www.midsomer.org).
The file itself may be downloaded from http://www.midsomer.org/files/dbschema.tgz.
Extract the files to your directory of choice (e.g. c:\perl\sybperl\) and run from the command prompt:
perl [folder path]dbschema.pl -h
If a long set of instructions is displayed, then the setup is correct.
Test connectivity to your database server as follows:
perl [folder path]dbschema.pl –U[userid] –P[password] –S[server] –D[database]
Here is an example of usage to get you going. With one command call, the utility will extract a database schema into a set of DDL files organized into folders by object type.
1. Create a directory called [your database], open a dos prompt and go to the directory.
2. Run the following command against your server:
perl [folder path]dbschema.pl –S[server] –U[user] –P[password] –D[database]
--nopermissions -O2 --nosetuser --nouse-database --nocomments -oddl
You now have a folder for each object type e.g. tables, procs, views etc. Within each folde