Checksums enabled by default for new databases   When you create a new database, global checksums are now enabled by default. Global checksums are calculated and verified each time a database page is read or written, and are used to determine whether a database page has been modified on disk. You can control whether global checksums are enabled for a database by using the -s[ + | - ] option for the Initialization utility, or by using the CHECKSUM clause of the CREATE DATABASE statement. See:

• Checksum enhancements
• Initialization utility (dbinit)
• CREATE DATABASE statement
• START DATABASE statement

Checkpoint log changes   In previous releases, when you stopped a database, SQL Anywhere completely truncated the checkpoint log. In version 12, a history of the checkpoint log usage is maintained in the database and is used to determine an appropriate size for the checkpoint log for the next session.

Maintaining the checkpoint log across sessions avoids the overhead of allocating it the next time the database is started and avoids file fragmentation that can occur when files are extended. Database files will now be larger when the database is shut down than they were in previous releases, but the additional space is reused for the checkpoint log the next time the database is restarted. See Checkpoint logs.

Network database server now allocates the maximum number of workers   In previous releases, at startup the network database server allocated the number of workers that corresponded to the database server multiprogramming level. In version 12 network servers, the number of workers allocated corresponds to the maximum server multiprogramming level. This increase in the number of workers increases the address space requirements of the network database server for worker stacks, and may impact the amount of database cache that the database server can allocate.

For example, on 32-bit Windows platforms, by default each worker requires 1 MB of address space for its stack. A version 11 network server starting on Windows would require 20 MB of address space for worker stacks, as its default multiprogramming level is 20. However, a version 12 network server starting on Windows requires 80 MB of address space, as the default maximum number of workers is 80. This change does not affect personal servers or Windows Mobile. See SQL Anywhere threading.

Old statistics are not loaded when a database is rebuilt   When you rebuild a version 11 or earlier database, the LOAD STATISTICS statement silently skips loading old string statistics into the new database, but upgrades the version of the string statistics. See LOAD STATISTICS statement.

Upgrading a database (using the Upgrade utility) does not upgrade the version of the string statistics.

Positioned DELETE and UPDATE statements   Previously, you could specify a TOP or FIRST clause in a positioned UPDATE or DELETE statement; however, the clauses would be ignored. Now, specifying TOP or FIRST in a positioned UPDATE or DELETE statement returns a syntax error. See UPDATE (positioned) statement [ESQL] [SP], and DELETE statement (positioned) [ESQL] [SP].

JDBC drivers now report CHAR and VARCHAR instead of just CHAR for both   Previously, when an application connected using the iAnywhere JDBC driver and attempted to describe the metadata of a table or result set that contains a CHAR column, the metadata would return the type name of the column as CHAR but the SQL type would still come back as Types.VARCHAR. If a JDBC application wanted to get the JDBC driver to return the SQL type of CHAR columns as Types.CHAR, then the application was required to set the odbc_distinguish_char_and_varchar database option. Now, the new SQL Anywhere JDBC driver and the deprecated iAnywhere JDBC driver return the type name CHAR and SQL type Types.CHAR for table and result set columns of type CHAR, and the type name VARCHAR and the SQL type Types.VARCHAR for columns of type VARCHAR regardless of the database option setting.

Changes to CURRENT UTC TIMESTAMP and UTC TIMESTAMP special values   The underlying data type for the CURRENT UTC TIMESTAMP special value, and the default value UTC TIMESTAMP special value, is now TIMESTAMP WITH TIME ZONE. If these values are used with columns defined as TIMESTAMP, the time zone offset will be truncated and no difference in behavior should be noticeable. However, if these values are used with CHAR or VARCHAR columns, the offset will result in different values being generated than before. See CURRENT UTC TIMESTAMP special value and UTC TIMESTAMP special value.

Personal server no longer starts TCP/IP by default   The personal database server only starts the shared memory protocol by default. If you want to use the TCP/IP protocol, you must specify it using the -x server option when starting the personal database server. See -x dbeng12/dbsrv12 server option and Communication protocol considerations.

TCP/IP connections   If you are connecting over TCP/IP, the database server name (specified by the ServerName (SERVER) connection parameter) is no longer mandatory if a host name is provided with the HOST connection parameter. See Host connection parameter.

Host (IP) protocol option   In previous releases, the Host protocol option indicated one or more hosts on which the database server may be running, but was considered a hint to the client library. If the database server was not found on those hosts, a network broadcast was done to find the database server. In this release, if the Host option is specified, only the specified hosts are searched for the database server and the client does not broadcast to find the database server by default.

This behavior is equivalent to setting the DoBroadcast protocol option to Direct. If the database server is running on a computer other than the ones specified by the HOST protocol option, it is not found. If you want the same behavior as previous releases, specify DoBroadcast=All in the connection string. See Host (IP) protocol option (client side only) and DoBroadcast (DOBROAD) protocol option.

ServerPort (PORT) protocol option   In previous releases, the PORT protocol option indicated one or more port numbers on which the database server may be listening, but was considered a hint to the client library. When the client library sent out a broadcast, it would use the port numbers specified by the PORT protocol option, as well as the default port number, 2638. In this release, if the PORT option is specified, only the specified ports are used to find the database server. See ServerPort (PORT) protocol option.

Idle connection parameter respected for shared memory connections   The Idle connection parameter specifies a connection's idle timeout period. Shared memory connections now respect this connection parameter. The default idle timeout for shared memory connections is zero (never idle timeout). See Idle connection parameter.

Database server name may not be the same as the name specified by the ServerName (Server) connection parameter   In previous releases, the value of the Name database property always matched the value specified in the ServerName (Server) connection parameter. However, if a client connects to a database and uses the new NodeType (NODE) connection parameter and the client is redirected to connect to a different database server, then the names do not match. See NodeType (NODE) connection parameter.

Some operations are not supported when connecting with an alternate server name   In previous releases, if a client connected to a database using an alternate server name, they could create, stop, and drop other databases on the same database server. These operations are no longer supported when you connect using an alternate server name.

Database mirroring behavior changes and deprecated features   Using the -xp server option to define database mirroring options such as the name of the arbiter server, the authentication string, and the synchronization mode is deprecated. However, you must still specify -xp on if you want to use the database server in a mirroring system. See -xp dbsrv12 database option.

You can now define the database mirroring settings using the following SQL statements:

• CREATE MIRROR SERVER statement
• ALTER MIRROR SERVER statement
• SET MIRROR OPTION statement

The following behavior changes to database mirroring have been introduced in this release:

• In previous releases, the name of the state information file for a mirror server defaulted to a name based on the server name. You must now specify the name of the state information file.
• In previous releases, web service requests directed to the mirror server were redirected to the primary server. In this release, web service requests are handled by the server that receives them.

For information about the enhancements to database mirroring in this release, see Database mirroring enhancements.

Embedded SQL cursor behavior change   Embedded SQL cursors now default to READ ONLY. Explicit FOR READ ONLY or FOR UPDATE clauses must now be specified in the PREPARE statement and not the DECLARE statement. See:

• DECLARE CURSOR statement [ESQL] [SP]
• PREPARE statement [ESQL]

Cursors closed for CREATE OR REPLACE PROCEDURE statements   When you execute a CREATE OR REPLACE PROCEDURE statement, any cursors that are open for a connection are closed. See CREATE PROCEDURE statement.

Back quote identifier delimiter   Back quotes can now be used as an identifier delimiter. See Identifiers.

Changes in locking behavior   To maximize concurrency, the key and non-key portions of a row can now be locked independently. Non-key columns of a row can be updated without interfering with the insertion and deletion of foreign rows referencing that row. See How locking works.

ODBC driver behavior change   The ODBC function SQLTables can be used to get a list of all schemas (users) by calling the function with the SQL_ALL_SCHEMAS argument. In previous versions, the list of users returned by this function only included users that owned a table. In a newly initialized database, this excluded some users, and in particular the DBA user. Now the complete list of schemas is returned, including those that do not own a table.

divide_by_zero_error option   The divide_by_zero_error option was not supported in version 11. This option is supported in version 12. If you are using materialized views, you must set this option to On (the default) to create new materialized views. See divide_by_zero_error option and Materialized views restrictions.

PrefetchBuffer (PBUF) connection parameter   In previous releases, the amount of memory for buffering rows specified by the PrefetchBuffer (PBUF) connection parameter was shared between all connections. In this release, the amount of memory specified by this connection parameter is available to each connection. See PrefetchBuffer (PBUF) connection parameter.

External logins automatically removed for dropped users   When you remove a user from the database, any external logins for the user are now dropped automatically. In previous releases, you had to remove the external logins separately. See Deleting a user from the database.

The database cleaner and database validation can no longer operate at the same time   In previous releases, database validation and the database cleaner could run at the same time, and report errors because of concurrent access to database pages. Database validation and the database cleaner no longer simultaneously operate on the same database. Validation waits for the database cleaner to finish, and the database cleaner waits for validation to finish if the database cleaner is started by calling sa_clean_database. The database cleaner can operate simultaneously with table or index validation.

Data types changed for columns in sa_index_density system procedure   The data type of the density and skew columns has been changed from numeric(8,6) to double. See sa_index_density system procedure.

DATEDIFF function   In previous releases, the DATEDIFF function returned an INTEGER for date parts of hours and smaller. DATEDIFF now returns an a BIGINT for these date parts. See DATEDIFF function [Date and time].

openxml system procedure   The openxml system procedure is no longer owned by the dbo database user. Any queries that use the openxml system procedure that qualify the procedure name with dbo. must be changed to remove dbo.. Running a statement similar to the following now returns an error:

SELECT ... FROM dbo.openxml(...)

In previous releases, the openxml system procedure converted NCHAR data to the CHAR encoding and parsed the data in CHAR format. Now NCHAR data is parsed in the NCHAR encoding if there are NCHAR columns in the output.

In previous releases, the XPath arguments in the WITH clause could only be literal strings. Now literal strings and variables are allowed. See openxml system procedure.

Databases that are upgraded from version 11 or earlier contain a row for openxml in the SYS.SYSPROCEDURE system view, but that definition cannot be used in the version 12 database. A syntax error is returned if you attempt to use the procedure as follows:

SELECT * FROM dbo."openxml"(...)

sa_text_index_vocab system procedure   Attempting to call sa_text_index_vocab on an NCHAR text index now returns an error. Use the new sa_text_index_vocab_nchar system procedure instead. See sa_text_index_vocab_nchar system procedure.

Additionally:

Default collation changed for Mac OS X   In previous releases, in the absence of any environment variables on Mac OS X, the Initialization utility (dbinit) would use ISO_8859-1:1987 for its default CHAR character set and ISO1LATIN1 for its CHAR collation (the same behavior as Linux). It now chooses UTF-8 with the UTF8BIN collation. See Recommended character sets and collations.

Reserved words   The following is a list of reserved words added to the database in SQL Anywhere version 12.0.0:

The following is a list of reserved words removed from the database in SQL Anywhere version 12.0.0:

WITH ( index-hint ) clause behavior change   Previously, if the primary key index, foreign key index, and/or the normal index on a table had the same name, the optimizer would resolve to the name of the primary or foreign key index. Now, the optimizer resolves to the name of the normal index. If one does not exist, the optimizer resolves to the name of the primary key first and then to the foreign key. See WITH table-hint clause, FROM clause.