Page tree
Skip to end of metadata
Go to start of metadata

Here is a method for answering the question "which objects are on (have allocations on) device Y?" in ASE 15.0 and above.

ASE divides each device up into 256-page allocation units; each allocation unit consists of 32 8-page extents.  Each extent can be allocated to only one index id for only one object or partition id.  Index id 0 is used for pages containing actual data rather than index keys.  Index id 255 is used for LOB data (text, image, unitext, java classes).

ASE has an undocumented command dbcc usedextents that dumps information about each extent structure in the database.   The output for one extent structure looks like this:

set switch on 3604
go
dbcc usedextents(dbname,0,0)
go
[…]
Allocation bitmap: 0xff ( 2384 2385 2386
OAMPG: 328 Extent ID 2384 on allocation page 2304
Object ID is 5
Index ID is 0
Partition ID is 52387 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(<database_name>, 0,0) 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_output.txt << EOF
set switch on 3604
go
dbcc usedextents(mydatabase,0,0)
go
EOF

awk '$1=="OAMPG:" {printf ("%d\t", $5) }\
$1=="Object" {printf (" %d \t", $4)}\
$1=="Index" {printf (" %d \n", $4)}' usedextents_output.txt > usedextents_awk.txt

This yields simple tabular data with one line per extent

[…]
174064 591338140 2
174096 591338140 2
[…]

(extent 174064 is allocated to object  591338140 index 2

You can then import that data into a table in ASE and then use various queries to answer questions about allocation such as "what tables are on device data_dev_1".

isql -Usa -P << EOF
use tempdb
go
create table usedextents (extent int, objectID int, indid tinyint)
go
exit
EOF
bcp tempdb..usedextents in usedextents_awk.txt -Usa -P -c

The following query is for 15.x and above.  Prior versions of ASE used a different relation between sysusages and sysdevices.

Which user tables in database "mydatabase" are on device "datadev1"?

declare @dbname varchar(255)
select  @dbname = "mydatabase" --  your db name here
declare @devicename  varchar(255)
select  @devicename = "datadev" -- your device name here

select distinct
	x.objectID as "objectID",
	object_name( objectID, db_id(@dbname) ) as "name"
from
	tempdb..usedextents x,
	master..sysusages u,
 	master..sysdevices d
where
	u.dbid = db_id(@dbname)
and	d.name = @devicename
and	x.extent between u.lstart and (u.lstart+u.size)
and	u.vdevno = d.vdevno
and	x.objectID > 99 -- User tables only
order by
	object_name(x.objectID, db_id(@dbname))
  • No labels

1 Comment

  1. Anonymous

    Seriously nice. Now Sybase is matching with other DBMS with more and more documentations.