Behavior changes and deprecated features

Following is a list of changes to SQL Anywhere databases and database servers introduced in version 10.0.1, grouped by category.

Behavior changes
  • Changes to when intra-query parallelism is used   Intra-query parallelism is no longer used for connections with background_priority set to on. Additionally, intra-query parallelism is not used if the number of server threads that are currently handling a request (ActiveReq server property) recently exceeded the number of CPU cores on the machine that the database server is licensed to use. See Parallelism during query execution.

    A new server property, ExchangeTasksCompleted, returns the total number of internal tasks used for intra-query parallelism since the database server started. See Database server properties.

  • Encryption results are no longer deterministic   Previously, encrypting values using the ENCRYPT function was deterministic. If you entered two identical input strings and two identical encryption keys, identical output data (ciphertext) was returned. Now, you can control whether encryption is deterministic using the new encrypt_aes_random_iv database option. The new default behavior is non-deterministic.

    Note

    Database servers that do not have this database option (version 10.0.0 and earlier) cannot decrypt data from databases that have this option set, even if it is set to Off.

  • ALTER DBSPACE RENAME attempts to open the dbspace if it was not open   Previously, if a database that uses dbspaces was started and one of its dbspaces could not be found, executing an ALTER DBSPACE ... RENAME statement for that dbspace updated the dbspace name in the catalog, but did not attempt to start the dbspace. Now, the database server attempts to open the dbspace after the catalog has been updated. See ALTER DBSPACE statement.

  • Changes to CREATE, ALTER, and DROP DBSPACE statements   The CREATE DBSPACE and DROP DBSPACE statements no longer accept the names of pre-defined dbspaces (SYSTEM, TEMPORARY, TEMP, TRANSLOG, and TRANSLOGMIRROR). If a user dbspace in a database created by an older version of the SQL Anywhere database server has the same name as one of the pre-defined dbspace names, the database server always refers to the user dbspace. See Pre-defined dbspaces.

  • Changes to the output of the sa_conn_info system procedure   The output of the sa_conn_info system procedure has been changed to give more information about locks that connections are waiting on. The LockName field has been removed. Instead, two new fields, LockRowID and LockIndexID, have been added. If the connection is waiting on a lock that is associated with a particular row identifier, LockRowID contains that row identifier. If the connection is waiting on a lock that is associated with a particular index, LockIndexID contains the identifier of that index. See sa_conn_info system procedure.

    You must upgrade your database to use this feature. See Upgrading version 10 and later databases.

  • DBA permission no longer required for some system procedures   The sa_dependent_views, sa_get_dtt, sa_check_commit, and sa_materialized_view_info system procedures no longer require DBA permission to run.

  • Default unit of measure for CREATE DATABASE statement removed   When creating a database using the CREATE DATABASE statement, specifying the unit of measurement is no longer optional if you specify a value for DATABASE SIZE. See CREATE DATABASE statement.

  • New maximum value for the default_timestamp_increment option   The maximum value for the default_timestamp_increment option is now 1000000 (1 second). See default_timestamp_increment option [database] [MobiLink client].

  • dbdata10.dll removed   The functionality provided by the dbdata10 Dynamic Link Library has been incorporated in the SQL Anywhere .NET provider DLL. As a result, for Windows CE, there are now platform-specific versions of the SQL Anywhere .NET provider DLL.

  • Default cache size increased on NetWare   The default size of the database server cache on NetWare has been increased from 2 MB to 8 MB. See -c server option.

  • Behavior changes resulting from client statement caching   The following behavior changes have been introduced as a result of the support for client statement caching:

    • An incorrect describe can occur in the case of the same SQL statement that was described as having no result set, and then later does have a result set. For example:
      CREATE PROCEDURE p() NO RESULT SET BEGIN ... END
      Prepare, Describe, Drop "call p"
      ALTER PROCEDURE p() RESULT( ... ) BEGIN ... END
      Prepare, Describe, Drop "call p"  // describe returns no result set
    • When client statement caching is enabled and RememberLastStatement is enabled (-zl server option), the LastStatement property is the empty string when reusing a cached statement.
    • When client statement caching is enabled, if sa_get_request_times or sa_get_request_profile is used to process the request level log, the number of times a statement is executed may be incorrect. See max_client_statements_cached option [database].

  • The Language utility (dblang) no longer requires administrator privileges   In previous versions of SQL Anywhere, users had to log in as an administrator to change language settings for localized versions of SQL Anywhere by using the dblang utility. This requirement has been removed.

  • Forward slashes in DISH service names no longer allowed   To prevent misinterpretation of DISH service names, forward slashes (/) are no longer permitted as part of the name for the service. See CREATE SERVICE statement.

  • Change to default value of SACommand.UpdateRowSource   Previously, the default value of SACommand.UpdatedRowSource was UpdatedRowSource.Both. It has been changed to UpdatedRowSource.OutputParameters. See UpdatedRowSource property.

  • Change to the default value of the PrefetchRows connection parameter   When using the .NET Data Provider, the default value of the PrefetchRows connection parameter has changed from 10 to 200, to improve performance. The default value of SAConnectionStringBuilder.PrefetchRow has also been changed to 200. Prefetching is disabled if the result set contains BLOB columns. See PrefetchRows connection parameter [PROWS].

  • Authenticated applications now use authenticate.sql instead of saopts.sql   In previous releases of the OEM Edition of SQL Anywhere, it was recommended that you store the authentication statement in the file install-dir\scripts\saopts.sql, so that it was applied whenever you created, rebuilt, or upgraded a database.

    It is now recommended that you store the authentication string in the file install-dir\scripts\authenticate.sql. See Upgrading authenticated databases.

  • Long hostnames on HP-UX now accepted   Starting with the HP-UX 11i v2 September 2004 Update, system administrators could enable support for 255 byte hostnames by setting a kernel parameter. However, on SQL Anywhere servers on HP-UX computers with long hostname support enabled, the MachineName property and AppInfo HOST key returned at most 64 bytes of the hostname. Both MachineName and AppInfo can now return 255 byte hostnames.

  • iAnywhere JDBC driver URL header   In previous releases, when applications connected to SQL Anywhere using the iAnywhere JDBC driver, the URL passed to the JDBC driver began with the header jdbc:odbc:. Now, you can also begin the URL header with jdbc:ianywhere:. It is recommended that you use jdbc:ianywhere: to avoid conflicts with the Sun JDBC-ODBC bridge. See Supplying a URL to the driver.

  • Retrieving a list of tables using jConnect when the Remarks value is longer than 128 characters in length   Previously, if a JDBC application connected using jConnect and requested a list of tables, the results could be empty even though tables exist. This occurred when the string_rtruncation option was set to On, the application used the DatabaseMetaData.getTables method, and the Remarks value for any table was longer than 128 characters. Now, Remarks values that are too long are truncated to 128 characters, and the list of tables is returned. You must either run jcatalog.sql, or upgrade your database, in order to use this change. See Installing jConnect system objects into a database or Upgrade utility (dbupgrad).

  • Comparing CHAR and NCHAR values   For SQL Anywhere 10.0.0, combining CHAR and NCHAR domains resulted in an NCHAR comparison. However, this meant that applications upgraded to 10.0.0 could get different results, or experience performance degradation, when using host variables bound as SQL_C_WCHAR. In SQL Anywhere 10.0.0 variables bound as SQL_C_WCHAR are represented as NCHAR. In SQL Anywhere 10.0.1, new inference rules have been introduced to improve compatibility with existing applications, and to provide consistent, predictable results when combining CHAR and NCHAR domains. See Comparisons between CHAR and NCHAR.

  • Asynchronous I/O disabled on Linux kernels earlier than 2.6.12   Because of a bug in Linux kernels earlier than 2.6.12, when you run a SQL Anywhere database server on one of the affected kernels, asynchronous I/O is disabled by default. If you want to use asynchronous I/O, then you must upgrade your kernel to 2.6.12 or later.

  • OEM Edition documentation moved   In previous releases of the SQL Anywhere OEM Edition, instructions for setting up authenticated applications were included in a separate .pdf or .html file. Now, this information is available in the following locations:

  • Unload utility -e and -t options no longer require case sensitive table names for case sensitive databases   In previous releases, when you unloaded a case sensitive database using the dbunload utility with the -e or -t options, these options required case sensitive table names. Now, the table names are now case insensitive.

  • Loading data into temporary tables    The behavior when loading data into temporary tables has changed. With the exception of a LOCAL TEMPORARY TABLE that is defined with ON COMMIT DELETE ROWS, a commit is now automatically performed before and after performing a LOAD TABLE on a temporary table. If the load fails, all rows in the temporary table are now removed, including rows which were present prior to the load.

    In the case of a LOCAL TEMPORARY TABLE with ON COMMIT DELETE ROWS, there is no change in behavior; no commits are performed. This means that in the event of a partial load due to a failure during the load, this type of temporary table would contain only some of the loaded rows, and may also be missing other rows that were present prior to the load.

    Also, loading into a temporary table now fails if the table contains rows referenced by the foreign key of another table.

    You cannot load into a GLOBAL TEMPORARY TABLE that is defined with ON COMMIT DELETE ROWS.

  • Default case sensitivity for Japanese databases using the UCA collation   The default case and accent sensitivity of UCA collations when creating a Japanese database is now sensitive. A Japanese database is defined as any database created on a Japanese computer where the OS language or character set is Japanese, or any database created with a Japanese CHAR collation, such as 932JPN, or EUC_JAPAN.

    The default case sensitivity of UCA collations when creating a non-Japanese database is still insensitive.

    The defaults for case and accent sensitivity can still be overridden using the dbinit -c and -a (or -c- and -a-) options, respectively, by using collation tailoring syntax, or by using the CASE and ACCENT clauses of the CREATE DATABASE STATEMENT. See Initialization utility (dbinit) and CREATE DATABASE statement.

Deprecated and discontinued features
  • SQL Flagger support for the SQL/1992 standard   SQL Flagger support for SQL:1992 (all levels) is deprecated.

  • dbinit -e option is deprecated   The dbinit -e option, used for specifying simple encryption when creating a database, has been deprecated. Use the -ea option (specifically, -ea simple) to specify simple encryption. See Initialization utility (dbinit).

  • SADbType.oldbit data type removed   The SADbType.oldbit enumeration constant has been removed from the SQL Anywhere .NET provider.

  • -gx server option deprecated   On Windows desktop platforms, the database server scheduler now attempts to maintain the affinity of requests so it can use CPU cache. As a result, requests run on one CPU as much as possible. As well, the -gx server option, which was used for specifying the number of operating system threads for the database server to use has been deprecated. This option is now ignored by the database server.

  • CASE and ACCENT clauses of CREATE DATABASE statement deprecated   As a result of the addition of collation tailoring support using the COLLATION and NCHAR COLLATION of the CREATE DATABASE STATEMENT, the CASE and ACCENT clauses of this statement are deprecated. See CREATE DATABASE statement.