Table Space Standards and Naming
- 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.
- Use separate tablespaces for current base facts.
- Separate MQTs by fact family. Share tablespaces among small MQTs of the same family with the same MDC and partitioning requirements.
- Share dimension and reference spaces, separating in groups, e.g. Product, Location, Time.
- 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:
- Log in to post comments
