//
you're reading...
Sybase ASE

Sybase ASE Knowledge summary Section3 – Database and transaction log

Creating a user database

Create Database database_name On equipment _1 = Size_1, 
// Units: M equipment _2 = Size_2, ""
log on log device = Log_Size
[With Override] 
// use when creating a database and transaction logs on the same device that option 
[For Load] 
// disable user access until the load or restore operation is complete database of examples:
Create Database test_db On data_dev = 100,
// Units: M Index_dev = 50 Log on log_dev = 30 Description
  1. the log on a separate device, help to improve database performance
  2. illustrates:Data_dev Index_dev log_dev
  3. If the database and logs on the same device, you can not achieve an incremental backup
  4. System and Default segment usually reduced to a range of devices, such as Default section and remove the device Index_dev System section on creating a new segment, used to store specialized database objects.

Change Database

  1. Change the database owner
  2. Typically there is a system administrator to create user database, the default owner is dbo. System process sp_changeddbowner can change the owner of a relational database, it must be changed in case of a database by a database administrator to perform the primary relationship. The syntax is as follows:

          sp_changeddbowner login_name [, True]
          

    where the parameter True half for permission to pass to the new owner.

  3. Extending Database
    1. Extended database space alter database database name expansion of space on the device name = @ Unit: M

      If the expansion device for the database is new, System and Default section will automatically expand to the device.

    2. Extended transaction log to the new device sp_logdevice database name, device name

      Example: In addition to expand the database 5M, for storing log

       
      			  alter database my_db on my_dev = 5 
      			  go
                    sp_logdevice my_db, my_dev 
      			  go
      			  
  4. Delete Database
  5. drop database database name You must remove all databases on it before removing the device, remove the device command is:
    sp_dropdevice

Transaction Log

Log files are used to record database every time you modify an active file. SQL Server Each database has its own log file, the system table syslogs, also called the transaction log. The transaction log is to undo the transaction and recovery when the transaction fails basis. In some cases, the transaction log is more important than the data itself.

What is a transaction?

Modify the database transaction is performed in units. A transaction is a sequence of operations, these operations are either all do-or-not, it is an indivisible unit of work. Any transaction with the following features.

  1. atomic execution (Atomic)
  2. to maintain data consistency (Consistency)
  3. mutual isolation (Isolation)
  4. The effect of persistent (Durability)

Four features of the above transaction is called a transaction ACID guidelines.

Transactions during operation, Sybase ASE to the transaction began, the transaction ends and insert the database, deleting and updating each operation is stored as a log record to the transaction log. First update transaction in the database buffer (memory) and will be used to record the operation buffer respectively active data page (data page) and log pages (log page). When running to commit tran when the log buffer is written to disk Top start, then the data from the buffer to disk pages that follow the “first and logs (write_ahead log)” principle, thus ensuring that the case of failure by logging can maximize recovery. Recovery must undo transaction failure has not submitted completed transactions if there is still not written to the database from the buffer device, but also to re-run the transaction. Appendix: Creating and loading a database instance use master

use master
declare @vdevno int
select @vdevno=max(convert(tinyint,substring(convert(binary(4),d.low),v.low,1)))+1 from master.dbo.sysdevices d,master.dbo.spt_values v where v.type=’E’and v.number=3 declare @v_str char(2)
select @v_str=convert(char(2),@vdevno) print @v_str disk init
name=”TESTDBDATA”,
physname=”D:\testdb\TestDBData.dat”, vdevno=@vdevno+1, size=153600 disk init
name=”TESTDBINDEX”,
physname=”D:\testdb\TestDBIndex.dat”, vdevno=@vdevno+2, size=102400 disk init
name=”TESTDBLOG”,
physname=”D:\testdb\TESTDBLOG.dat”, vdevno=@vdevno+3, size=76800
create database TESTDB on TESTDBDATA=300,TESTDBINDEX=200 log on TESTDBLOG=150 use TESTDB
execute sp_addsegment indexdev,TESTDBDATA,TESTDBINDEX execute sp_dropsegment “default”,TESTDBDATA,TESTDBINDEX execute sp_dropsegment system,TESTDBDATA,TESTDBINDEX use master
load database TESTDB from ‘d:\bak\testdb_backup.dmp’ online database TESTDB
Advertisements

About daviewning

I am an Oracle DBA

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: