Friday, January 29, 2016

DB2 for z/OS: Thoughts on History and Archive Tables

I'll state up front that I'm using the terms "history table" and "archive" table not in a generic sense, but as they have technical meaning in a DB2 for z/OS context. A history table is paired with a "base" table that has been enabled for system-time temporal support (introduced with DB2 10 for z/OS), and an archive table goes with a base table that has been enabled for DB2-managed archiving (also known as "transparent archiving" -- a feature delivered with DB2 11). Last week, I posted to this blog an entry that explored the use of system-time temporal and DB2-managed archiving as alternative solutions for different data archiving scenarios. Today I want to draw attention to some considerations related to history and archive tables.

Actually, it's not so much history and archive tables themselves that are on my mind. Tables are an embodiment of logical database design. What I'm thinking about is physical database design. You see, history tables and archive tables have to be logically identical to their associated base tables -- same columns, in the same order and with the same data type. Must history and archive tables be identical to the associated base table in a physical sense? NO. That physical-difference flexibility is something that a DB2 DBA should definitely have in mind as he or she contemplates enabling system-time temporal support or DB2-managed archiving for a base table.

Here's an example of what I'm talking about: suppose that a table you want to enable for system-time temporal support or DB2-managed archiving resides in a traditional segmented table space. Does that mean that the associated history or archive table has to reside in such a table space? NO. It could be that a universal range-partitioned or partition-by-growth table space would be a better choice for the base table's history or archive table (that 'or' means that a single table cannot be enabled for both system-time temporal support and DB2-managed archiving -- it's one or the other, and my previous blog entry was intended to help you make that choice). Think about it. There's a good chance that a history or an archive table will end up being a lot larger than its associated base table, and partitioned table spaces can hold a very large amount of data (up to 128 TB).

Indexes are another aspect of physical database design that can be different, in a number of ways, for history or archive tables versus their associated base tables. Is there an index on a base table that would not be so useful (i.e., that would not have benefits in excess of its CPU and disk space and DBA-time costs) for an associated history or archive table? Fine -- don't define that index for the history or archive table. Similarly, would an index NOT defined on a base table be useful for an associated history or archive table? Fine -- define it for the history or archive table. Even for an index you want on both a base table and its associated history or archive table, should the index page size be different? Should index compression be used or not used? Should data rows in a history or archive table be clustered differently versus rows in the associated base table?

And you can go right on down the line with other aspects of physical database design. It might make sense to assign a base table's table space to a PGSTEAL(NONE) buffer pool, but would that be a good choice for the table space of an associated history or archive table? If LOCKSIZE(ROW) is needed for a base table's table space, would page-level locking be more appropriate for an associated history or archive table? If hash organization of data delivers performance benefits for a base table, would cluster-organized data work out better for the associated history or archive table?

You get the idea. What you want to do, in considering enablement of system-time temporal support or DB2-managed archiving for a base table, is keep in mind that LOGICAL equivalence of the base table and its history table, or the base table and its archive table, does not require PHYSICAL equivalence between these tables. Think about the physical database design characteristics that would be optimal for the base table, and determine whether those or DIFFERENT physical database design characteristics would be right for the associated history or archive table.

No comments:

Post a Comment