Getting Started with Sybase Express for Linux

Maybe I'm a complete loner here, but I was tickled pink to learn that Sybase, my former employer, had released a free version of their flagship product, Adaptive Server Enterprise (ASE), for Linux. The free version is called ASE Express.

After I downloaded it to test it out, I realized that many people who use PostgreSQL or MySQL probably don't think about database software with the mindset that ASE was created in. It's also glaringly obvious to anyone who has given ASE Express a shot that the Quickstart documentation falls far short of what users need to do anything productive with it (and links to the other docs are broken at time of writing). So let's go over some of the finer points that will get you to the point where there is useful documentation available to take you the rest of the way.

Installing ASE Express

Anyone who reads my articles regularly knows that I don't usually cover how to install software on a Linux system, simply because I assume everyone already knows how to run make. However, this install is slightly out of the ordinary.

I created a directory called /tmp/sybase and put the tarball there. The tarball is what I consider a "miscreant tarball," because it fails to create its own parent directory, and will happily spew files all over whatever directory you untar it in. Once the tarball is in a safe place, untar it, but don't run the setup script yet!

One thing the documentation does cover is preparatory steps before installation begins. I'll quickly go over these. The first step is to create a "sybase" user on your system. I also created a "sybase" group, for consistency and convenience (I can add myself to the group, for example). So I ran groupadd sybase, and then useradd -d /opt/sybase -g sybase. I know I'm planning to install to /opt/sybase, so I made that the home directory for the new user. Don't forget to give the user a password!

Next are the kernel shared memory parameters. Default on most systems is 32K, but Sybase requires 64K. On most systems, you can use sysctl to list the current value for the kernel.shmmax parameter by running sysctl kernel.shmmax. The value returned is in bytes. You can set the value to 64K on the fly by running sysctl -w kernel.shmmax=67108864, but this will get lost upon reboot. To make the setting stick, you can put the sysctl command in your rc.local file, or you can put the setting in /etc/sysctl.conf on Red Hat, Fedora, and SUSE systems (and probably others).

Once that's all finished, you'll need to become the sybase user and run the setup script located in the directory you untarred in. This will launch the Java-based InstallShield application, which walks you through the installation. The only section of the installation that new users might find tricky is the screen which asks you to select the components to be installed. The reason this is tricky is because there is no corresponding documentation telling you what these components are! Of course, you want an ASE server; that part was easy. But the "Backup," "Monitor," and "XP" servers? Do you need these? Are these essential? They're all checked by default!

You can certainly get away with installing everything, whether you use it or not. The monitor server is useful for getting statistics back about ASE's performance. The backup server will configure itself to talk to a tape device, and does pretty much what you'd imagine it does -- it backs things up -- but in a way that's optimized for dealing specifically with Sybase's products.
The XP server is not a bad joke, and it's not Windows-related. It's an external engine managed by the main ASE process, and it's pretty cool. XP server houses what are called extended procedures. Regular stored procedures are extremely powerful on their own, but they limit you to the confines of the Transact-SQL language. An extended procedure bridges the gap between SQL and the rest of the system. For example, there's no function in Transact-SQL for sending email, but with an extended procedure, you can just call the mail system command directly. The beauty of this is that it's called the same way as a regular stored procedure in your SQL, so it's pretty transparent to the end user of the procedure. The difference is that on the back end, ASE is sending an RPC request with the name of the procedure to execute to the external XP server daemon. In short, you'll want this, even if only to learn more about it.

That should get you through the installation. At the end, the procedure pops up a window and shows you everything it's doing, including starting up the server with some default values, which are fine for now, but deadly in production. More on that later.

It's running - now what?

If you've never used Sybase, I really have no clue how the folks at Sybase expect you to know what to do next. They don't even tell you, "hey, there's a GUI you can use to manage all this, and there's a default user and stuff too!" Well, I've just told you, but there's even more to know than that. Here's the scoop:

ASE comes with a GUI called "Sybase Central," which looks somewhat like the open source GUI database management tools. However, even if you're logged in as the sybase user, you can't automagically run the thing. First of all, where's the binary? There's not even an /opt/sybase/bin directory! First of all, the binary is called scjview, but it's not in your PATH. Launching it directly also won't work as you want it to. The secret magical incantation here is that there is a shell initialization script that needs to be sourced when the sybase user logs in. In fact, there's one for Bourne shells and one for C shells. Since I'm using Bash, I log in as the sybase user, open my .bash_profile, and add this line:

. /opt/sybase/

Once that's done, you should be able to launch scjview, but the fun's not over! The login! This one actually took me a while, because even when I worked for Sybase, nobody used a default login. Then I noticed this in a ps -ef listing:

sybase 31309 1 0 Sep14 ? 00:00:00 sh -c /opt/sybase/ASE-12_5/bin/monserver -SLIVID -Usa -P -l/opt/sybase/ASE-12_5...

Sigh. Yes, the -U is the username, and the -P is the (blank!?!) password. Open up scjview, choose Tools/Connect, and you should be greeted with a login window asking you for a username, password, and a server to connect to. The default server name should be your hostname in all caps, and this is the one you want. Your username should be "sa," and the password field, as unnatural as it feels, should be blank. This is the default value I referred to as "deadly" earlier. Press OK, and you should be well on your way to DBA gurudom.

Further reading

This article should help you get into learning more about ASE Express. Be sure to check out all the online help accessible from within Sybase Central. There's plenty there to get you started. You can also take a gander at some of the documentation on Sybase's Web site.

Other tools

Sybase has been around a long time. So have Sybase users. As a result, there are actually lots of tools available for use with Sybase products, and ASE, available on freshmeat.

In addition, Sybase has made lots of cool stuff to go along with ASE. For command-line junkies, you'll want to learn all about the isql utility, which is basically a Sybase shell where you can do at least as much as you can do in the GUI, only faster. There's a GUI version of isql, called, of course, jisql. I'm not sure if it's installed with ASE express by default or not -- I believe I had to choose it explicitly. It's useful I suppose, but if you want a straight-up SQL GUI for Sybase, you'll probably find one that's just as good (and not written in Java) on freshmeat.

In closing

I hope this has been helpful to some of you who wish to learn how to use and administer a database that is widely used in large production environments. Learning the proper use of ASE may also help you discover why all the database snobs pick on your favorite database so much, not to mention that the word "Sybase" is a nice addition to any resume.