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?
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
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
% 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
% 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)
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!