Miscellaneous

  • Adaptive Server Anywhere renamed   In version 10.0.0, Adaptive Server Anywhere has been renamed SQL Anywhere.

  • Upgrade changes   The Upgrade Database wizard, the Upgrade utility (dbupgrad), and ALTER DATABASE UPGRADE statement cannot be used to upgrade version 9.0.2 and earlier databases to version 10. To upgrade older databases to version 10, you must rebuild the database by performing an unload and reload. See Upgrading SQL Anywhere.

  • Password changes   In newly-created databases, all passwords are case sensitive, regardless of the case-sensitivity of the database. The default DBA password for new databases is sql.

    When you rebuild an existing database, the case sensitivity of the password is determined as follows:

    • If the password was originally entered in a case-insensitive database, the password remains case-insensitive.
    • If the password was originally entered in a case-sensitive database, uppercase and mixed case passwords remain case sensitive. However, if the password was entered in all lowercase, then the password becomes case-insensitive.
    • Changes to existing passwords and new passwords are case sensitive.

    The database server now uses SHA256 to hash passwords. The old (proprietary) hashing algorithm is still supported for passwords reloaded from old databases, but all new passwords will use SHA256.

    Passwords are now stored in UTF-8, so they continue to work if the database is reloaded into a database with a different character set.

    In previous releases when connecting from embedded SQL, it was possible to connect to a database with DBA permission and then successfully make a second connection to the same database for any user without specifying the password. Now the password must be specified on every connection.

  • Blank padding changes   In previous releases of SQL Anywhere, the semantics of string comparisons with blank-padded databases was as if the two strings being compared were padded with an infinite number of blanks. In this version 10, these semantics have been changed so that the comparison is performed by ignoring trailing blanks in each string.

    For equal (=) and not equal (<>) comparisons, there is no change in semantics; the two techniques (blank padding versus ignoring trailing blanks) yields the same results. However, there are differences for inequality comparisons. For example, suppose you have the two-byte string value 'a*' where the '*' represents a character in the database's collation that is less than the value of a blank. In previous versions of SQL Anywhere, the comparison predicate 'a*' < 'a' returns TRUE. In version 10, the predicate yields FALSE, since the shorter string is not padded with blanks before being compared.

    For more information about blank padding, see Initialization utility (dbinit) and CREATE DATABASE statement.

  • Case of return values for properties   Server properties (returned by the PROPERTY function) that returned YES or NO in previous releases now return Yes or No. Database properties (returned by the DB_PROPERTY function) and connection properties (returned by the CONNECTION_PROPERTY function) that returned ON or OFF in previous releases now return On or Off. This change may affect case-sensitive databases or applications that use case-sensitive string comparisons.

  • Changes to server property return values   In previous releases, the ConnsDisabled and RememberLastStatement server properties returned the values ON and OFF. They now return the values Yes and No. See Server-level properties.

  • Default location of sasrv.ini file has been changed   The default location of sasrv.ini is now %ALLUSERSPROFILE%\Application Data\SQL Anywhere 10 on Windows and ~/.sqlanywhere10 on Unix. In previous releases, the Unix file was named .sasrv.ini. On all platforms, the file is now named sasrv.ini.

  • Connections to database servers with long names   On Windows and Unix, version 9.0.2 and earlier clients cannot connect to version 10.0.0 and later database servers with names longer than 40 bytes.

  • Character set conversion is always enabled on the database server   The -ct database server option for enabling and disabling character set conversion is no longer supported. Character set conversion is always enabled for the database server, but if the database server determines that it is not required, then it is not used. You can disable character set conversion from the client by specifying CharSet=none. See CharSet connection parameter [CS].

  • Character set conversion unsupported on Windows CE   Character set conversion is not supported on Windows CE. In previous releases, character set conversion was disabled on the database server for Windows CE, and you could use any character set for the database. Now you must create databases for Windows CE using either the operating system character set or UTF-8. See Installation considerations: Using ICU on Windows Mobile.

  • Changes to system procedures and functions   Following is a list of changes to system procedures and functions:

    • Several system procedures have been made internal   The external system procedures xp_read_file, xp_write_file, xp_sprintf, xp_scanf, and xp_cmdshell are now internal system procedures.

    • sa_validate system procedure   The sa_validate system procedure now requires VALIDATE authority. See sa_validate system procedure.

    • sa_reset_identity system procedure   The table-name parameter is now required. Additionally, if the owner-name parameter is not specified, the table-name parameter must uniquely identify a table in the database. See sa_reset_identity system procedure.

    • sa_locks system procedure   The output of the sa_locks system procedure has been changed to return additional information, including the connection ID, the user ID, the table name, the lock class, and lock duration. See sa_locks system procedure.

    • RAND function    Previously, each connection was seeded with the same value so that the RAND function would return identical sequences for each connection. Now, each connection is uniquely seeded so that each connection will see a different random sequence. See RAND function [Numeric].

    • DB_CALLBACK_START and DB_CALLBACK_FINISH callback functions   The DB_CALLBACK_START and DB_CALLBACK_FINISH callback functions are now supported on all platforms (previously, they were only supported on Windows platforms). See db_register_a_callback function.

  • Scattered reads no longer used for files specified using a UNC name   Scattered reads are no longer used for files on remote computers, or for files specified using a UNC name such as \\mycomputer\myshare\mydb.db. See Use an appropriate page size.

  • Column ordering in primary and foreign key constraints   When creating primary key constraint, you can specify any order to the columns, regardless of the order in which columns appear in the table. Also, you can now create foreign keys that have a column order different from the primary key to which they refer, provided you specify the mapping between the foreign key columns and primary key columns. See the PRIMARY KEY clause in CREATE TABLE statement.

  • Duplicate column names no longer allowed in indexes   Previously, duplicate references to columns in an index were allowed, except for primary key, foreign key, and unique constraint specifications. Now, the behavior is consistent across all types of indexes; specifying duplicate column names returns an error. Additionally, if an older database contains an index with duplicate column references, the dbunload utility drops the duplicate columns from the index when generating reload.sql. See CREATE TABLE statement.

  • Encryption database property   Executing SELECT DB_PROPERTY( 'Encryption' ) may now return a value other than None, even when the database is not encrypted. This occurs when table encryption is enabled for the database. If you have an application that executes this command as a method for checking whether the database is encrypted, use SELECT DB_PROPERTY( 'EncryptionScope' ), instead. See Understanding database properties.

  • Change in syntax for starting HTTPS using FIPS   Previously, you would specify -xs HTTPS_FIPS(...). Now, you must specify -xs HTTPS(FIPS=yes;...). The former syntax is still supported, but is deprecated. See -xs server option.

  • Maximum user ID length is 128 bytes   In previous releases, when a statement required a user ID, the database server truncated user IDs longer than 128 bytes before using them in database server. If the string_rtruncation option was set, a truncation error was returned. The database server now returns an error if you specify a user ID that is longer than 128 bytes, regardless of the setting of the string_rtruncation option. See Identifiers.

  • Maximum length for server names   The maximum length of database server names has been increased from 40 bytes to 250 bytes on TCP/IP and shared memory connections. See -n server option.

  • Changes to acceptable characters in identifiers   Double quotes and backslashes are no longer permitted in identifiers. See Identifiers.

  • LicensesInUse server property renamed   The server property LicensesInUse has been renamed to UniqueClientAddresses. See Server-level properties.

  • SQL Anywhere OLE DB provider name has changed   The SQL Anywhere OLE DB provider (previously, ASAProv, ASAProv.90, ASAProv.80) is now called SAOLEDB. The version 10 provider can be referenced specifically by the name SAOLEDB.10.

  • SQL Anywhere sample database ODBC DSN has changed   The ODBC data source (previously, ASA 9.0 Sample) is now called SQL Anywhere 10 Demo.

  • Changes to connection strings   For ODBC and OLE DB connections, the precedence of where a connection parameter is found is now: connection string, SQLCONNECT environment variable, data source. Previously, in ODBC and OLE DB the data source had higher precedence than SQLCONNECT. See Notes about connection parameters.

  • Empty value connection parameters now treated as unspecified   For all APIs, connection parameters that are specified with empty values are treated as though the parameter was not specified. In previous releases, an empty value was treated as unspecified or as an empty string, depending on the location it was specified in and the API being used. See Notes about connection parameters.

  • Transaction log cannot be turned off while auditing is on   In previous versions of the software, you could stop using the transaction log for a database that had auditing turned on. Now, you must use a transaction log if auditing is turned on for a database. You must turn auditing off if you want to stop using the transaction log.

  • Databases with auditing turned on cannot be started in read-only mode   In previous versions of the software, you could start databases in read-only mode with auditing turned on. Now, databases with auditing on cannot be started in read-only mode.

  • Precision of signed BIGINT columns now 19 instead of 20   Previously, when an ODBC application described a signed BIGINT column using SQL_BIGINT, a precision value of 20 was returned, which was incorrect. Now, a value of 19 is returned. You need to change any applications that relied on the previous (incorrect) value.

  • Java VM enhancements   SQL Anywhere no longer offers the Java option as a separately licensed component. Java in the database now uses an external VM to run your Java code instead of using an internal VM. As a result, you can now use any Java VM you want and you are no longer restricted to particular JDK versions or Java targets. Newly-initialized databases are always Java enabled.

    This results in the following changes:

    • Unsupported database options   The following options are no longer supported in SQL Anywhere:

      • describe_java_format
      • java_heap_size
      • java_namespace_size
      • java_page_buffer_size
      • java_input_output
      • return_java_as_string

    • Unsupported properties   Support has been removed for the following properties:

      • Database properties:
        • JDKVersion
        • JavaHeapSize
        • JavaNSSize
      • Database server properties:
        • IsJavaAvailable
        • JavaGlobFix
      • Connection properties:
        • JavaHeapSize
        • java_input_output

    • New JavaVM property   The JavaVM database property returns the path to the Java VM that the database server uses to execute Java in the database.

    • Unsupported compatibility view columns   The following columns are no longer available in the system compatibility views:

      • SYSINFO.classes_version
      • SYSJAVACLASS.replaced_by
      • SYSJAVACLASS.type_id

    • Java options deprecated for database utilities   The following database utility options have been deprecated:

      • Initialization utility (dbinit): -ja, -jdk
      • Unload utility (dbunload): -jr
      • Upgrade utility (dbupgrad): -ja, -jdk, -jr, -j

    • Java support deprecated for some Java-related clauses in the CREATE DATABASE and ALTER DATABASE statements   The CREATE DATABASE statement no longer supports the JAVA ON | OFF and JDK version clauses, while the ALTER DATABASE statement no longer supports the REMOVE JAVA clause.

    • New Java file   In addition to the changes mentioned, the following file has been added: java\sajvm.jar.

  • Ping utility (dbping)   Previously, the Ping utility (dbping) reported an error if the database server returned NULL for a property value. Now, dbping prints NULL when a property value is unknown and exits with a success return code. You can specify the -en option if you want dbping to exit with a failed return code when a property value is unknown. See Ping utility (dbping).

  • Environment variables renamed   The following environment variables have been renamed for this release:

    Previous name New name
    ASTMP SATMP
    ASDIR SADIR
    ASLOGDIR SALOGDIR
    ASLANG SALANG
    ASCHARSET SACHARSET

  • Changes to PHP module file names   The naming convention for the PHP module files has been changed. In previous versions, the files were named phpX_sqlanywhereY.dll, where X was the PHP major version number and Y was the major SQL Anywhere version number. The PHP module files are now named php-a.b.c_sqlanywhereY.dll, where a.b.c is the full version number of the PHP source the file is built against and Y is the major SQL Anywhere version number. For example, php-5.0.2_sqlanywhere10.dll.

  • Specifying values for the PrefetchBuffer connection parameter   The PrefetchBuffer connection parameter now interprets values less than 16384 as kilobytes for backwards compatibility. Using kilobytes without the k suffix is deprecated. If the value of PrefetchBuffer is adjusted because it was out of the valid range or specified in kilobytes without the k suffix, the client log file shows the actual PrefetchBuffer value used. See PrefetchBuffer connection parameter [PBUF].

  • System-defined domains cannot be dropped   You can no longer drop system-defined domains, such as MONEY or UNIQUEIDENTIFIERSTR, from a database. See DROP statement.

  • Changes to database utilities   Following is a list of changes to the database utilities, as described:

    • The Service utility (dbsvc) can grant the Login as a Service privilege   The Service utility (dbsvc) prompts you to grant the Login as a Service privilege if the -a option is used and you try to run a service under an account that does not have the Login as a Service privilege enabled. If the -y option is used, dbsvc attempts to grant the Login as a Service privilege without prompting you. See Service utility (dbsvc) for Windows.

    • The Unload utility (dbunload) -an option can be used against a remote server   Prior to this change you could only run dbunload -an against a server on the same computer. Now you can run dbunload -an against a server that is running on a different computer. See Unload utility (dbunload).

    • The Server Enumeration utility (dblocate) host name or IP address formats   The host name or IP address can use any format, regardless of whether -n is specified. For example, if a server is running on myhost.mycompany.com, which has IP address of 1.2.3.4, to list only servers running on this computer from any computer with the mycompany.com domain, any of dblocate myhost, dblocate myhost.mycompany.com, or dblocate 1.2.3.4 can be used. In previous versions, only dblocate myhost.mycompany.com or dblocate -n 1.2.3.4 would have worked since the given hostname or IP address had to match the address string (excluding the port number) displayed by dblocate. See Server Enumeration utility (dblocate).

  • Default-related changes   The following changes have been made to defaults:

    • Default TCP/IP listening address changed for personal database server   On Windows, the personal database server now listens for connections on 127.0.0.1, rather than 0.0.0.0. This change means that users running SQL Anywhere with Windows Firewall enabled do not need to add dbeng10 to the exception list before it can be used.

      As a result of this change, trying to connect with LINKS=tcpip(HOST=hostname;DOBROADCAST=none) will not work if hostname is the real host name or IP address of the computer. However, using a hostname of localhost or 127.0.0.1 will work.

    • Default database page size changed to 4096   The default database page size for SQL Anywhere databases has been changed to 4096 bytes from 2048 bytes. This page size has been shown to improve performance in many environments. See CREATE DATABASE statement.

      If you do not specify the -gp option and start a database server with no databases loaded, the default page size on the database server is 4096.

    • Default maximum cache size changes   The default, maximum cache size on Windows (non-AWE) has been increased. The default maximum cache size is now limited to the lesser of:

      • 90% of (total_physical_memory - 4 MB), but no less than 2 MB
      • (available address space - 512 MB)

    • Unix cache size   The way the maximum cache size is calculated on Unix has changed. The default maximum cache size is now calculated as follows:

      • On 32-bit Unix platforms, it is the lesser of 90% of total physical memory or 1,834,880 KB.

        On 64-bit Unix platforms, it is the lesser of 90% of total physical memory and 8,589,672,320 KB.

      See Limiting the memory used by the cache and -ch server option.

    • Unix stored procedures   When upgrading existing Unix applications, if you are using the 64-bit database server, any existing external stored procedures must be changed to 64-bit.

    • Default size when converting NULL constants to NUMERIC or string data types   When converting a NULL constant to the NUMERIC data type, or to string data types such as CHAR, and VARCHAR, the length is now set to 0, instead of 32767.

    • Default URI for openxml system procedure has changed    When using the openxml system procedure, if a namespace declaration is not specified, then by default the prefix mp is bound to the Uniform Resource Identifier (URI). In previous releases of the software, this URI was urn:ianywhere-com:asa-xpath-metaprop. The default URI has been renamed to urn:ianywhere-com:sa-xpath-metaprop. See openxml system procedure.

  • Changes to cache size percentage calculation for -c, -ch-, and -cl server options   When using P (percentage) with -c, -ch, or -cl, the system now calculates percentage against either the amount of physical system memory, or the amount of available address space, whichever is lower. This eliminates the risk of allocating more memory for the cache than is available for addressing. See -c server option, -ch server option, and -cl server option.

  • Procedure_profiling server option renamed   The correct name of the server option that controls procedure profiling is now ProcedureProfiling. The previous form, Procedure_profiling, is still accepted, but will be unsupported in a future release. See sa_server_option system procedure.

  • TCP/IP port number does not need to be specified by clients connecting to a database server on HP-UX which is not using the default port   In previous versions of the software, if you started a database server on HP-UX, you had to specify a port number using the ServerPort [PORT] protocol option if the default port (2638) was already in use or if you did not want to use the default port.

    On HP-UX, the TCP/IP ServerPort protocol option is no longer required when multiple database servers are started on one machine. On Mac OS X, the TCP/IP ServerPort option must still be specified when starting a network server if a server is already running on the same computer. See ServerPort protocol option [PORT].

  • SOAP CONCRETE response renamed from ASADataSet to SimpleDataset   The CONCRETE response has been renamed from ASADataSet to SimpleDataset. See CREATE SERVICE statement.

  • Unload Database wizard behavior changes   You can no longer unload a database into an database version earlier than version 10. When you unload a version 9.0.2 or earlier database into a version 10 database, you cannot connect to database automatically once the rebuild completes.

  • Extract Database wizard behavior changes   You cannot extract version 9.0.2 and earlier databases. You must extract from a version 10 database.

  • -ui and -ux server options unsupported on Solaris   The -ui and -ux options are no longer supported on Solaris. They are still available on Linux.

  • Converting numeric data types   When converting a DOUBLE type to NUMERIC, SQL Anywhere now uses an algorithm that more precisely approximates the original DOUBLE value. With these changes, DOUBLE values with 15 or fewer significant digits are precisely converted to NUMERIC. In some cases, this may lead to different answers than previous versions of SQL Anywhere. See Converting between numeric sets.

  • sa_validate system procedure changes   The data, index, and full options for the sa_validate system procedure are no longer required and their use is deprecated. Unless you are requesting an express or checksum validation, the checks carried out using the former data, index, and full options are now performed by default. See sa_validate system procedure.

  • a_validate_type enumeration changes   The VALIDATE_DATA, VALIDATE_INDEX, and VALIDATE_FULL parameters for the a_validate_type enumeration are no longer required and their use is deprecated. The validations performed by these options are now performed by default when VALIDATE_NORMAL is specified. See a_validate_type enumeration.

  • SQLPATH environment variable syntax change   The syntax for the SQLPATH environment variable has changed on Unix. In previous versions, the path elements were separated by semi-colons (;) for all operating systems. In SQL Anywhere 10, the path elements are separated by colons (:) on Unix platforms, and by semi-colons on other platforms.

Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0