Sybase for Mac oS

This week, Sybase announced the beta availability of Adaptive Server Enterprise (ASE) for Mac OS X. My experience with Sybase on Mac OS X goes back to proto-Mac OS X, when I picked up a two floppy set of Sybase version 4.something for my NeXTStation. That was kind of cool, running a solid data server that fit on two floppies.

A Watershed Event

So, what’s the significance of the Sybase announcement? Some are saying that this is the first major enterprise-class database to come to Mac OS X, and with all due respect, this is debatable. Apple has been shipping the enterprise-class MySQL with their enterprise-class Mac OS X Server, and other enterprise-class databases, such as PostgreSQL and OpenBase, are available for Mac OS X. It’s all in what you define as enterprise-class. (And we could argue about that for weeks!)

But still, the release of Sybase ASE is a watershed event, because it’s a certain kind of major enterprise database we’re talking about here, one of the old guard databases like DB2 and Oracle. They’ve been around a long time and have a solid, long-standing reputation. They inspire a confidence that generates a lot of buzz in the industry press.

What’s happening now is a lot like what happened with Linux: when these databases became available for Linux, something folks had been saying all along was validated: Linux is ready for the enterprise. It doesn’t matter whether one single company builds a system with Linux and one of these major enterprise databases; their availability was a stamp of approval.

As with the database rush to Linux, Sybase is again leading the way. On Linux, it started with their quiet release of CT-Lib, which enterprising freaks used to connect even to Microsoft SQL Server. A short while later, the Sybase server appeared for Linux, along with Oracle, DB2, and others.

So Sybase is once again sticking its neck out, to its own benefit and that of other big database vendors. It won’t be long before we see Oracle for Mac OS X (actually, they’ve already committed). So this is big news. But is it fun?

First Look

I headed over to http://www.sybase.com/mac and grabbed the trial version of the ASE beta. It comes as a disk image containing an installer, some release notes, and ODBC libraries. The installer is a typical Mac OS X package installer, and did not require a reboot on my system. It installed everything into /Applications/Sybase, and took up about 550 megabytes of disk space. At the end of the installation, the Sybase server is launched (process name: dataserver), and is ready to accept logins from either the isql command-line utility (/Applications/Sybase/OCS-12_5/bin/isql) or jisql, the Java-based GUI query tool (/Applications/Sybase/jISQL.app. Figure 1 shows the jisql login window.

Safe Shutdowns

Mac OS X 10.1 does not support shutdown scripts, so you should shut down Sybase before you power down or reboot your computer. To do this, log into jisql as the superuser (Username: sa, blank password), and type the command shutdown into the Input Window, and click Go, as shown in Figure 2. (You could also use isql for this)

To start the server again, run these commands as a user with administrative privileges:

% cd /Applications/Sybase/ASE-12_5/install
% sudo startserver -f RUN_SYBASE

Interacting With the Server

As shown in Figure 2, you can use the jisql application to send commands to the database server. If you’re a command-line junkie, you can also use the isql query tool. But, you’ll need to set up your environment first by sourcing SYBASE.csh:

% source /Applications/Sybase/SYBASE.csh

Once you’ve done this, you can log in to isql using the sa username and blank password. After you’ve logged in, you can issue queries against the database. In the following listing, I’m inspecting all the users in the database server’s sysusers table (note that each command should be followed by the go directive):

% isql -Usa -P""
1> select name from sysusers
2> go
 name
 ------------------------------
 dbo
 dtm_tm_role
 guest
 ha_role
 navigator_role
 oper_role
 probe
 public
 replication_role
 sa_role
 sso_role
 sybase_ts_role                 

(12 rows affected)

A Sample Database

Poking around system tables can be pretty boring. Sybase includes a sample database of authors and publications. To use this database, log in and issue the use pubs2 command. (This is the Sybase analogue of cd.) Once you’re in the pubs2 database, you can use SQL statements to work with the various tables:

% isql -Usa -P""
1> use pubs2
2> go
1> select au_fname, au_lname from authors
2> GO
 au_fname             au_lname
 -------------------- ----------------------------------------
 Abraham              Bennet
 Reginald             Blotchet-Halls
 Cheryl               Carson
 Michel               DeFrance
 Ann                  Dull
 Marjorie             Green
 Morningstar          Greene
 Burt                 Gringlesby
 Sheryl               Hunter
 Livia                Karsen
 Chastity             Locksley
 Stearns              MacFeather
 Heather              McBadden
 Michael              O'Leary
 Sylvia               Panteley
 Albert               Ringer
 Anne                 Ringer
 Meander              Smith
 Dick                 Straight
 Dirk                 Stringer
 Johnson              White
 Akiko                Yokomoto
 Innes                del Castillo                             

(23 rows affected)

Superuser

So far, you’ve been using the superuser without a password. This is a bad idea, even if it is the default; it’s like having a blank root password. You should change the password as soon as possible with the sp_password stored procedure. The following isql session sets sa’s password to secret:

% isql -Usa -P""
1> sp_password NULL, secret
2> GO
Password correctly set.
(return status = 0)

Just as you don’t do everyday tasks on your Unix machine using the root account, you shouldn’t use the sa account for database development. Use the create database statement to create a separate database for your development, add a new user with sp_addlogin, and make that user the owner of the new database with sp_changedbowner. That way, you have the Sybase equivalent of a home directory to mess around in:

% isql -Usa -Psecret
1> create database dev_database on default=4
2> go
CREATE DATABASE: allocating 1024 logical pages (4.0 megabytes) on disk
'master'.
1> sp_addlogin bjepson, open_sesame, dev_database
2> go
Password correctly set.
Account unlocked.
New login created.
(return status = 0)
1> use dev_database
2> go
1> sp_changedbowner bjepson
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
Database owner changed.
(return status = 0)

Now I’ve got a user called bjepson, who is the owner of the four megabyte dev_database database. I can log in with the command isql -Ubjepson -Popen_sesame. You can do the same with your own installation, and start playing around!