Main features

  • Support for parallelism to improve performance   The database server now supports the use of multiple processors for processing a single query. Intra-query parallelism is beneficial when the number of simultaneously executing queries is less than the number of available processors. See Parallelism during query execution.

  • Support for database mirroring   SQL Anywhere now supports database mirroring, which is a mechanism to increase the availability of a database. It involves using either two or three database servers running on separate computers and communicating with each other in either synchronous or asynchronous mode. See Introduction to database mirroring.

    The following features have been added to support database mirroring:

    In addition to database mirroring, SQL Anywhere now provides Veritas Cluster Server agents for both databases (SADatabase agent) and database servers (SAServer agent). See Using the SQL Anywhere Veritas Cluster Server agents.

  • Support for snapshot isolation   When you use snapshot isolation, the database keeps a copy of the original data while a user is changing it, and makes the original data available to other users who want to read it. Snapshot isolation is completely transparent to users, and can help reduce deadlocks and lock contentions. See Snapshot isolation.

    The following features have been added or enhanced to support snapshot isolation:

  • Support for application profiling and diagnostic tracing   Existing application profiling capabilities, such as stored procedure profiling and request logging, have been integrated into a single, unified interactive interface the SQL Anywhere plug-in for Sybase Central. When you profile your application from Sybase Central, recommendations are provided to help you improve database performance.

    For more information about application profiling in Sybase Central, see Application profiling.

  • Support for materialized views   To improve performance in environments where the database is large and frequent queries result in repetitive aggregation and join operations on large amounts of data, SQL Anywhere now supports materialized views. See Working with materialized views.

    The database server has been enhanced to automatically decide, based on cost, which materialized views can be used to answer parts of a query instead of using base tables referenced directly by the query. See Improving performance with materialized views.

    Two new system tables, ISYSMVOPTION and ISYSMVOPTIONNAME, have been added to store information about materialized views. See SYSMVOPTION system view and SYSMVOPTIONNAME system view.

  • Support for NCHAR data   SQL Anywhere now supports the NCHAR data type. NCHAR data types are used for storing Unicode character data. See NCHAR data type.

    The following new functions have been added in support of NCHAR:

    The following functions SORTKEY and COMPARE functions have new parameters to support the NCHAR data type:

    SQL Anywhere now properly sorts multibyte character sets when using the Unicode Collation Algorithm (UCA).

    The Initialization utility (dbinit) and Unload (dbunload) utilities also have new options to support the NCHAR data type. See Initialization utility (dbinit) and Unload utility (dbunload).

    SQL Anywhere now uses International Components for Unicode (ICU) for Unicode support. See International languages and character sets.

    To support ICU and the handling of NCHAR data, the following property changes have been made:

    • A new NcharCharSet database and connection extended property has been added. This property returns the NCHAR character set in use by the database or connection.

    • A new AccentSensitive database property has been added. This property returns the status of the accent sensitivity feature.

    • The CharSet database and connection properties are now extended properties.

    See Database properties and Connection properties.

  • Internal performance enhancements   To improve database server performance, virtual machine technology has been used to re-architect the representation and evaluation of SQL expressions, which significantly improves throughput.

  • Support for view dependencies   The catalog now stores information about the dependencies of views. Specifically, the catalog keeps track of the views, tables and columns upon which each view in the database depends. When you make an alteration to an object upon which a view depends, the database server automatically performs additional operations to ensure that the view definition is not left in a state where it could return incorrect results. See View dependencies.

    Two new tables, ISYSDEPENDENCY and ISYSOBJECT, have been added to store information about system objects and their dependencies. See SYSDEPENDENCY system view and SYSOBJECT system view.

  • Improved checkpoint algorithm   The database server can now initiate a checkpoint and perform other operations while the checkpoint takes place. Previously, all activity would stop while the checkpoint took place. If a checkpoint is already in progress, then any operation like an ALTER TABLE or CREATE INDEX that initiates a new checkpoint must wait for the current checkpoint to finish. See Understanding the checkpoint log.

  • Locking enhancements   The following enhancements have been made to locking:

    • Classes of locks   SQL Anywhere now supports four distinct classes of locks: schema locks, table locks, row locks, and position locks. The sa_locks system procedure has been modified to more clearly document the types of locks that each transaction holds to permit more accurate analysis of locking issues. See How locking works and sa_locks system procedure.

    • Support for intent locks   A new type of lock, called an intent lock, has been introduced for both table locks and row locks. Intent locks are used by an application to signal its intention to update a table or a set of rows within that table. Intent locks are now acquired when an application uses SELECT FOR UPDATE or FETCH FOR UPDATE statements (or their equivalent constructions in various programming interfaces). Intent locks block other intent locks and write locks, but do not block read locks. This support gives a higher degree of concurrency to those applications that use locking as an explicit concurrency control mechanism. See Using cursors and Intent locks.

    • Key range locking eliminated in some situations   Changes to index maintenance algorithms now permit the database server to place write locks on individual index entries, rather than on a range of keys. This will improve concurrency and eliminate unnecessary blocking due to concurrent INSERT operations in a variety of circumstances. See How locking works.

  • Indexing enhancements   The following enhancements have been made to indexing:

    • New index implementation   Previous releases of SQL Anywhere contained two different indexing implementations that were chosen automatically based on the declared size of the indexed columns. In SQL Anywhere 10, a new implementation of compressed B-tree indexes is used throughout, and older B-tree indexing technology has been eliminated. The new indexes store a compressed form of the index key value in the index entry, separate and distinct from the value in the row. This is required to support snapshot isolation.

    • Support for snapshot isolation   In previous SQL Anywhere releases, index entries were deleted on UPDATE or DELETE statements immediately. To support snapshot isolation, there is potential for several index entries to point to the same logical row with different index key values. These multiple index entries are managed by the database server so that any one connection can see only one of the entries for any given row. Periodically, a daemon within the server will physically delete these extra index entries when they are no longer needed (as transactions COMMIT or ROLLBACK). Retaining index entries for uncommitted DELETEs also improves semantic consistency of the concurrency control mechanisms in SQL Anywhere. See Snapshot isolation.

  • Improved BLOB storage control and performance   You can now control the amount of a BLOB value that is stored in a table row (inline). You can also control whether to index BLOB values. These enhancements improve searching through, and accessing, BLOBs, and are made available through three new clauses in the CREATE TABLE and ALTER TABLE statements: INLINE, PREFIX, and [NO] INDEX. BLOB values can now be shared within, or among rows of the same table, reducing storage requirements by eliminating the need to store duplicate BLOB values. See Storing BLOBs, CREATE TABLE statement, and ALTER TABLE statement.

  • Support for column compression   You can now compress individual columns in a table. Compression is achieved using the deflate compression algorithm. This is the same compression used by the COMPRESS function, and is also the algorithm used in Windows .zip files. See CREATE TABLE statement and ALTER TABLE statement.

  • Support for table encryption   Instead of encrypting an entire database to secure data, you can now encrypt individual tables in the database. Table encryption must be enabled in the database when it is initialized. See Table encryption.

Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0