|
This section introduces the new features in Adaptive Server Anywhere version 9.0.1. It provides an exhaustive listing of major and minor new features, with cross references to locations where each feature is discussed in detail.
OLAP query extensions A set of online analytical processing (OLAP) features enable more detailed analysis of the data in your database. The enhancements include the ability to add subtotal rows into result sets in a flexible way using CUBE and using GROUPING SETS, as well as window functions that provide rolling averages and other advanced features.
Support for these OLAP features is built in to the Query Editor, which lets you build queries that use ROLLUP, CUBE, and GROUPING SETS operations. See:New statistical functions Several statistical functions have been added.
See:New string functions The following string functions have been added:
LOAD TABLE enhancements The LOAD TABLE statement now has a clause that allows you to limit the statistics that are created, allowing faster table loading. It also has a SKIP option that allows you to ignore the first few lines of a file.
See LOAD TABLE statement.SELECT ... INTO base-table This new SELECT syntax creates a base table and fills that table with data from a query.
See SELECT statement.Extended support for variables in SQL statements Several statements have been made more flexible by permitting variables as well as constants in some locations. This is especially useful in stored procedures and batches, where variables can be declared and used. It provides functionality previously only available, in more cumbersome form, in EXECUTE IMMEDIATE.
The following statements have this extended support for variables:The TOP clause of the SELECT statement can now reference integer variables as well as constants. See SELECT statement.
BACKUP statement backup-directory and archive-root. See BACKUP statement.
RESTORE statement filename, archive-root, and new dbspace-name. See RESTORE DATABASE statement.
LOAD TABLE statement filename. See LOAD TABLE statement.
UNLOAD statement and UNLOAD TABLE statement filename. See UNLOAD statement and UNLOAD TABLE statement.
SET statement enhancement The SET statement now accepts the option ansi_nulls (equivalent to the ansinull option) for compatibility with Microsoft SQL Server.
For information, see SET statement [T-SQL].ALTER TABLE statement enhancement ALTER TABLE can now add a NOT NULL column with a default value to a non-empty table. This feature provides increased flexibility when modifying existing tables.
See ALTER TABLE statement.ALTER VIEW statement enhancements The ALTER VIEW statement now supports a RECOMPILE clause that allows you to re-create view definitions when the columns in the underlying tables are modified.
See ALTER VIEW statement.MESSAGE statement enhancements A FOR CONNECTION clause has been added to the MESSAGE statement.
Also, a DEBUG ONLY clause has been added to the MESSAGE statement. When the debug_messages option is set to ON, debugging messages appear for all stored procedures and triggers that contain a MESSAGE statement that includes the DEBUG ONLY clause. See MESSAGE statement and debug_messages option [database].Database page checksums Database page checksums are used to detect whether a database page has been modified on disk. When a database is created with checksums enabled, a checksum is calculated for each page before it is written to disk. When a page is read from disk, its checksum is calculated again and compared to the stored checksum. If the values are different, the page has been modified or otherwise corrupted while on disk. Existing databases must be unloaded and reloaded into a database with checksums enabled to use this feature. You can check whether checksums are enabled for a database using the Checksum property.
For more information about creating databases with checksums, see CREATE DATABASE statement, Initialization utility (dbinit), and Database-level properties. Checksums can also be used to validate a database. See VALIDATE statement, Validation utility (dbvalid), and sa_validate system procedure.Many enhancements have been made to provide better performance for a wide range of tasks, including complex queries. Some of these enhancements are purely internal. Others are listed here:
Parallel index scans On volumes with multiple disk spindles, such as hardware or software RAID arrays, the query optimizer can now scan tables using an index in parallel.
See Parallel index scans.Clustered Hash Group By algorithm For better performance, the Adaptive Server Anywhere query optimizer can use a new algorithm that is particularly useful for certain classes of GROUP BY queries where the HAVING clause returns a small proportion of rows.
See Clustered Hash Group By algorithm and optimization_workload option [database].Database server cache warming Three new database server command line options have been added to support cache warming. Cache warming is designed to help reduce the execution times of the initial queries executed against a database by pre-loading the database server's cache with database pages that were referenced the last time the database was started. Using cache warming can improve performance when the same queries are executed against the database each time it is started.
See -cc server option, -cr server option, -cv server option, and Using cache warming.Optimizer hints WITH (XLOCK) is a new table hint feature in the FROM clause. XLOCK indicates that rows processed by the statement from the hinted table are to be locked exclusively. The affected rows remain locked until the end of the transaction. It works at all isolation levels.
The WITH INDEX hint forces the optimizer to use a specified index during query optimization. This is an advanced feature that may lead to poor performance if used incorrectly, and so should be used by experienced users only. See FROM clause and Use indexes for frequently-searched columns.Increased default stack size for internal execution threads on NetWare The default stack size for internal execution threads on NetWare has been increased to 128 KB.
See -gss server option.Perl interface The Perl new DBD::ASAny driver for the Perl DBI module allows you to access and modify Adaptive Server Anywhere databases from Perl scripts.
InstallShield projects SQL Anywhere studio now includes InstallShield Merge Module Projects and Object Projects. These projects allow InstallShield to generate Merge Modules and Objects with which you can redeploy the software currently installed on your computer. Previous versions of SQL Anywhere included the Merge Modules and Objects. These allowed you to redeploy the original software, but provided no convenient means of deploying after you had applied an EBF.
BACKUP enhancements The BACKUP statement now includes an ON EXISTING ERROR clause for image backups. When this clause is specified, an error occurs if any of the files to be created during the backup already exist.
The archive backup form of the BACKUP statement has been extended to support options previously available only with image backups. See BACKUP statement. The Backup utility can now create a backup on the server computer. Previously, the utility could only create backups on the client computer. See Backup utility (dbbackup).Unload utility (dbunload) enhancements The Unload utility now automatically handles view dependencies when unloading databases. The -j option that was used in previous versions of the software to output view definitions multiple times to the reload.sql file has been deprecated. Now, the Unload utility automatically handles unloading view definitions that depend on other views.
The Unload utility also allows you to change the database page size when unloading into a new database. See Unload utility (dbunload).Server Enumeration utility (dblocate) enhancements The Server Enumeration utility (dblocate) now allows you to supply a host name or IP address to limit the search for database servers to a specific computer. As well, it supports a -n option that specifies that IP addresses are not to be resolved into computer names, which results in better performance.
See Server Enumeration utility (dblocate).Adaptive Server Anywhere Console utility supports integrated logins When you connect to the Adaptive Server Anywhere Console (dbconsole) utility on Windows NT/2000/XP, the Connect dialog allows you to use an integrated login to connect to the database.
See Connect dialog: Identification tab (SQL Anywhere).The request log file size can be changed without restarting the database server On starting the database server, you can specify the size of the request log file with the -zs server option. You can use the sa_server_option system procedure to change the size of the request log file without restarting the database server.
See sa_server_option system procedure.Additional information added for profiling system triggers The sa_procedure_profile system procedure and sa_procedure_profile_summary system procedure now return extra information about system triggers when procedure profiling is turned on in the database.
See sa_procedure_profile system procedure and sa_procedure_profile_summary system procedure.New system table A new system table has been added that maintains information about the different versions of the software and platforms a database has been started with.
See ISYSHISTORY system table.New collations There are two new collations available: one to support Lithuanian (1257LIT, ANSI Code Page 1257) and one to support Turkish (1254TRKALT). This Turkish collation considers I-dot and I-no-dot equal.
See Supported and alternate collations and Alternative Turkish collation 1254TRKALT.dedicated_task option When specified, a request handling task is dedicated to handling requests from a single connection. This pre-established connection allows you to gather information about the state of the database server if it becomes otherwise unresponsive.
See dedicated_task option [database].Interactive SQL allows you to specify the encoding used to read and write files The Interactive SQL READ, INPUT, and OUTPUT statements now support an optional encoding clause that allows you to specify the character encoding that is used to read or write the file. The default_isql_encoding option has been added to allow you to specify the character encoding that is used for subsequent READ, INPUT, and OUTPUT statements.
See default_isql_encoding option [Interactive SQL], READ statement [Interactive SQL], INPUT statement [Interactive SQL], and OUTPUT statement [Interactive SQL]. You can also specify the character encoding used to read or write the file when using the Interactive SQL import and export wizards. See Importing and Exporting Data and Export dialog.Interactive SQL supports integrated logins When you connect to Interactive SQL on Windows NT/2000/XP, the Connect dialog allows you to use an integrated login to connect to the database.
See Connect dialog: Identification tab (SQL Anywhere).Interactive SQL allows you to configure the font used for displaying result sets You can choose the font, font style, and point size for data that appears in the Results pane in Interactive SQL.
For information about configuring the Results pane in Interactive SQL, see Options dialog: Results tab.Interactive SQL allows you to specify the initial folder used for file browsing When browsing for files in Interactive SQL, you can specify whether Interactive SQL uses the current directory (as defined by the operating system) for the initial directory, or the last folder where a file was opened.
See Options dialog: General tab.Sybase Central allows you to configure the font used for displaying result sets You can choose the font, font style, and point size for data that appears on the Data tab in Sybase Central when a table is selected.
For information about configuring the Data tab in Sybase Central, see Plug-in Preferences dialog: Table Data tab.Create Remote Server wizard now supports creating external login for current user The Create Remote Server wizard now allows you to create an external login for the current user so that you do not have to create an external login before you create the remote server.
See Creating remote servers using Sybase Central.Sybase Central supports integrated logins When you connect to Sybase Central on Windows NT/2000/XP, the Connect dialog allows you to use an integrated login to connect to the database.
See Connect dialog: Identification tab (SQL Anywhere).Columns can be sorted using the View menu in Sybase Central The Sybase Central View menu has a Sort item that allows you to sort columns in the right pane as an alternative to clicking the column headings in the right pane.
Foreign key settings can be modified from the foreign key property sheet You can change foreign key settings in Sybase Central from the Foreign Key property sheet.
See Change Settings dialog.Proxy Table wizard now displays primary key column information Previously, when creating a proxy table using the Proxy Table wizard, there was no way to determine which columns belonged to the remote table's primary key. Now, the columns in the primary key are identified in the wizard.
Utility wizards can be canceled The Upgrade Database wizard, Backup Database wizard, Restore Database wizard, Validate Database wizard, Compress Database wizard, Uncompress Database wizard, and the Create Backup Images wizard can be canceled. They also include a messages dialog that displays status information about whether the operation has succeeded or failed.
Sybase Central supports account names of the form domain\user when creating and editing services The Create Service wizard and Service property sheet now allow you to enter account names of the form domain\user when creating and editing services. You can enter the account name in the Other Account field on the Account tab of the Service property sheet or in the Create Service wizard.
Database server uses asynchronous I/O on Linux platforms When running the database server on Linux, the database server uses asynchronous I/O by default when possible. The -ua database server option allows you to turn off the use of asynchronous I/O.
See -ua server option.openxml supports equality predicates The openxml function allows you to use equality predicates in the XPath expression. This feature allows you to locate nodes within the XML document using attribute values.
See openxml system procedure.TransactionStartTime connection property This property returns the time the database was first modified after a COMMIT or ROLLBACK.
See Connection-level properties.UserAppInfo property This property returns the portion of a connection string specified with the AppInfo connection parameter.
See Connection-level properties.ConsoleLogFile server property This property returns the name of the file where messages from the Server Messages window are logged when the -o server option is specified.
See Server-level properties.DriveType database property for UNIX platforms The DriveType database property has been extended to UNIX platforms.
See Database-level properties.Connection IDs start at 1 and are incremented for each new connection to the database server When the database server is started, each connection to the server is assigned a connection ID, starting with 1, and the connection number is incremented with each new connection to the server. The connection IDs are logged in the -z server output and the LogFile connection parameter output. They are also used by the CONNECTION_PROPERTY, NEXT_CONNECTION, NEXT_DATABASE, and DROP CONNECTION functions, and by request logging.
See CONNECTION_PROPERTY function [System], NEXT_CONNECTION function [System], NEXT_DATABASE function [System], and Request logging.Improved cache management on NetWare and UNIX When the cache size specified with -c is greater than the amount of available memory on UNIX or NetWare, the database server now calculates the maximum cache size based on available memory.
For more information about how the database server calculates the maximum cache size in these circumstances, see -c server option.odbc_distinguish_char_and_varchar option The odbc_distinguish_char_and_varchar option controls how the Adaptive Server Anywhere ODBC driver describes CHAR columns.
See odbc_distinguish_char_and_varchar option [database].