dumping sybase schema

Posted by anton
on Tuesday, October 30, 2007

currently i have a privilege to work with sybase 12.5. perhaps i am spoiled with the ease of mysqldump db_name [tables] or echo .dump [tables] | sqlite3, but i expect any modern database to have a scriptable way to dump schema for selected tables in create statements, as well as data in insert statements that simply could be piped back when needed.

while dumping data is easy enough using bcp, scriptable schema extracts are a bit trickier (especially if you want it to be cross-platform).

but we are lucky – it took sybase only 20+ years to introduce a command-line utility written in java called ddlgen in version 15 of its flagship enterprise product (in my case it worked against 12.5 as well).

ddlgen

$ ls -lR c:/programs/sybase/ddlgen/
c:/programs/sybase/ddlgen/:
    ddlgen.sh
    lib/

c:/programs/sybase/ddlgen/lib:
    DDLGen.jar
    dsparser.jar
    jconn3.jar
  • rig up the wrapper script:
$ cat c:/programs/sybase/ddlgen/ddlgen.sh 
JAVA_HOME=c:/programs/java/jdk/jdk1.6.0_03
LIB_DIR=`dirname $0`/lib
CLASSPATH=$LIB_DIR/jconn3.jar:$LIB_DIR/dsparser.jar:$LIB_DIR/DDLGen.jar

$JAVA_HOME/bin/java \
-mx500m \
-classpath `cygpath --mixed --path $CLASSPATH` \
com.sybase.ddlgen.DDLGenerator $*

backup scripts

  • schema-out.sh
source env.sh

[ ! -d $OUT_DIR ] && mkdir -p $OUT_DIR

for table in $TABLES; do
    out_file=`cygpath --mixed --absolute $OUT_DIR/${table}-schema.txt`
    printf "dumping $table schema to $out_file... " 
    $DDLGEN -U $USERNAME -P $PASSWORD -S $SERVER:$PORT -D $DATABASE -TU -N$table -O $out_file
    printf "done\n" 
done
  • bcp-out.sh
source env.sh

[ ! -d $OUT_DIR ] && mkdir -p $OUT_DIR

LOG=`dirname $0`/bcp-out.log
cat /dev/null > $LOG

for table in $TABLES; do
    out_file=`cygpath --mixed --absolute $OUT_DIR/${table}-bcp.txt`
    printf "dumping $table to $out_file... " 
    bcp $DATABASE.dbo.$table out $out_file -c -t, -S $SERVER -U $USERNAME -P $PASSWORD >> $LOG
    printf "done\n" 
done
  • schema-in.sh
source env.sh

LOG=`dirname $0`/schema-in.log
cat /dev/null > $LOG

for table in $TABLES; do
    in_file=`cygpath --mixed --absolute $OUT_DIR/${table}-schema.txt`
    [ ! -f $in_file ] && echo "$in_file does not exist for $table, skipping" && continue
    printf "loading $table schema from $in_file... " 
    isql -S$SERVER -U$USERNAME -P$PASSWORD < $in_file >> $LOG
    printf "done\n" 
done
  • bcp-in.sh
source env.sh

[ ! -d $OUT_DIR ] && mkdir -p $OUT_DIR

LOG=`dirname $0`/bcp-in.log
cat /dev/null > $LOG

for table in $TABLES; do
    in_file=`cygpath --mixed --absolute $OUT_DIR/${table}-bcp.txt`
    [ ! -f $in_file ] && echo "$in_file does not exist for $table, skipping" && continue
    printf "loading $table from $in_file... " 
    bcp $DATABASE.dbo.$table in $in_file -c -t, -S $SERVER -U $USERNAME -P $PASSWORD >> $LOG
    printf "done\n" 
done

do i feel silly? yes. do i feel petty? yes. does it make me feel better about myself, given that the sybase DBA told me to contact dbartisan support to see if i could script their tool to do this? oh yes.

Comments

Leave a response

  1. Sam CooleyJune 20, 2008 @ 08:43 AM
    FYI, I found the schema-out.sh to be a helpful example of using ddlgen for a larger extract. I modified that script to support other database objects and include more logging and use of datestamps to distinguish exports. I found that I had to remove spacing between the parameter flags and values as mentioned here: http://www.sypron.nl/2002Q4_ddlgen.pdf in order to get ddlgen working. Thanks for the examples.