Home » Databases » Sybase » ASE » ASE 15 sysdevices changes messing your code up?

ASE 15 sysdevices changes messing your code up?

Starting in ASE 15, Sybase changed the master..sysdevices table, adding the columns vdevno and resizedate. Sybase also changed the behavior of the low and the high columns so are aren’t able to easily map a master..sysusages.vstart to a master..sysdevices entry. The low & high columns are now offsets which allow for much larger devices (4 terabytes) up from the previous limit of 32GB.

So, how does this change things for you as a developer and a DBA? In your application you will need to check for the version number and either use the old method (pre-15) of mapping the vstart to the low/high in sysdevices or the new method (15.0+) of mapping on the vdevno. The following example finds empty devices that do not contain any database fragments:

Old method (pre-15):

select sd.name as "Unused devices",
       sd.phyname as "Physical location",
       ((1. + (sd.high - sd.low)) * @@pagesize) / 1048576. as "Device size in MB"
  from master..sysdevices sd
  where
    name not in (
      select sd.name
      from master..sysdevices sd, master..sysusages su
      where
        su.vstart >= sd.low
        and su.vstart < = sd.high
    )
   and sd.name not like "tapedump%"

New method (15.0+):

select sd.name as "Unused devices",
       sd.phyname as "Physical location",
       ((1. + (sd.high - sd.low)) * @@pagesize) / 1048576. as "Device size in MB"
  from master..sysdevices sd
  where
    name not in (
      select sd.name
      from master..sysdevices sd, master..sysusages su
      where
         su.vdevno = sd.vdevno
    )
   and sd.name not like "tapedump%"

Example is based on Ed Barlow’s Stored Procedures.

Share Button

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Facebook login by WP-FB-AutoConnect