sysdevices system table

All metadata describing server, database or objects in the database are saved in system tables. Data in those tables can be queried, but should not be directly modified.

One of such table is sysdevices, listing all defined on server devices. To change/add/remove records in that table use:

• disk init - to create new devices, 
• disk resize - to extend already created devices, 
• disk mirror - to enable mirroring of device, 
• disk unmirror - to stop mirroring,
• sp_dropdevice - to remove device, 
• sp_deviceattr - to change options related to the device
• sp_diskdefault - to mark a device as default or unmark this property

Moving database device using mirroring

In IT at all one apect doesn't change. This is the need for changes! Even if the location of database devices will be very well designed in the moment, with time it can be discovered that there is too less disk space on the device or file system and the database will beed to be migrated to new device.

This can be done in different ways. One of them is using mirroring. It seems to be quite good as no down time would be required. The idea is:

• enable mirroring of the old device to the new device located on new file system
• when devices are mirrored stop mirroring excluding the old device 
• in that way only the new device will be used!

Mirroring devices - strategies

Mirroring in Sybase ASE allows to avoid failures of databases. So the answer to question "what to mirror" is the same as "what is the most important"?

• master database and device is surely one of the most important componentes of server. Failure in master device will cause the entire server to stop. In case of master database additional step should be done apart from just mirroring devices.
  When Sybase ASE starts it needs to load master database. If the device would be destroyed when server was offline, it will not be able to start. Remember that information about mirroring is stored in sysdevices table in... master. Server without master is not aware of exiting mirror. To solve this issue parameter -r can be added to RUN file pointing to a mirrored master device.

Mirroring devices

SAP ASE allows to mirror device making instant copy of a device in another file/device. In case the primary device would contain a bad block, correct data could be read from a spare location. Mark that mirrored are devices - not databases, so in case when a database is stretched between multiple devices, mirroring only some devices doesn't give full protection for the database.

To mirror a device an option 'disable disk mirroring' must be set to off. Following command allows to verify this setting:

execute sp_configure 'disable disk mirroring'

In case the option would be set to true, you can change it's value starting:

sp_configure 'disable disk mirroring', 0

That option is static - this means server restart is required to make mirroring possible.

Managing devices

Dropping a device

After a database has been dropped a device not used by an
 database can usually also be dropped(of course dba may decide not to release disk space). To remove device following command could be used:

exec sp_dropdevice 'BetaDevice'

where BetaDevice is logical name of device that should be removed. After issuing that command only row in sysdevices is removed. File that the device was build on, need to removed manually. The same concerns also a raw device.

If the device is in use - so you have a database using it - don't worry. Such command will end with an error "Device is being used by a database. You can't drop it.".

Default device and why you should change it?

Every one likes to work with intelligent programs: just put some specific information and the remaining part of parameters should be taken from a set of default parameters. Sybase ASE also works that way. When a new database will be created, one or more devices should be specified, but if this part of command will be omitted, than... a default device will be used. Initially master device is the default one. This is not the best situation, as if by a mistake no device will be specified during database creation it will be placed on master device. According to information in Administration guide it is not recommended situation:

Caution!
Do not store user databases on the master device; doing so makes it difficult to recover both the system databases and any user databases stored on the master device.

Possible errors during device creation

When raw device is being created and something goes wrong, usually an error message displayed to the administrator working in his tools is not very explanatory. This may be error message like this:

Could not execute statement.

DISK INIT encountered an error while attempting to open/create the
physical file for device '/dev/sdb1'. Consult the Adaptive Server error
log for more details. For the Cluster Edition, check the error log of
each instance.
SQLCODE=5123
Server=SAP01, Severity Level=16, State=1, Transaction State=0, Line=50
File: "devices.sql", line 1

To verify what happened behind the scene, the best is to log at ASE error log on the server. The default error log location on server SAP01 is:

/opt/sap/ASE-16_0/install/SAP01.log

Raw device creation

As raw devices guarantee that write will be completed within transaction (unlike file device even with with DIRECTIO option) and additionally have very good write performance (unlike file with DSYNC option), this is common in production to see raw devices.

To create a raw device you need first to create a device in OS. On Linux station where drive has been attached using IDE controller the disk can be visible via a symbolic file /dev/sdb. First of all partitions should be created on the disk.

To work with partitions execute following steps:

• start command 

fdisk /dev/sdb

File based device creation

To create a new database you need first to have devices created. As already described inthe post
Raw devices or files? What is better? we can create raw devices and regular files functioning as devices.

Let's start with an example showing how to create a device basing on a file. All what is required to start with the device creation is a folder on file system having enough free space. First decide where to locate the devices. It is not a bad idea to have a dedicated folder for file devices. Because data files and log files are used differently you should probably think about locating files on separate filesystem anyway. Here let's put  all the files in the same folder.

[sap@localhost ~]$ pwd
/opt/sap
[sap@localhost ~]$ mkdir databases
[sap@localhost ~]$ cd databases
[sap@localhost databases]$ mkdir alfa

Having folder, enough space and read and write permissions there, we can switch to SQL:

disk init name ='AlfaDataDev', 
          physname = '/opt/sap/databases/alfa/alfadatadev.dat', 
          size=10240
GO
disk init name ='AlfaLogDev', 
          physname = '/opt/sap/databases/alfa/alfalogdev.dat', 
          size=2560, 
          skip_alloc = true
GO

Raw devices or files? What is better?

One of the questions when a new device is to be created is if the device should be a raw device or a file. There are a couple of factors that should be taken into consideration:

• Normal files are easier to manage. When database grows and you need to add a new disk space to the database, it is enough to create a new file and add it as a device to the database. In case of a raw device you have much more administrative work to do...
• Normal files have better performance during writes to the disk. Files are usually buffered by OS, so modifying a file in memory is faster than sending the modification to the disk, however...

System database's devices

Information regarding devices in Sybase/ SAP ASE can be found in many system tables, however this will be a very bad idea to modify them directly. When you need a new device some dedicated commands should be used.

System databases are created during installation and normally you don't need to create them manually. Probably the only moment when a manual creation of databases will be required is when installation has been somehow damaged and you need to recover from the disaster.

During installation following devices have been created:

SELECT name,phyname FROM sysdevices

name                           phyname                                                       
------------------------------ -------------------------------------------------
master                         /opt/sap/data/master.dat       
tapedump1                      /dev/nst0                         
tapedump2                      /dev/nst1                         
sysprocsdev                    /opt/sap/data/sysprocs.dat
systemdbdev                    /opt/sap/data/sybsysdb.dat
tempdbdev                      /opt/sap/data/tempdbdev.dat
sybmgmtdev                     /opt/sap/data/sybmgmtdb.dat 

and those devices are used by following databases (we are skipping tape devices here as you see in WHERE clause):

Cannot execute file RUN_SYBASE

If Sybase ASE has not started automatically you may wish to start it manually. Command used to start server is:

startserver

located by default in /opt/sap/ASE-16_0/bin. This script by default searches for a file /opt/sap/ASE-16_0/install/RUN_SYBASE. This file contains startup parameters used by server during start. However if during the installation you chosed to name the server differently, such a file is not present. Instead of this another file should be placed in that directory. On my server it is RUN_SAP01. To direct startserver script to use that file you can pass the path as parameter using -f:

./startserver -f /opt/sap/ASE-16_0/install/RUN_SAP01

Devices - introduction

To separate database objects from physical server components all databases are build on logical devices. Logical devices seat between a database and physical disk structures. Having database build on devices it is quite easy to migrate database to another server. All you need are just devices with the same names on the another system!

A device can be a file (common solution on Windows implementation) or a raw device (usually used in Unix implementation).

Pages in Sybase / SAP ASE: Virtual and Logical pages

In many places, where command do something with disk or database, you can specify page size. There is also mentioned that default size of the page is 2KB, but this can be set to 4KB, 8KB and so on...
Generally we have two types of pages that can be described by parameters of some commands. There are:

• Virtual pages
• Logical pages