A Black Eye Heals as Sybase System 11 Recovers from Performance and Quality Problems in System 10.
Sybase has always been known as one of the most technologically advanced relational DBMSs. However, when Sybase SQL Server 10 was field tested, it met with many negative comments about its quality. Sybase SQL Server 11 was released in October 1995, prima rily in response to the performance demands raised by customers and analysts and pressure from competitors. Sybase SQL Server 11 was developed according to an ISO 9000 certification process in an attempt to ensure its quality. The result is a redesigned DBMS that is scalable and tunable and that performs well, exploiting the power provided by the underlying hardware and operating system.
Sybase does not support domains per se, but you can simulate domains by creating rules to support your domain constraints. The rules must then be bound to each of the columns in which the domain would have been used. You can define key, column, and refer ential-integrity constraints declaratively, as part of the CREATE TABLE and ALTER TABLE statements. The constraints can all be named, but you cannot alter the messages returned when the constraint is violated. For referential-integrity constraints, Sybas e only supports the default NO ACTION option when an integrity constraint is violated. More advanced options must be coded using Sybase's powerful triggers and stored procedures.
A Sybase table always has a heap structure. If you define a clustered index for the table, the data in the table is physically stored in key order. You can only have one clustered index per table; a non- clustered index for a table is just a fast access path to the data stored in the table. Sybase indexes always have a B-tree structure. For each index, you can specify the fill factor and the number of rows it must store per page. You can spread individua l tables and indexes over different physical storage devices; in fact, you can place the index pages for a non-clustered table on physical devices that are separate from the data itself. You can also partition a table, which creates multiple "page chains " for the table, reduces the access to the last page of the table, and allows parallel I/O for bulk table operations.
A Sybase trigger is a special type of stored procedure that is associated with a specified table and that executes automatically when the table is changed by a SQL INSERT, UPDATE, or DELETE operation. The actions of the trigger are specified in the Sybas e Transact-SQL language as part of the trigger definition. Alternatively, the trigger can call a stored procedure to perform the required processing. A Sybase SQL trigger can contain an if clause, which can be used for conditional trigger firing, based o n the updated column values. A Sybase trigger only fires after the triggering operation has completed. When a trigger fires, the old values (if triggered by an UPDATE or DELETE operation) and the new values (if triggered by an INSERT or UPDATE operation) are available in two logical tables, called Inserted and Deleted respectively. The trigger's statements can then access the logical tables to determine the rows affected by the triggering operation. A table can have a maximum of three triggers - one eac h for INSERT, UPDATE, and DELETE statements. A trigger fires only once per triggering operation, and triggers can be nested up to 16 levels and compiled and stored in the system catalogs in the same manner as stored procedures. A very powerful feature of Sybase triggers is their ability to access database objects outside the currently connected database.
A stored procedure is a named sequence of database operations stored (in an executable form) in the system catalogs by the DBMS, which can be activated by calling its name. A stored procedure can contain SQL iNSERT, UPDATE, DELETE, and SELECT statements, as well as flow-of-control statements, such as if . . . ELSE, WHILE, BREAK, CONTINUE, BEGIN . . . END, GO TO . . . , and RETURN. A stored procedure can return multiple result sets to the client application, and it can call other stored procedures (this feature can be used for modular and reusable code development). The statements of a stored procedure may also reference objects in other databases, as long as the access paths are properly defined.
The closest feature Sybase has to database event alerters is the WAITFOR command. It can be used to instruct the current session to wait for a specified time, time interval (up to 24 hours in advance), event, or the execution of a statement block, stored procedure, or transaction.
Sybase uses locking as its concurrency control mechanism; it can lock data pages or entire tables. Sybase always attempts to use page locks, but once a statement accumulates more locks than specified by the LOCK PROMOTION THRESHOLD server configuration p arameter, it escalates to a table-level lock. You can affect the locking strategy by issuing the HOLDLOCK, NOHOLDLOCK, or SHARED keywords with your query or by changing the transaction's isolation level. Sybase supports four isolation levels. It allows d irty reads on level 0, and you can use optimistic locking for browse-only transactions.
Sybase supports cursors according to the Entry Level ANSI SQL-92 standard, with the addition of the FOR READONLY and FOR UPDATE clauses. You can only read forward through a Sybase cursor.
SQL Server supports left and right outer joins, using a Transact-SQL syntax similar to the well-known Oracle outer join syntax. Left and right outer joins are specified by *= and =* in the WHERE clause of the SELECT statement - not in the FROM clause, as specified in the ANSI SQL-92 syntax.
In addition to Transact-SQL and embedded SQL, Sybase supports DBLIB, CTLIB, and the common ODBC API. DBLIB and CTLIB form part of the OpenClient/OpenServer API used by all of the products in the Sybase family.
A powerful GUI-based monitoring tool, Sybase SQL Monitor doesn't influence the performance of the system because it monitors the server's shared memory. It can be used to record and play back performance statistics. The SQL Monitor also includes an API, which lets you build your own monitoring utilities.
Sybase SQL Server satisfies the C2 level of security specification. It has several protection mechanisms that you can deploy to enforce your security policy. These mechanisms include user identification and authentication through a login mechanism, discr etionary access controls through the SQL GRANT and REVOKE commands, and a configurable audit trail that records events of interest to the security administrator.
Sybase has a separate server, called the Sybase Backup Server, to manage all of the backup and restore activities for the Sybase SQL Server. It can take full online backups of your databases and your transaction logs, and it can address up to 32 backup d evices in parallel. In line with the Sybase SQL Server Manager, it can be used to manage multiple dumps and loads from multiple remote SQL Servers.
Editor's note: The Connectivity and Distribution section and the Replication section are online addendums. They did not appear in the printed article due to lack of space.
DirectConnect is Sybase's gateway middleware product. Sybase calls it a single-source data access product. Through DirectConnect you can get full SQL-based read-write access to many databases simultaneously. Supported databases include Adabas, AS/400, DB 2, Informix, CA-OpenIngres, Oracle, and VSAM. A special release of DirectConnect, called MainframeConnect for DB2, works in conjunction with DirectConnect for MVS to give you access to DB2 databases on MVS. In addition to full read-write SQL, dynamic SQL , and cursor-based SQL operations, DirectConnect gives you management and monitoring facilities, integration with additional monitoring services, and full security and integrity controls.
Two related utilities are InfoHub, which makes non-relational mainframe databases appear as relational databases to Sybase client programs, and InfoPump, which is used to move bulk data between various heterogeneous data sources.
In addition, Sybase also offers its well-known remote procedure call (RPC) mechanisms, which can be called from triggers, stored procedures, and application programs. Sybase's RPCs can be located within or outside transaction boundaries and can be comple ted with or without a two-phase commit protocol.
OmniConnect is Sybase's multidatabase offering. Through OmniConnect, a single SQL-based transaction can span multiple, heterogeneous data sources transparently. These data sources are accessed through DirectConnect, the main building block for OmniConnec t. Through OmniConnect you can get common SQL access to data stored in Adabas, AS/400, DB2, IDMS, IMS, Informix, Ingres, ISAM, Microsoft SQL Server, Oracle, Rdb, RMS, sequential files, SQL/DS, Sybase SQL Server, Teradata, and VSAM databases and file syst ems. All of the various data sources are registered in the global data catalog; from then on, the data can be used in full distributed read-write operations, as if it's local. You can access multiple disparate SQL data sources, with full distributed quer y optimization, and integrate the results without even running the Sybase SQL Server. You can also acquire the OmniConnect toolkit, with which you can build your own Omni Access Modules to link your data sources to the OmniConnect family.
At this stage, Sybase can replicate to any of the databases previously mentioned under connectivity, and it can replicate from Oracle, DB2 and Lotus Notes, with replication agents for most of the other data sources supported by DirectConnect in the pipel ine.
The Sybase Replication Server, which is managed through the GUI-based Sybase Replication Server Manager, can be used to manage all of the replication configurations and all of the replication servers from a single console. The Sybase Replication Server M anager can also display replication performance information graphically, a capability that is useful for performance tuning and trouble-shooting. It can also be set to notify an operator of changes in a replication server's operational status or of chang es in the replication configuration.