query to which reports the devices used by the database, and the amount of space on each device in sybase database

[shiv]

Hi,
I am trying to write a query to which reports the devices used by the
database, and the amount of space on each device in sybase database.

I came across with two queries : (
http://manuals.sybase.com/onlinebooks/group-asarc/srv10024/sag/@Generic__BookTextView/31089;pt=31343
)

1. select sysdevices.name, sysusages.size / 512 from sysdevices,
sysdatabases, sysusages where sysdatabases.name = "sybsystemprocs"
and sysdatabases.dbid = sysusages.dbid and sysdevices.low <=
sysusages.size + vstart and sysdevices.high >= sysusages.size +
vstart -1

and
( http://fengnet.com/book/Backup.and.Recovery/I_0596102461_CHP_17_SECT_9.html
)

2. select sysdevices.name as DevName, sysdatabases.name as DBName,
sysusages.size/512 as Size from sysdatabases, sysusages, sysdevices
where sysdevices.name="BadDeviceName" and sysdevices.low <=
sysusages.vstart and sysdevices.high >= sysusages.vstart and
sysusages.dbid = sysdatabases.dbid

I did not get the logic of 1st query as why we need to use
"sysdevices.low <= sysusages.size + vstart" and "sysdevices.high >=
sysusages.size + vstart -1" ? Could any one confirm on this so that I
can go ahead and pick proper query?

Regards,
Shiv

[Bret Halford]

Post by shiv
Hi,
I am trying to write a query to which reports the devices used by the
database, and the amount of space on each device in sybase database.
I came across with two queries : (
)
1. select sysdevices.name, sysusages.size / 512 from sysdevices,
sysdatabases, sysusages where sysdatabases.name = "sybsystemprocs"
and sysdatabases.dbid = sysusages.dbid and sysdevices.low <=
sysusages.size + vstart and sysdevices.high >= sysusages.size +
vstart -1
and
( http://fengnet.com/book/Backup.and.Recovery/I_0596102461_CHP_17_SECT_9.html
)
2. select sysdevices.name as DevName, sysdatabases.name as DBName,
sysusages.size/512 as Size from sysdatabases, sysusages, sysdevices
where sysdevices.name="BadDeviceName" and sysdevices.low <=
sysusages.vstart and sysdevices.high >= sysusages.vstart and
sysusages.dbid = sysdatabases.dbid
I did not get the logic of 1st query as why we need to use
"sysdevices.low <= sysusages.size + vstart" and "sysdevices.high >=
sysusages.size + vstart -1" ? Could any one confirm on this so that I
can go ahead and pick proper query?
Regards,
Shiv

Are you writing this for 15.x? If so neither query will be correct
as the sysusages and sysdevices tables are now joined on the vdevno
field; the vdevno is no longer imbedded in the virtual page number.

Doesn't sp_helpdb already do this?

[shiv]

Post by shiv
Hi,
I am trying to write a query to which reports the devices used by the
database, and the amount of space on each device in sybase database.
I came across with two queries : (
)
1. select sysdevices.name, sysusages.size / 512  from sysdevices,
sysdatabases, sysusages  where sysdatabases.name = "sybsystemprocs"
and sysdatabases.dbid = sysusages.dbid    and sysdevices.low <=
sysusages.size + vstart  and sysdevices.high >= sysusages.size +
vstart -1
and
(http://fengnet.com/book/Backup.and.Recovery/I_0596102461_CHP_17_SECT_...
)
2. select sysdevices.name as DevName, sysdatabases.name as DBName,
sysusages.size/512 as Size from sysdatabases, sysusages, sysdevices
where sysdevices.name="BadDeviceName" and sysdevices.low <=
sysusages.vstart and sysdevices.high >= sysusages.vstart and
sysusages.dbid = sysdatabases.dbid
I did not get the logic of 1st query as why we need to use
"sysdevices.low <= sysusages.size + vstart" and "sysdevices.high >=
sysusages.size + vstart -1" ? Could any one confirm on this so that I
can go ahead and pick proper query?
Regards,
Shiv

Are you writing this for 15.x?  If so neither query will be correct
as the sysusages and sysdevices tables are now joined on the vdevno
field; the vdevno is no longer imbedded in the virtual page number.
Doesn't sp_helpdb already do this?

Hi, Halford,
I am trying to run it on 12.x version. on 15.x version as you said, I
can make use of vdevno as below.
"select v.name, v.phyname, v.status, u.segmap from dbo.sysdevices v,
dbo.sysusages u where v.vdevno = u.vdevno"
But i need to run this on 12.x . And My main aim is to get the
physical name (which sp_helpdb does not provide).

In my case the 1st query is having some issues because vstart is
giving me values in negative (may be it is more than 2^32) because of
which when I say "sysdevices.high >= sysusages.size + vstart -1 " it
thrown a Arithmatic overflow exception. My queries are :
1. If we get vstart value in negative then it does not make sense to
compare this value against high and low values of any device.
2. If we just use "sysdevices.low <= sysusages.vstart and
sysdevices.high >= sysusages.vstart" i wont get any Arithmetic
exception. But is this correct query ?
3. Any idea how to get source code of sp_helpdb? Just curious if I can
extract some part from this procedure.

Regards,
Shivaraj

[Sherlock, Kevin [TeamSybase]]

"shiv" <***@gmail.com> wrote in message news:2dc58db1-4153-429e-97e7-***@m16g2000yqc.googlegroups.com...
On Nov 24, 10:03 pm, Bret Halford <***@sybase.com> wrote:
3. Any idea how to get source code of sp_helpdb? Just curious if I can
extract some part from this procedure.


code of sp_helpdb is in $SYBASE/$SYBASE_ASE/scripts/installmaster

or you could get it from :

exec sybsystemprocs..sp_helptext sp_helpdb

or more recent versions of ASE:

exec sybsystemprocs..sp_showtext sp_helpdb