Sybase for Mac OS X: First Look

by Brian Jepson

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.






Figure 1. Logging into jisql as the database administrator




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





Figure 2. Shutting down the Sybase data server




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!