Storage group and table space media attributes
Automatic storage table spaces inherit media attribute values, device read rate and data tag attributes, from the storage group that the table spaces are using by default.
- OVERHEAD
- This attribute specifies the I/O controller time and the disk seek and latency time in milliseconds.
- DEVICE READ RATE
- This attribute specifies the device specification for the read transfer rate in megabytes per second. This value is used to determine the cost of I/O during query optimization. If this value is not the same for all storage paths, the number should be the average for all storage paths that belong to the storage group.
- DATA TAG
- This attribute specifies a tag on the data in a particular storage group, which WLM can use to determine the processing priority of database activities.
Attribute | Default setting |
---|---|
DATA TAG | NONE |
DEVICE READ RATE | 100 MB/sec |
OVERHEAD | 6.725 ms |
When creating an automatic storage table space, you can specify a tag that identifies data contained in that table space. If that table space is associated with a storage group, then the data tag attribute on the table space overrides any data tag attribute that may be set on the storage group. If the user does not specify a data tag attribute on the table space and the table space is contained in a storage group, the table space inherits the data tag value from the storage group. The data tag attribute can be set for any regular or large table space except the catalog table space (SQL0109N). The data tag attribute cannot be set for temporary table spaces and returns the SQL0109N message error.
TRANSFERRATE = ( 1 / DEVICE READ RATE ) * 1000 / 1024000 * PAGESIZE
PAGESIZE | TRANSFERRATE |
---|---|
4 KB | 0.04 milliseconds per page read |
8 KB | 0.08 milliseconds per page read |
16 KB | 0.16 milliseconds per page read |
32 KB | 0.32 milliseconds per page read |
The data tag, device read rate, and overhead media attributes for automatic storage table spaces can be changed to dynamically inherit the values from its associated storage group. To have the media attributes dynamically updated, specify the INHERIT option for the CREATE TABLESPACE or ALTER TABLESPACE statement.
select tbspace,
cast(case when a.datatag = -1 then b.datatag else a.datatag end as smallint)
eff_datatag,
cast(case when a.overhead = -1 then b.overhead else a.overhead end as double)
eff_overhead,
cast(case when a.transferrate = -1 then
(1 / b.devicereadrate) / 1024 * a.pagesize else a.transferrate end as double)
eff_transferrate
from syscat.tablespaces a left outer join syscat.stogroups b on a.sgid = b.sgid
If you upgrade to 10.1, the existing table spaces retain their overhead and transferrate settings, and the overhead and device read rate attributes for the storage group are set to undefined. The newly created table spaces in a storage group with device read rate set to undefined use the Db2® database defaults that were defined when the database was originally created. If the storage group's media settings have a valid value, then the newly created table space will inherit those values. You can set media attributes for the storage group by using the ALTER STOGROUP statement. For non-automatic table spaces, the media attributes are retained.