This section introduces the new features in Adaptive Server Anywhere version 9.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.
UNIQUEIDENTIFIER native data type The UNIQUEIDENTIFIER data type is now a native data type rather than a domain defined on BINARY(16). As a result, Adaptive Server Anywhere automatically carries out type conversions as needed, so that the STRTOUUID and UUIDTOSTR conversion functions are not needed to handle UNIQUEIDENTIFIER values.To use the UNIQUEIDENTIFIER data type in databases created before this release, you must upgrade the database file format by unloading and reloading the database. See UNIQUEIDENTIFIER data type.
Conflict function for RESOLVE UPDATE triggers The CONFLICT function can be used in conflict resolution triggers to determine if a particular column is a source of conflict for an UPDATE being performed on a SQL Remote consolidated database.See CONFLICT function [Miscellaneous].
Procedure profiling enhancements Profiling information can now be filtered per user and per connection using the sa_server_option stored procedure.See Enabling profiling using sa_server_option and sa_server_option system procedure.
Remote servers can be tested before they are created or modified The Create Remote Server wizard in Sybase Central has a Test Connection button that allows you to test whether the connection information supplied in the remote server definition allows you to connect successfully before the remote server is created.The Remote Server property sheet in Sybase Central also has a Test Connection button that allows you to test whether you can successfully connect to a remote server if its properties are changed. See Creating remote servers using Sybase Central.
INPUT and OUTPUT statements accept the ESCAPES clause The ESCAPES clause allows you to specify that characters are recognized and interpreted as special characters by the database server.See INPUT statement [Interactive SQL] and OUTPUT statement [Interactive SQL].
WAITFOR can wake up when it receives a message from another connection The WAITFOR statement can now wake up when it receives a message from another connection using the MESSAGE statement.See WAITFOR statement.
Derived tables appear in query plans Derived tables now appear as nodes in query execution plans.
ALTER DOMAIN statement The ALTER DOMAIN statement allows you to rename user-defined domains and data types.See ALTER DOMAIN statement.
NO RESULT SET clause for procedures Declaring a stored procedure NO RESULT SET can be used when external environments need to know that the stored procedure does not return a result set.See CREATE PROCEDURE statement.
Column statistics updated during index creation The CREATE INDEX statement now has the side effect that column statistics are updated for the indexed columns.See CREATE INDEX statement.
PHP module The SQL Anywhere PHP module allows access to Adaptive Server Anywhere databases from the PHP scripting language.See SQL Anywhere PHP API.
Web service clients In addition to acting as a web-service provider, Adaptive Server Anywhere can now act as a web-service client, making it possible to create stored procedures and stored functions that access Adaptive Server Anywhere web services, as well as standard web services available over the internet.See SQL Anywhere Web Services.
Multiple web service formats supported The format of the WSDL file provided by a DISH service, as well as that of data payloads returned of part of SOAP responses, can now be selected to best suit the needs of the client applications. You can now choose between DNET for Microsoft .NET, CONCRETE for clients that automatically generate interfaces, and a general-purpose XML format.See Creating SOAP and DISH web services.
odbc_describe_binary_as_varbinary option This option allows you to choose whether you want all BINARY and VARBINARY columns to be described to your application as BINARY or VARBINARY.See odbc_describe_binary_as_varbinary [database].
New prefetch option value The prefetch option now has an additional value of Always. This value means that cursor results are prefetched even for SENSITIVE cursor types and cursors that involve a proxy table.See prefetch option [database].
db_locate_servers_ex function This function provides programmatic access to the information displayed by the dblocate -n option, listing all the Adaptive Server Anywhere database servers on a specific host.See db_locate_servers_ex function.
SNMP Agent Adaptive Server Anywhere can now be monitored from Simple Network Management Protocol (SNMP) applications.See The SQL Anywhere SNMP Extension Agent.
Deadlock reporting You can now obtain information about connections involved in deadlock using a new database option, log_deadlocks, and a new system stored procedure, sa_report_deadlocks. When you turn on the log_deadlocks option, the database server records information about deadlocks in an internal buffer. You can obtain deadlock information from this internal buffer by calling sa_report_deadlocks.See Determining who is blocked.
New collations The following collations have been added in this release:
1252SWEFIN has been added to support Swedish and Finnish. On Swedish and Finnish systems, the database server will choose 1252SWEFIN as the default collation for a new database if no collation is specified..
1255HEB has been added to support Hebrew. On Hebrew Windows systems, the database server will choose 1255HEB as the default collation for a new database if no collation is specified.
1256ARA has been added to support Arabic. On Arabic Windows systems, the database server will choose 1256ARA as the default collation for a new database if no collation is specified.
950ZHO_HK and 950ZHO_TW have been added to support Chinese. 950ZHO_HK provides support for the Windows Traditional Chinese character set cp950 plus the Hong Kong Supplementary Character Set (HKSCS). The 950ZHO_TW collation provides support for the Windows Traditional Chinese character set cp950, but doesn't support HKSCS. Ordering is based on a byte-by-byte ordering of the Traditional Chinese characters. These collations supercede the deprecated 950TWN collation.
1252SPA has been added to support Spanish. On Spanish Windows systems, the database server will choose 1252SPA as the default collation for a new database if a collation is not specified.
874THAIBIN has been added to support Thai. This is the recommended collation for Thai on both Windows and UNIX systems.
New Service utility (dbsvc) options The Service utility (dbsvc) supports the following new options:
-cm option This option displays the command used to create the specified service. This may be useful for deploying services, or for restoring them to their original state.
-sd option This option allows you to provide a description of the service, which appears in the Windows Service Manager.
-sn option This option allows you to provide a name for the service, which appears in the Windows Service Manager.
New Data Source (dbdsn) utility options The Data Source utility (dbdsn) supports the following new options:
-cm option This option displays the command used to create the specified data source. This may be useful for deploying data sources, or for restoring them to their original state.
Driver connection parameter You can use the Driver connection parameter to specify a driver for an ODBC data source when creating data sources using the Data Source utility (dbdsn) on Windows. On UNIX, if you do not specify the Driver connection parameter, the Data Source utility automatically adds a Driver entry with the full path of the Adaptive Server Anywhere ODBC driver based on the setting of the ASANY9 environment variable.
Disk full callback support The -fc database server option allows you to specify a DLL containing a callback function that can be used to notify users, and possibly take corrective action, when a file system full condition is encountered.See -fc server option.
Validate Database wizard enhancements When you validate a database using the Validate Database wizard in Sybase Central, the wizard indicates the current table being validated, as well as the overall progress of the validation operation. In addition, for databases with checksums enabled, you can validate both tables and checksums at the same time.See Validating a database.
Unloading table data in Sybase Central You can now unload data from one or more tables in Sybase Central in one step using the Unload Data dialog.See Using the Unload Data dialog.
New columns added to sa_index_density and sa_index_levels Three new columns have been added to the result sets returned by the sa_index_density and sa_index_levels stored procedures: TableId, IndexId, and IndexType. If you want to revert to the old behavior of these stored procedures, you can drop the stored procedure and recreate it with the columns that were included in the result set in previous versions of the software.See sa_index_density system procedure and sa_index_levels system procedure.
HISTORY option for BACKUP and RESTORE DATABASE statements The HISTORY option allows you to control whether BACKUP and RESTORE DATABASE operations are recorded in the backup.syb file.See BACKUP statement and RESUME statement.
Support for integrated logins using Windows user groups In addition to creating integrated logins for individual users on Windows NT/2000/XP, you can now create integrated login mappings to user groups on Windows NT/2000/XP. It is recommended that you upgrade your database before using this feature.See Creating integrated logins for Windows user groups.
Managing the size of the request log The -zn database server option allows you to specify how many request log files should be retained.See -zn server option.
Free pages at the end of the transaction log are removed when the file is renamed by a backup Transaction log files are grown in fixed-size increments for better performance. When the transaction log is renamed as part of a backup, the free pages at the end of the log are removed, which helps free up disk space.
Remote server connections can now be explicitly closed In previous releases, connections from Adaptive Server Anywhere to remote servers were disconnected only when a user disconnected from Adaptive Server Anywhere. You can now explicitly disconnect Adaptive Server Anywhere from a remote server using the new CONNECTION CLOSE clause of the ALTER SERVER statement.See ALTER SERVER statement.
Initialization files can be obfuscated with dbfhide The File Hiding utility (dbfhide) can now be used to obfuscate the contents of .ini files used by Adaptive Server Anywhere and its utilities.See File Hiding utility (dbfhide).
FIPS-certified security On all supported Windows platforms except Windows CE, you can now use secure communication with FIPS 140-2 certified software from Certicom.See Starting the database server with transport-layer security. Strong database encryption using FIPS140-2 certified software from Certicom is also available on supported 32-bit Windows platforms. See Encrypting a database.
New connection properties The following connection properties have been added:
Proper formatting Event Viewer messages When deploying Adaptive Server Anywhere databases, you should set a registry entry that controls the formatting of messages in the event viewer.See Deploying database servers.
log_deadlocks option This option allows you to control whether the database server logs information about deadlocks in an internal buffer. This option can be used with the sa_report_deadlocks procedure to obtain information about deadlock.See log_deadlocks option [database].
rollback_on_deadlock option This option allows you to control whether a transaction is automatically rolled back if it encounters a deadlock.See rollback_on_deadlock [database].
temp_space_limit_check option This option allows you to control what happens when a connection requests more than its quota of temporary file space.See temp_space_limit_check option [database].
New system stored procedures Several new system stored procedures have been added:
sa_rowgenerator procedure The sa_rowgenerator system procedure is provided as an alternative to the RowGenerator table for returning a result set with rows between a specified start and end value.You can use this procedure for such tasks as generating a result set with rows for every value in a range or generating test data for a known number of rows in a result set. See sa_rowgenerator system procedure.
sa_send_udp stored procedure This procedure sends a UDP packet to the specified address and can be used with MobiLink server-initiated synchronization to wake up the Listener utility (dblsn.exe).See sa_send_udp system procedure.
sa_verify_password stored procedure This procedure is used by the sp_password stored procedure to verify the current user's password.See sa_verify_password system procedure. See sa_verify_password system procedure.
Maximum cache size on Windows CE In previous releases of SQL Anywhere Studio, the maximum cache size on Windows CE was 32 MB. This limit has been removed and the cache size is now limited by the amount of available memory on the device.
New database server options for UNIX The following database server options have been added for UNIX:
-uc starts the database server in console mode on UNIX.See -uc server option.
-ui attempts to display the Server Startup Options dialog and Server Messages window when you start a database server on Linux and Solaris with X window support. If the server cannot find a usable display, the server starts in console mode.See -ui server option.
-ux displays the Server Startup Options dialog and Server Messages window when you start a database server on Linux and Solaris with X window support.See -ux server option.