Indexing enhancements The following enhancements have been made to indexing in this release:
Support for index sharing When you create a primary key, secondary key, foreign key, or unique constraint, you now create a logical index that points to a physical index (an actual indexing structure on disk). The database server automatically determines whether a new physical index is required to satisfy the logical index. This model allows the sharing of physical indexes and prevents the creation and maintenance of duplicate physical indexes, which wastes disk space. See Index sharing using logical indexes.
Improved storage of index information There are several improvements to how index information is organized in the database. For example, the list of all indexes, including primary and foreign key indexes, is now stored in a single system table, ISYSIDX.Three new system tables, ISYSPHYSIDX, ISYSIDXCOL, and ISYSFKEY provide additional information about the indexes listed in ISYSIDX. See:
Index Consultant enhancements The Index Consultant has been enhanced to improve recommendations with respect to clustered indexes, database and server states in the workload, and complete workload statistics reporting. It has been integrated into the Application Profiling tool.
Improved control over how indexes are created When an application creates a referential integrity constraint (primary key, foreign key, or unique constraint), the database server enforces the constraint by implicitly creating an index on the columns that make up the key of the constraint. The database server now allows you to specify how that index is created. You can specify the order of columns in the constraint key and the sequencing of values (ascending or descending) for each column in the index. In addition, there is no requirement that the order and sequencing of columns in a foreign key match the corresponding primary key or unique constraint.Additional improvements include:
The primary key order can now be changed without having to reorder the columns in the table.
The sequencing of columns in all constraint indexes can be specified to match application requirements.
Foreign key indexes can now be tailored to match the application requirements for the foreign key table without being tied to the primary table design.
Foreign keys can now have unique constraints.
New outer join elimination rewrite optimization Outer joins are eliminated from the query before execution if the resulting query is semantically equivalent to the original query. See Semantic query transformations.
Date format strings now use character-length semantics Date format strings now use character-length semantics to control the amount of text substituted for format specifiers; previously, byte-length semantics were used. For example, when formatting dates using strings, MMM used to imply the use of 3 bytes to store the month, now it implies 3 characters.
Directory access servers You can now create a remote server that accesses the directory structure of the computer running the database server by creating a directory access server. See Using directory access servers.
Norwegian collation 1252NOR has been added to support Norwegian. On Norwegian Windows systems, the database server chooses 1252NOR as the default collation for a new database if no collation is specified. See Supported and alternate collations.
UTF8BIN collation The UTF8BIN collation has been added to offer improved sorting of binary data. This new collation replaces the UTF8 collation, which is now deprecated. See Supported and alternate collations.
Server Messages window enhancements The following enhancements have been made to the Server Messages window:
New right-click choices for the window title bar On all supported Windows platforms (except Window CE), when you right-click the title bar of the Server Messages window you can now choose About or Clear Message Area. Choosing About displays information about the database server, while choosing Clear Messages Area erases all of the messages in the Server Messages window. Replicas of this window (the console output log, the Sybase Central Server Messages and Executed SQL pane, and the SQL Anywhere Console utility) are not affected by the clearing action.
Environment variables used by database server can be logged to Server Messages window The -ze server option displays a list of the environment variables for a database server in the Server Messages window. This feature is not available on NetWare or Windows CE. See -ze server option.
Controlling window minimization on startup By default, the Server Messages window minimizes once the database server starts. You can specify the -qn option if you do not want the Server Messages window to minimize once the database server is started. See -qn server option.
Ability to track when a table was last updated The database server now keeps track of the last time a table was updated. This is achieved using the new last_modified_at column in the SYSTAB system view. See SYSTAB system view.
JDBC driver now supports the SQL Server Native Client ODBC driver The JDBC driver now checks if the ODBC driver is the Microsoft SQL Server Native Client ODBC driver and appropriately sets the default result set type and other attributes.
SNMP traps when changing to another server during mirroring The SQL Anywhere SNMP Extension Agent now sends a trap when it is connected to a server involved in mirroring, the connection drops, and a new connection is reestablished but to a different server.This trap indicates that the original server went down, and the server that was acting as the mirror became the primary. See Using traps.
Changes to request logging The request log is now stored in a comma-delimited text format, reducing it to roughly one third of its original size. Also, where possible, instead of a normal time entry, times are now recorded as either an equal sign (=), which means the same time as the previous entry in the log, or +nnn, where nnn is the number of milliseconds after the previous entry in the log. Additional information is now also recorded. For example, for queries, the isolation level, number of rows fetched, and cursor type are now recorded. For INSERT, UPDATE, and DELETE statements, the number of rows affected and number of triggers fired are now recorded. See Request logging.The sa_get_request_times system procedure supports only the new request log format. However, the tracetime Perl script, tracetime.pl, processes both old and new request log formats. The tracetime script also performs faster on logs of the new format, noticeably so on large request logs.
ODBC driver enhancements SQL Anywhere is using new drivers for remote data access when connecting to Adaptive Server Enterprise and DB2 databases. See: Changes to ODBC drivers used by MobiLink, QAnywhere, and remote data access.
SQLANYSAMP10 environment variable The SQLANYSAMP10 environment variable specifies the location of the directory containing the SQL Anywhere 10 samples, including the demo.db and the custdb.db sample databases. See SQLANYSAMP10 environment variable.