Creating and Managing User Databases

System administrators use a number of commands and stored procedures to create and manage databases.

This table summarizes the commands for creating, modifying, and dropping user databases and their transaction logs.

• Permissions for Managing User Databases
  By default, only the system administrator has create database permission, although he or she can grant permission to use the create database command.
• Using the create database Command
  You must have create database permission, and you must be a valid user of master to use the create database command.
• Assigning Space and Devices to Databases
  SAP ASE allocates storage space to databases when a user executes the create database or alter database command.
• Placing a Transaction Log on a Separate Device
  Use the log on clause of the create database command to place a transaction log (the syslogs table) on a separate database device.
• Shrinking Log Space
  The alter database command includes a log off parameter that removes unwanted portions of a database log, allowing you to shrink log space and free storage without re-creating the database.
• Calculating the Transaction Log Growth Rate
  Use sp_logging_rate to calculate the transaction log growth rate for a specified time period.
• Database Recovery with the for load Parameter
  When you create a new database, SAP ASE generally clears all unused pages in the database device. Use the for load option if you are going to use the database to load from a database dump, either for recovery from media failure or for moving a database from one machine to another. Using for load runs a streamlined version of create database that skips the page-clearing step, and creates a target database that can be used only for loading a dump.
• Using the with override Option with create database
  The with overide option allows machines with limited space to maintain their logs on device fragments that are separate from their data.
• Changing Database Ownership
  A system administrator might want to create user databases and some of the initial work, then grant ownership of them to another user.
• Altering Databases
  When your database or transaction log grows to fill all the space allocated with create database, you can use alter database to add storage.
• Using the drop database Command
  Use drop database to remove a database from SAP ASE.
• System Tables That Manage Space Allocation
  When you create a database on a database device and allocate a certain amount of space to it, SAP ASE first makes an entry for the new database in sysdatabases.
• SAP ASE Support for Replication by Column Value
  SAP ASE extends Multi-Path Replication™ by including a new distribution model that provides support for distributing replicated rows based on the data values in one or more columns in a single table.
• Getting Information about Database Storage
  The sp_helpdb and sp_spaceused system procedure help determine which database devices are currently allocated to databases and how much space each database uses.