Getting Started With Sybase
Installation

I've successfully installed and used Sybase Adaptive Server Enterprise versions 11.9.2 and 11.0.3.3 on Linux. I've installed Sybase ASE version 11.0.3.3 on FreeBSD but could never get it to work properly. I've never had access to any other version of Sybase ASE for Linux or FreeBSD or any version of Sybase ASE for other platforms.

You can download Sybase ASE from the downloads section of the Sybase site. It comes as a set of RPM's for Linux and a tarball for FreeBSD.

Debian Linux

The easiest way to install Sybase ASE on Debian Linux is to install RPM and then use it to install the RPM's. On newer Debian releases, if you have configured apt as illustrated in the APT-HOWTO, run apt-get install rpm. If your distribution came with RPM, you may be prompted to enter a CD. If not, it will be downloaded from the internet. Once RPM is installed, log in as root and run rpm --initdb. Then, you can follow the directions below for installing on RPM Based Linux. The only difference is that you must use rpm -i --nodeps instead of rpm -i to install the RPM's.

Slackware Linux

The easiest way to install Sybase ASE on Slackware Linux is to install RPM and then use it to install the RPM's. The RPM package is available from ftp.slackware.com. You can install it using installpkg. Once RPM is installed, log in as root and run rpm --initdb as root. Then, you can follow the directions below for installing on RPM Based Linux. The only difference is that you must use rpm -i --nodeps instead of rpm -i to install the RPM's.

RPM-based Linux

Both versions of Sybase ASE for Linux require glibc version 2.1.2 or greater. I was able to install them both on RedHat 6.2 and 7.2, Mandrake 8.1 and Turbolinux 7.0. I was not able to install them on Redhat 5.2.

Version 11.9.2

To install version 11.9.2 on RPM Based Linux, you should download the following RPMS (at minimum) and install them using rpm -i.

sybase-openclient-11.1.1-3
sybase-common-11.9.2-3
sybase-ase-11.9.2-3

The other RPMS provide documentation and localization support for several languages.

During RPM installation, a sybase user will be created. You should give this user a password.

Version 11.0.3.3

To install version 11.0.3.3 on RPM Based Linux, you should download the following RPMS (at minimum) and install them using rpm -i.

sybase-ase-11.0.3.3-6
sybase-ocsd-11.0.3.3-6

The other RPMS provide documentation and localization support for several languages.

Version 11.0.3.3 does not create a sybase user when the RPMS are installed. After installation, you must run the following commands as root.

export SYBASE=/opt/sybase
/opt/sybase/install/sybinstall.sh

Answer Yes or y to all questions. The script will create a sybase user and prompt you for a password.

FreeBSD

Sybase also provides tarballs for FreeBSD which must be untarred in /usr/local. The FreeBSD version is actually a set of Linux binaries with FreeBSD-native development libraries. To run the FreeBSD version, you must have Linux Binary Compatibility and the RPM package installed.

The install/sybinstall.sh doesn't work out of the box. I modified it as follows:

After applying these modifications, the script ran, but it failed to build the server. Also, anything I compiled against the native API crashed immediately.

I'm sure that there is some combination of software versions and hacks that will make it work, but I have yet to determine that combination.

Starting the Database at Boot Time

The RPM distribution of Sybase ASE installs a script in /etc/rc.d/init.d which starts the database at boot time. For non-RPM distributions, you may need to install a script like the following. Replace /opt/sybase-11.9.2 with whatever directory Sybase ASE is installed in.

#!/bin/sh

export SYBASE=/opt/sybase-11.9.2

export PATH=$PATH:$SYBASE/bin:$SYBASE/install

case "$1" in
        start)
                for i in `ls $SYBASE/install/RUN_*`
                do
                        su -c "startserver -f $i" sybase > /dev/null 2>&1;
                done
                ;;
        stop)
                kill `ps -efa | grep sybase | grep -v grep | awk '{print $2}'`
                ;;
        *)
                echo $"Usage: $0 {start|stop}"
                exit 1
esac

exit 0

Install this script and run it with the "start" option to start up the database. Running it with the "stop" option shuts the database down. To access a database, it must be running.

Creating a Database

The instructions for initializing a server for version 11.0.3.3 are very different.

See Initializing an 11.9.2 sever and Initializing an 11.0.3.3 server.

Once you have initialized the server, you should start the server. From this point on, the database creation process is the same for both versions.

The initialization process should have created a server named localhost (or whatever name you assigned it) with an administrative user named sa which has no password.

To set the administrative user's password, log into the database with the following command. When it prompts you for a password, just press return. Note that in the following commands, the /opt/sybase-11.9.2 path is just an example. For version 11.0.3.3 you should just use /opt/sybase. On non-Linux platforms, you may even use a different path that that.

/opt/sybase-11.9.2/bin/isql -U sa -S localhost

At the prompt, run the following stored procedure to give sa the password sapassword.

sp_password null,"sapassword","sa"
go

Now logout.

quit

Log back in using the following command. When it promts you for a password, type sapassword.

/opt/sybase-11.9.2/bin/isql -U sa -S localhost

Though you could create your own tables using the sa user and the system database, it's not a good idea. You should create a user database. The following commands create a database named testdb.

create database testdb
go
quit

Now log into the database you just created with the following command.

/opt/sybase-11.9.2/bin/isql -U sa -S localhost -D testdb

Creating a user is a 2 step process. First a universal login must be created, then it must be added as a user to a particular database. Execute the following stored procedures and queries to create a login named testuser with password testpassword, add it as a user of testdb and give it all permissions.

sp_addlogin "testuser","testpassword"
go
sp_adduser "testuser"
go
grant all to testuser
go
quit

Now you can log in as testuser using the following command. When promted for a password, enter testpassword.

/opt/sybase-11.9.2/bin/isql -U testuser -S localhost -D testdb

A common problem when using Sybase ASE in this minimal configuration is running out of log space. If you run out of log space, queries will hang until space is freed up. You might get an error like this:

The transaction log in database testdb is almost full. Your transaction is
being suspended until space is made available in the log.

To dump the log, log into the master database as the sa user as follows. When it promts you for a password, type sapassword.

/opt/sybase-11.9.2/bin/isql -U sa -S localhost

Once logged in, execute the following series of commands.

dump tran testdb with no_log
go

This should be enough to get you started. To set up more complex configurations, consult the Sybase ASE documentation.

Accessing a Database

The first step in accessing a Sybase ASE database is setting up your environment. The Sybase client is called isql. Note that unixODBC and Interbase both have clients called isql as well which could be installed in /usr/bin, /usr/local/bin or /opt/interbase/bin. If you have unixODBC or Interbase installed, you'll have to decide which client you want the isql command to run. If you want it to run the Sybase client, follow the instructions below. Otherwise, you'll have to run the Sybase isql client by it's full pathname.

In the configuration script for whatever shell you use, create an environment variable called SYBASE containing the directory you intalled Sybase ASE in. Then add $SYBASE/bin to your PATH environment variable.

For Bourne shells:

SYBASE=/opt/sybase-11.9.2
PATH=$PATH:$SYBASE/bin
export SYBASE PATH

For C-shells:

setenv SYBASE /opt/sybase-11.9.2
setenv PATH ${PATH}:${SYBASE}/bin

Now, if you log out and log back in, you can access the database using the isql client.

isql requires a username, server and database name to access a database. The server parameter does not refer to the hostname of the machine you want to connect to, but rather to an entry in the $SYBASE/interfaces file. When a database is created, the process puts an entry in the $SYBASE/interfaces file on the machine the database was created on. This entry can then be copied into the $SYBASE/interfaces file on other machines to provide them remote access to the database. Below is a sample entry for a server named remotehost which refers to an instance of Sybase ASE running on testhost.testdomain.com, on port 4100, accessible over tcp on ethernet and a similar entry for a server named localhost which is running on localhost.


remotehost
	master tcp ether testhost.testdomain.com 4100
	query tcp ether testhost.testdomain.com 4100

localhost
	master tcp ether localhost 4100
	query tcp ether localhost 4100

Similar entries can be made to access servers running on other hosts.

To access the database testdb on the server localhost as the user testuser, run the following command.

/opt/sybase-11.9.2/bin/isql -U testuser -S localhost -D testdb

Once you're connected to the database, the isql client prompts you to enter a query. Queries may be split across multiple lines. To run a query, type go on a line by itself. To exit, type quit on a line by itself.

A sample isqlsession follows.

[user@localhost user]$ /opt/sybase-11.9.2/bin/isql -U testuser -S localhost -D testdb
Password: 
1> create table testtable (
2> col1 char(40),
3> col2 integer
4> )
5> go
1> select name from sysobjects where type='U'
2> go
 name                           
 ------------------------------ 
 testtable                      

(1 row affected)
1> sp_help testtable
2> go
 Name                           Owner                         
         Type
 ------------------------------ ------------------------------ ---------------------- 
 testtable                      testuser                      user table             

(1 row affected)
 Data_located_on_segment        When_created               
 ------------------------------ -------------------------- 
 default                               Jan 14 2002 12:24PM 
 Column_name     Type            Length Prec Scale Nulls Default_name   
         Rule_name       Identity 
 --------------- --------------- ------ ---- ----- ----- --------------- --------------- -------- 
 col1            char                40 NULL  NULL     0 NULL           NULL                   0 
 col2            int                  4 NULL  NULL     0 NULL           NULL                   0 
Object does not have any indexes.
No defined keys for this object.
Object is not partitioned.
Lock scheme Allpages
The attribute 'exp_row_size' is not applicable to tables with allpages lock scheme.
 
 exp_row_size reservepagegap fillfactor max_rows_per_page 
 ------------ -------------- ---------- ----------------- 
            1              0          0                 0 
(return status = 0)
1> insert into testtable values ('hello',50)
2> go
(1 row affected)
1> insert into testtable values ('hi',60)
2> go
(1 row affected)
1> insert into testtable values ('bye',70)
2> go
(1 row affected)
1> select * from testtable
2> go
 col1                                     col2        
 ---------------------------------------- ----------- 
 hello                                             50 
 hi                                                60 
 bye                                               70 

(3 rows affected)
1> update testtable set col2=0 where col1='hi'
2> go
(1 row affected)
1> select * from testtable
2> go
 col1                                     col2        
 ---------------------------------------- ----------- 
 hello                                             50 
 hi                                                 0 
 bye                                               70 

(3 rows affected)
1> delete from testtable where col2=50
2> go
(1 row affected)
1> select * from testtable
2> go
 col1                                     col2        
 ---------------------------------------- ----------- 
 hi                                                 0 
 bye                                               70 

(2 rows affected)
1> drop table testtable
2> go
1> quit
Accessing a Database With SQL Relay

Accessing Sybase from SQL Relay requires an instance entry in your sqlrelay.conf file for the database that you want to access. Here is an example sqlrelay.conf which defines an SQL Relay instance called sybasetest. This instance connects to the testdb database on the server localhost as the user testuser with password testpassword.

<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>

        <instance id="sybasetest" port="9000" socket="/tmp/sybasetest.socket" dbase="sybase" connections="3" maxconnections="5" maxqueuelength="0" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass">
                <users>
                        <user user="sybasetest" password="sybasetest"/>
                </users>
                <connections>
                        <connection connectionid="sybasetest" string="server=localhost;db=testdb;user=testuser;password=testpassword;" metric="1"/>
                </connections>
        </instance>

</instances>

Now you can start up this instance with the following command.

sqlr-start -id sybasetest

To connect to the instance and run queries, use the following command.

sqlrsh -id sybasetest

The following command shuts down the SQL Relay instance.

sqlr-stop sybasetest