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.

Where's my table stored?

3 posts in General Discussion Last posting was on 2013-01-30 17:14:32.0Z
Simon Posted on 2013-01-23 18:46:47.0Z
Sender: 3daa.51002f13.1804289383@sybase.com
From: Simon
Newsgroups: sybase.public.ase.general
Subject: Where's my table stored?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <51003017.3e2a.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 23 Jan 2013 10:46:47 -0800
X-Trace: forums-1-dub 1358966807 172.20.134.41 (23 Jan 2013 10:46:47 -0800)
X-Original-Trace: 23 Jan 2013 10:46:47 -0800, 172.20.134.41
Lines: 18
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31674
Article PK: 1159273

Good evening,

Does anybody know whether there's a way to know on which
'device' a table is stored?

Let's stay I have a database create as
device1=10m, device2=10m, device3=10m and I have a 15m
table.
I'd like to know where these table is stored.

I have also another question - probably obvious for you
gurus - is it possible for a table to span on several
devices?

Thanks for your advices.


Simon


Bret Halford Posted on 2013-01-23 18:56:41.0Z
From: Bret Halford <bret.halford@sap.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:17.0) Gecko/20130107 Thunderbird/17.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Where's my table stored?
References: <51003017.3e2a.1681692777@sybase.com>
In-Reply-To: <51003017.3e2a.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <51003269@forums-1-dub>
Date: 23 Jan 2013 10:56:41 -0800
X-Trace: forums-1-dub 1358967401 172.20.134.152 (23 Jan 2013 10:56:41 -0800)
X-Original-Trace: 23 Jan 2013 10:56:41 -0800, vip152.sybase.com
Lines: 118
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31675
Article PK: 1159274


On 1/23/2013 11:46 AM, Simon wrote:
> Good evening,
>
> Does anybody know whether there's a way to know on which
> 'device' a table is stored?
>
> Let's stay I have a database create as
> device1=10m, device2=10m, device3=10m and I have a 15m
> table.
> I'd like to know where these table is stored.
>
> I have also another question - probably obvious for you
> gurus - is it possible for a table to span on several
> devices?
>
> Thanks for your advices.
>
>
> Simon
>

Yes, although there usually is little reason to need
to know. tables can certainly span multiple devices.
Space on disk is allocated to table in 8-page extents
(if the page size is 2k, extents are 16KB chunks of disk
space).

The DBCC USEDEXTENTS command will dump information about each
allocated extent in a record format that looks like this:


dbcc traceon(3604)
go
dbcc usedextents(dbname,0,0)
go

OAMPG: 328 Extent ID 2384 on allocation page 2304
Object ID is 5
Index ID is 0
Partition ID is 5
Allocation bitmap: 0xff ( 2384 2385 2386 2387 2388 2389 2390 2391 )
Dealloc bitmap: 0x00 ( )
Forward bitmap: 0x00 ( )
Reserve bitmap: 0x00 ( )
status: 0x00 (EX_DEALL_NOSTATUS )
Sort bit is off
Reference bit is off
Spacebits bitmap: 0x88888888
Page: 2384 (0x08 (Less than 100% occupied))
Page: 2385 (0x08 (Less than 100% occupied))
Page: 2386 (0x08 (Less than 100% occupied))
Page: 2387 (0x08 (Less than 100% occupied))
Page: 2388 (0x08 (Less than 100% occupied))
Page: 2389 (0x08 (Less than 100% occupied))
Page: 2390 (0x08 (Less than 100% occupied))
Page: 2391 (0x08 (Less than 100% occupied))
Buddy Page for extent (se_extbuddypage): 0


If you run dbcc usedextents and save the output in a file, you
can then filter out the extentid, object id, and indexid using a simple
awk script:

---------------------
isql -Usa -P -o usedextents.out << EOF
dbcc traceon(3604)
go
dbcc usedextents(qptunedb,0,0)
go
EOF

awk '$1=="OAMPG:" {printf ("%d\t", $5) } \
$1=="Partition" {printf (" %d \t", $4)} \
$1=="Index" {printf (" %d \n", $4)} ' usedextents.out > awk.out

------------------------

This yields simple tabular data that looks like

174064 591338140 2
174096 591338140 2

i.e (partition 591338140 index 2 is allocated extent 174064)

You can then import that data into ASE and
use it in various queries to answer questions like yours.

-----------------------------------------------------
isql -Usa -P << EOF
use tempdb
go
create table usedextents (extent int, objectid int, indid tinyint)
go
!! bcp tempdb..usedextents in awk.out -Usa -P -c

-- sample query for 15.x and above

select distinct
d.name as "devices containing table"
from
usedextents x,
master..sysusages u,
master..sysdevices d
where
u.dbid = <dbid>
and d.name = <devicename>
and x.extent between u.lstart and u.lstart+size
and u.vdevno = d.vdevno
and x.objectid = object_id("<mytable>")
go



--
Bret Halford
Support Architect, ASE Tactical Support Team, AGS Primary Support
Sybase, Inc., an SAP Company
385 Interlocken Crescent, Suite 300, Broomfield, Colorado, 80021


Simon Posted on 2013-01-30 17:14:32.0Z
Sender: 1a9d.510953fc.1804289383@sybase.com
From: Simon
Newsgroups: sybase.public.ase.general
Subject: Re: Where's my table stored?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <510954f8.1af8.1681692777@sybase.com>
References: <51003269@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 30 Jan 2013 09:14:32 -0800
X-Trace: forums-1-dub 1359566072 172.20.134.41 (30 Jan 2013 09:14:32 -0800)
X-Original-Trace: 30 Jan 2013 09:14:32 -0800, 172.20.134.41
Lines: 127
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31685
Article PK: 1307381

Wouah Bret,

Thanks a lot. That's why I love this forum

Simon

> On 1/23/2013 11:46 AM, Simon wrote:
> > Good evening,
> >
> > Does anybody know whether there's a way to know on which
> > 'device' a table is stored?
> >
> > Let's stay I have a database create as
> > device1=10m, device2=10m, device3=10m and I have a 15m
> > table.
> > I'd like to know where these table is stored.
> >
> > I have also another question - probably obvious for you
> > gurus - is it possible for a table to span on several
> > devices?
> >
> > Thanks for your advices.
> >
> >
> > Simon
> >
>
> Yes, although there usually is little reason to need
> to know. tables can certainly span multiple devices.
> Space on disk is allocated to table in 8-page extents
> (if the page size is 2k, extents are 16KB chunks of disk
> space).
>
> The DBCC USEDEXTENTS command will dump information about
> each allocated extent in a record format that looks like
> this:
>
>
> dbcc traceon(3604)
> go
> dbcc usedextents(dbname,0,0)
> go
>
> OAMPG: 328 Extent ID 2384 on allocation page 2304
> Object ID is 5
> Index ID is 0
> Partition ID is 5
> Allocation bitmap: 0xff ( 2384 2385 2386 2387 2388 2389
> 2390 2391 ) Dealloc bitmap: 0x00 ( )
> Forward bitmap: 0x00 ( )
> Reserve bitmap: 0x00 ( )
> status: 0x00 (EX_DEALL_NOSTATUS )
> Sort bit is off
> Reference bit is off
> Spacebits bitmap: 0x88888888
> Page: 2384 (0x08 (Less than 100% occupied))
> Page: 2385 (0x08 (Less than 100% occupied))
> Page: 2386 (0x08 (Less than 100% occupied))
> Page: 2387 (0x08 (Less than 100% occupied))
> Page: 2388 (0x08 (Less than 100% occupied))
> Page: 2389 (0x08 (Less than 100% occupied))
> Page: 2390 (0x08 (Less than 100% occupied))
> Page: 2391 (0x08 (Less than 100% occupied))
> Buddy Page for extent (se_extbuddypage): 0
>
>
> If you run dbcc usedextents and save the output in a file,
> you can then filter out the extentid, object id, and
> indexid using a simple awk script:
>
> ---------------------
> isql -Usa -P -o usedextents.out << EOF
> dbcc traceon(3604)
> go
> dbcc usedextents(qptunedb,0,0)
> go
> EOF
>
> awk '$1=="OAMPG:" {printf ("%d\t", $5) } \
> $1=="Partition" {printf (" %d \t", $4)} \
> $1=="Index" {printf (" %d \n", $4)} ' usedextents.out >
> awk.out
>
> ------------------------
>
> This yields simple tabular data that looks like
>
> 174064 591338140 2
> 174096 591338140 2
>
> i.e (partition 591338140 index 2 is allocated extent
> 174064)
>
> You can then import that data into ASE and
> use it in various queries to answer questions like yours.
>
> -----------------------------------------------------
> isql -Usa -P << EOF
> use tempdb
> go
> create table usedextents (extent int, objectid int, indid
> tinyint) go
> !! bcp tempdb..usedextents in awk.out -Usa -P -c
>
> -- sample query for 15.x and above
>
> select distinct
> d.name as "devices containing table"
> from
> usedextents x,
> master..sysusages u,
> master..sysdevices d
> where
> u.dbid = <dbid>
> and d.name = <devicename>
> and x.extent between u.lstart and u.lstart+size
> and u.vdevno = d.vdevno
> and x.objectid = object_id("<mytable>")
> go
>
>
>
> --
> Bret Halford
> Support Architect, ASE Tactical Support Team, AGS Primary
> Support Sybase, Inc., an SAP Company
> 385 Interlocken Crescent, Suite 300, Broomfield, Colorado,
> 80021