Database

Creation of Database

whenever we create a database two system tables will be updated

1. Sysdatabases
2. Sysusages

Sysdatabases contains a row for every database which specifies the database name and owner

Sysusages contains a row for every database fragment (segmap value) indicating the size and logical starting disk and address for that fragment

* The segment value of Data is “3”

* The segment value of Log is “4”

* If Data and Log resides on same device the segmap value will be “7”

*** It is recommended by ASE to create Data and Log on separate devices so that the recovery would be possible whenever the database corrupted

Options of creating Databases

• Data and Log Mix
• Data and Log on separate device
• with override (with override) { forcibly writing the log to new device }
• with forload (FOR Load)

Data and Log Mixed device

Syntax : create database <database name >on <device name>= “Size M”

Example : 1> create database syb01 on datadev01 = “25M”

2> go

“ sa ” has the privilige to create database

Database can be expanded but they cannot be SHRUNK,but it is not recommended

Data and Log on seprate devices

Syntax: create database <database name >on <device name (device to be created already ) >=“size M” log on <logdevicename> = “size M”

Example : 1> create database syb01 on datadev01= “20M” log on logdev01= “5M”

2> go

with override (with override)

The minimum database size you can create is the size of model

Syntax :

Example : 1> create database littledb on diskdev1 = "4M" log on diskdev1 = "1M" with override

2> go

with for load (for Load)

Syntax:

Example: 1> create database database01 on datadev01 = “10M” on logdev02= “2M” for load

2> go

To bring a database online / offline

Syntax : online database <database name >

Example online database database001

Syntax : offline database <database name >

Example offline database database001

Drop Database

Syntax : drop database < database name >

Example : drop database database001

go

To know the database ID

Returns the ID number of the specified database

1 Syntax : db_id(database_name)

Example

1>select db_id("sybsystemprocs")

2>go

------

4

2 sp_helpdb or sp_helpdb<database_name>

Changing Db owner

• Database owners (SA) can transfer ownership to other users
• The new owner login must be valid server login and cannot already be a user of the Database
• It should be executed within the database transferred

Syntax : 1>use <db_name>

2>go

Example : 1> use database001

2> go

1> sp_changedbowner majera01

Now ,the SA and new owner majera01 can use the database .

Database options

Reference : (http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc20020_1251/html/databases/X12767.htm)

Database options controls many different aspects of database behavior

The system administrator and the database owner can use database options to configure the settings for an entire database. Database options differ from sp_configure parameters, which affect the entire server, and set options, which affect only the current session or stored procedure.

Database options control

• behavior of transaction
• default for table columns
• restriction of user access
• performance of recovery of BCP operations
• log behavior

Using the sp_dboption procedure

Use sp_dboption to change settings for an entire database. The options remain in effect until they are changed. sp_dboption:

Displays a complete list of the database options when it is used without a parameter

Changes a database option when used with parameters

You can change options for user databases only. You cannot change options for the master database. To change a database option in a user database (or to display a list of the database options), execute sp_dboption while using the master database.

syntax : sp_dboption [dbname, optname, {true | false}]

To make an option or options take effect for every new database, change the option in the model database

Options of database's

• abort tran on log full
• allow nulls by default
• async log service
• auto identity
• dbo use only
• ddl in tran
• delayed commit
• identity in non unique index
• no chkpt on recovery
• no free space acctg
• readonly
• scratch database
• select into/bulkcopy/pllsort
• single user
• trunc log on chkpt
• trunc .log on chkpt
• unique auto_identity index

Examples : Will be updated shortly

Before executing the log device the database should be in single user mode

1> sp_dboption syb001,”select singleuser ”,true

2>go

• After executing the database change the single user to FALSE again
• The transaction in the log segment are stored in Syslogs table
• Each and every database has syslogs table

There are 5 tables are common in all

• sysusers
• syslogs
• sysalternates
• sysprotects
• sysloginroles
• syslogshold

Turn off database options

To turn off database options:

Run sp_helpdb to show the options that are set for each database.

Use sp_dboption and the results from sp_helpdb to turn off all options except select into/bulk copy on tempdb.

Example, to turn off the trunc log on chkpt database option, enter:

sp_dboption sybsystemprocs, 'trunc log on chkpt', false

Turn off the trunc log on chkpt database option to avoid upgrade failure.

Extending a database on a separate device

Syntax : Alter < database_name >on < device_name > = “sizeM”

Example : Alter database001 on device001 = “5M”

go

Extending a database on a LOG device

Syntax : Alter < database_name > log on < device_log_name > = “sizeM”

Example : Alter database001 log on logdevice001 = “5M”

go

Moving the log to New Device

For the database created with data and log on the same device in that case we can separate the log device and move to new device .

The following are the steps:-

Alter the database onto the new device

• alter database < database > on < devicename > = “size M”
• sp_logdevice ,use this procedure to make the devices as log devices
• sp_logdevice <database name >,<logical device name >

Restart the server

• whenever a database created two system tables will be updated (sysdatabses and sysusages)and whenever a database altered ASE server puts and entry in sysusages

• If master database gets filled up it can be altered on a master device
• Model database should NOT BE LARGER than tempdb
• You can expand a database while it is in use