New Features and Upgrading Guide
   CHAPTER 3. New Features in Adaptive Server Anywhere 6.0     

Database administration enhancements

This section describes enhancements made to the database administration utilities and database options.

  Database file manipulation statements

A new set of statements allows you to carry out database administration tasks that alter database files using SQL statements. This allows command scripts using these statements to be constructed, for reproducible database administration actions. It also makes the administration tasks available from any client environment, not just from the Adaptive Server Anywhere administration utilities.

New SQL statements 

The new statements include the following:

• CREATE DATABASE statement

• CREATE WRITEFILE statement

• CREATE COMPRESSED DATABASE statement

• DROP DATABASE statement.

• ALTER DATABASE statement

• ALTER WRITEFILE statement

Utility database 

A new feature has been added that enables you to execute these statements without connecting to a database first. The utility database is a phantom database (there is no database file) which you can specify on a connection string. You can execute the database administration statements from a connection to the utility database.

     For information on using the utility database, see Using the utility database.

New system procedures 

Other administration tasks can be carried out from system stored procedures. These include validating all the tables in a database and obtaining file usage information (number of pages, and so on).

     For information on these procedures, see System and catalog stored procedures.

Database administration utilities 

The database administration utilities are unaffected in their functionality by the introduction of the SQL statements for database administration tasks. However, these utilities now use the SQL scripts in the scripts subdirectory of your installation directory to carry out their tasks.

     For more information, see The Initialization utility.

  Resource management options

The database administrator can use a set of database options to control the resources available to individual users. These options act as resource governors.

Resource governor options include the following:

• JAVA_HEAP_SIZE Limits the memory a connection can use to hold Java information.

• JAVA_NAMESPACE_SIZE Limits the memory a database can use to hold Java class name information.

• MAX_CURSOR_COUNT Limits the number of cursors for a connection.

• MAX_STATEMENT_COUNT Limits the number of prepared statements for a connection.

For full descriptions of each of these, see Database Options.

  New database options

Several new options have been introduced, and option settings can be accessed by client applications in a new manner.

Accessing database options 

Database option values are now available as function calls. In previous releases, database option settings were available to client applications only for Embedded SQL applications, using the GET OPTION statement. Database options are now available to all client applications using property functions. The GET OPTION statement is now deprecated.

For example, the following statement returns the value of the BLOCKING option:

SELECT connection_property ( 'blocking' )

     For more information, see System functions.

New options 

New options have been introduced. For descriptions of each of these, see Database Options.

• ANSI_CLOSE_CURSORS_ON_ROLLBACK

• COMPRESSION

• CONTINUE_AFTER_RAISERROR

• ESCAPE_CHARACTER

• JAVA_HEAP_SIZE

• JAVA_NAMESPACE_SIZE

• MAX_CURSOR_COUNT

• MAX_STATEMENT_COUNT

• PERCENT_AS_COMMENT

• PREFETCH

For the DATE_FORMAT option, a string jjj has been added to display the day of the year, from 1 to 366.

  Miscellaneous administration enhancements

Several new features have been added to the administration utilities.

• Unload and reload for SQL Remote consolidated databases     The dbunload and dblog utilities have additional features that allow them to be used for unloading and reloading a consolidated database in a SQL Remote installation.

       For more information, see The DBUNLOAD command-line utility and The DBLOG command-line utility.

• Unloading definitions only for specified tables     You can now unload table definitions only, for a set of specified tables.

• Added security for unloading and reloading databases     When unloading and reloading a database or extracting a database for SQL Remote, files created on disk contain potentially sensitive database information.

  For sites where this could be a problem, the -an and -ac options for the Unload and Extraction utilities provide more security. With these options, no data is written to disk. The price for the additional security is a lower performance, because the internal LOAD TABLE and UNLOAD TABLE statements cannot be used.

• Database initialization and upgrade     Inclusion of Sybase jConnect support and inclusion of the Sybase runtime Java classes is optional.