you're reading...
Sybase ASE

Sybase ASE Knowledge summary Section2 – Database management Device and storage space


Installation Initialization

Initial installation SQL Server, the installation program and scripts initialize the master device, and the establishment of master, model, tempdb and sybsystemprocs database. System database, predefined devices and segments organized according to the following default:

  1. master, model, tempdb database is installed on the master device
  2. sybsystemprocs database installed on the device selected during installation (sysprocsdev)
  3. Create three predefined segments for each database: system, default and logsegment
  4. all user-created database default device is the master
  5. If you choose to install the audit database sybsecurity, it is located on your device

The main problem with the storage device management considerations

  1. recovery
  2. when the physical disk crash, disk mirroring or save the log on a separate physical device to restore the database provides two mechanisms.

  3. performance
  4. Disk read and write speed is the bottleneck I/O operations, the database objects correctly placed on the physical device is conducive to improve performance; the logs and database objects on the system performance can be improved on a separate device.
    The table on a hard disk while the index on another hard disk, since the work points placed on two hard drives, so you can ensure that the physical read and write speed. Disk mirroring can reduce disk write speed.


Sybase database all the data stored on the device.

  1. Device Concept
  2. Sybase device is pre-configured to store a database of a special contiguous disk space, and it is mapped to an operating system file or a raw disk partition. It has two corresponding name: the logical name and the physical name. NT only supports devices mapped to the file.

    The relationship between the device and the database: many relationships. A database can be created or expanded to a plurality of devices, a device may also be used to store a plurality of databases. Different device operating systems can read and write it in parallel, so we can artificially place a database on more than one database device.

    Classification of the device:. Database Device and Dump Device database devices store the database and transaction log dump device used to store the backup of the database or log.

  3. Create a device command syntax:
  4.         DISK INIT Name=’device_name’, Physname=’physical_name’, Vdevno=virtual_device_number Size=number_of_pages [„„.] 
            DISK INIT Name=’My_Device’, Physname=’D:\database\My_device.dat’, Vdevno=3 Size=5000

    Note: The logical name, physical name, the device virtual number, device size dump device is created:

            sp_addumpdevice{‘disk’|’tape’} Logical_Name, Physical_Name, TapeSize
  5. Default Device
  6. In the absence of a specific device, any user-created data object is automatically stored in the default device.

    After the initial setup, the master device is pre-designated master system as the default device, so to create your own default device as soon as possible. Make sure the following device is not a default device: The system master device, specifying only the device is used by the log.

            Sp_diskdefault device name [, DefaultOn | DefaultOff]
  7. Disk Mirroring
  8. Disk mirroring is for database security considerations, when the media fails, disk mirroring can provide uninterrupted recovery. Disk mirroring is an absolute copy of the data on the disks. If a hard drive accident, the damaged copy automatically becomes offline, so all the reading and writing are directed undamaged copy.

    Sybase disk mirroring is carried out at the device level, disk mirroring device is essentially a mirror. When a device is mirrored operations, Sybase automatically create an additional device, image processing program by the original copy of all data on the device to the mirror device. If you want to become a database mirroring, each device is assigned to the database must be a mirror image. Master the special status of devices in the server, if it is damaged, SQL Server will crash. Therefore, if possible always mirrored Master device (another disk).Mirror command syntax:

            Disk Mirror Name = 'device_name', Mirror = 'physical_name' [, writes = serial | noserial]
            // 'device_name' is being mirrored device
  9. Remove Device
  10. sp_dropdevice logical_name [, delfile] containing the device of the database can not be deleted.

Creating and Using Segments

Segment (Segment) is a logical combination of the database device disk space, it can be seen as pointing to one or more tags database devices. Use segments can control database objects storage location can be stored on a database object classification different segments.

Relations between the device and the segment: many relationship. You can create multiple segments on a device, a segment can also cover multiple devices.

  1. The advantages of using segments
    1. control of the use of space: in the database objects on a segment will not grow in the outer section
    2. improve performance: in the section on different disk devices can read and write in parallel
    3. handle large table: the use of segments can be placed on a large table subparagraph separate physical devices, such as the addition of a paragraph of text or image data stored on a table
  2. Create segment
  3.         sp_addsegment segment name, database name, device name

    Description: Create a section for a database on the specified device. range extension segment

            sp_exetendsegment segment name, database name, device name

    Description: The device must be available in the database, or the need to expand the database to the new equipment; segment, database, device specified must exist. narrow the scope of section:

            sp_dropsegment segment name, database name, device name

    Description: When with a third parameter, the command does not delete the paragraph, but narrowed the scope of section. If a section contains another section to be exclusive device, we need to narrow the scope of this paragraph

  4. Use segment
  5. Two databases on different segments of the same equipment, they do not affect each other;
    When added to the database space, additional space will be automatically assigned to each of its sections; for example:

            alter database my_db on data_dev = 50

    On data_dev devices add 50M space my_db, this 50M space is automatically allocated to each segment of the database. Note: If data_dev for the database is new, system and default segments are automatically extended to the device. You can use the alter database command to log on option to allocate additional log space.

    • Create a new object in the section

                     create table table_name (column name Data type) [on segment name]
                     create [clusterd | non clusterd] index index_name on table_name (column name) [on segment name]

      Note: By definition, a clustered index on the table is always the same segment.

    • Place the segment name sp_placeobject existing object on a segment, the object name
      Note: This command does not put an object from one database device to another mobile device, it affects only future space allocation. Text field or image field of a large table can be placed on a separate device segment. Sp_placeobject segment name “table Field Name”

    • create a clustered index on the segment By definition, a clustered index on the table is always the same segment. If you create a table on one segment, and create a clustered index on another segment, then the table is moved together with its indexes, the entire table will leave the segment to create the table and move to create a clustered index in the segment. In this way, you can quickly and easily move to the designated table on the specified device.

    • The system predefined segments When you create a database, Sybase automatically creates three predefined segments: system segments: storage system tables (including all user-defined objects)

      default segments: storage of various objects created by the user, unless they are clearly assigned to different segments. Logsegment segments: storage database transaction log.

  6. Use threshold management
  7. Threshold (Threshold) management is an automatic control mechanism database free space, Sybase threshold management allows users to free space on a segment of the database to set thresholds and define the corresponding stored procedure. When the free space in the segment falls below the threshold set head, Sybase automatically run the corresponding stored procedure.

    In an actual operation of the database, the growth rate is generally higher than the growth of the log data, the log segment when the free space runs out, SQL Server default data manipulation will suspend all transactions, the client application stops.

    Stores its transaction log on a separate database for each segment are automatically provided with a last-chance threshold (Last Chance Threshold), the threshold is the estimated value back up the transaction log required free space. When the free space in the segment falls below the threshold set, Sybase sp_thresholdaction automatically run stored procedure called. Name and parameters of the process by a system of predefined content written by the users. Here is a simple example.

            CREATE PROCEDURE dbo.sp_thresholdaction
            /* This procedure parameters passed by location, name can be changed, but its definition and the order can not be changed */ 
    		/* db_name varchar (30), Database name, seg_name varchar (30), segment name, space_lefe int, the remaining free space, status int */
    		/* last-chance threshold, its value is 1, the other threshold value of 0 */ 
            AS BEGIN
            /* User-written procedure contents */ 
            dump transactiondb_name with truncate_only 

About daviewning

I am an Oracle DBA


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: