What's New in SQL Anywhere Studio
What's New in Version 7.0.0
New features in version 7.0.0
This section introduces the new features in Adaptive Server Anywhere version 7.0. It provides an exhaustive listing of major and minor new features, with cross references to locations where details of each feature appear in the manuals.
If you have the printed version of this book, and if you do not have the complete SQL Anywhere Studio documentation set, you should look in the online documentation for the detailed description of each feature. To locate the information in the online documentation, go to the index and enter the specified title.
Task scheduling and event handling in the database You can now add scheduled operations to the database. This can be useful for automatic backups, periodic reports to fill summary tables, and other tasks.
The database server can also be instructed to execute event handlers when certain events occur, including disk space thresholds on the drives holding the database file or the transaction log file, or failed connection attempts.
Event handlers can be created and altered using Sybase Central, and can be debugged using the Adaptive Server Anywhere debugger.
For more information see Automating Tasks Using Schedules and Events, and CREATE EVENT statement.
Updated Sybase Central Sybase Central has been rewritten and contains significant new features. In particular, Sybase Central is now available from any supported platform, and not just Windows operating systems.
Updated Interactive SQL The Interactive SQL [dbisql] utility has been enhanced and is now available as a windowed-application from any supported platform.
New validation features Additional validation of databases is provided by the new VALIDATE INDEX statement and by enhancements to the VALIDATE TABLE statement. This statement is called both by the Validation [dbvalid] utility, and by the sa_validate system procedure. The enhancements are available through all these routes.
For more information, see VALIDATE INDEX statement, and VALIDATE TABLE statement.
Lock troubleshooting A new system procedure, sa_locks, provides information on locks in the database. If lock issues are identified, information on the connection processes involved can be found using the AppInfo connection property.
For more information, see sa_locks system procedure, and AppInfo connection parameter [APP].
Unloading result sets The new UNLOAD SQL statement allows query result sets to be unloaded into a comma-delimited text file.
For more information, see UNLOAD statement.
Validate backup copies of databases If you backup a database using the WAIT BEFORE START clause, the backup copy is created in such a fashion that it can be started in read-only mode and validated.
For more information, see BACKUP statement.
Default global autoincrement This feature provides an easy way to generate integer keys which are unique across all databases in a SQL Remote replication environment.
Distributed transactions and three-tiered computing Distributed transactions include operations on more than one server in a single transaction. A transaction server controls the commit and rollback behavior of distributed transactions.
In this release, Adaptive Server Anywhere can participate in distributed transactions coordinated by the Microsoft Distributed Transaction Coordinator (DTC). Products such as Sybase Enterprise Application Server and Microsoft Transaction Server can use DTC for transaction coordination, so DTC support enables Adaptive Server Anywhere to participate in three-tiered computing with these products.
For more information, see Three-tier Computing and Distributed Transactions.
OLE DB provider OLE DB is a data access model from Microsoft. It uses the Component Object Model (COM) interfaces and, unlike ODBC, OLE DB does not assume that the data source uses a SQL query processor. While it has been possible to access Adaptive Server Anywhere via OLE DB using an OLE DB/ODBC bridge provided by Microsoft, this release of Adaptive Server Anywhere includes an OLE DB provider. This provider brings several benefits:
OLE DB is the principal data access option for the forthcoming version of Windows CE.
Some features, such as updating through a cursor, are not available using the OLE DB/ODBC bridge.
If you use the Adaptive Server Anywhere OLE DB provider, ODBC is not required in your deployment.
For more information, see The OLE DB and ADO Programming Interfaces.
Java connectivity improvements If you use jConnect to connect to Adaptive Server Anywhere from a Java application, you can now take advantage of many of the features previously available only to ODBC and Embedded SQL applications, such as autostarting of database servers, and detailed control over network communications using communications parameters.
TCP/IP connectivity Establishing a client/server connection over TCP/IP is now simpler. Clients no longer need to specify the port number when attempting to connect, even if the server is running on a port other than the default port number (2638). If the default port number is in use when a database server is started, the server acquires an unused port number from the operating system.
If you are trying to connect through a firewall (using UseUDP=NO), and if the database server is not running on port 2638, you must still specify a port number. For more information on this scenario, see Connecting across a firewall.
The Server Location [dblocate] utility displays all Adaptive Server Anywhere database servers running TCP/IP on a network. For more information, see The Server Location utility.
SPX connectivity You can use the SPX protocol for connecting to databases. This feature is particularly useful in Novell NetWare environments with IPX/SPX as the primary network protocol. SPX is recommended over IPX.
For more information on SPX at the client, see CommLinks connection parameter [LINKS]. For information on SPX on the server, see -x server option. For network communication parameters that you can use with SPX, see Network communications parameters.
Dynamic cache sizing On Windows NT and UNIX, the size of the database server cache increases and decreases depending on the load on the database server and the other demands on system memory. This feature removes the need for choosing an explicit cache size under in many circumstances, and can also boost performance. On Windows 95/98, a less comprehensive cache resizing is implemented.
For more information, see Using the cache to improve performance.
Indexing enhancements Additional flexibility has been added to control the amount of information stored in indexes (the hash size) to improve index selectivity. Also, the architecture of primary and foreign key indexes has been altered.
For indexes on multiple columns, or for indexes on columns in which the first set of characters or digits are similar across many rows, control over hash size provides a way of increasing the selectivity of indexes, and so improving performance.
For more information, see Using indexes, CREATE INDEX statement, and CREATE TABLE statement.
For information on how to find the number of levels in an index, see sa_index_levels system procedure.
In previous releases, primary and foreign keys have had a single index automatically associated with them, which describes all primary key values and all the related foreign key entries. In some situations, this architecture lead to poor performance. The new index organization separates these indexes, which leads to improved performance in some situations.
For more information on key indexes, see Using keys to improve query performance.
Your database must be unloaded and reloaded to take advantage of variable hash size indexes, and separate key indexes. Running the Upgrade [dbupgrad] utility is not sufficient.
Separate storage for string extensions The physical storage of values longer than 255 characters has been reorganized. The pages allocated for a table are now divided into two disjoint sets. The first set contain only rows. Where a column value in a row contains a string longer than 255 characters, only a prefix of the string (up to 255 characters) and a reference to a string extension are stored in the row. For strings longer than 255 characters, the string extensions are allocated in the second set of table pages. This change improves performance on queries requiring scans of tables storing long values because a sequential scan of a table only needs to traverse the pages in the first set.
Your database must be unloaded and reloaded to take advantage of this feature.
New database page-sizes In addition to 1K, 2K, and 4K page sizes, you can now create databases with page sizes of 8K, 16K or 32K.
Large page sizes can improve performance in some cases, particularly for large databases. However, there are additional memory requirements with large page sizes, and so they should only be used after investigation of the costs and benefits.
For more information, see Creating a database using the dbinit command-line utility, and CREATE DATABASE statement.
For information on the number of indexes per table and how it depends on page size, see Size and number limitations.
Optimizer tuning You can use the OPTIMIZATION_GOAL option to instruct the optimizer to optimize for the time it takes to return the first row of a query, or the overall time it takes to return all rows. The default is to optimize for the first row. If you are using applications such as PowerBuilder DataWindow applications, which require a complete result set, you may wish to change this option setting.
For more information, see OPTIMIZATION_GOAL option [database].
Optimizer enhancements Further enhancements to the optimizer have been implemented to assist with performance of queries that use internal temporary tables and that use primary and foreign key indexes. These enhancements require no user action.
Larger numbers of users and other identifiers Many identifiers in the system tables identifying database objects have been changed from SMALLINT to UNSIGNED INTEGER. This change increases the number of objects that can be held in a database without violating an absolute limit.
Inserting and exporting images and documents Two new system external functions allow you to read and write the contents of files. These functions allow direct inserting of images, documents, and so on into tables from environments such as Interactive SQL.
For more information, see Inserting documents and images, xp_read_file system procedure, and xp_write_file system procedure.
New interface for external functions Stored procedures and user-defined functions that reference external libraries now use a new interface. The new interface provides a wider range of operating systems (including UNIX), a wider range of data types, removes the restriction that returned data fit into 255 bytes, and supports NULL as a valid value for arguments. The older interface is still supported, but should not be used for new development work.
For more information, see Creating procedures and functions with external calls.
START DATABASE, STOP DATABASE and STOP ENGINE statements These statements were previously available only from Interactive SQL. They are now available from all applications.
For more information, see START DATABASE statement, STOP DATABASE statement, and STOP ENGINE statement.
FIRST and TOP clause in updates and deletes The FIRST and TOP clauses can be used to update or delete only the first one or more of any set of rows satisfying a WHERE clause.
For more information, see DELETE statement, and UPDATE statement.
Explicit table locking The LOCK TABLE statement allows direct control over concurrency at a table level, independent of the current isolation level.
For more information, see LOCK TABLE statement.
Expressions in Transact-SQL outer joins The *= and =* operators in a WHERE clause provide a way of specifying outer joins for users who wish to use the Transact-SQL dialect. In previous releases, only column names could be used in such joins. Now as long as each side of the join operator refers to a single table, any expression can be used in these joins. For example, the following query is now possible:
select *
from customer, sales_order
where substr( customer.id, 1, 1 ) *=
substr( sales_order.cust_id, 1, 1)Cursors in stored procedures can reference variables In stored procedures and user-defined functions, you can declare a cursor on a variable using the following syntax:
DECLARE cursor-name CURSOR USING variable-name
where variable-name is a string variable containing the SELECT statement for the cursor.
For more information, see DECLARE CURSOR statement [ESQL] [SP].
Additional database and server properties The following properties have been added:
PageSize The database server uses a single page size from startup until it is closed down. This page size is the maximum page size database that can be mounted by the database server. You can now obtain this page size using the PageSize server-level property function:
select property( 'PageSize' )
AppInfo This function provides identification information for a client application. It is a connection property:
select connection_property( 'AppInfo' )
For more information, see AppInfo connection parameter [APP].
IsRuntimeServer This function returns YES if the database server is a limited desktop runtime personal database server. Otherwise, it returns NO.
Log truncation points Properties for replication-specific log offsets have been added. The properties LTMTrunc, RemoteTrunc, and SyncTrunc return the minimal confirmed log offset for the Replication Agent, SQL Remote, and MobiLink dbmlsync replication, respectively. These offsets are also known as truncation points because they indicate the point at which the transaction log can be truncated. The property CurrentRedoPos returns the current offset in the log file, where the next database operation is to be logged.
For a complete list of property functions and information on how to access them, see Database properties.
Referential integrity checks before commit A new system procedure (sa_check_commit) allows you to check for referential integrity conflicts before committing changes to a database.
For more information, see sa_check_commit system procedure.
SQL function enhancements The following functions have been added or enhanced.
REPLACE function This new function replaces all occurrences of a substring with another substring.
For more information, see REPLACE function [String].
LIST function enhancement The LIST function now accepts an optional second value, which is the delimiter string that separates the list items.
For more information, see LIST function [Aggregate].
Output redirection change The output redirection functionality in Interactive SQL has been extended to include three new Interactive SQL statements and an Export option in the File menu.
You can now use an OUTPUT TO statement to redirect content from the Results pane to a new file. You can add an APPEND clause to append the content to the end of an existing file, or you can add a VERBOSE clause to include the content of the Messages pane with the output.
In earlier versions, output redirection in Interactive SQL could only be done with the symbols >#, >>#, >&, and >>&. You can still use these symbols, but the new Interactive SQL statements allow for more precise output and code that is easier to read.
For more information, see Exporting query results in the ASA User's Guide.
Embedded SQL enhancements A new function, db_string_ping_server, has been introduced to test that a database server can be located with a specified current connection string.
For more information, see db_string_ping_server function.
New LOAD TABLE / UNLOAD TABLE format A new format has been added to the UNLOAD TABLE statement to allow data to be output in BCP format and to the LOAD TABLE statement to allow the import of Adaptive Server Enterprise generated BCP out files containing blobs.
For more information, see LOAD TABLE statement or UNLOAD TABLE statement.
Last default timestamp The new global variable @@dbts returns a TIMESTAMP value that represents the last value generated for a column using DEFAULT TIMESTAMP.
For more information, see Global variables.
Troubleshooting enhancements On starting the database server, you can log operations executed by the server to a file using the -zr option. You can use the sa_server_option procedure to control the same behavior while the server is running.
For more information, see sa_server_option system procedure, and -zr server option.
Archive backup on NetWare The archive backup format is now supported on NetWare. Archive backups to tape require NetWare 5.
For more information, see BACKUP statement.
Added filtering for dbtran The command version of the Log Translation [dbtran] utility allows further filtering of the output.
For more information, see Log translation utility options.
Faster table truncation The TRUNCATE TABLE statement is much faster for version 7.0 databases, for tables with foreign keys.
Suppressing event log messages If you run the database server as a Windows NT service, you can suppress event log messages using a registry entry.
For more information, see Suppressing Windows event log messages.