Miscellaneous

Altering invalid views Previously a regular view with INVALID status could not be altered, requiring you to drop the view and recreate it. Now, you can alter an invalid view to change the definition so that it is no longer invalid.

Support added for big-endian and little endian UTF-16 encodings SQL Anywhere now supports both big-endian and little endian UTF-16 encoding on all platforms, regardless of the endianness of the platform. You can use UTF-16 encoding in the LOAD TABLE and UNLOAD statements and with the CSCONVERT function. However, you cannot use UTF-16 encoding as the encoding for a connection or database. See LOAD TABLE statement and UNLOAD statement.

A database upgrade is required to take advantage of this feature. See Upgrading SQL Anywhere.

Index performance enhancements Index performance has been improved, especially when you are operating with a full cache. To benefit from the index performance enhancements, you must rebuild your indexes. The easiest way to do this is to rebuild the database. After rebuilding, you may find that your database file is much smaller. This is normal and should not be a cause for concern.

INLINE and PREFIX settings now respected for compressed columns Previously, the INLINE and PREFIX settings specified for a column were ignored and treated as 0 if the column was compressed. Now, the settings for the column are respected, even if the column is compressed. See Storing BLOBs and CREATE TABLE statement.

Host variables now allowed in batches References to host variables are now allowed within batches, with some restrictions. See Introduction to batches.

Enhancements to the InList algorithm Previously, the InList algorithm was used by the optimizer only if all the elements of the IN list were either constant values or could be evaluated at optimization time to a constant value. Now, the IN list predicate can contain values that are evaluated only at open time (such as CURRENT DATE, CURRENT TIMESTAMP, or non-deterministic system and user-defined functions), as well as values that are constant within one execution of a query block (outer references). See InList algorithm (IN).

Plan caching for simple DML statements Plan caching has been extended to include SELECT statements that qualify for query bypass (simple statements). See Plan caching.

Size of new databases reduced The following system table columns are now compressed to reduce the size of new (empty) databases by approximately 200 KB. This is beneficial when creating databases for use on Windows Mobile.

ISYSEVENT.action
ISYSJARCOMPONENT.contents
ISYSPROCEDURE.proc_defn
ISYSSOURCE.source
ISYSTEXTCONFIG.char_stoplist
ISYSTEXTCONFIG.nchar_stoplist
ISYSTRIGGER.trigger_defn
ISYSVIEW.view_def

Increased default and minimum packet size The default packet size has been increased to 7300 bytes on all operating systems except Windows Mobile. On Windows Mobile, the default continues to be 1460 bytes. The minimum packet size has been increased to 500 bytes. See CommBufferSize (CBSIZE) connection parameter and -p dbeng12/dbsrv12 server option.

New ODBC classes supported for remote data access Support for the following ODBC classes has been added:

msaccessodbc
mysqlodbc
ulodbc
adsodbc

For more information, see ODBC-based server classes.

Migrating Access databases

If you previously used the SQL Anywhere for MS Access Migration utility (upsize tool) to migrate Microsoft Access databases to SQL Anywhere, you can now use the msaccessodbc class.

Database server messages enhancements Messages from the database server now have a category and severity assigned to them. You can access this information using the sa_server_messages system procedure, and you can configure the number of messages maintained with the MessageCategoryLimit property. See sa_server_messages system procedure.

New VALIDATE_COMPLETE parameter for a_validate_type enumeration The a_validate_type enumeration has a new parameter, VALIDATE_COMPLETE for performing all possible validations on the database. See a_validate_db structure.

External unload enhancements When you perform an external unload of a database, the beginning of the reload.sql that is generated now contains a commented CREATE DATABASE statement. This statement can be used to create a database that is equivalent to the one being unloaded.

If the unloaded database was created with version 9 or earlier of SQL Anywhere and had a custom collation, the COLLATION clause appears as follows:

COLLATION collation-label DEFINITION collation-definition

where collation-definition is a string that specifies the custom collation.

If the unloaded database was created with strong encryption, the value of the KEY clause in the CREATE DATABASE statement appears as three question marks (???).

For more information, see Internal versus external unloads and reloads.

New SQL Anywhere Extension Agent OIDs The following OIDs have been added in this release:

saAgent.saRestart
saAgent.saInifile

For more information, see SQL Anywhere MIB reference.

Deadlock system event The Deadlock system event fires whenever a deadlock occurs. The event handler can use the sa_report_deadlocks procedure to obtain information about the conditions that led to the deadlock. You must upgrade existing databases if you want to use the Deadlock system event. See Understanding system events.

Increased database limits Several SQL Anywhere database limits have been increased. See SQL Anywhere size and number limitations.

Changes to execution plans Long plans generated by the optimizer now display the following entries related to the overall plan:

Costed Best Plan Number of different best access plans found by the optimizer.
Costed Plans Number of different access plans considered by the optimizer.
Optimization Time The time spent optimizing the query.

See Execution plan abbreviations.

Graphical plans now display the following entries:

Costed Best Plan Located in the Optimizer Statistics section of the root node, this entry provides the number of different best access plans found by the optimizer.
Costed Plans Located in the Optimizer Statistics section of the root node, this entry provides the number of different access plans considered by the optimizer.
Optimization Time Located in the Optimizer Statistics section of the root node, this entry provides the time spent optimizing the query.
FirstRowRunTime Located in any Node Statistics section, this entry provides the time to fetch the first row.
Joins considered Located in the Advanced Details section of any join operator, this entry lists all join operators considered by the optimizer during the optimization process for the subtree on the right-hand side of the join operator.
Prefilter predicates Located in a new scan node section in the Details pane, this entry lists all predicates that are evaluated before the scan is started.
Scan predicates Located in a scan node section in the Details pane, this entry lists the predicates that are evaluated as columns that are fetched from the row. If a scan predicate rejects a row, further columns are not read. Scan predicates are simple, single column predicates such as T.x <= 3 or T.x IS NULL.
Post scan predicates Located in a new scan node section in the Details pane, this entry lists the predicates that are evaluated immediately after a row has been read from the table page. Post scan predicates can refer to multiple columns and can use functions or arithmetic.
Residual predicates Located in a new scan node section in the Details pane, this entry lists predicates that are evaluated after a set of rows has been fetched into memory. Residual predicates usually contain complex operations such as subqueries or user-defined functions and can not be evaluated as scan predicates or post scan predicates.
Indexes considered Located in the Advanced Details pane, this entry lists all the index or table scans considered by the optimizer during the optimization process for the table referenced by this scan operator. The format of each item in the list is similar to the details listed for a scan operator used in the access plan in the Details pane.
Primary Key Table Located in the Index section of an index scan operator, this entry provides the primary key table name.
Primary Key Table Estimated Rows Located in the Index section of an index scan operator, this entry provides the number of rows in the primary key table.
Primary Key Column Located in the Index section of an index scan operator, this entry provides the names of the primary key columns.
Sequential Transitions Located in the Index section of an index scan operator, this entry provides the statistics kept for each physical index indicating how clustered the index is.
Random Transitions Located in the Index section of an index scan operator, this entry provides the statistics kept for each physical index indicating how clustered the index is.
Key Values Located in the Index section of an index scan operator, this entry provides the number of unique entries in the index.