What's New in SQL Anywhere Studio
  What's New in Version 8.0.0
    Behavior changes in version 8

Adaptive Server Anywhere behavior changes

The following are behavior changes from previous versions of the software.

For a list of newly deprecated and unsupported features, see Deprecated and unsupported features.

• Java in the database separately licensable    As a consequence, the default behavior when creating a database is to exclude support for Java in the database.

  Java in the database is no longer needed in UltraLite reference databases, as the UltraLite generator has been changed to use an external Java VM.

  For more information, see Welcome to SQL Anywhere Studio.

• Aggregate functions and outer references    Adaptive Server Anywhere version 8 follows new SQL/99 standards for clarifying the use of aggregate functions when they appear in a subquery. These changes affect the behavior of statements written for previous versions of the software: previously correct queries may now produce error messages, and result sets may change.

  For more information, see Aggregate functions and outer references.

• User-supplied selectivity estimates    Adaptive Server Anywhere allows you to specify explicit selectivity estimates to guide the choice of access plan. These estimates were most useful as workarounds to performance problems where the software-selected access plan was poor. The new USER_ESTIMATES connection option controls whether the optimizer uses or ignores user-supplied selectivity estimates.

  If you have used these estimates as a workaround to performance problems, we recommend setting the USER_ESTIMATES option to OFF because an explicit estimate may become inaccurate and may force the optimizer to select poor plans. This version includes query processing enhancements such as internal join algorithms which provide a significant improvement in query performance.

  For more information about user-supplied selectivity estimates, see USER_ESTIMATES option [database] and Explicit selectivity estimates.

• Row ordering    A side-effect of improvements to query processing for version 8.0 is that row ordering is less deterministic. In the absence of an ORDER BY clause, Adaptive Server Anywhere returns rows in whatever order is most efficient. This means the appearance of result sets may vary depending on when you last accessed the row and other factors. The only way to ensure that rows are returned in a particular order is to use ORDER BY.

  The LIST function is among those functions particularly affected by this change.

• Access plan changes    The access plans selected by this release of Adaptive Server Anywhere are less likely to use indexes than previous releases of the software. Improvements to the efficiency of table scans, together with a more selective cost model used in comparing the cost of access plans, leads to a more accurate assessment of the usefulness of indexes than in previous versions of the software.

• Cursor changes    A side effect of cursor enhancements is that the cursors in this version provide behavior closer to defined standards than before. This may produce cursor sensitivity changes for some cursors, as Adaptive Server Anywhere supplies behavior that better matches the expectations of ODBC and other interfaces. For example, embedded SQL SCROLL cursors now disallow prefetching, so that value changes are reflected in the cursor.

  This change may affect existing applications that check return codes only for SQL_SUCCESS and not SQL_SUCCESS_WITH_INFO. Applications that check for SQL_SUCCESS_WITH_INFO receive a warning if the cursor behavior is different from that requested. The warning is SQLCODE=121, SQLSTATE 01S02.

  Insensitive cursors are not updatable.

  For more information, see Insensitive cursors.

• Stored procedure storage    Stored procedures are now stored as written. Adaptive Server Anywhere does create an internal representation of the procedure, which is used for profiling.

• OPEN CURSOR on insert not supported    The ability to open a cursor on an INSERT statement has been dropped. Opening an updateable cursor on a SELECT statement gives the same capabilities in an industry-standard manner.

• User-defined functions    User defined function parameters and return values are now cached. If a function is used several times within a SQL statement, the cached parameter values may result in the cached result being used, instead of the function being evaluated again. In previous releases, user-defined functions were re-evaluated each time they were needed. The new behavior provides better performance and more consistent results, but may change results compared to previous releases of the software.

• NUMBER(*) function changes    The use of the NUMBER function has been restricted to avoid problematic behavior. NUMBER is intended for use in the select-list of a query, to provide a sequential row-numbering of the result set, and this use is still permitted.

  The NUMBER function may now give negative numbers in cases where it previously did not, such as if you carry out an absolute fetch with a value of -1 and then move backward through the cursor. The new behavior corresponds to the ISO/ANSI fetch offset.

  Use of the NUMBER function in many circumstances, such as a WHERE clause or a HAVING clause, now gives an error.

  For more information, see NUMBER function [Miscellaneous].

• Custom collation changes    Previously, the -d option in the Collation utility accepted three parameters; now it accepts only two parameters. The cust-map-file parameter is no longer accepted. The syntax for the Collation utility is

  dbcollat -d coll-defn-file custom-file

  As well, the script files collsqmp.sql and custmap.sql are no longer present and cannot be used for built-in or custom collations, respectively.

  For newly-created databases, the SYSCOLLATIONMAPPINGS table contains only one row with the collation mapping. For databases created with previous versions of Adaptive Server Anywhere, this table contains a row for each built-in collation.

  For more information, see The Collation utility and SYSCOLLATIONMAPPINGS system table.

• Trigger name changes    Trigger names no longer need to be unique across a database. They only need to be unique within the table to which they apply. The syntax of DROP TRIGGER and COMMENT ON TRIGGER has consequently changed so that you can only specify an owner if you also specify a table. This means that older scripts that qualify triggers with only an owner will now result in a "Table not found" error.

• Addresses changed in sample database    The addresses in the Adaptive Server Anywhere 9.0 Sample database are different from those in previous releases.

• JAR file name for internal JDBC driver changed    The internal JDBC driver classes are now installed as a JAR file named ASAJRT instead of ASAJDBC.

• RESTORE DATABASE statement permissions    A connection to the utility database is no longer required to execute a RESTORE DATABASE statement. The permissions required to execute a RESTORE DATABASE statement are controlled by the -gu command line option.

  For more information, see RESTORE DATABASE statement.

• Return empty string as a NULL string for TDS connections    The TDS_EMPTY_STRING_IS_NULL option controls whether the server returns empty strings as a string containing one blank character or a NULL string for TDS connections.

  For more information, see TDS_EMPTY_STRING_IS_NULL option [database].

• COMMENT statement changed    Previously, the syntax for COMMENT ON INDEX included an optional owner name of the index. The index name can now optionally include the owner and table. The syntax for COMMENT ON INDEX is now

  COMMENT ON INDEX [ [ owner.]table.]index-name IS comment

  For more information, see COMMENT statement.

• Character set translation enabled by default    In previous versions of Adaptive Server Anywhere, character set translation was turned off by default and you had to specify the -ct command line option to enable character set translation. Character set translation is now enabled by default, but can be disabled using the -ct- command line option.

  When the server determines that the connection's character set differs from the database's character set, the server applies character set translation to all the character strings sent to and from the server for that connection.

  The server disables character set translation for a connection when it determines that the database and the connection have equivalent character sets.

  In most cases, character set translation should be enabled. One possible change in behavior occurs when binary data is inserted into a database and is fetched as character data, or vice versa. In this case, the data may not be returned exactly as it was entered because the server applies character set translation only to character data. To avoid this problem, applications should not send or fetch character data using a binary type.

  For more information, see -ct server option and Turning off character set translation on a database server.

• CONVERT, TIMESTAMP_FORMAT and DATE_FORMAT    When using the TIMESTAMP_FORMAT or DATE_FORMAT options, if you specify a character symbol in mixed case (such as Mmm), Adaptive Server Anywhere now chooses the case that is appropriate for the language that is being used. In addition, the CONVERT function now converts character dates into the case that is appropriate to the language that is being used. For example, in English the appropriate case is May, while in French it is mai.

  For more information, see DATE_FORMAT option [compatibility], TIMESTAMP_FORMAT option [compatibility], and CONVERT function [Data type conversion].

• Change to three-valued Boolean logic    Two-valued Boolean logic applies only to cases of expr = NULL, where expr refers to a base column or an expression over a base column. Otherwise, three-valued logic applies. The ANSINULL option now affects only this specific case in the query's WHERE clause.

• Sybase Central and Interactive SQL accept COMMLINKS connection parameter    In previous versions of Adaptive Server Anywhere, Sybase Central and Interactive SQL (the dbisql command line utility) ignored the COMMLINKS connection parameter. Sybase Central and Interactive SQL now accept this parameter.

  As a result of this change, some connection strings may behave differently than in previous versions of Adaptive Server Anywhere. Specifically, if you do not supply COMMLINKS=tcpip, Interactive SQL and Sybase Central do not look for servers on the network.

  For more information, see CommLinks connection parameter [LINKS].

• Clients ignore SQLLOCALE environment variable    Clients can use the CharSet connection parameter to specify the character set to be used on a connection. In previous versions of Adaptive Server Anywhere, the CHARSET parameter of the SQLLOCALE environment variable was used to change the client's default character set if the CharSet connection parameter was not supplied. Clients now ignore the SQLLOCALE environment variable.

• Unsupported character sets cause connection failure    Clients can use the CharSet connection parameter to specify the character set to be used on a connection. However, if the server does not support the requested character set, the connection fails. When a client requested an unsupported character set in previous versions of Adaptive Server Anywhere, the connection succeeded with a warning. If the client does not specify a character set, but the client's local character set is unsupported by the server, the connection succeeds, but with a warning that the character set is not supported.

  This behavior occurs in version 8 clients connecting to version 6.x, version 7.x, and version 8 database servers.

• Default packet size change    The default packets size for client/server communications has been changed from 1024 bytes to 1460 bytes.

  For more information on packet size, see CommBufferSize connection parameter [CBSIZE], and -p server option.

• dbdsn utility manages Adaptive Server Anywhere data sources only    The dbdsn command line utility for managing Adaptive Server Anywhere ODBC data sources is now explicitly restricted to Adaptive Server Anywhere data sources only.

• LOGIN_PROCEDURE option requires DBA authority    The LOGIN_PROCEDURE option can only be set by a user with DBA authority. In previous versions of Adaptive Server Anywhere, DBA authority was not required to set this option. A user with DBA authority can change the setting of this option for other users, but users without DBA authority cannot change their own setting of this option. As a result of this change, the DBA can ensure that a common procedure, if necessary, is executed when a user connects.

  For more information, see LOGIN_PROCEDURE option [database].

• ESTIMATE_SOURCE returns new values    The ESTIMATE_SOURCE function returns more detailed values than previously.

  For more information, see ESTIMATE_SOURCE function [Miscellaneous].

• NetWare 4.10 unsupported    Novell NetWare version 4.11 and later is still supported. Versions 3.x and 4.10 are unsupported.

• NetBios unsupported    The NetBios port is no longer supported. If you use NetBios, you should switch to TCP/IP or SPX.

• IPX unsupported    The IPX port is no longer supported. If you use IPX, you should switch to SPX or TCP/IP.

• Deprecated collations    The following collations are no longer supported. Where indicated, they have been superceded by different collations:

  Deprecated	Superceded by
  437	437LATIN1
  850	850LATIN1
  852	852LATIN2
  860	860LATIN1
  863	863LATIN1
  865	865NOR
  SJIS	932JPN
  SJIS2	932JPN
  WIN_LATIN1	1252LATIN1
  WIN_LATIN5	1254TRK
  Internal	850LATIN1
  437EBCDIC

• -e option no longer supported    The -e command line option and the -e option in the Data Source Utility, used to encrypt client/server communications, are no longer supported. The -ec option has replaced them. On the server, -ec simple uses the same encryption algorithm as -e in previous versions of Adaptive Server Anywhere.

• NONE parameter deprecated    The NONE parameter for the ISQL_PLAN option is no longer supported. The query optimization plan now appears on the Plan tab in the Results pane. When you click the Plan tab, a plan always appears. Previously, the plan appeared in the Messages pane.

• WITH HASH SIZE n clause deprecated    The WITH HASH SIZE clause is no longer supported.

• MAX_WORK_TABLE_HASH_SIZE option deprecated    The MAX_WORK_TABLE_HASH_ SIZE option is no longer supported.

• MAX_HASH_SIZE option deprecated    The MAX_HASH_SIZE option is no longer supported.

• SATMP environment variable deprecated    The SATMP environment variable used by UNIX versions of Adaptive Server Anywhere to indicate a directory where temporary files are kept is no longer supported. On UNIX, the ASTMP environment variable can be used to indicate where temporary files are kept.

  For more information, see ASTMP environment variable.

• dbtran -id option removed    The -id command line option on the dbtran command line utility is not present in this software.