As I returned from my globe-trotting adventures in New Orleans, where I talked about mobile databases at the CA World 1998 conference, and after my obligatory detour to surf the warm waves of the French Caribbean, I was pleased to see that the latest in traveling database software had completed its journey to my desktop: Sybase Adaptive Server Anywhere (ASA) 6.0! Those of you who read my mobile database miniseries this time last year may remember that I thought its predecessor, Sybase SQL Server Anywhere 5.5, was quite amazing. Now you understand why I was thrilled to evaluate Sybase's latest offering to see what it offered to mobile database users and developers, because Sybase touts it as the most powerful small-footprint database server optimized for mobile and workgroup environments.
The most important new feature in Sybase ASA 6.0 is support for Java in the database. It has a Java virtual machine (JVM) inside the database server that executes Java classes installed in the database. The DBMS creates each new database with a base set of built-in Java classes to provide the basic Java functionality.
You can define your own Java classes in the database. These then become available as user-defined SQL data types. When you use one of these Java classes as the data type for a column, you can store Java objects in the tables in your database. The methods and fields of a Java class are respectively accessed as functions and columns from SQL statements. The SQL syntax for accessing the methods and fields of a class includes the column name with a dot before the name of the field or the method.
In the Java classes, you can use JDBC methods to access the relational data stored in your SQL tables. This combination, together with the JVM in the server, provides a new method to write and implement stored procedures that is much more powerful than conventional SQL. The result is that you can now implement business logic in the database using SQL, Java, or a combination of the two.
Although not so applicable to mobile users, Sybase ASA can exploit SMP architectures. (I would love a dual-processor, dual-disk notebook. If you ever hear of one ...) Sybase ASA can run using multiple operating system threads - by default one per processor - and can thus take advantage of multiple processors. It supports intertransaction parallelism, meaning that it can run separate transactions from separate connections in parallel on separate processors using separate threads. This parallelism allows Sybase ASA to handle larger databases and more concurrent users with faster throughput. But you won't gain anything in single-user, single-transaction applications. Sybase ASA doesn't support intraquery or intratransaction parallelism yet. However, even on single-processor notebooks, its reengineered multithreaded architecture results in more efficient thread switching between multiple users' connections, especially if you have enough memory to keep the thread information cached.
The query rewrite option of the Sybase ASA query optimizer, first introduced in the later maintenance releases of Sybase SQL Anywhere, has been extended to rewrite most transformable subqueries as joins and eliminate unnecessary sorting for distinct clauses where the data is already sorted. In addition to improved performance, this option also makes life easier for developers because they can now use a convenient, readable, or maintainable phrasing in their queries instead of having to use a specific style of query to achieve acceptable performance. In queries where the subqueries cannot be rewritten, the optimizer can generate an execution plan that caches up to four pages of subquery results for reuse. If the optimizer does not generate an appropriate query execution plan, you can supply it with estimates of the outcome of various parts of the query using a nonstandard SQL syntax. Although this feature may be exciting for serious DBAs, nobody should have to use it very often. This is not the kind of feature you should use in applications running against self-managing mobile databases.
The communication between client applications and the database server has been simplified to function in the same way on a single machine (such as on a mobile database platform) as on a networked configuration. On a standalone platform, the application makes calls to the ASA interface DLL, which, in turn, communicates with the database server. In a client/server configuration, the interface DLL connects to a network DLL on the client side, which communicates to the database server via a network DLL on the server side. The network DLLs can communicate over NetBIOS, TCP/IP, IPX, or using QNX messages. The calls to the interface library are the same in both cases.
Another significant change is that Sybase ASA now supports a native ODBC interface, where the ODBC commands are handled directly by the server without requiring a driver to translate ODBC calls to native DBMS calls. It supports ODBC 3.0 with level-2 compatibility except for three-part table names, asynchronous statement execution, and the ability to timeout login requests and SQL queries. This means that Sybase ASA can appear to a client application as a Sybase Open Server. Access through the Sybase Open Server Gateways has been replaced in Sybase ASA by a native Open Client/Open Server interface called Tabular Data Streams (TDSs). This means you no longer need Sybase gateways when using Open Server clients, especially when you're using the SQL Remote replicator. Sybase jConnect, required for JDBC access, also uses the TDS interface, although it does not require the Open Client libraries. As a result, applications can access a Sybase ASA database using Transact-SQL, ANSI-standard embedded SQL, JDBC (through Sybase jConnect), ODBC 3.0, or the native CT-LIB API.
There are several new extensions to the SQL supported, including unsigned versions of the INT and SMALLINT data types, 64-bit BIGINT data types (signed and unsigned to support different value ranges) and additional date and time functions. You can add FIRST or TOP n clauses to a SELECT list to limit the data returned from the server, and you can use full SQL queries instead of table names in the SELECT statement's FROM clause. During table creation, you can define computed columns, which derive their values through expressions applied to the values in other columns. Although these columns cannot be updated directly, you can define update triggers that fire when their values are changed as a result of updates to the underlying columns.
Sybase ASA databases are managed through SQL Central, which also contains wizards to help DBAs code SQL statements correctly. Databases can also be maintained through SQL scripts that now support statements to create, drop, and alter databases, in addition to all the other standard SQL statements. The scripts also support new statements, for instance, to obtain database file-usage information by executing a stored procedure. SQL Central has a new facility through which you can log the statements that it executes to a script file. This is extremely useful if you have to update or maintain the databases of many intermittently connected mobile users asynchronously. DBAs can perform these tasks using an easy-to-use GUI interface, but they can now also obtain a script to perform the same operations on all the mobile databases without online interaction.
As a full-blown, multiuser DBMS, Sybase ASA accepts connections from multiple clients simultaneously. It has full transaction control using row-level locking. You can specify its security controls per user or per group. The new release also has resource governors through which you can control memory usage, number of cursors, and number of prepared statements per connection.
You can also configure its recovery facilities for various scenarios, from single-disk mobile notebooks to multidisk servers with offline storage media. It can run without a log file on lightweight platforms, or it can run with one or two mirrored log files. It supports online and offline full and incremental backups. Database definitions and large volumes of data can be exported and loaded using unload and reload facilities, which you can place under stringent security controls to guarantee that sensitive data is not accidentally wiped out by an unauthorized reload operation.
Sybase ASA has a highly flexible disk usage scheme. Tables and indexes are allocated to table spaces, and you can add the table spaces while the system is running. A table space has to fit in a physical file, which can be much larger than 2GB on Windows NT's NTFS file system, and a table can only be allocated to a single table space. Sybase ASA reuses the space left by deleted rows - as a result, you never have to reclaim wasted space. However, to shrink a database after a huge purge exercise, you may have to unload and reload it.
Through SQL Remote, Sybase's bidirectional replication facility, you can now replicate between Sybase ASA and Sybase Adaptive Server databases. The publisher/subscriber metaphor used in SQL Remote is well suited to mobile database architectures. A publisher can make a subset of its data available to a number of subscribers, even if that data is a subset of data it received from another publisher. In this way, data received from one publisher can be cascaded through intermediary subscribers and publishers to multiple subscribers. One copy of each replicated data item is called the primary copy, which resides in the so-called consolidated database (which you can see as its home base). Updates to copies of the primary copy at other sites are replicated to the consolidated database as submissions. A submission is only accepted at the consolidated database if it doesn't conflict with the primary copy. If there is a conflict, you can resolve it using SQL Remote's built-in conflict resolution mechanisms, which let you choose among the following: minimum value, maximum value, average, additive (for numeric columns only), overwrite, discard, latest timestamp, or earliest timestamp. You can also write your own conflict triggers, which ensure that the data is kept consistent throughout replication. Once a submission has been accepted, it becomes part of the primary copy of the consolidated database and is automatically included in the data made available to the other subscribers through publications. In this way you can implement updates to one mobile database cascading via the host database to the rest of the mobile databases - a useful feature for mobile database architectures. Sybase calls it the consolidating server. Not many systems support this type of cascading replication via the host database.
SQL Remote uses standard message-based APIs, such as MAPI, Lotus VIM, SMTP POP, and FTP message servers to replicate transactions. These asynchronous, message-based, replicate-anywhere communication facilities, together with the multitier granular replication configurations, are ideal for mobile and occasionally connected users. You have to use the Replication Agent for Sybase Adaptive Server (also called the Log Transfer Manager) if you want a Sybase ASA database to act as the primary site in a replication configuration. Sybase ASA also supports Using PowerDynamo (previously called NetImpact Dynamo, the Web server bundled with Sybase ASA), through which you can replicate database changes and entire Web sites using the database's replication utilities. In an SMP environment, the SQL remote connections can access the database through different threads from the users' connections, which results in fewer bottlenecks and faster throughput of replicated transactions.
Although Sybase ASA and Sybase Adaptive Server offer the same replication facilities, you must be careful because there are a few subtle differences in replicating from each of their respective databases. Their support of some data types, triggered operations, transactions recording, and conflict resolution is implemented differently. In each DBMS, these functions are implemented and packaged in a style specific to that DBMS. This is acceptable for each respective user base, but the fine differences can be tricky if you have to work on both systems.
Although not new in 6.0, there are a few outstanding features in Sybase ASA that I want to highlight as well. Sybase ASA's declarative referential integrity constraints are probably the most complete I have come across, with RESTRICT, SET NULL, SET DEFAULT, and CASCADE options for updates and deletes. I wish the mainstream host DBMSs would wake up and implement this functionality. The only thing Sybase may want to add to ASA (and this is a pet peeve of mine) is customizable declarative integrity constraint violation messages.
Sybase ASA supports statement- and row-level triggers, which can fire before or after operations that activate them. A statement-level trigger fires after the entire operation has completed. A statement-level trigger lets you implement business logic or integrity checks once using a set-based operation (what relational databases were intended for) instead of firing the trigger and doing the work repetitively, row by row, for every updated row.
Sybase ASA's stored procedures can use cursors and dynamic SQL statements, and they have strongly typed IN, OUT, and INOUT parameters. A procedure can also return a result set in a tabular format, such as the results of a query. You can create user-defined functions, which can be used in SQL expressions in any place where you can use a built-in nonaggregate function.
Using Sybase ASA, you can call a function in an external DLL as a so-called external procedure from a stored procedure or from a user-defined function. Under NetWare you can call functions in a NetWare Loadable Module (NLM), but under Unix you cannot use external procedures. Sybase ASA includes a set of system procedures that uses DLL functions to send MAPI email messages. Although this is very powerful, it has potential drawbacks because the external libraries share the DBMS server's memory space. If the library contains faulty code, it can crash the DBMS server or corrupt the database.
There are very powerful ALTER TABLE facilities within Sybase ASA. In addition to adding and dropping integrity constraints, you can add, drop, rename, or change the data type of a column without having to unload and reload the data.
Sybase ASA will be generally available by the time you read this. What is even more exciting, though, is that Sybase is porting the product to hand-held PCs running Windows CE. The beta release of Sybase ASA 6.0 for Windows CE is expected in the second quarter of 1998. This will be the same 6.0 release as I reviewed here, except that Java support was removed from the CE version to reduce space requirements. The JVM may be reintroduced in a later release if it becomes a requirement.
The second interesting development is the small "fingerprint" (as they call it) deployment release of Sybase ASA, which was demonstrated at the Microsoft Windows CE Developers Conference in San Jose, Calif. in May this year. You use this when you want to deploy an application with the Sybase ASA server embedded as a data store. When you package an application on your development platform for deployment with Sybase ASA embedded in the fingerprint configuration, Sybase ASA analyzes the application, preoptimizes the queries, and only includes the components of the server that the application requires. For example, if you don't use outer joins in your application, that part of the database server will not be included in the embedded server. Using this technology, you can embed a fully functional database server with your application using only 50K. This will make it possible to port Sybase ASA applications to small and specialized devices such as the Palm PC and Auto PC. The fingerprint release is expected to go beta in the third quarter of 1998.
This is an exciting development for organizations looking to deploy mobile database technology in areas where notebook computers are too cumbersome. For example, it will help in cases where the terrain is not conducive to sitting down behind a notebook computer, where a notebook computer's boot-up time is too long, where the notebook is not robust enough for the environment, or simply where the users move around so much and so fast that they cannot lug a notebook computer around, let alone open it, start it up, and use it. The scenarios are endless - online stock tickers, customer service, police, military and paramedics, transport, shipping and travel, surveying, nature conservation, farming, and even journalism. Hey, I could be writing articles on a hand-held PC in the French Caribbean and store them as rich text objects in the database! OK, small ones then, for a newspaper perhaps, as long as it fits in the roughly 10MB space left after CE and the DBMS have taken their share of the 16MB memory. But not to worry, this memory space will grow quickly. Vendors are already announcing hand-held PCs with expandable memory.
The mobile database market is turning into a race. This time last year, Sybase SQL Server Anywhere was almost a clear winner in the market I would label "mobile databases with proprietary replication to mainstream host databases," with only Oracle Lite and CA-OpenIngres/Desktop offering any competition. But since then, Oracle Lite has seen some improvements, for example, in Java support and more advanced replication. IBM DB2 has put a Personal Edition out on Windows 95, and Microsoft has made an aggressive attack on the mobile and occasionally connected user market with its release of SQL Server 7.0 for Windows 95. (See my July 1998 column for more details on Microsoft SQL Server 7.0 and its applicability to mobile users.)
It will be interesting to see if Sybase ASA 6.0 will help Sybase maintain its dominance of the mobile database market now that all its mainstream competitors are jumping into the race as well. Sybase is expanding this market to include hand-held PCs and other devices with smaller memory requirements, but even there the opposition is not standing still. Oracle has also announced a release of Oracle Lite for Windows CE, and other DBMS vendors such as Raima Corp. are also playing in that field. Only time will tell, but it will tell soon.
DBMS, in my opinion, has had a long and successful run - I still have a 1992 edition containing the first article I wrote. In parting, as I take up the road of freelance writing after enjoying the sanctuary of the "Server Side" column for a number of years, I would like to thank all the regular readers for their interesting discussions and constructive feedback. It was always encouraging that you found the material useful. I want to thank the editorial staff at DBMS - especially Clara Parkes, who was my editor at DBMS for almost three years - for a most enjoyable professional relationship, which has benefited me tremendously. If I may quote one of the well-known Caribbean reggae musicians, "I'm a travelin' man, and I've got to move onư"