dumping sybase schema

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

• rip it out from freely downloadable developers’ distro of sybase

$ 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.