What's New in SQL Anywhere Studio
What's New in Version 9.0.0
New features in version 9.0.0
This section introduces the new features in Adaptive Server Anywhere version 9.0. It provides an exhaustive listing of major and minor new features, with cross references to locations where each feature is discussed in detail.
XML support Adaptive Server Anywhere 9.0 includes a broad range of support for XML, including storing XML documents, exporting relational data as XML, importing XML, and returning XML from queries on relational data.
FOR XML clause The SELECT statement supports a FOR XML clause with three modes, RAW, AUTO, and EXPLICIT, that allow you to obtain query results as an XML document. Each mode allows you a different level of control over the format of the XML that is generated.
For more information, see Obtaining query results as XML and SELECT statement.
FOR_XML_NULL_TREATMENT option You can use the FOR_XML_NULL_TREATMENT option to control how NULL values are returned by a query that includes the FOR XML clause.
For more information, see FOR_XML_NULL_TREATMENT option [database].
OPENXML procedure For more information, see OPENXML function [String].
SQL/XML support SQL/XML is a draft standard that describes the ways SQL can be used in conjunction with XML. As part of its SQL/XML support, Adaptive Server Anywhere includes an XML data type that can be used to store XML documents in the database.
For more information, see XML data type [Character].
Adaptive Server Anywhere also supports the following SQL/XML functions that provide an alternative method to the FOR XML clause for generating XML documents from your relational data:
XMLAGG function This aggregate function generates a forest of XML elements from a collection of XML elements.
For more information, see XMLAGG function [String].
XMLCONCAT function This function generates a forest of XML elements by concatenating together the XML values that are passed in to it.
For more information, see XMLCONCAT function [String].
XMLELEMENT function This function generates an XML element for which you can optionally specify element content, attributes, and attribute content.
For more information, see XMLELEMENT function [String].
XMLFOREST function This function generates a forest of XML elements.
For more information, see XMLFOREST function [String].
XMLGEN function This function generates an XML value based on an XQuery Constructor.
For more information, see XMLGEN function [String].
HTTP server in the database Adaptive Server Anywhere database servers can now act as web servers, allowing you to write and run web-based applications using only an Adaptive Server Anywhere database and a web browser of your choice.
This feature allows the database server to handle standard HTTP and HTTPS requests, as well as standard SOAP requests. Service types available are HTTP, HTTPS, XML, RAW, SOAP, and DISH. DISH is a SOAP service handler.
To gain the benefits of this enhancement on databases created before this release, you must upgrade the database using the Upgrade utility.
For more information, see Using the Built-in Web Server.
Index Consultant The Index Consultant is a tool to assist you in proper selection of indexes. It analyzes either a single query or a set of operations, and recommends indexes to add to your database and to remove from the database.
To gain the benefits of this enhancement on databases created before this release, you must upgrade the database using the Upgrade utility.
For more information, see Index Consultant overview.
64-bit version available A full 64-bit version of the software is available for Windows Server 2003 on Itanium II chips. A deployment release is available on 64-bit Linux and HP-UX operating systems.
For details of platform support, see SQL Anywhere Studio Supported Platforms.
The WITH clause can now be used before a select to specify common table expressions Common table expressions are temporary view definitions that exist only within the scope of a SELECT statement. They can be recursive, or non-recursive. They sometimes let you write queries in a more elegant manner. They also permit you to perform multiple levels of aggregation within a single query. They can be used only within a top-level SELECT statement, within the top-level SELECT statement within a view definition, or within the top-level statement within an INSERT statement.
For more information, see Common Table Expressions.
Recursive union can now be performed using a common table expression of a particular form Recursive common table expressions allow you to write recursive queries. These are particularly useful when querying tables that represent hierarchical data structures or directed graphs. Each recursive common table expression contains an initial subquery, which is executed first, and a recursive subquery. The A reference to the view, which must appear within the FROM clause of the recursive subquery, references the rows added to the view during the previous iteration. You must be particularly careful to provide conditions that stop the recursion if the data structure you are querying may contain cycles.
For more information, see Recursive common table expressions.
INTERSECT and EXCEPT operations are now supported These operations compute the intersection and difference between two or more result sets. They complement the UNION operation.
For more information, see the following:
SELECT statements can operate on stored procedure result sets In SELECT statements, a stored procedure call can now appear anywhere a base table or view is allowed.
If you want statistics on stored procedure calls to be stored, you must upgrade the database using the Upgrade utility. Without statistics, you may get bad plans if you try to join the result of a stored procedure call.
For more information, see FROM clause.
Online analytical processing features added Several OLAP features have been added to the allowed SQL language:
ROLLUP operation For queries with a GROUP BY clause, the ROLLUP operation adds subtotal rows into the result set. Each subtotal row provides an aggregate over a set of rows in the GROUP BY result set.
For more information, see The ROLLUP operation: adding summary information to GROUP BY queries
The LIST function can include ordered lists The LIST function has been extended to provide sorted lists of items.
For more information, see LIST function [Aggregate].
Additional aggregate functions Functions have been added to compute sample-based and population-based standard deviations and variances.
For more information, see Aggregate functions.
The CREATE INDEX statement permits an index to be created on a built-in function This feature is a convenience method that adds a new computed column to a table, and creates an index on that column.
For more information, see CREATE INDEX statement, and Creating indexes.
ORDER BY clause allowed in all contexts In previous releases, many SELECT statements in view definitions, in subqueries, or in UNION operations were not allowed to use an ORDER BY clause. This restriction has now been removed.
In some cases, particularly when combined with the FIRST or TOP clause, using a SELECT with an ORDER BY clause does affect the results of a view definition or a set operation. In other contexts, the ORDER BY clause is allowed but makes no difference to the operation.
SELECT statements can now include START AT as part of the TOP clause START AT provides additional flexibility in queries that explicitly limit the result set.
For more information, see SELECT statement.
Constraints can now be named Check constraints, unique constraints, and referential integrity constraints can now be assigned names. This permits modification of table and column constraints by changing individual constraints, rather than by modifying an entire table constraint.
To gain the benefits of this enhancement on databases created before this release, you must upgrade the database file format by unloading and reloading the database.
For more information, see ALTER TABLE statement, CREATE TABLE statement, and Using table and column constraints.
Lateral derived tables permit outer references in the FROM clause Outer references can now be made from derived tables and from stored procedures in the FROM clause. To indicate that an outer reference is being made, the LATERAL keyword is used.
For more information, see FROM clause.
EXECUTE IMMEDIATE allows more flexible escape character processing A new option WITH ESCAPES OFF allows escape character processing to be suppressed. This feature makes it easier to construct dynamic statements that include file paths.
For more information, see EXECUTE IMMEDIATE statement [SP].
EXECUTE IMMEDIATE supports queries that return result sets This new feature allows more dynamic construction of statements inside stored procedures.
For more information, see Using the EXECUTE IMMEDIATE statement in procedures, and EXECUTE IMMEDIATE statement [SP].
CREATE FUNCTION and ALTER FUNCTION now permit Transact-SQL syntax You can now create user-defined functions in the Transact-SQL dialect that return a scalar value to the calling environment.
For more information, see CREATE FUNCTION statement.
Values of autoincrement columns are now available when inserting multiple rows When inserting rows through a value-sensitive (keyset driven) cursor, the newly inserted rows appear at the end of the cursor result set.
A consequence of this change is that the value of an autoincrement column for the most recent row inserted can be found by selecting the last row in the cursor. For example, in embedded SQL the value could be obtained using FETCH ABSOLUTE -1 cursor-name.
For more information, see Modifying rows through a cursor.
Remote Data Access now handles UUID/GUID columns Remote Data Access can now manage SQL Server unique identifier columns.
For more information, see Data type conversions: Microsoft SQL Server, and UNIQUEIDENTIFIERSTR data type [Character].
Remote Data Access now names remote connections Remote Data Access connections made via ODBC are now given names, so that they can be dropped.
For more information, see Managing remote data access connections.
New function returns data type of an expression The EXPRTYPE function returns the data type of an expression.
For more information, see EXPRTYPE function [Miscellaneous].
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].
OUTPUT statement accepts ASIS keyword When ASIS is specified, values are written to the file without any escaping.
For more information, see OUTPUT statement [Interactive SQL].
Indexes and foreign keys can be altered The ALTER INDEX statement allows indexes and foreign keys to be renamed. It also allows an index type to be changed to clustered or nonclustered for user-created indexes as well as primary or foreign key indexes.
To gain the benefits of 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 ALTER INDEX statement.
Multiple distinct aggregates permitted in queries Aggregate functions can take DISTINCT column-name as an argument. In previous versions of the software, only one aggregate function with a DISTINCT argument could be included in a query. Now, multiple such functions can be used. The following query is permitted in version 9, but not in earlier versions of the software:
SELECT count( DISTINCT first_name ),
count( DISTINCT last_name )
FROM contactFull length and abbreviated day names are recognized in all supported languages for event schedules When creating events, the database server recognizes both full-length and abbreviated day names in any of the languages supported by Adaptive Server Anywhere. Previously, schedules in non-English languages required full day names.
For more information, see CREATE EVENT statement.
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 allows applications and databases to be distributed without revealing the logic in stored procedures, functions, triggers, and views.
To gain the benefits of this enhancement on databases created before this release, you must upgrade the database using the Upgrade utility.
For more information, see Hiding the contents of procedures, functions, triggers and views.
The Validation utility gives more detailed return codes The Validation utility (dbvalid) gives more specific return codes to indicate the reason a failure occurs.
For more information, see Validating a database using the dbvalid command-line utility.
Two new server properties Two new server properties have been added. CommandLine gives you the line that was used to start the server, and CompactPlatformVer gives a condensed version of the PlatformVer server property.
For more information, see Server-level properties.
New sp_remote_primary_keys stored procedure In order to obtain primary key information about remote tables using remote data access, a new stored procedure called sp_remote_primary_keys has been added.
To gain the benefits of this enhancement on databases created before this release, you must upgrade the database using the Upgrade utility.
For more information, see sp_remote_primary_keys system procedure.
New connection_property returns the name of the communication link for the connection The new CommNetworkLink connection property returns the name of the communication link for the connection.
For more information, see Connection-level properties.
NetWare now supports full character set conversion In 8.x, NetWare supported single-byte-to-single-byte character set conversion, but in 9.0, all character sets supported by the other platforms are also supported on NetWare.
For more information, see -ct server option.
Unload utility can unload column lists The Unload utility (dbunload) can now unload the column list for the LOAD TABLE statements that it generates in the reload.sql file, facilitating easier reordering of the columns in a table
For more information, see The Unload utility.
Database server registers with LDAP The database server can now register itself with an LDAP server, so that clients and the Locate Utility [dblocate] can query the LDAP server to find it. This allows clients running over a WAN or through a firewall to find servers without specifying the IP address to find such servers. LDAP is only used with TCP/IP, and only on network servers.
For more information, see Connecting using an LDAP server or LDAP communication parameter [LDAP].
Improved handling of a large number of connections The liveness timeout value now increases automatically when there are more than 200 connections in an effort to better handle a large number of connections.
For more information, see -tl server option and LivenessTimeout connection parameter [LTO].
Request log filtering, host variable support Output to the request-level log can now be filtered to include only requests from a specific connection or for a specific database. As well, host variable values can now be output to a request log.
For more information, see sa_server_option system procedure, Monitoring and Improving Performance, sa_get_request_times system procedure, and -zr server option.
BACKUP statement and DBBACKUP allow renaming of log copy You can use the BACKUP statement and the Backup utility [dbbackup] to rename the log copy.
For more information, see The Backup utility and the BACKUP statement.
START DATABASE statement allows log truncation on checkpoint and read-only mode The START DATABASE statement now allows a database to be started either with log truncation on checkpoint enabled, or in read-only mode.
For more information, see START DATABASE statement.
Adaptive Server Anywhere supports different auditing options In previous versions of Adaptive Server Anywhere, you could choose to turn auditing on or off. Now you can specify which options you want to audit.
For more information, see sa_disable_auditing_type or sa_enable_auditing_type.
Three new values can be passed to the event_parameter function Three new values can be passed to the event_parameter function. ScheduleName returns the name of the schedule which fired the event. AppInfo returns the value of the connection_property('AppInfo') for the connection which caused the event. DisconnectReason returns a string indicating why the connection terminated.
For more information, see EVENT_PARAMETER function [System].
New server property specifies how many concurrent users are connected to the network server The new LicensesInUse property determines the numbers of concurrent users currently connected to the network server. Each concurrent user is determined by the number of unique client network addresses connected to the server, not the number of connections. For example, if three client machines are connected to a server, and each client machine has two connections, select property( 'LicensesInUse' ) is '3'.
For more information, see Server-level properties.
The Service Creation [dbsvc] utility can now start and stop services Two new options have been added to the Service Creation [dbsvc] utility. Dbsvc -u <service_name> starts the service named service_name, and dbsvc -x <service_name> stops the service named service_name.
For more information, see Managing services using the dbsvc command-line utility.
The network server supports the LocalOnly communication parameter [LOCAL] You can use the LocalOnly communication parameter [LOCAL] with the server. Running a server with the LocalOnly communication parameter set to YES allows the network server to run as a personal server without experiencing connection or CPU limits.
For more information, see LocalOnly communication parameter [LOCAL].
New minimum database server cache size when using Address Windowing Extensions The minimum size of the database server cache when using Address Windowing Extensions (AWE) on Windows 2000, Windows XP, and Windows Server 2003 is now 2 Mb. In previous releases, the minimum cache size when using AWE was 3 Gb-256 Mb.
For more information, see -cw server option.
New database property specifies drive type The new DriveType database property provides information about the drive on which the database file is located.
For more information, see Database properties.
Adaptive Server Anywhere NetWare now faster The Adaptive Server Anywhere server for NetWare now uses LibC rather than CLIB. LibC is a C runtime library that allows better interaction with the new kernel of the NetWare operating system than the legacy CLIB library. All client-side software for NetWare (including dblib, dbisql, dbconsole, and dbremote) still uses CLIB. This has the benefit of increasing the maximum file size on NetWare to the same as NTFS, allowing multiple CPUs if available, and allowing TCP and SPX to use Winsock, which is faster than previous versions.
For more information, see Physical Limitations and Behavior changes in version 9.0.
External function enhancements on NetWare External functions or external stored procedures on NetWare can now use multiple NLMs without naming conflicts.
For more information, see External function prototypes.
Connections can specify language of error messages Each connection to the database server can now request the language in which the database server reports error messages and various other strings. The language used by the connection is independent of the language used by the server. The database server also uses the language requested by the connection to interpret date strings.
Two new server properties identify processor type Two new server-level properties have been added. ProcessorArchitecture identifies the processor type, and on platforms where a processor can be emulated NativeProcessorArchitecture identifies the native processor type.
For more information, see Server-level properties.
The new features listed here are query optimization enhancements that require no user action to use. They take effect without user intervention. If you study query execution plans, you may see the effect of these optimizations.
The optimization enhancements do not require a database upgrade, but they do operate most effectively on a database created using version 9 software.
Cost-based subquery optimization The optimizer has greatly extended the scope of optimizations that are available for subqueries. In previous releases, subqueries were either rewritten as joins during semantic query optimization or were optimized separately from the remainder of a query. Now subqueries that are too complex to be rewritten as joins can still be optimized as an integral part of the query.
Buffered row fetching improves performance of sequential scans When reading rows from a database page for a sequential table scan, Adaptive Server Anywhere can now copy rows into a buffer before returning them to the consumer. Depending on the complexity of the query, this can provide significant time savings.
Top N queries executed more efficiently A new algorithm for executing queries that use the TOP N clause permits faster execution.
For more information, see Sort Top N.
New algorithm for determining which frequencies are kept in histograms Previously, column histograms created singleton buckets for values with selectivity > 1%. Now, the condition for singleton buckets is relaxed, and instead the histogram tries to keep a minimum number of singleton buckets.
For more information, see Optimizer estimates.
Property QueryCachedPlans shows how many query plans are currently cached The new property, QueryCachedPlans, shows how many query execution plans are currently cached for a given connection, or across all connections. It can be used in combination with QueryCachePages, QueryOptimized, QueryBypassed, and QueryReused to determine the best setting for the MAX_PLANS_CACHED option.
For more information, see Connection-level properties and Database properties.
Plans are cached faster for procedure statements The scope of statements for which access plans are cached has been extended to include queries within stored procedures whose result sets are returned by the procedure to the calling environment. This enhancement eliminates the need to re-optimize some statements.
For more information, see Access plan caching.
Index statistics maintained as each index is updated Statistics are maintained for all indexes, including those on catalog tables, as each index is updated, providing accurate statistics to the optimizer at virtually no performance cost. Statistics persist in SYSATTRIBUTE in the form of one row for each statistic for an index.
To gain the benefits of this enhancement on databases created before this release, you must upgrade the database file format by unloading and reloading the database.
For more information, see SYSATTRIBUTE system table.
New performance monitor statistics Two new performance monitor statistics, Comm: Licenses in Use, and Connection Count, have been added to allow users to track the number of connections in use.
For more information, see Communications statistics and Miscellaneous statistics.
The option APPEND { ON | OFF } has been added to the UNLOAD and UNLOAD TABLE statements A new APPEND option allows unloaded data to be appended to the end of the specified file.
For more information, see the UNLOAD statement or the UNLOAD TABLE statement.
Temporary tables can now be declared as NOT TRANSACTIONAL When NOT TRANSACTIONAL is used, the table is not affected by COMMIT or ROLLBACK. This extension is useful when procedures that access the table are called repeatedly without a COMMIT.
For more information, see CREATE TABLE statement, and DECLARE LOCAL TEMPORARY TABLE statement.
Persistent index statistics Maintaining accurate statistics about the physical properties of candidate indexes facilitates the optimizer's cost based decisions about which indexes to use. Statistics now persist in SYSATTRIBUTE, and are maintained as each index is updated. Additionally, the VALIDATE statement verifies that the statistics on the specified index(es) are accurate and generates an error if they are not. This provides accurate statistics to the optimizer at virtually no performance cost.
To gain the benefits of this enhancement on databases created before this release, you must upgrade the database file format by unloading and reloading the database.
For more information, see SYSATTRIBUTE system table and the VALIDATE INDEX statement
New OPTIMISTIC_WAIT_FOR_COMMIT option added This option is meant to mimic 5.x locking behavior when transactions add foreign rows before primary rows. While it is not intended for general use, it can be helpful when migrating 5.x applications to version 8.x.
For more information, see WAIT_FOR_COMMIT option [database].
New extended property function added The new db_extended_property() is similar to db_property() except that it also allows an optional property-specific string parameter to be specified.
For more information, see DB_EXTENDED_PROPERTY function [System].
Two new properties added Two new properties have been added: FileSize and FreePages. Each of these properties can take an optional argument which specifies the dbspace for which the property is being requested.
For more information, see Database properties.
Server's quiet mode enhanced The server's quiet mode and error logging switches have been enhanced to allow the server to suppress a variety of messages. Additionally, the -qw option has replaced the -q option, and the -qi option has replaced the -Q option.
Adaptive Server Anywhere plug-in changes The Adaptive Server Anywhere plug-in for Sybase Central has been reorganized. Much of the information that was previously available in property sheets, dialog boxes, and folders in the left pane is now available on tabs in the right pane. For example, to view information about a foreign key, you now select the table that has the foreign key in the left pane and then select the Foreign Keys tab in the right pane. In previous versions, there was a separate Foreign Keys folder in the left pane.
Several other changes have been made to the plug-in, including the following:
The Code Editor and Table Editor are no longer separate windows. Now you can edit stored procedures, functions, triggers, and events, as well as edit tables, in the right pane of Sybase Central.
The toolbar buttons now change to include options specific to the object selected.
The SQL Statements log and server messages (the same information that appears in the Server Messages window) can now be viewed directly in the Sybase Central main window. To view this information, in Sybase Central choose File
The Adaptive Server Anywhere plug-in provides several new wizards to guide you through tasks, including creating tables, unique constraints, and web services.
Enhanced clipboard support in the Adaptive Server Anywhere plug-in Clipboard support has been enhanced in the Adaptive Server Anywhere plug-in so you can copy and paste most objects within Sybase Central into other applications, such as Interactive SQL or a text editor. When you copy objects into other applications, depending on the object you select, either the object name or the SQL for the object appears. For example, if you copy an index in Sybase Central and paste it into a text editor, the CREATE INDEX statement for that index appears.
For more information, see Copying database objects in Sybase Central.
Debugger changes The database object debugger that lets you debug both stored procedures and Java classes has been integrated into Sybase Central. The user interface has been redesigned.
For more information, see Debugging Logic in the Database.
Sybase Central, Interactive SQL, and the Console utility include an option to automatically check for software updates Sybase Central, Interactive SQL, and the Console utility can be configured to automatically check for software updates. This option can be set from the Options dialog in Interactive SQL and the Console utility, and can be set from the Help menu in Sybase Central when the Adaptive Server Anywhere plug-in is loaded. In previous releases, you had to go to a web site to obtain this information.
For more information, see Options dialog: Check for updates tab.
Enhancements made to the Console utility There have been a number of enhancements to the Console utility, including changes to the interface, support for multiple connections, sorting, and drag and drop.
Fast launching of Sybase Central and Interactive SQL On Windows, Sybase Central and Interactive SQL include a fast launcher that is designed to reduce application startup time when you start Sybase Central or Interactive SQL. Running Adaptive Server Anywhere 9.0 starts two background processes, an instance of dbisqlg.exe and an instance of scjview.exe, which are the fast launcher processes for Interactive SQL and Sybase Central, respectively. Both of these executables are started when the user logs in.
For more information, see Options dialog: General tab.
Syntax highlighting editor in Interactive SQL You can configure the appearance of syntax typed in the SQL Statements pane of Interactive SQL using the Interactive SQL Options dialog.
For more information, see Options dialog: Editor tab.
Printing from Interactive SQL You can print the contents of the SQL Statements pane and of the graphical plan in Interactive SQL.
For more information, see Interactive SQL main window description.
Graphical plan enhancements The graphical query access plan display has been enhanced in several ways:
The number of rows that passes from one operator to another is indicated by varying line thickness.
Slow operations are highlighted by a red border.
The statistics display has been extended and reorganized.
You can now print the access plan.
Database Utilities accept @filename parameters All of the database administration utilities except Interactive SQL [dbisql], the Debugger utility [dbprdbg], the Language utility [dblang], and the Console utility [dbconsole] now accept parameters contained within a file using the @file syntax. The filename can occur at any point in the command line, and parameters contained in the file are inserted at that point. Multiple files can be specified, and the file specifier can be used with command line switches. Note that the @file syntax is not recursive.
For more information, see the @filename server option.
Row numbers can appear beside results in Interactive SQL Interactive SQL has an option to display row numbers beside results. This option can be set on the Results tab of the Interactive SQL options dialog.
For more information, see Options dialog: Results tab.
Interactive SQL can be set as the default editor for .SQL files On Windows platforms, you can create a file association for .SQL files so that when you double-click the file, Interactive SQL is used to open the file.
For more information, see The Interactive SQL utility.
Interactive SQL Command History dialog enhancements You can now copy and delete commands from the Command History dialog in Interactive SQL, as well as select multiple commands in the window. The command history now persists between Interactive SQL sessions.
For more information, see Printing SQL statements.
Warning messages now have W prefix Prior to version 9.0, all warning and error messages had a prefix of I or E. Warning messages now have a prefix of W. This change affects dbmlsrv9, dbmlsync, dbremote, ssremote, dbltm, and ssqueue.