Introduction to Sybase, Part 1: Setting Up the Server

Sybase has released their SQL Database product for Linux. This is a port of their full commercial database product—it is not crippled in any way. This series of articles will describe how to install and configure the server and how to set up and use clients. It would be impossible to teach everything about developing and maintaining client-server applications in a short series of articles, but these should get you started.

The Sybase SQL Server is an industrial-strength client-server database engine. It manages data and allows many clients to access this data efficiently and securely. The Sybase SQL Server allows you to concentrate on writing your application rather than on writing data access and security code.

Before installing the server, you must determine if your system can handle it. The server with all the documentation will require approximately 180MB of disk space plus space for your databases. A running server will take a minimum of 21MB of RAM. If you have many clients, you should give your server much more RAM; so for small development systems, a minimum of 32MB is required. For small-scale production servers, I would suggest a minimum of 64MB of RAM. As usual, the more RAM you give it, the faster it runs.

I have installed the server on numerous Red Hat 5.1 systems, as it requires the newer glibc libraries. If you have an older Linux installation that does not include the glibc libraries (Red Hat 4.2 and below), you should upgrade your system. If you have a non-Red Hat system, you will also need an RPM tool to install the files. The server is distributed as two RPM files. According to the Sybase Linux web page (http://linux.sybase.com/), you can install the server only on Red Hat Linux 5.x and Caldera OpenLinux 1.3. In my opinion, you could install it on any Linux system—it would just be a bit more difficult than if you are using the Red Hat or Caldera version.

The web URL to access these files is http://linux.sybase.com/. This site will point you to one of the sites that offers the files for download. Remember this site—it contains important information you will need when using your SQL Server. After entering your registration information, you will be able to download two RPM files: one containing the Sybase SQL Server, the other with documentation in HTML and PostScript. This is approximately 40MB of data, so if you have a slow Internet connection, be prepared to wait a while. Fortunately, installing the software will take much less time than downloading it.

To install the server, log in to your Linux system as root and type the following command:

rpm -i sybase-ase-11.0.3.3-1.i386.rpm

You will be asked to read the license agreement; then it will install the Sybase server to the /opt/sybase directory. On my system, this is a problem because I usually don't have enough space on the partition to hold /opt/sybase, so I make a link that redirects /opt to /usr/local before installing. This works because it doesn't matter where the software is installed—it will work from any directory. From now on, I'll assume you have installed the server in its default location.

During the installation, you will be asked to create a group named sybase and a user named sybase. You will use the sybase user to perform maintenance on the database server. All users who need access to the Sybase server should be members of the group named sybase.

Now install the documentation files by running the following command:

rpm -i sybase-doc-11.0.3.3-1.i386.rpm

One thing the installation does not do is set the owner of the files correctly. While you are still logged in as root, issue the following command:

chown -R sybase:sybase /opt/sybase

Now that all the files are installed on your system, it is time to configure an SQL server and a backup server. A backup server is used to back up data in the SQL server while the SQL server is running. It guarantees that when restored, your database will have the proper integrity. Copying your database files from the operating system will not guarantee that your database tables will be restored properly.

A single host can have multiple SQL servers if necessary. I would not recommend doing so, but it is an option. For our example, we will configure a single SQL server and a single backup server on our host.

Log in as the user sybase using the password you set when installing the SQL server. Since it is your first login as the sybase user, the login script will ask if you would like to run sybinit. The sybinit program is used to configure new and existing Sybase servers and is located in the /opt/sybase/install directory.

If you installed the documentation, you can follow along with the installation by using your browser and accessing the file /opt/sybase/doc/howto/howto-ase-quickstart.html. There isn't room in the magazine to display all the screens you will see. These screens are documented in the Quickstart Guide that comes with the server.

The first option you should pick is option 3, “Configure a Server product”. Configure the SQL Server first by selecting option 1. Since this is a new server, select option 1 again. Each server should have a unique name. I recommend naming the server based on its function. For example, a production decision support server could be named dss_prod. A development accounting server could be named acctng_dev. For this example, name the server linux_dev. When you finish filling in the data for a screen, press ctrl-A to save your data. Press ctrl-A now. At this point, nine more steps are required to configure this SQL server.

Select option 1. The interfaces file tells Sybase products where servers are located. Each server will listen on a specific port on its host. Just like SMTP mail, TELNET and web services, Sybase servers need a unique port to allow network connections. The interface file will hold the server name, host name and port number for each sybase server on your network. Select option 3 to add the port information for the linux_dev server. The sybinit program will automatically fill in the host name for your host. You should specify the TCP/IP port your server will listen on. For this example, select option 2, then specify 2360 as the port number. Any unused port will work. Press ctrl-A to save this entry in the interfaces file. After you confirm that everything is correct, press ctrl-A again.

If you have multiple sybase servers that you access, you can add information for each server into your interfaces file.

Before continuing, a little background information will be helpful. Database tables will hold the data for your application. For example, an accounts payable application would have an invoice table, a vendor table and a payment table. A database can hold many tables. A Sybase server will have multiple databases. An accounting application might have general ledger, accounts payable and accounts receivable databases. When a server is installed, it will have four databases:

The sybase server manages disk space in devices. A device is a pre-allocated file of a specific size. A device file can be up to 2GB. A single server can have many devices. Databases are created on devices.

Select option 2. The master device holds the master database, the model database and the tempdb database. Its default size is 21MB. You can move the location of this file if you wish. If you have enough disk space, you can leave it in its current location. Press ctrl-A to save this screen. The sybinit program will give you a warning about the file name you selected. This is normal; on Linux, it will always give this warning. On other UNIX operating systems, the Sybase server devices should be raw partitions on the disk. This isn't possible on the current version of Linux, so we have to put our devices in operating system files.

Select option 3. The sybsystemprocs database contains stored procedures used to maintain the server. This database can also be used to store any procedures you write for server maintenance. I recommend you double the size of this database, so that you can add additional stored procedures to your server. When you do this, you have to select option 5 to increase the size of the device before you select option 1 to increase the size of the database. Put 32MB for options 5 and 1. You can leave the rest of the options as they are, unless you would like to place the device file in a different location. Press ctrl-A to save this screen.

Select option 4. As the server runs, it writes error messages to a text log file. This screen lets you set the location and name of this file. I recommend you give this file the same name as the database server. Change option 1 to /opt/sybase/install/linux_dev.log. Press ctrl-A to save this screen.

Select option 5. The database server needs to know the name of its backup server. I always give the backup server the same name as the database server, with _bs on the end. Change option 1 to linux_dev_bs. Press ctrl-A to save this screen.

Select option 6. The server can use many languages. I have never used anything other than us_english, so I can't tell you what will happen when choosing another language. For our example, just press ctrl-A to save the default for this screen.

Select option 7. You can configure which character set to use when communicating between a client and the server. Each client will tell the server which character set it should use. If you do not know for sure that you need another character set, you should accept the defaults for this screen. Press ctrl-A to save this screen.

Select option 8. Here you can specify which order to use when sorting data. By default, the server uses a binary order when sorting. This is the fastest sort method; however, when sorting words that are upper and lower case, the server uses the ASCII character set to sort so that uppercase letters are sorted before lowercase letters. Change the sort order to the dictionary sort order so that words are sorted properly regardless of case. Press ctrl-A.

Select option 9. If you wish, you can have the server maintain auditing information about users. For our example, we won't install auditing. Press ctrl-A to configure the server to not maintain auditing records.

We have now told the sybinit program everything it needs to know to configure your SQL server. Press ctrl-A to save your configuration. The sybinit program will now configure your SQL server. It will warn you about the master device file again, but it will create the devices and prepare the server for use. Your new SQL database server is now running on your system.

Press ctrl-A to go to the previous screen. The next step is to configure a new backup server. Select option 2, then option 1 to configure a new backup server. The name of the backup server should be linux_dev_bs. Press ctrl-A to save the backup server name.

I recommend changing the name of the backup server log file to /opt/sybase/install/linux_dev_bs.log. This server needs to be specified in the interfaces file also. It will listen on its own unique port. Select option 2. Select option 3 to add a new listener. As before, the host name has already been specified. Change option 2 to 2361. This will be the port for the backup server. Press ctrl-A to save this screen. Press ctrl-A again to write this entry in the interfaces file.

Everything else should be correct, so press ctrl-A to save this screen. The sybinit program will now configure the backup server and start it for you. Both the SQL server and the backup server should now be running on your host.

We are almost done, but there are three steps we need to finish. Press ctrl-A to go to the previous screen. Select option 4, “Configure an Open Client/Server Product”. These three products should be configured before you use them. To be honest, I don't know what configuring these products does because no options are available and they seem to work before configuring; however, it can't hurt to follow the directions. Select each of the three options one after the other to configure them. When you are finished, press ctrl-A to leave this screen. Press ctrl-A again to leave the sybinit program.

All of your entries are recorded in a log file. If you are configuring a production system, print this log file and keep it on hand in case you need to recreate the server that crashed at two in the morning.

There is a basic client that comes with the Sybase SQL Server. This program is called isql. It is an interactive SQL program that allows you to enter SQL commands to the server and see the results. At the command line, type:

isql -Usa -Slinux_dev

The -U option tells the isql program which user name to use; sa is the system administrator account. It is similar to root on Linux. It has all rights in the server. The -S option specifies to which server to connect. In our case, the server name is linux_dev. The isql program will ask you for a password. Right after an installation, the sa user does not have a password, so just press enter. You can now enter SQL commands that will run on your SQL server.

The first command to run is a stored procedure that lets you change the password of a user. At the 1> prompt, type

sp_password null,'

sp_password is the name of the stored procedure. The sa user has no password, so we pass null as the first parameter. This parameter should be the password of the current user. The second parameter is the new password; put this password in quotes.

At the 2> prompt, type:

go

go tells the isql program to execute the command. If you make a mistake while typing a command before typing go, you can type reset to erase the command and try again.

All of the configuration information for the SQL server is stored in tables in the master database. Type the following:

1> select name from sysdatabases
2> go

This is an SQL command that queries the sysdatabases table. It will list the names of all the databases in your server. Almost all of the configuration information for the SQL server is stored in database tables. The documentation will give you more information on these tables.

To quit the isql program, type quit at the 1> prompt.

The SQL server comes with a script that will install an example database on your server. To install this database, type the following command at the $ prompt:

sql -Usa -i ~sybase/scripts/installpubs2 \
   -Slinux_dev

Type your new password when prompted. The script will create a database called pubs2, then create tables with data in them. You can now type queries like the following:

isql -Usa -Slinux_dev
Password:
1> use pubs2
2> go
1> select * from authors
2> go
au_id  au_lname    ...
----------- ------------------- ...
172-32-1176 White    ...
213-46-8915 Green    ...
...
1> quit

Before you shut down your Linux system, you should shut down your Sybase servers. Do this using the isql program. Log in as the sa user to shut down the server. Once you are in isql, type shutdown SYB_BACKUP to shut down the backup server. SYB_BACKUP is the default name for a backup server. Then type shutdown to shut down the SQL server; this will remove both servers from memory. Now you can shut down your Linux system. If you don't shut down the servers properly, you could corrupt data. I recommend writing a script to perform this task automatically.

To start up the servers, you need to be logged in as the user sybase. Change to the install directory and type:

 ./startserver -f ./RUN_linux_dev

to start the SQL server and then

 ./startserver -f
./RUN_linux_dev_bs

You have installed the SQL server and the backup server and you know how to start and stop it. There is still more to learn about the server. At the end of this article is a list of resources that can help you learn about your new Sybase SQL server. I recommend reading the PostScript documentation that comes with the server. If you don't want to print the hundreds of pages of documentation, you can use ghostscript to view them. For an easier way to view the documentation, go to http://sybooks.sybase.com/dynaweb and select Sybase Version 11.0.x Products. You can read all the documentation via Sybase's web site.

Next month's installment will be about writing database clients and installing the Sybase extension for Perl (sybperl) that will enable writing database clients in Perl.

Resources