Many IT systems require historical data to be kept, in addition to the current status information. Db2 10 for z/OS introduced temporal data support to provide this functionality, whereby the historical data is automatically maintained. This intensive course provides an in-depth look at how temporal tables may be created and exploited. This course also covers the concept and usage of archive tables introduced with Db2 11 for z/OS.
DB2 for z/OS: Exploiting Temporal Tables
A good understanding of Db2 and experience of using SQL.
- identify applications that may benefit from using temporal tables
- describe how temporal tables may be used to automatically handle historical data by using data versioning
- explain the difference between system-period and application-period data versioning
- define and use system and application-period temporal tables
- describe and use the new period-specification clauses in the SQL language
- use new utility options specific to temporal tables
- describe and use archive tables'.
Introduction to Temporal Tables
The need for historical data; traditional methods for handling historical data; temporal table support; system-period temporal tables; application-period temporal tables.Defining System-Period Temporal Tables
Creating system-period temporal tables; Defining the system-period temporal table; ROW BEGIN, ROW END & TRANSACTION START ID columns; Defining the history table; Enabling system-period data versioning; Enabling an existing table for system-period data versioning; System-period data versioning example; Catalog tables.Defining Application-Period Temporal Tables
Defining the application-period temporal table; Overlapping data; BUSINESS_TIME WITHOUT OVERLAPS; Modifying existing tables; Index creation; Catalog tables.SQL Extensions for Temporal Tables
SYSTEM_TIME period-specification; FOR SYSTEM_TIME AS OF . . .; FOR SYSTEM_TIME FROM . . . TO . . .; FOR SYSTEM_TIME BETWEEN . . . AND . . .; BUSINESS_TIME period specification; FOR BUSINESS_TIME AS OF . .; FOR PORTION OF BUSINESS_TIME; Traditional UPDATE; UPDATE for time period - qualifying rows; Row category and behaviour; Row behaviour example (UPDATE); Row behaviour example (DELETE); Reasons to be cautious!!; Special registers and BIND options (DB2 11).Archive Tables
Archiving old data; Archive tables (Db2 11 for z/OS); Defining archive tables; NSERT, UPDATE & MERGE behaviour; SELECT behaviour.Db2 Utility Options for Temporal Tables
Utility restrictions; LISTDEF additions; LISTDEF behaviour examples; RECOVER utility; LOAD utility.Practical Exercises
Defining temporal tables; manipulating and reporting data in temporal tables.
Face-to-face learning in the comfort of our quality nationwide centres, with free refreshments and Wi-Fi.
Find dates and prices
Online booking is currently not available for this course, to find out more please call us on 01753 898320 or email us at email@example.com to discuss how we can help.
Fully accredited to ensure we provide the highest possible standards in learning