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