September 22, 2004

SysAdmin to SysAdmin: Getting started with Sybase Express

Author: Preston St. Pierre

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/SYBASE.sh

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
. If you've used Sybase Express and want to share your opinion, feel free to submit your review to our Product Guide.

Click Here!