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

SQL Anywhere 11.0.1 (Français) » SQL Anywhere 11 - Changes and Upgrading » What's new in version 11.0.0 » SQL Anywhere

 

SQL Anywhere behavior changes

Following is a list of behavior changes to SQL Anywhere introduced in version 11.0.0, grouped by category.

  • Catalog changes   The following table contains the changes to the catalog for version 11.0.0.

    You must upgrade your database to get these changes. See Upgrading SQL Anywhere.

    Table name and/or view name Description of change Additional information
    ISYSTAB/ SYSTAB
    • A new column, dbspace_id has been added as an eventual replacement to the current file_id column.

    • The file_id column is deprecated. Use dbspace_id instead. For global temporary tables, SYSTAB.file_id now points to the temporary dbspace, instead of the system dbspace.

    • A new column, last_modified_tsn, has been added to store a sequence number for the transaction that modified the table.

    See SYSTAB system view.
    ISYSIDX/ SYSIDX
    • A new column, dbspace_id has been added as an eventual replacement to the current file_id column.

    • The file_id column is deprecated. Use dbspace_id instead.

    See SYSIDX system view.
    ISYSFILE This system table is deprecated. All columns, with the exception of lob_map, are now found in the (new) ISYSDBSPACE system table. The lob_map column is now found in the (new) ISYSDBFILE system table. See:
    ISYSDBFILE/ SYSDBFILE New table to hold information about dbspaces. See SYSDBFILE system view.
    ISYSDBSPACE/ SYSDBSPACE New table to hold information about dbspaces. See SYSDBSPACE system view.
    SYSDBSPACEPERM/ ISYSDBSPACEPERM New table to hold dbspace permissions. See SYSDBSPACEPERM system view.
    ISYSOBJECT/ SYSOBJECT The file_id column has been renamed to dbspace_id. Also, the object_type column can contain two new values: 17 (Text configuration), and 18 (Dbspace). See SYSOBJECT system view.
    SYSINDEXES The indextype field now identifies foreign keys and primary key indexes as Primary Key and Foreign Key, respectively to distinguish them from other indexes. See SYSINDEXES consolidated view.
    ISYSCAPABILITYNAME This table no longer exists in the catalog. The corresponding SYSCAPABILITYNAME system view is still available, but it generated using server properties. See SYSCAPABILITYNAME system view.
    ISYSEVENTTYPE This table no longer exists in the catalog. The corresponding SYSEVENTTYPE system view is still available, but it generated using server properties. See SYSEVENTTYPE system view.
    ISYSVIEW New column called mv_last_refreshed_tsn to store a sequence number for the transaction that refreshed the materialized view. See SYSVIEW system view.
    ISYSLOGINMAP/ SYSLOGINMAP New table to hold information about login policies. See SYSLOGINMAP system view.
    ISYSLOGINPOLICY/ SYSLOGINPOLICY New table to hold information about login policies. See SYSLOGINPOLICY system view.
    ISYSLOGINPOLICYOPTION/ SYSLOGINPOLICYOPTION New table to hold information about login policies. See SYSLOGINPOLICYOPTION system view.
    ISYSTEXTCONFIG/ SYSTEXTCONFIG New table to hold information about text configuration objects. See SYSTEXTCONFIG system view.
    ISYSTEXTIDX/ SYSTEXTIDX New table to hold information about text indexes. See SYSTEXTIDX system view.
    ISYSTEXTIDXTAB/ SYSTEXTIDXTAB New table to hold information about text indexes. See SYSTEXTIDXTAB system view.

  • PHP function name changes   All PHP functions have been renamed to have sasql_ as their prefix, instead of sqlanywhere_. The sqlanywhere_ prefix is still allowed in the name when calling a function, but is deprecated. You should change your application to use the new prefix.

  • INSERT ... ON EXISTING UPDATE statement now fires triggers   Previously, when you executed an INSERT ... ON EXISTING UPDATE statement, triggers did not fire if data was updated. Now, the database server fires statement-level after triggers for the updates.

  • REFRESH MATERIALIZED VIEW statement   You can no longer specify STATEMENT SNAPSHOT and READONLY STATEMENT SNAPSHOT as the isolation level for the refresh since the effect of these options is the same as specifying SNAPSHOT for the isolation level. See REFRESH MATERIALIZED VIEW statement.

  • REORGANIZE TABLE statement   Attempting to execute multiple REORGANIZE TABLE statements simultaneously on the same table now results in an error.

  • sa_validate system procedure   The express and checksum arguments for sa_validate are now obsolete; specifying them no longer has an effect. Checksum validation is now performed by default. Also, when the sa_validate system procedure is called without specifying any arguments, in addition to validating all tables, materialized views, and indexes, the database server also validates the database itself, including checksums. See sa_validate system procedure.

  • -gss server option   The -gss server option is now supported on Windows XP and later. In previous releases, this option was not supported on Windows operating systems. See -gss server option.

  • -gx server option no longer supported   Support for the -gx server option has been removed in this release. Specifying the -gx option when starting a SQL Anywhere database server results in an error.

  • LazyClose connection parameter default setting is now AUTO   In previous versions, when an application closed a cursor, a round trip to the database server was required unless the LazyClose connection parameter was set to YES. Now, cursor close requests are queued for many cursors by default, eliminating a round trip and resulting in improved performance. The LazyClose connection parameter now accepts three values: YES, NO, and AUTO (the default). YES was the default setting in previous releases. See LazyClose connection parameter [LCLOSE].

  • Embedded SQL import library changes   The Watcom and Borland versions of the DBLIB import libraries are no longer included. These are dblibtw.lib and dblibtb.lib, respectively. An import definition file (dblib11.def file) is provided as a replacement for these import libraries.

  • Database tools import library changes   The Watcom and Borland versions of the database tools import libraries are no longer included. These are dbtlstw.lib and dbtlstb.lib, respectively. An import definition file (dbtool11.def) is provided as a replacement for these import libraries.

  • DBLIB indicator behavior defined when no rows received   On a fetch or execute where no rows are received from the database server (on an error or the end of the result set), indicator values are now unchanged. See Indicator variables.

  • ODBC SQLGetConnectAttr   Using the ODBC SQLGetConnectAttr call to get the SQL_ATTR_CONNECTION_DEAD attribute now gets the value SQL_CD_TRUE if the connection has been dropped, even if no requests have been sent to the server since the connection was dropped. Determining if the connection has been dropped is done without making a request to the server, and the dropped connection is detected within a few seconds. The connection can be dropped for several reasons, such as an idle timeout.

    In previous releases, SQL_ATTR_CONNECTION_DEAD only got the value SQL_CD_TRUE if the connection was disconnected or if the ODBC driver made a request to the server (for example, by calling SQLExecDirect) after the connection was dropped.

  • Databases cannot be created or started that are named utility_db   The name utility_db is now reserved for the SQL Anywhere Server utility database. If you attempt to create a new database or start an existing database named utility_db.db, an error is returned. If you have an existing database named utility_db, you can start it with a different name. See Using the utility database.

  • Computed column dependencies   Previously, to allow an update or insert operation to proceed without error, an application could have used triggers to assign non-NULL values to columns that were declared NOT NULL. This impacted computed columns that were dependent on the column, since it could result in a computed value that did not reflect the intended computation. Now, an attempt to set a NULL value in a NOT NULL column that a computed column depends on, fails with an error message and no triggers are fired. See Inserting and updating computed columns.

  • Dbspace names containing a period generate an error   In previous releases, if a dbspace name that was not quoted contained a period, then the part of the dbspace name before the period was silently ignored by the server. The database server now generates an error for these names.

  • SQL Anywhere web server no longer supports SSL version 2.0   When using the SQL Anywhere web server, only SSL version 3.0 and TLS version 1.0 connections are supported. SSL version 2.0 connections are not supported.

  • CREATE SERVICE option DATATYPE default value has changed   The default value of the DATATYPE clause has changed from OFF to ON. If you want the old behavior then you must explicitly include DATATYPE OFF in the CREATE SERVICE definition. See CREATE SERVICE statement.

  • Some secured features renamed   The following secured features have been renamed for this release:

    Deprecated name New name
    xp_read_file read_file
    xp_write_file write_file
    unload_table write_file
    load_table read_file

    For more information, see -sf server option.

  • Checksum behavior changes    For databases created with version 11 or upgraded to version 11, the database server automatically enables checksums to databases running off of media such as network drives or removable drives. Checksums remain enabled as long as the database resides on such a device. See Using checksums to detect corruption.

  • HTTP connections do not cause autostop   In previous releases, when you configured a database to stop automatically, the database would stop if an HTTP connection disconnected and there were no other connections to the database. Databases now autostop only when the last command sequence or TDS connection disconnects.

    If the only connection to a database is an HTTP connection, and the database is configured to stop automatically, when the HTTP connection disconnects, the database does not autostop. As well, if a database that is configured to stop automatically has an HTTP connection and a command sequence or TDS connection, when the last command sequence or TDS connection disconnects, the database autostops, and any HTTP connections are dropped. See -ga server option and AutoStop connection parameter [ASTOP].

  • Database mirroring behavior change   In previous releases, if the connection parameters specified in the -xp option for the primary or mirror server were invalid, the database server would repeatedly attempt to connect, but the connection would never succeed. In this release, if the connection parameters specified in the -xp option are invalid, and there are multiple databases running on the server, then the mirrored database fails to start and does not attempt to reconnect. If the mirrored database is the only database running on the database server, then the database server does not start.

  • Default refresh behavior for materialized views   Previously, the default refresh behavior for materialized views was WITH EXCLUSIVE MODE. Now, default refresh behavior depends on whether the materialized view is defined as IMMEDIATE REFRESH and whether snapshot isolation level is enabled for the database. See REFRESH MATERIALIZED VIEW statement.

  • post_login_procedure database option behavior change   The default setting of the post_login_procedure database option is now the sa_post_login_procedure system procedure. See post_login_procedure option [database].

  • non_keywords database option   In previous releases, in addition to specifying individual keywords, you could also turn off all keywords since a specified release by using the following special values in the list of keywords:
    keywords_4_0_d, keywords_4_0_c, keywords_4_0_b, keywords_4_0_a, keywords_4_0,
     keywords_5_0_01, keywords_5_0

    These special values are no longer supported. You can still turn off individual keywords. See non_keywords option [compatibility].

  • quoted_identifier database option setting respected for remote data access   The local setting of the quoted_identifier option now controls the use of quoted identifiers for Adaptive Server Enterprise and Microsoft SQL Server when you are using remote data access. For example, if you set the quoted_identifiers option to Off locally, then quoted identifiers are turned off for Adaptive Server Enterprise. See :

  • Changes to the scope of the precision and scale database options   In previous releases, you could set the precision and scale database options for individual users or specify that the setting had a temporary scope. However, these settings can affect the recoverability of a database. If the temporary or user-level settings differ from the corresponding PUBLIC settings when executing DDL statements that create or alter tables and domains, you may encounter problems while rebuilding the database. The following behavior now applies for the precision and scale database options:

    Database server version Version 10 or earlier database Version 11 database Database upgraded to version 11 Unloading a version 10 or earlier database
    11

    PUBLIC settings allowed

    User settings allowed

    Temporary settings not allowed

    PUBLIC settings allowed

    User settings not allowed

    Temporary settings not allowed

    PUBLIC settings allowed

    User settings not allowed

    Temporary settings not allowed

    PUBLIC settings unloaded

    User settings discarded during unload

    10 or earlier

    PUBLIC settings allowed

    User settings allowed

    Temporary settings allowed

    N/A N/A

    PUBLIC settings unloaded

    User settings unloaded

    Version 10 and earlier database servers continue to allow you to set the scale and precision options temporarily, as well as for individual users.

    Caution

    It is recommended that you do no rely on user-level or temporary settings for the precision and scale database options because of the potential problems you can encounter when rebuilding databases, and because of the unpredictable database server behavior that can occur.

    See:

  • OPTION clause behavior change   The OPTION clause for the INSERT, UPDATE, DELETE, SELECT, UNION, EXCEPT, and INTERSECT statements now returns an error if you specify a database option that the clause does not support. See:

  • Rollback log behavior change for read-only databases   In previous releases, operations on read-only databases involving transactional temporary objects were not treated as transactional: no rollback log information was kept for them. In this release, transactional temporary objects in read-only databases have fully-transactional semantics. They are subject to commits, rollbacks, and rollbacks to savepoints.

  • Itanium 64-bit supported platform changes   In previous versions, a full 64-bit version of the software was available for Windows Server 2003 on Itanium II chips, and a deployment release was available on 64-bit Linux and HP-UX operating systems.

    In this release, only the deployment release for 64-bit HP-UX is available.

  • Unload utility (dbunload) behavior changes   In previous releases, the dbunload -ea, -ek, and -ep options had to be specified with the -an or -ar option to control encryption for the new database. Now, if you unload a database, or any part of it, but do not reload it, the -ea, -ek, and -ep option control the encryption of the table data files that are created. When you use these files to reload a database from Interactive SQL, you must specify the encryption key as a parameter to the READ statement. See Unload utility (dbunload).

    As well, in previous releases the version of dbunload used to extract a database did not have to be the same version as the database server running the database. Now, when dbunload is used with a version 10.0.0 or later database, the version of dbunload used must match the version of the database server used to access the database. If an older version of dbunload is used with a newer database server, or vice versa, an error is reported.

  • Extraction utility (dbxtract) behavior change   In previous releases the version of dbxtract used to extract a database did not have to be the same version as the database server running the database. Now, when dbxtract is used with a version 10.0.0 or later database, the version of dbxtract used must match the version of the database server used to access the database. If an older version of dbxtract is used with a newer database server, or vice versa, an error is reported.

  • Changes in locking behavior   In previous releases, an UPDATE or DELETE statement executing at isolation level 0 could block on a row lock for a row that was not affected by the statement. It is now less likely for an UPDATE or DELETE statement to take an intent or exclusive lock on a row that is not affected by the statement. When developing applications, you should use caution when using isolation level 0 or 1 with UPDATE and DELETE statements, and ensure that the behavior is acceptable for your application. See Locking during updates and Locking during deletes.

  • Changes to property names   The following properties have been renamed in this release:

    Old name New name
    CacheHitsEng CacheHits
    CacheReadEng CacheRead
    DiskReadEng DiskRead
    ReadHint DiskReadHint
    ReadHintScatter DiskReadHintPages
    ReadHintScatterLimit DiskReadHintScatterLimit

    For more information, see Connection, database, and database server properties.

  • Language Selection utility (dblang)   In previous releases, this utility was only installed when the International Resources Development Kit (IRDK) was selected during installation. In this release, all international resources and the Language Selection utility (dblang) are installed all the time.

  • Default dbspace for temporary tables and indexes   Temporary tables can only be created in the TEMPORARY dbspace. If you specify the SYSTEM dbspace in the IN clause of the CREATE TABLE statement, the IN clause is ignored, and the temporary table is created in the temporary dbspace. If you specify a user-defined dbspace in the IN clause of the CREATE TABLE statement, an error is returned. As well, the default_dbspace option is ignored when creating temporary objects.

  • Loading data into temporary tables   When loading data into temporary tables you can no longer load a local temporary table that is ON COMMIT DELETE. In previous releases, you could load data into a local temporary table defined with ON COMMIT DELETE ROWS.

    An autocommit is now performed automatically when you run a LOAD TABLE statement; in previous releases, this did not always occur.

  • Database server options   The -uc and -ui server options are now supported on Mac OS X. Previously they were only supported on Linux. On Linux the -ui server option opens the Server Startup Options window, displays the database server messages window, and starts the database server whether or not the X window server starts. On Mac OS X -ui displays database server messages in a new window and starts the database server in shell mode if a usable display isn't available. The -uc server option starts the database server in shell mode. See -uc server option and -um server option.

  • Remote data access no longer works with ODBC drivers that do not support UNICODE calls   Remote data access no longer works with ODBC drivers that do not support UNICODE calls. As a result, with non-UNICODE ODBC drivers, remote data access does not perform any character set translation on data coming in from the ODBC driver.

  • SYSFILE system view   A row for the temporary file is now included in the SYSFILE compatibility view. See SYSFILE compatibility view (deprecated).