Changing Database Options

Database owners and system administrators can use the Database Properties wizard to change database options.

  1. In the left pane of the Administration Console, expand ASE Servers > Schema Objects > Databases.
  2. Select one of:
    • User Databases
    • System Databases
    • Temporary Databases
    • Proxy Databases
    Note: You cannot update any database options for the master database, or for archive databases.
  3. Click the Name field of the database, then click the drop-down arrow and select Properties.
  4. Click Options to see the list of options that you can set for this database.
    Database options that you can set include:
    abort tran on full log
    Determines how active transactions are treated when the database’s log becomes critically low on space:
    • To cancel all user queries that need to write to the transaction log until space in the log has been freed, select this option.

    • To suspend transactions and awaken them when space has been freed, unset this option.

    allow nulls by default
    Affects the ability of columns in newly created database tables to accept NULL values:
    • If you select this option, columns in newly created tables allow null values unless the column definitions explicitly state “not null.”

    • If you do not select this option, nulls are not allowed unless the column definitions explicitly permit them.

    allow wide dol row
    Allows wide, variable-length data-only-locked (DOL) rows in user databases.

    allow wide dol row is supported by SAP ASE version 15.7 and later.

    async log service
    Provides greater scalability and higher throughput in logging subsystems for high-end symmetric multiprocessor systems.

    async log service is supported by SAP ASE version 15.5 and later.

    auto identity
    Automatically adds a 10-digit IDENTITY column in a new table when a user creates the table without specifying a primary key, a unique index, or an IDENTITY column.
    dbo use only
    Restricts database access to the database owner.
    ddl in tran
    Allows users to include data definition language syntax within their transactions.

    Generally, avoid using DDL commands inside transactions. For more information about this option, see the Reference Manual.

    delayed commit
    When enabled, all local transactions use delayed commits so that control returns to the client without waiting for the I/O on log pages to complete, and I/O is not issued on the last log buffer for delayed commit transactions. delayed commit is supported by SAP ASE version 15.5 and later.
    Note: Delayed commit is not used if you enable both delayed commit and async log service for a database.
    enforce dump tran sequence
    When set to true, prevents operations that disallow a subsequent dump transaction.

    enforce dump tran sequence is supported by SAP ASE version 15.7 and later.

    erase residual data
    Allows you to enable or disable the removal of residual data based on your needs.

    When you enable the option at a session level, all the page deallocations during that session have their residual data removed. This includes page deallocations of tables that have the erase residual data explicitly turned off.

    identity in nonunique indexes
    Automatically includes an IDENTITY column in a table’s index keys, so that all indexes created on the table are unique.
    no chkpt on recovery
    Sets the database so that a checkpoint record is added to the database after it is recovered due to restarting the server.

    This checkpoint, which ensures that the recovery mechanism does not re-run unnecessarily, changes the sequence number on the database. If the sequence number on the secondary database has been changed, a subsequent dump of the transaction log from the primary database cannot be loaded into it.

    Select this option if you keep an up-to-date copy of a database. This prevents the secondary database from getting a checkpoint from the recovery process so that subsequent transaction log dumps from the primary database can be loaded into it.

    no free space acctg
    Determines whether the database enables free-space accounting and execution of threshold actions for non log segments.

    Suppressing free-space accounting speeds recovery time because the free-space counts are not recomputed for those segments. However, it disables updating the rows-per-page value stored for each table, so system procedures that estimate space usage may report inaccurate values.

    Note: Only system security officers can change the no free space acctg option.
    read only
    Prevents modification of any data in the database.
    scratch database
    The database that stores the sysaltusages table. See the System Administration Guide: Volume 2.
    select into/bulk copy/pllsort
    Allows users to perform nonlogged operations. Nonlogged operations include select into for permanent tables, the bulk-copy utility bcp, and the writetext utility.

    You need not select this option to allow select into for temporary tables or to run bcp on a table with indexes, because inserts are logged.

    Attempting to dump the transaction log in a database after unlogged changes have been made to the database with select only or bulk-copy produces an error message instructing you to use dump database instead.

    single user
    Allows only one user at a time to use the database.
    trunc log on chkpt
    Truncates the transaction log (removes committed transactions) every time the database is check pointed.

    If you select this option, you cannot dump the transaction log. You may want to select this option during development work, when backups of the transaction log are typically not needed.

    Note: If you select trunc log on chkpt for development purposes, clear it periodically and dump the transaction log. If you never dump the transaction log, it continues to grow, and eventually you run out of space in the database.
    unique auto_identity index
    If a database’s auto_identity is turned on, newly created tables automatically get a column named SYB_IDENTITY_COL. This helps maintain data integrity, since unique IDs are commonly used.
Related tasks
Changing Database Ownership
Modifying Database Storage Allocations
Modifying the Transaction Log Cache and the Log I/O Buffer Size
Viewing Database Statistics
Related reference
Database Properties