What's New in SQL Anywhere Studio
  What's New in Version 8.0.0
    New features in version 8
      Adaptive Server Anywhere new features

Administration and troubleshooting

In addition to the administration enhancements added to Sybase Central, listed above, version 8 includes the following administration enhancements.

• Improve table performance without disrupting access    The REORGANIZE TABLE statement can be used to improve performance when a full rebuild of the database is not possible, due to the requirements for continuous access to the database. Use this statement to defragment rows in a table, or to compress indexes which have become sparse due to DELETEs. It can also reduce the total number of pages used to store the table and its indexes, as well as reduce the number of levels in an index tree.

  To reorganize tables based on a primary key, foreign key, or index, the database must be Adaptive Server Anywhere version 7 or above.

  For more information, see REORGANIZE TABLE statement.

• Fast database validation    A new type of validation check has been added that reduces the amount of time it takes to validate a database. This option is of particular interest to people who need to validate large databases with small cache sizes. Affected tools include the sa_validate system procedure, the Validation utility (dbvalid) and the VALIDATE TABLE statement.

  For more information, see Improving performance when validating databases.

  To use this feature on databases created before this release, you must upgrade the database file format by unloading and reloading the database.

• Backup does not need to wait for outstanding transactions to complete    If a backup instruction requires the transaction log to be truncated or renamed, uncommitted transactions are carried forward to the new transaction log. This means that the server no longer waits for outstanding transactions to be committed or rolled back before initiating a backup.

  For more information, see Translating a transaction log using the dbtran command-line utility and Backup internals.

  To use this feature on databases created before this release, you must upgrade the database file format by unloading and reloading the database.

• Obtaining fragmentation statistics    File, table, and index fragmentation can all decrease performance. In Adaptive Server Anywhere 8.0 when you start a database on Windows NT, the server automatically displays information about the number of file fragments in each dbspace.

  The new system procedures, sa_table_fragmentation and sa_index_density, allow database administrators to obtain information about the fragmentation in a database's tables and indexes.

  For more information about file fragmentation, see File fragmentation.

  For more information about table fragmentation, see Table fragmentation and sa_table_fragmentation system procedure.

  For more information about index fragmentation, see Index fragmentation and sa_index_density system procedure.

• Obtain the most recently prepared SQL statement for a connection    The database server -zl command line option turns on capturing of the most recently prepared SQL statement for each connection to databases on a server. You can also turn on this feature using the sa_server_option stored procedure with the remember_last_statement setting.

  When this feature is turned on, the LastStatement property function and the sa_conn_activity system procedure return the most recently prepared SQL statement for the current connection and all connections to databases on a server respectively.

  For more information, see -zl server option, sa_conn_activity system procedure, and sa_server_option system procedure.

• -cw command line option    This server option lets you use cache sizes up to 64 Gb on Windows 2000, Windows XP, and Windows Server 2003.

  For more information, see -cw server option.

• -qp option    This server option lets you suppress messages about performance in the database server window.

  For more information, see -qp server option.

• Improved debugging server log    The information logged in the connection debugger has been improved to give more context about the portion of the connection being attempted; to remove the CONN: prefix; to increase the number of TCP/IP messages.

• Databases can hold more procedures    The primary key values for the SYSPROCEDURE, SYSPROCPARM, SYSPROCPERM, and SYSTRIGGER system tables have been changed from SMALLINT to UNSIGNED INT. This change increases the number of procedures that a database can hold.

  For more information about the number of procedures a database can hold, see Size and number limitations.

  To use this feature, you must upgrade the database file format.

• Monitoring query performance    New system procedures and utilities have been included to measure query performance.

  For more information, see sa_get_request_profile system procedure, sa_get_request_times system procedure, and Monitoring query performance.

• New diagnostic properties    Properties allow you to obtain information about connections, databases, and the current database server. The following connection properties have been added in this release:

  • UtilCmdsPermitted property

  • TempTablePages property

  • LastStatement property

  • PacketSize property

  • Max_plans_cached property

  • QueryCachePages property

  • QueryLowMemoryStrategy property

  • Min_table_size_for_histogram property

    For more information, see Connection-level properties.

  The following database properties have been added in this release:

  • DBFileFragments property

  • LogFileFragments property

  • BlobArenas property

  • SeparateForeignKeys property

  • VariableHashSize property

  • TableBitMaps property

  • FreePageBitMaps property

  • SeparateCheckpointLog property

  • Histograms property

  • LargeProcedureIDs property

  • PreserveSource property

  • TransactionsSpanLogs property

  • Capabilities property

  • TempTablePages property

  • CompressedBTrees property

  • ProcedurePages property

  • QueryCachePages property

  • QueryLowMemoryStrategy property

    For more information, see Database-level properties.

  The following server properties have been added in this release:

  • MachineName property

  • IsJavaAvailable property

  • PlatformVer property

    For more information, see Server-level properties.

• Additional performance monitor statistics    Several performance monitor statistics have been added for this release.

  For more information, see Database performance statistics.

• Login procedure allows connections to be disallowed    The LOGIN_PROCEDURE option allows a stored procedure to be called for each new connection. This procedure can now be used to disallow database connections.

  For more information, see LOGIN_PROCEDURE option [database].

• dbsvc enhancements    The dbsvc command line utility for managing Windows services has been extended to list service name used to start and stop the service with the system net start and net stop commands, and to handle dependencies on other services and groups.

  For more information, see The Service Creation utility.

• Source format preserved for stored procedures    The source format, including spaces and line breaks, is now stored in the database as a comment. This comment is used for procedure profiling.