What's New in SQL Anywhere Studio
What's New in Version 8.0.2
New features in version 8.0.2
This section introduces the new features in Adaptive Server Anywhere version 8.0.2. It provides an exhaustive listing of major and minor new features, with cross references to locations where each feature is discussed in detail.
Clustered index support Creating a clustered index on a table causes the rows in that table to be stored in approximately the same order as they appear in the index. You can use the LOAD TABLE statement to load a table with information in the clustered order. As you insert information into the table, the clustering characteristics of the table degrade. You can use the REORGANIZE TABLE statement to reestablish the clustering order. Clustered indexes can improve performance.
To use clustered indexes on databases created before this release, you must upgrade the database file format by unloading and reloading the database.
For more information, see Using clustered indexes.
Unique identifier support Adaptive Server Anywhere supports unique identifiers (UUIDs and GUIDs). UUIDs (universally unique identifiers) and GUIDs (globally unique identifiers) are a mechanism for uniquely identifying rows, even across distinct databases in a synchronization environment.
For more information, see The NEWID default.
Update existing rows with ON EXISTING clause You can use the ON EXISTING clause of the INSERT statement to update existing rows with new values, as long as the table has a primary key.
For more information, see Changing data using INSERT, or the INSERT statement.
BACKUP statement supported on Windows CE Adaptive Server Anywhere allows you to create image backups of databases operating on the Windows CE platform, or to rename or truncate the database's transaction log.
For more information, see Types of backup, or the BACKUP statement.
Graphical plan enhancements The graphical plan has been enhanced to include more information, resulting in a new look.
For more information, see Graphical plans.
Use of work tables is now explicit The use of work tables is now postponed until as late as possible in the plan. When work tables are used, they now appear explicitly in the graphical plan.
For more information, see Graphical plans or Use of work tables in query processing.
New joins added New joins added to this release include the nested loops semijoin, the nested loops anti-semijoin, the hash semijoin and the hash anti-semijoin.
For more information, see Join algorithms.
Obtain plan for SQL queries of a specific cursor-type You can now obtain plans for SQL queries based on their cursor type, using the PLAN, EXPLANATION, GRAPHICAL_PLAN functions.
For more information, see GRAPHICAL_PLAN function [Miscellaneous], EXPLANATION function [Miscellaneous], or PLAN function [Miscellaneous].
For information about setting these plan options in Interactive SQL, see Options dialog: Plan tab.
Character set conversion function A new function CSCONVERT is available to convert strings between character sets.
For more information, see CSCONVERT function [STRING].
Variable test function A new function VAREXISTS is available to test whether a user-defined variable has been created or declared with a given name. After this test, the variable can be created if necessary, and then used safely.
For more information, see VAREXISTS function [Miscellaneous].
Hide procedure text to keep your logic confidential You can obscure the logic contained in stored procedures, functions, triggers and views using the SET HIDDEN option. This is allows applications and databases to be distributed without revealing the logic in stored procedures, functions, triggers, and views.
For more information, see Hiding the contents of procedures, functions, triggers and views.
LOAD TABLE now accepts delimiters of more than 1 byte The LOAD TABLE statement now supports delimiters that are up to 255 bytes.
For more information, see the LOAD TABLE statement.
New statement provides compatibility for Adaptive Server Enterprise and Microsoft SQL Server You can use the DEALLOCATE statement to release resources associated with a cursor. This statement is provided for Adaptive Server Enterprise and Microsoft SQL Server compatibility.
For more information, see the DEALLOCATE statement.
ALTER DATABASE statement behaves like dblog utility You can use the ALTER DATABASE statement to change the transaction log and mirror log names associated with a database file. Previously, you could only do this using the Transaction Log (dblog) utility.
For more information, see the ALTER DATABASE statement.
LOAD TABLE can be used for both global and local temporary tables Adaptive Server Anywhere now supports the LOAD TABLE statement on declared local temporary tables. Previously, only global temporary tables were supported.
For more information, see the LOAD TABLE statement.
SET statement can be used to assign variable values You can now assign values to variables using the SET statement in Transact-SQL procedures.
INSERT statement now supports WITH AUTO NAME If you specify WITH AUTO NAME in an INSERT statement, the names of the items in the SELECT list determine the associations of values to destination columns.
For more information, see INSERT statement.
EXIT statement enhanced The Interactive SQL EXIT statement can now set an exit code for Interactive SQL.
For more information, see EXIT statement [Interactive SQL].
Specify the optimization goal for a query in the FROM clause You can use the FASTFIRSTROW table hint to set the optimization goal for the query without setting the OPTIMIZATION_GOAL option to first-row.
For more information, see FROM clause.
New utility allows you to hide the contents of files Configuration files, also known as command files, sometimes contain passwords. As an enhanced security feature, Adaptive Server Anywhere has a new utility, called the File Hiding utility, that allows you to hide the contents of configuration files using simple encryption.
For more information, see The File Hiding utility
Certicom encryption changes Security has been enhanced to support two types of Certicom encryption, ECC_TLS and RSA_TLS. The encryption known in previous versions of Adaptive Server Anywhere as Certicom encryption has been renamed to ECC_TLS encryption. The Certicom parameter is still accepted and is equivalent to ECC_TLS encryption. Adaptive Server Anywhere now also supports RSA_TLS encryption.
For more information, see the -ec server option or the Encryption connection parameter [ENC].
New communication parameters can improve network responsiveness The LazyClose and PrefetchOnOpen network communication parameters can improve performance on networks with poor latency or with applications that process many requests.
For information about these parameters, see the LazyClose connection parameter [LCLOSE] and the PreFetchOnOpen communication parameter
Scattered reads now used on Windows NT/2000/XP Previously, sequential scans of large tables copied pages to a 64K buffer and then into the cache. Now, providing you are running in a Windows NT Service Patch 2 or higher environment, or in a Windows 2000/XP environment, and provided your page size is at least 4K, scattered reads copy the pages directly to the cache, thus saving time and improving performance.
For more information, see Use an appropriate page size.
Improved time resolution in request-level logging The times obtained using procedure profiling or request-level logging now have a resolution of 1 millisecond. This change primarily affects servers running on Windows operating systems.
Running multiple versions of the Performance Monitor If you run multiple versions of Adaptive Server Anywhere simultaneously, you can also run multiple versions of the Windows Performance Monitor simultaneously.
For more information about the Windows Performance Monitor, see Monitoring database statistics from Windows Performance Monitor.
Changing server's temp folder via a registry setting On Windows CE platforms, you can use the registry to specify which temporary directory the server uses.
For more information, see Registry settings on Windows CE.
New iAnywhere JDBC driver This robust and high-performance JDBC driver enjoys the benefits of ODBC data sources and the Command Sequence client/server protocol. It is an alternative to the jConnect JDBC driver.
For information on the iAnywhere JDBC driver, see Using the iAnywhere JDBC driver.
For information on choosing a JDBC driver, see Choosing a JDBC driver.
Triggers can discriminate among the actions that caused a trigger to fire You can now carry out different actions depending on whether the trigger was fired by an UPDATE, INSERT, or DELETE operation. This feature enables you to share logic among the different events within a single trigger, and yet carry out some actions in an action-dependent manner.
For more information, see Trigger operation conditions.
RETURN_DATE_TIME_AS_STRING is no longer TDS specific All connections can now use the RETURN_DATE_TIME_AS_STRING option.
For more information about this option, see RETURN_DATE_TIME_AS_STRING option [database].
Units can be specified when adding space to a dbspace You can extend database files by a specific size, in units of pages, kilobytes, megabytes, gigabytes, or terabytes.
For more information, see the ALTER DBSPACE statement
sa_make_object system procedure This system procedure can be used in a SQL script to ensure that a skeletal instance of an object exists before executing an ALTER statement which provides the actual definition.
For more information, see sa_make_object system procedure.
New global variable compatible with Microsoft SQL Server A new global variable has been introduced to allow for Microsoft SQL Server compatibility. The @@fetch_status global variable is the same as the @@sqlstatus global variable, except that it returns the status of the most recent fetch in different values.
For more information, see Global variables.
Character set conversion supported on NetWare NetWare now supports character set translation.
For more information, see -ct server option.
Information utility reports the version of installed Java classes The dbinfo utility and a_db_info structure now report the version of the Java classes installed in a database.
For more information, see The Information utility and a_db_info structure.
Suppress warnings on fetch operations Versions 8.0 and later of the database server return a wider range of fetch warnings than earlier versions of the software. The ODBC Configuration for Adaptive Server Anywhere dialog allows you to suppress warning messages returned from the database server to ensure that they are handled properly for applications that are deployed with earlier versions of the software.
For more information, see ODBC tab.
Controlling updates to primary key columns Setting the new prevent_article_pkey_update option to ON disallows updates to the primary key columns of tables that are part of a publication. This option helps ensure data integrity, especially in a replication and synchronization environment.
For more information, see the PREVENT_ARTICLE_PKEY_UPDATE option [database].