Table Space Standards and Naming

  1. Devote separate tablespace for each large base history fact and optionally by time horizon (e.g. year) for range partitioned tables. This will facilitate backup/restore and archival migration to different levels of storage.
  2. Use separate tablespaces for current base facts.
  3. Separate MQTs by fact family. Share tablespaces among small MQTs of the same family with the same MDC and partitioning requirements.
  4. Share dimension and reference spaces, separating in groups, e.g. Product, Location, Time.
  5. Use a separate common space for all "distributed by replication" reference and dimension tables.

A naming convention for each tbspace can be synthesized as follows: TS in the form TStfffpssneexxx:

TS
literal designating all table space names. Can also be adapted to differentiate SMS vs DMS and Automatic options, e.g. TS, TD or TA
Type t
Letter designator of whether the tablespace is temporary (T), User Temporary (U), Regular Data (D), Index (I) or System (S) for reorg or utility use
Family fff
designates the dimension group, fact family or application area. Three to six letters can be used, e.g. SLS, PRDT, INVADJ
Purpose
One letter designating if this is a Base table space (B) or MQT (M) or Replicated (R) or Utility/Support space (U)
Page Size
Normally 08 for our system but could be 04 or 16 if such page sizes are ever adopted.
Node Group
THe node number for single partition tablespaces, P for parallel and A for all partitions.
Extent Size
Usually 32, but for MDC tables can also be 04, 08, 16.
Suffix
Optional suffix, typically numeric, to serialize similar objects or distinguish archival time ranges
VDM Access: