Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 » SQL Anywhere 11 - Changes and Upgrading » 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 later.

    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 Log Translation utility (dbtran).

    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 Reducing file fragmentation.

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

    For more information about index fragmentation, see Reducing index fragmentation and skew 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 messages 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 SQL Anywhere 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 Monitor 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

      For more information, see Connection 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 properties.

    The following server properties have been added in this release:

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

    For more information, see Performance Monitor 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 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 Service utility (dbsvc) for Windows.

  • 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.