Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

query which reports the devices used by the database, and the amount of space on each device in 12.5 Sybase version.

5 posts in General Discussion Last posting was on 2009-12-16 01:41:42.0Z
shivaraj Posted on 2009-11-25 05:13:03.0Z
Sender: 1861.4b0b8523.1804289383@sybase.com
From: shivaraj
Newsgroups: sybase.public.ase.general
Subject: query which reports the devices used by the database, and the amount of space on each device in 12.5 Sybase version.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b0cbcdf.4829.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 Nov 2009 21:13:03 -0800
X-Trace: forums-1-dub 1259125983 10.22.241.41 (24 Nov 2009 21:13:03 -0800)
X-Original-Trace: 24 Nov 2009 21:13:03 -0800, 10.22.241.41
Lines: 28
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28689
Article PK: 77932

Hi,
I am trying to write a query which reports the devices used
by the database, and the amount of space on each device (for
12.5 sybase version). I found

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

2. select sysdevices.name as DevName, sysdatabases.name as
BName,
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

could you please let me know which one is correct? I could
not get the logic behind "sysdevices.low <= sysusages.size +
vstart" and "sysdevices.high >= sysusages.size + vstart -1"
in first query. If some one explains how this logic works
that will be great.

Regards,
Shivaraj


shivaraj Posted on 2009-11-25 09:07:48.0Z
Sender: 1861.4b0b8523.1804289383@sybase.com
From: shivaraj
Newsgroups: sybase.public.ase.general
Subject: Re: query which reports the devices used by the database, and the amount of space on each device in 12.5 Sybase version.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b0cf3e4.50b7.1681692777@sybase.com>
References: <4b0cbcdf.4829.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 25 Nov 2009 01:07:48 -0800
X-Trace: forums-1-dub 1259140068 10.22.241.41 (25 Nov 2009 01:07:48 -0800)
X-Original-Trace: 25 Nov 2009 01:07:48 -0800, 10.22.241.41
Lines: 48
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28690
Article PK: 77933

In my view ["sysdevices.low <= sysusages.size
+ vstart" and "sysdevices.high >= sysusages.size + vstart
-1"]

should be written as

["sysdevices.low <= vstart" and "sysdevices.high >=
sysusages.size + vstart"]

As we are just interested in identify if vstart (starting
location) and size+vstart (ending location) of any database
lies between the low and high position of a device.

Am I right here?

Regards,
Shivaraj

> Hi,
> I am trying to write a query which reports the devices
> used by the database, and the amount of space on each
> device (for 12.5 sybase version). I found
>
> 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
>
> 2. select sysdevices.name as DevName, sysdatabases.name as
> BName,
> 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
>
> could you please let me know which one is correct? I could
> not get the logic behind "sysdevices.low <= sysusages.size
> + vstart" and "sysdevices.high >= sysusages.size + vstart
> -1" in first query. If some one explains how this logic
> works that will be great.
>
> Regards,
> Shivaraj


"Mark A. Parsons" <iron_horse Posted on 2009-11-25 11:18:36.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: query which reports the devices used by the database, and the amount of space on each device in 12.5 Sybase version.
References: <4b0cbcdf.4829.1681692777@sybase.com> <4b0cf3e4.50b7.1681692777@sybase.com>
In-Reply-To: <4b0cf3e4.50b7.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 091120-0, 11/20/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b0d128c@forums-1-dub>
Date: 25 Nov 2009 03:18:36 -0800
X-Trace: forums-1-dub 1259147916 10.22.241.152 (25 Nov 2009 03:18:36 -0800)
X-Original-Trace: 25 Nov 2009 03:18:36 -0800, vip152.sybase.com
Lines: 59
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28692
Article PK: 77931

sp_helpdb already provides what you're looking for.

If you want the results in a different format, I suggest you take a look at the source code for sp_helpdb. Said source
code will answer your question about the sysdevices/sysusages interaction.

NOTE: The hardcoded '512' is only valid on a dataserver with a 2KB page size; for dataservers with a larger page size
the use of '512' will produce invalid results. If you look at the source code for sp_helpdb you'll see how the
hardcoded '512' is replaced with a value derived from master..spt_values, ie, the stored proc automatically calculates
the correct values based on the dataserver's page size.

shivaraj wrote:
> In my view ["sysdevices.low <= sysusages.size
> + vstart" and "sysdevices.high >= sysusages.size + vstart
> -1"]
>
> should be written as
>
> ["sysdevices.low <= vstart" and "sysdevices.high >=
> sysusages.size + vstart"]
>
> As we are just interested in identify if vstart (starting
> location) and size+vstart (ending location) of any database
> lies between the low and high position of a device.
>
> Am I right here?
>
> Regards,
> Shivaraj
>
>
>> Hi,
>> I am trying to write a query which reports the devices
>> used by the database, and the amount of space on each
>> device (for 12.5 sybase version). I found
>>
>> 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
>>
>> 2. select sysdevices.name as DevName, sysdatabases.name as
>> BName,
>> 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
>>
>> could you please let me know which one is correct? I could
>> not get the logic behind "sysdevices.low <= sysusages.size
>> + vstart" and "sysdevices.high >= sysusages.size + vstart
>> -1" in first query. If some one explains how this logic
>> works that will be great.
>>
>> Regards,
>> Shivaraj


shivaraj Posted on 2009-11-26 06:26:14.0Z
Sender: 1861.4b0b8523.1804289383@sybase.com
From: shivaraj
Newsgroups: sybase.public.ase.general
Subject: Re: query which reports the devices used by the database, and the amount of space on each device in 12.5 Sybase version.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b0e1f86.d63.1681692777@sybase.com>
References: <4b0d128c@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 25 Nov 2009 22:26:14 -0800
X-Trace: forums-1-dub 1259216774 10.22.241.41 (25 Nov 2009 22:26:14 -0800)
X-Original-Trace: 25 Nov 2009 22:26:14 -0800, 10.22.241.41
Lines: 84
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28698
Article PK: 77940

Thanks for your answer. As of now, I am not much bothered
about hard coded 512 value. I am more concerned towards
identifying the devices on which the databases are residing.
So I was just wondering is it really required to use

select v.name, v.phyname, v.status, u.segmap from
dbo.sysdevices v, dbo.sysusages u where v.low <= u.size +
u.vstart and v.high >= u.size + u.vstart -1

query than simpler one like

select v.name, v.phyname, v.status, u.segmap from
dbo.sysdevices v, dbo.sysusages u where sysdevices.low <=
vstart and sysdevices.high >= sysusages.size + vstart

query. I will check with sp_helpdb code and check if this
can be answered.

> sp_helpdb already provides what you're looking for.
>
> If you want the results in a different format, I suggest
> you take a look at the source code for sp_helpdb. Said
> source code will answer your question about the
> sysdevices/sysusages interaction.
>
> NOTE: The hardcoded '512' is only valid on a dataserver
> with a 2KB page size; for dataservers with a larger page
> size the use of '512' will produce invalid results. If
> you look at the source code for sp_helpdb you'll see how
> the hardcoded '512' is replaced with a value derived from
> master..spt_values, ie, the stored proc automatically
> calculates the correct values based on the dataserver's
> page size.
>
> shivaraj wrote:
> > In my view ["sysdevices.low <= sysusages.size
> > + vstart" and "sysdevices.high >= sysusages.size +
> > vstart -1"]
> >
> > should be written as
> >
> > ["sysdevices.low <= vstart" and "sysdevices.high >=
> > sysusages.size + vstart"]
> >
> > As we are just interested in identify if vstart
> > (starting location) and size+vstart (ending location) of
> > any database lies between the low and high position of a
> > device.
> > Am I right here?
> >
> > Regards,
> > Shivaraj
> >
> >
> >> Hi,
> >> I am trying to write a query which reports the devices
> >> used by the database, and the amount of space on each
> >> device (for 12.5 sybase version). I found
> >>
> >> 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
> >>
> >> 2. select sysdevices.name as DevName, sysdatabases.name
> as >> BName,
> >> 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 >>
> >> could you please let me know which one is correct? I
> could >> not get the logic behind "sysdevices.low <=
> sysusages.size >> + vstart" and "sysdevices.high >=
> sysusages.size + vstart >> -1" in first query. If some one
> explains how this logic >> works that will be great.
> >>
> >> Regards,
> >> Shivaraj


Vivek Kak Posted on 2009-12-16 01:41:42.0Z
Sender: 1d8a.4b283738.1804289383@sybase.com
From: Vivek Kak
Newsgroups: sybase.public.ase.general
Subject: Re: query which reports the devices used by the database, and the amount of space on each device in 12.5 Sybase version.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b283ad6.1dd0.1681692777@sybase.com>
References: <4b0e1f86.d63.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 15 Dec 2009 17:41:42 -0800
X-Trace: forums-1-dub 1260927702 10.22.241.41 (15 Dec 2009 17:41:42 -0800)
X-Original-Trace: 15 Dec 2009 17:41:42 -0800, 10.22.241.41
Lines: 106
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28772
Article PK: 78015

Hello Shivraj,

Here you go:
select db_name=d.name, size = size / @@maxpagesize,
usage = convert(char(20),b.name)
from master.dbo.sysdatabases d, master.dbo.sysusages u,
master.dbo.sysdevices dv,master.dbo.spt_values b
where d.dbid = u.dbid
and dv.low <= size + vstart
and dv.high >= size + vstart - 1
and dv.status & 2 = 2
and b.type = "S"
and u.segmap & 7 = b.number
and dv.name=<device_name>
order by db_name,usage



HTH,
Vivek

> Thanks for your answer. As of now, I am not much bothered
> about hard coded 512 value. I am more concerned towards
> identifying the devices on which the databases are
> residing. So I was just wondering is it really required to
> use
>
> select v.name, v.phyname, v.status, u.segmap from
> dbo.sysdevices v, dbo.sysusages u where v.low <= u.size +
> u.vstart and v.high >= u.size + u.vstart -1
>
> query than simpler one like
>
> select v.name, v.phyname, v.status, u.segmap from
> dbo.sysdevices v, dbo.sysusages u where sysdevices.low <=
> vstart and sysdevices.high >= sysusages.size + vstart
>
> query. I will check with sp_helpdb code and check if this
> can be answered.
>
>
> > sp_helpdb already provides what you're looking for.
> >
> > If you want the results in a different format, I suggest
> > you take a look at the source code for sp_helpdb. Said
> > source code will answer your question about the
> > sysdevices/sysusages interaction.
> >
> > NOTE: The hardcoded '512' is only valid on a dataserver
> > with a 2KB page size; for dataservers with a larger page
> > size the use of '512' will produce invalid results. If
> > you look at the source code for sp_helpdb you'll see how
> > the hardcoded '512' is replaced with a value derived
> > from master..spt_values, ie, the stored proc
> > automatically calculates the correct values based on
> > the dataserver's page size.
> >
> > shivaraj wrote:
> > > In my view ["sysdevices.low <= sysusages.size
> > > + vstart" and "sysdevices.high >= sysusages.size +
> > > vstart -1"]
> > >
> > > should be written as
> > >
> > > ["sysdevices.low <= vstart" and "sysdevices.high >=
> > > sysusages.size + vstart"]
> > >
> > > As we are just interested in identify if vstart
> > > (starting location) and size+vstart (ending location)
> > > of any database lies between the low and high position
> > > of a device.
> > > Am I right here?
> > >
> > > Regards,
> > > Shivaraj
> > >
> > >
> > >> Hi,
> > >> I am trying to write a query which reports the
> > devices >> used by the database, and the amount of space
> > on each >> device (for 12.5 sybase version). I found
> > >>
> > >> 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
> > >>
> > >> 2. select sysdevices.name as DevName,
> > sysdatabases.name as >> BName,
> > >> 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
> > >> >> could you please let me know which one is correct?
> > I could >> not get the logic behind "sysdevices.low <=
> > sysusages.size >> + vstart" and "sysdevices.high >=
> > sysusages.size + vstart >> -1" in first query. If some
> > one explains how this logic >> works that will be great.
> > >>
> > >> Regards,
> > >> Shivaraj