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

Other useful commands working with devices or topics around devices are:

• sp_helpdevice - to display information regarding device(s)
• sp_helpdb - to display information regarding databases
• sp_configure - to change system parameters like enable/disable mirroring or set maximal number of devices

sysdevices table is directly related with sysusages table, which allows to bind device to database. See below link to display full relations poster of SAP Sybase ASE. Here a small part of it:

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc70204.1570/pdf/ase_sys_poster.pdf?noframes=true

Tables have following meaning:

• sysdevices stores information regarding each device. A device is a file of raw partition that contains pages, next dedicated to databases
• sysusages stores information regarding continuous space that builds a device. One device can be built on more continuous areas of pages on disk. Column binding the both tables is vdevno
• sysdatabases lists databases on system and relates with sysusages with the help of dbid column.

Following query allows to display information presenting databases and devices used by those databases:

SELECT DISTINCT
      db.dbid, db.name, dvc.vdevno, dvc.name
FROM sysdatabases db
JOIN sysusages    usg ON db.dbid    = usg.dbid
JOIN sysdevices   dvc ON dvc.vdevno = usg.vdevno

Of course dedicated commands like sp_helpdevice or sp_helpdb display information better formatted, with more details, but sometimes dba needs to build some procedures on his own. Understanding relations between system tables makes this task much easier.

Below a very short explanation of meaning of the columns of table sysdevices.Full description can be found under this link.

• vdevno - number of device. Number is assigned by system, cannot exceed system parameter 'number of devices'
• name - unique name of the device
• low and high - address of the first and last page in the device. Those numbers allow unique numbering of each page on the disk in the Sybase installation on a computer.
• cntrltype - value defining device as database device or tabe (backup device)
• phyname - path to device (phyname not physname like in disk init command!)
• mirrorname - path to mirroring file/partition, filled in only if mirroring has been defined
• status - column encoding information regarding options set for a device  - see table below. To filter for example all devices that have mirror enabled following expression should be used in the WHERE clause: (512 & status) = 1, to filter only default devices use: (1 & status)=1

Decimal	Hex	Status
1	0x01	Default disk
2	0x02	Physical disk
4	0x04	Not used – logical disk
8	0x08	Skip header
16	0x10	Dump device
32	0x20	Serial writes
64	0x40	Device mirrored
128	0x80	Reads mirrored
256	0x100	Secondary mirror side only
512	0x200	Mirror enabled
1024	0x400	Master device is mirrored
2048	0x800	Used internally – mirror disabled
4096	0x1000	Used internally – primary device must be unmirrored
8192	0x2000	Used internally – secondary device must be unmirrored
16384	0x4000	UNIX file device uses dsync setting (writes flushed to physical media)

In case of disaster it could be a good idea to have those tables exported to text. Also results of sp_helpdevice or sp_helpdb could be regularly saved outside of Sybase ASE. Having that kind of documation dba can quicker recreate all required by database devices. This is one of the best practicies recommended by SAP.