Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere 16 - Changes and Upgrading » What's new in version 16.0


SQL Anywhere behavior changes

Following is a list of behavior changes to SQL Anywhere introduced in version 16.0. For information about supported platforms and versions, see [external link]

  • Default security model for some system procedures has changed   Some pre-16.0 system procedures perform operations in the database that required permissions. In the pre-16.0 security model, these procedures executed with the permissions of the definer (owner), so that a user only needed permission to execute the procedure itself, not the permissions for all the operations the procedure would perform. There were some exceptions where DBA authority was also required.

    As of version 16.0 and higher, the default security model changes for these procedures. By default in newly created databases, these procedures execute with the privileges of the person invoking them. So, to execute a procedure, the invoker needs to have the privileges noted in the documentation for the procedure. The invoker also needs EXECUTE privilege on the procedure, but they inherit this by being a member of PUBLIC.

    You can control whether to use the old model or new model at database creation time using the new SYSTEM PROCEDURE AS DEFINER clause of the CREATE DATABASE statement, or the -pd option of the Initialization Utility (dbinit). However, some system procedures always require the privileges noted in the documentation, regardless of the security model setting. For a list of these procedures, see Running pre-16.0 system procedures as invoker or definer.

    When upgrading a database, the default behavior is to maintain the security model that is already in place. So, if you are upgrading a 12.0.1 database, for example, your upgraded database will use the old security model, unless you specify otherwise.

    To control which security model to use at upgrade time, use the SYSTEM PROCEDURE AS DEFINER clause of the ALTER DATABASE statement, or the -pd option of the Upgrade Utility (dbupgrad). The same exceptions regarding the set of system procedures that always need both EXECUTE and specific privileges apply for upgrading as for creation.


    The security model decision (invoker vs. definer) does not impact the default behavior for user-defined procedures, which continues to default to definer. Even if the default for system procedures is changed to invoker, the default for user-defined procedures remains as definer.

  • Relative paths and disk sandboxing   In previous releases, relative paths always defaulted to the working directory of the database server. Now, if disk sandboxing is enabled for a database, relative paths are relative to the database directory, not the database server directory. See Disk sandboxing.

  • SELECT * supported in view definitions   In previous releases, SELECT * was only supported in the main query of the CREATE VIEW statement. Now it is supported in the main query, a subquery, a derived table, or a subselect of the CREATE VIEW statement. See CREATE VIEW statement.

  • Minimum password length changed   In previous releases, the default minimum password length was 0 characters. The default minimum length for passwords has been changed to 3 characters. If your application allows passwords that are 0 characters, you can execute the following statement on new SQL Anywhere databases to change the default setting to match previous releases:
    SET OPTION PUBLIC.min_password_length=0;

    For more information, see min_password_length option.

  • Default behavior has changed when creating indexes on local temporary tables   In previous releases, the database server always executed a COMMIT before creating an index on a local temporary table. Now, the database server does not perform a COMMIT before creating an index on a local temporary table. You can control this behavior by setting the auto_commit_on_create_local_temp_index database option. See auto_commit_on_create_local_temp_index option.

  • New maximum packet size   The maximum packet size has increased from 16000 to 65535 bytes. SQL Anywhere 12 and earlier clients are limited to 16000 bytes when they are connected to SQL Anywhere 16 database servers. The default packet size has not changed. See CommBufferSize (CBSIZE) connection parameter.

  • -ch database server option limit changed   If you specify a maximum cache size that is less than 64 MB with the -ch option, the database server adjusts the maximum cache size to 64 MB. If you require a maximum cache size that is less than 64 MB (this setting is not recommended), then you can use the -chx option. These changes do not apply on Windows Mobile. See -ch database server option.

  • Concurrent index building   In previous releases, the CREATE INDEX statement acquired an EXCLUSIVE table lock when an index was being built. Now, the operation acquires an EXCLUSIVE table lock for short periods of time at the beginning and at the end of the operation and a SHARED lock for most of the operation, so that other connections can access the table data while the index is being created. The connection creating the index is blocked from accessing the table until the index is created. You must upgrade existing databases to use this feature. See CREATE INDEX statement.

  • Mirroring connections can dropped when the connections prevent the transaction log from being applied   Connections to a copy-node or the mirror database are dropped in some cases when these connections prevent the transaction log from being applied. For example, if a connection is using a procedure that the transaction log is trying to alter or drop, then the connection that is blocking the transaction log from being applied is dropped, and a message is printed to the server console. See Queries executed on the mirror database.

  • Plan caching changes   Query execution plans are not cached for queries that have long running times because the benefits of avoiding query optimization are small compared to the total cost of the query. Additionally, the database server does not try to reconstruct reusable query plans for queries that are very sensitive to the values of their host variables.

  • New default for request variables to HTTP server   In previous releases, there was no limit on the number of HTTP input variables that could be sent in a request. The MaxRequestVars protocol option limits the number of HTTP input variables. The default for the MaxRequestVars protocol option is 10000 variables. See MaxRequestVars (MAXVARS) protocol option.

  • New default behavior for database assertions   In previous releases, database assertion failures were treated as database server assertion failures, causing the database server to shut down or return an error to all client connections. Database assertion failures are now treated separately from database server assertion failures, and cause the database to shut down while the database server continues to run. See -ufd database server option.

  • Secure feature behavior changes   Secure feature keys now have a minimum length of 6 characters and some secure features cannot be disabled globally—they can only be disable for individual connections. See Secure features.

  • Sensitive information is obfuscated in output   Passwords and encryption keys are obfuscated when statements that contain them are printed to the request log, logged by diagnostic tracing, or used as column names by DESCRIBE statements. This behavior also applies to output of the REWRITE function, the LastStatement connection property, and statements recorded in event tracing output.

    Sensitive parameters and passwords and keys are hidden for the following functions, procedures, and statements:

    • ENCRYPT function
    • DECRYPT function
    • sa_verify_password system procedure
    • sp_addlogin procedure (Adaptive Server Enterprise compatibility procedure)
    • sp_password system procedure
    • xp_startmail system procedure
    • xp_startsmtp system procedure
    • sp_create_secure_feature_key system procedure
    • sp_alter_secure_feature_key system procedure
    • sp_use_secure_feature_key system procedure
    • GRANT CONNECT statement
    • CREATE DATABASE statement
    • START DATABASE statement
    • DROP DATABASE statement
    • CREATE EXTERNLOGIN statement
    • SET TEMPORARY OPTION secure_feature_key = key statement

  • Personal server (dbeng16) licensing change   The personal server is limited to four cores on one CPU. Previously, the personal server was limited to one CPU. See Server Licensing utility (dblic).

Database utility behavior changes
System procedure and function behavior changes
Programming interface behavior changes
SQL statement behavior changes