This section introduces the new features in Adaptive Server Anywhere version 9.0.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.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_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.
openxml procedure See openxml system procedure.
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.
See XML data type.
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.
XMLCONCAT function This function generates a forest of XML elements by concatenating together the XML values that are passed in to it.
XMLELEMENT function This function generates an XML element for which you can optionally specify element content, attributes, and attribute content.
XMLFOREST function This function generates a forest of XML elements.
XMLGEN function This function generates an XML value based on an XQuery Constructor.
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.
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.
See Index Consultant.
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.
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.
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 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.
INTERSECT and EXCEPT statements are now supported These statements compute the intersection and difference between two or more result sets. They complement the UNION statement.
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.
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.
See Using ROLLUP.
The LIST function can include ordered lists The LIST function has been extended to provide sorted lists of items.
Additional aggregate functions Functions have been added to compute sample-based and population-based standard deviations and variances.
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.
See CREATE INDEX statement and Create indexes.
ORDER BY clause allowed in all contexts In previous releases, many SELECT statements in view definitions, in subqueries, or in UNION statements 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.
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.
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.
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.
EXECUTE IMMEDIATE supports queries that return result sets This new feature allows more dynamic construction of statements inside stored procedures.
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.
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.
Remote Data Access now handles UUID/GUID columns Remote Data Access can now manage Microsoft SQL Server unique identifier columns.
See Data type conversions: Microsoft SQL Server and UNIQUEIDENTIFIERSTR data type.
Remote Data Access now names remote connections Remote Data Access connections made via ODBC are now given names, so that they can be dropped.
New function returns data type of an expression The EXPRTYPE function returns the data type of an expression.
EXIT statement enhanced The Interactive SQL EXIT statement can now set an exit code for Interactive SQL.
OUTPUT statement accepts ASIS keyword When ASIS is specified, values are written to the file without any escaping.
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.
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 contact |
Full 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.
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.
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.
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.
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.
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.
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.
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
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.
See Connecting using an LDAP server or LDAP protocol option [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.
See -tl server option and LivenessTimeout connection parameter [LTO].
Request log filtering, host variable support Output to the request 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.
See sa_server_option system procedure, Improving database 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.
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.
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.
See sa_disable_auditing_type system procedure or sa_enable_auditing_type system procedure.
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.
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 computers are connected to a server, and each client computer has two connections, select property( 'LicensesInUse' ) is '3'.
For more information, see Database server 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.
The network server supports the LocalOnly protocol option [LOCAL] You can use the LocalOnly protocol option [LOCAL] with the server. Running a server with the LocalOnly protocol option set to YES allows the network server to run as a personal server without experiencing connection or CPU limits.
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.
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.
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.
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.
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.
Database password case sensitivity is independent of database case sensitivity The CREATE DATABASE statement, Initialization [dbinit] utility, and Create Database wizard allow you to specify whether passwords are to be case sensitive or case insensitive. The case sensitivity setting for passwords is independent of the database case sensitivity setting used for string comparisons. The new CaseSensitivePasswords database property allows you to check the password case sensitivity setting for a database.
See CREATE DATABASE statement and Initialization utility (dbinit).
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.
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.
Property QueryCachedPlans shows how many execution 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.
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.
See 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.
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.
The option APPEND { ON | OFF } has been added to the UNLOAD statement A new APPEND option allows unloaded data to be appended to the end of the specified file.
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.
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.
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 or later.
New extended property function added The new DB_EXTENDED_PROPERTY function is similar to DB_PROPERTY except that it also allows an optional property-specific string parameter to be specified.
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.
See Database properties.
Server's quiet mode enhanced The server's quiet mode and error logging options 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 properties windows, window 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 Table Editor is no longer a separate window. Now you edit tables directly in the right pane of Sybase Central.
You can edit stored procedures, functions, triggers, and events in the right pane of Sybase Central or in a separate Code Editor window if you want to have multiple windows open at one time.
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 database server messages window) can now be viewed directly in the Sybase Central main window. To view this information, in Sybase Central choose File » Server Messages and Executed SQL. The Server Messages and Executed SQL pane appears at the bottom of the main Sybase Central window.
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.
Debugger changes The debugger that lets you debug both stored procedures and Java classes has been integrated into Sybase Central. The user interface has been redesigned.
Sybase Central, Interactive SQL, and the Adaptive Server Anywhere Console utility include an option to automatically check for software updates Sybase Central, Interactive SQL, and the Adaptive Server Anywhere Console utility can be configured to automatically check for software updates. This option can be set from the Options window in Interactive SQL and the Adaptive Server Anywhere 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.
Enhancements made to the Adaptive Server Anywhere Console utility There have been a number of enhancements to the Adaptive Server Anywhere 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.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.
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 window.
Printing from Interactive SQL You can print the contents of the SQL Statements pane and of the graphical plan in Interactive SQL.
Graphical plan enhancements The graphical 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 Language Selection utility (dblang), and the Adaptive Server Anywhere Console utility (dbconsole) now accept parameters contained within a file using the @file syntax. The file name can occur at any point in the configuration 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.
See @data 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 window.
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.
Interactive SQL Command History window enhancements You can now copy and delete commands from the Command History window in Interactive SQL, as well as select multiple commands in the window. The command history now persists between Interactive SQL sessions.
See Printing SQL statements, execution plans, and result sets.
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.
| Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |