Installing and Configuring the Sybase SQL Anywhere Server for OS/2

Written by Fernando Lozano

After five months at EDM/2 writing about mSQL, this article will be about another database that can be run under OS/2. Yes, there are many application requirements that can't be satisfied by mSQL and you will need another database, but this does not mean that you should just abandon mSQL for a more powerful database. It's just a question of picking the right tool for the right job (or the tool that fits your budget).

As a consultant, I've already had the opportunity to work on projects using most well-known databases and some not so well known. Sometimes you can choose which one will be used, but sometimes the customer or other factors dictate the use of a particular database.

Fortunately, OS/2 is not short of options in this field. Even the Warp client is adequate as a server platform for a small to medium workgroup, and OS/2 software will not force you to use Warp Server when you do not need a sophisticated file server, but just a database server (or a web server, or a messaging server, or...)

As time permits, I intend to write about every database that supports OS/2 as a server platform and its use for web and Java development. (Database vendors, you can send me product evaluation code and documentation right now!) But this does not mean I'll stop writing about mSQL.

mSQL allows me to explain most concepts involved in information systems development, and everyone can run it. You won't need a high-end machine for running the beast, or a T1 line for downloading the software. And there's no need for a six month course on database administration and tuning. Better yet, everything you learn about mSQL can be applied with few modifications to more powerful databases.

What I think would be ideal is for OS/2 development tool vendors to bundle mSQL with their tools, or at least client support and point their customers to a download site. Both development tools vendors and database vendors would gain: OS/2 developers would have more incentive to buy the tools, and there would be more demand for other databases.

The Sybase SQL Anywhere
This article will focus on the Sybase SQL Anywhere database server. This database is actually the Watcom SQL server. Watcom was a very strong OS/2 supporter, providing us with high-quality tools like VX-REXX and Watcom C/C++. The Watcom SQL server was very strong as an embedded database for shrink-wrapped packages. [Note that although Watcom has not supported OS/2 as well since it was bought out by Sybase, they still do release OS/2 versions of the tools inherited from Watcom. Apparently even the low level of support for OS/2 from Sybase is better than what the other database vendors offer. Ed.]

Today, most database vendors segment the market into three niches: the personal/mobile databases, the workgroup databases and the enterprise databases.

The first one targets individual developers, who can work on the personal version and deploy on one of the more powerful versions. Later this niche also focused on mobile users, letting them work on a copy of the company database while disconnected from the office.

The second group is where you have more alternatives. A workgroup can be anything from a 5 node LAN to a 300-user department. On the low end, there are many advantages to using a database server over a file-based database as a DBF file or Access. The main ones are reliability and ease of administration.

The third group is where your company may start to think about turning off the Big Iron. Some companies found it wiser to plug the big iron into the network and transform it on a super-server. On the other end, SMP RISC machines running Unix or SMP Intel machines running OS/2, SCO Unix (and Linux!) are serious contenders for the data center.

Sybase was always one of the leaders of the database server market, but as it matured Sybase found itself in trouble because they had no product for the low-end workgroup segment or the personal/mobile niche. Another problem Sybase had was the lack of good development tools in their portfolio, while most of their adversaries had well known ones.

The second problem made Sybase start negotiations to buy PowerSoft, the makers of the popular PowerBuilder. When PowerSoft bought Watcom, interested in its superior compiler technology, Sybase had the chance to solve the first problem too.

And then Watcom SQL became Sybase SQL Anywhere. You can navigate through the Sybase site and find no mention of this, but Watcom's strong cross-platform presence was retained in most products inherited by Sybase. If you order the Sybase SQL Anywhere evaluation CD, nothing on the web site or the CD cover will give you any hint, but inside you'll find OS/2 and Netware versions of the database.

So, go ahead and order the 60-day trial CD! You will get much more than a "toy" database (as Watcom SQL was considered because of its focus on the embedded market), but a strong server with declarative referential integrity, stored procedures, multiuser and multiprotocol capabilities. It's a real workgroup server which can compete head-to-head against Oracle, Informix and DB2.

Installing SQL Anywhere
My previous articles were about getting the software running and how to write programs using it, and this one will not be any different. But I have to advise you to not download the SQL Anywhere database server. Order the evaluation CD. The reason is that the download version does not contains the SQL Anywhere Open Server Gateway.

The Open Server Gateway is a family of Sybase products that lets you access many databases as if they were Sybase servers, using the proprietary TDS protocol. Remember, SQL Anywhere is actually Watcom SQL and does not speak TDS natively, in addition to other differences in SQL syntax.

But why should I want to connect to SQL Anywhere as if it were a Sybase Open Server? The JDBC driver supplied by Sybase, as part of the jConnect product, only speaks TDS. So, if you want to write a Java application that connects to SQL Anywhere, you need the Open Server Gateway.

When you get your Sybase SQL Anywhere trial CD, follow the following procedure:
 * 1) Insert the "Sybase SQL Anywhere Professional, 60-day evaluation copy" CD on your CD-ROM drive.
 * 2) Enter directory X:\SERVER\OS2, where X: is your CD-ROM drive letter.
 * 3) Run the application SETUP.EXE inside this directory. This will start the familiar Watcom software installer.
 * 4) Click YES in response to the license agreement.
 * 5) Select "Install/reinstall the software" and click OK.
 * 6) Type the path where you want the Sybase SQL Anywhere to be installed and the Win-OS/2 path of your installation, then click OK. The server also installs OS/2 and Windows client support.
 * 7) If you will write C/C++ programs, or maybe if you'll write Pascal programs, select "Install C/C++ development components". As (I presume) you have never used SQL Anywhere or Watcom SQL before, there's no need to select "Install DLLs for 3.2 and 4.0 applications".
 * 8) Select "Install SQL Anywhere Open Server Gateway" and "Install Sybase Open Server components", then fill in the path where you want these to be installed (please do not use the same path you used before for SQL Anywhere) and click OK. Remember, if you do not install these components you will have no Java access to your SQL Anywhere server. If you missed them, you can always run the installer later and install these components.
 * 9) Now click OK in response to the "SETUP will now copy any selected files" prompt. If you want to verify your choices, you can click the Previous... button.
 * 10) Wait while SETUP.EXE copies the files to your hard disk.
 * 11) If you have Lotus Smartsuite for OS/2 installed, SETUP.EXE may complain about the ODBC DLLs already present on your HD. Select "Install the new file and keep the existing file" because SETUP.EXE wants to install the 16-bit Windows ODBC manager and drivers, while SmartSuite has 32-bit OS/2 driver manager and drivers, so you want both to be installed. Then click OK. You may also check "Use this choice for all subsequent DLLs".
 * 12) When SETUP asks about modifying CONFIG.SYS, choose "Make all modifications for you" and click OK.
 * 13) SETUP.EXE will tell you where it saved a backup of the original CONFIG.SYS file. Click OK.
 * 14) A folder "Sybase SQL Anywhere 5.0" will be created on your desktop.
 * 15) Then SETUP.EXE will inform you that SQL Anywhere has been successfully installed. Click Ok.
 * 16) Click OK again to the prompt about rebooting your computer and the shutdown of your system.

Verifying the installation
After rebooting, let's test if our installation is working as intended. Open the "Sybase SQL Anywhere 5.0" folder and double-click the "StandAlone Sample Database" icon. This will start a standalone SQL Anywhere engine, which will ask for confirmation about the trial license. Just reply "y" and minimize the window. letting SQL Anywhere run in the background.

Now return to the "Sybase SQL Anywhere 5.0" folder and double-click the "ISQL" icon. At the login prompt, the "User ID" should be "dba" and the "Password" should be "sql". This is the default administrator (DBA) login for SQL Anywhere.

Then you'll have the "Interactive SQL - sademo (dba) on sademo" window open on your desktop. The nice ISQL windows show three panels: the topmost one will show the results of any SQL statement you send to the database; the center panel will show database statistics about the last statement executed; and the bottom panel is where you type the SQL statement to be executed.

Let's try a simple SELECT. The cursor should already be in the bottom panel, so you just need to type: select * from customer And click Execute. The result of the query will fill the Data panel and the Statistics panel will tell you that 126 rows were returned.

If you do not understand the syntax above, I'd recommend you getting a book about SQL and relational databases. To do serious work, the book by Carl J. Date, "An introduction to Database Systems" is indispensable.

ISQL can help you construct SQL statements against the database you are connected to. On the Edit menu, select "Insert Table..." and you'll have a window listing all tables defined in the sademo database. Note that some have the prefix "DBA.", others the prefix "DBO." and the last ones the prefix "SYS.".

These prefixes tell which user created the tables. As you connected as user "dba", your tables are the ones with the prefix "DBA.". If you do not include the prefix on your SQL statements, the user name is used as default. Prefixes "DBO." and "SYS." are reserved for internal use of SQL Anywhere.

If you select a table name and click Insert, the table name will be included in the Command panel at the cursor position. But you can also click the Columns... button to see a list of columns (fields) for the selected table and insert the field names on the Command panel.

When you are finished, close the ISQL window and stop the SQL Anywhere Sample database by pressing "q" while the server OS/2 Window is in the foreground. Please do not just close the window, as this can lead to data loss as the server will not be shut down properly.

SQL Anywhere architecture
The SQL Anywhere database consists of a database engine, also called the "standalone engine" that can accept connections from local processes by using an operating-system supplied IPC mechanism. This version of SQL Anywhere does not accept remote network connections.

A true network server is implemented by using the SQL Anywhere Server. The server can be configured for a number of network protocol options, and can support many concurrent users.

The client machines have to run the SQL Anywhere Client to be able to connect to the SQL Anywhere Server. The Client communicates with a local application as if it were a local SQL Anywhere Engine, and directs the requests to the remote Server. The results are sent from the Server to the Client, which in turn forwards them to the local application.

If you open ISQL and click "More >>" in the "Interactive SQL Logon" Window, you'll see a number of parameters that may be necessary if you use a real client/server setup, but you'll never use all these options. Note that you can specify a Database Name, a Database File and a Server Name.

Each Engine/Server (or Client) can have a name assigned by a command line option when starting, and you can use this name when connecting to that specific server.

SQL Anywhere Databases
A single SQL Anywhere Engine (or Server) can manage multiple open databases. Each database is entirely contained in a single *.DB file, and you can specify the path to this file at logon. The path file is the real path on the server file system. As specifying this is generally undesirable, you can specify a Database Name in the SQL Anywhere configuration, and then use this name at logon.

If you specify a database file name during the Engine/Server start up (as the icons created for "Server Sample Database" and "StandAlone Sample Database" does) this becomes the default database for clients that do not specify a Database File nor a Database Name.

An easy way to create a new, blank SQL Anywhere database is to use the "Database Tools" windows from ISQL.