Sybase to MSQL

This is the instruction page for taking the sybase database on tarsus, that defaults for use student (which is the database used by KnowledgeManager on the NeXT), and copying it into an MSQL database.

Fisrt, the data needs to be dumped from the sybase database. Then it needs to be molded into a format that is readable by msql. Since, currently, msql does not provide a decent mechanism for bulk imports, this requires a file that is made up of a bunch of single INSERT commands. Once these files have been made, they must be run through the msql monitor program msql, to create and load the tables. Since the msql version of the KB database has an extra column in the terms table, and and extra table called cvector_map, this extra data must be generated and added to the database. There is a program called bv that does this step for you. Below are the step by step instructions for completing the entire database copy. Finally, since msql does not have the triggers and stored procedures that sybase does, for generating sequence numbers for the UWDAID field of the terms table and the ID of the links table, but does support autonomous sequence transactions, you must also build the sequence for the these tables. The program seq does this for you.

Run sybaseDump.pl
This is a perl script in my home directory tree that gets the data from the terms and links tables in the sybase database, and parses it into the SQL command format needed for msql. To run this script, execute the following commands on scala:
```
 % cd ~bradley/sybase-msql
 % sybaseDump.pl
 
```
When this script is finished, you will find two output files named terms.sql and links.sql.
Create the destination database
Log in to which ever machine is running the msql datbase server, where you wish your database to reside. In the msql bin directory, there is a program called msqladmin. To create a new database, you must run this program in the following manner:
```
 % msqladmin create dbname
 
```
where dbname is replaced with the name you want for your new database. Currently, the msql database server I am using is on brain, in my home directory tree. The path to the bin directory is: ~bradley/MSQL/bin.
Move the .sql files to you server machine

The files terms.sql and links.sql that resulted from Step 1, need to be copied to the machine that is running the msql database server, so they can be run through the msql monitor to create and load the terms and links tables. This can be done with the ftp program.
Run the .sql files through the monitor.
On the msql database server machine, there is a program called msql in the msql bin directory. To load the data into the database, execute the following commands:
```
 % msql dbname < terms.sql
 % msql dbname < links.sql
 
```
where dbname is the name of the msql datbase you are loading the data into. NOTE: The database you are loading the data into, must NOT have existing tables named terms, links, or cvector_map, as the scripts you run will attempt to create them. Running these two commands may take some time, since they are loading a few megabytes of data into the database one row at a time. You will know when each command is done, when the msql monitor says 'Bye!'.
Run bv on your database
On the msql database server machine, you must run the program bv on your database by issuing the following command:
```
 % bv dbname
 
```
where dbname is the name of the database. This step may take a while as well, as it must do some calculation on each row of the terms table, to build the child_vector column.
The program bv exists in my home directory tree on brain, at: ~bradley/kb-database/bv. If you wish to use bv on another machine, simply copy the files bv.c and Makefile to that machine and compile them using the command:
```
 % make bv
 
```
NOTE: If you do this, you may need to modify the Makefile, to point to your version of the msql client library libmsql.a. It is also important to remember that the client library must be of the same version as the msql server that it is going to connect to.
Run seq on your database
On the msql database server machine, you must run the program seq on your database by issuing the following command:
```
 % seq dbname
 
```
where dbname is the name of the database. This step should take a few seconds. It finds the largest value in the UWDAID column of the terms table, and creates a sequence on that table, starting with the next available number. It also does this for the ID column of the links table. This program can only be run on the database if a sequence does not already exist on the terms table. Like bv, mentioned previously, seq resides in ~bradley/kb-database and can be compiled in the same manner if necessary.

Last Updated: 23 Mar 1997
Scott W. Bradley