These are the changes that have been made to system procedures to support virtually-hashed tables:
sp_addsegment – you cannot create a segment on a device that already has an exclusive segment. If you attempt to do so, you see an error message similar to:
A segment with a virtually hashed table exists on device orders_dat.
sp_extendsegment – you cannot extend a segment on a device that already has an exclusive segment, and you cannot extend an exclusive segment on a device that has another segment.
For example, if you attempt to extend segment orders_seg on a device orders.dat, which already has an exclusive segment, you see an error message similar to:
A segment with a virtually hashed table exists on device orders.dat.
If you attempt to extend exclusive segment orders_seg on device orders.dat, which has other segments, you see an error message similar to:
You cannot extend a segment with a virtually hashed table on device orders.dat, because this device has other segments.
sp_placeobject – you cannot use sp_placeobject on a virtually-hashed table, and you cannot place other objects on an exclusive segment. For example, if you attempt to place a virtually-hashed table named orders on a segment, you see an error message similar to:
sp_placeobject is not allowed for orders, as it is a virtually hashed table.
If you attempt to place object t on segment myseg, which contains a virtually-hashed table, you see an error message similar to:
Segment myseg has a virtually hashed table; therefore, you cannot place an object on this segment.
sp_chgattribute – does not allow you to change attributes for virtually-hashed tables. For example, if you attempt to change the attributes for table order_line (a virtually-hashed table) like this:
sp_chgattribute 'order_line', 'exp_row_size', 1
Adaptive Server issues an error message similar to:
sp_chgattribute is not allowed for order_line, as it is a virtually hashed table.
sp_help – for virtually-hashed table, reports:
That a table is virtually-hashed with this message:
Object is Virtually Hashed
The hash_key_factors for the table with a message using this syntax:
column_1:hash_factor_1, column_2:hash_factor_2..., max_hash_key=max_hash_value
For example:
attribute_class attribute int_value char_value comments --------------------- --------------- ------------------------ -------------------------------------- ----------- hash clustered tables hash key factors NULL id:10.0, id2:1.0, max_hash_key=1000.0 NULL
sp_spaceused – the unused columns of sp_spaceused report the empty data pages in the hash region of virtually-hashed tables. unused reports the sum of space occupied by pages that are reserved but not allocated, and the amount of space occupied by pages that are preallocated in the hash region but not used. The data column reports the amount of space for data pages in use.
If you issue sp_spaceused without parameters, it reports the space usage for the database. If there are virtually-hashed tables in the database, it computes the total space empty pages occupy in the hash regions for all tables. The unused column reports this calculated value, but the data column reports the total space minus the amount of space occupied by empty pages.
For example, if you create this table and run sp_spaceused to investigate its space usage:
create table order_line( id int, id2 int, name char(100), primary key using clustered (id, id2) = (10, 1) with max 10000 key ) on myseg
sp_spaceused reports this:
name rowtotal reserved data index_size unused ------------- -------- ----------- ------ ------------ ------- order_line 0 1146KB 2KB 2KB 1142KB
Because the data pages in the hash region are empty, sp_spaceused includes the
space they occupy (1112 KB) when computing the value for unused
.