Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
former_member188958
Active Contributor

Which device is my table on?

Every so often I see a question from someone interested in knowing which tables are stored on which devices, or which devices have allocations for table X.  I'm not sure how the information would be useful as the information is dynamic.  ASE handles allocation automatically and in general makes no attempt to keep all the allocations for an object on the same device (though user defined segments can be used to keep a table entirely on a device).  All the same, here is a method for answering the question "which objects are on (have allocations on) device Y?".
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 diagnostic command dbcc usedextents that dumps information about each extent structure in the database.   The command reads every allocation page in the database. It is fairly fast and does minimal blocking;  I have no concerns over running it on production systems. The output for a single 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
[…]
(This shows that extent 174064 is allocated to the table with object id  591338140  for use by the index with indid 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))

12 Comments