IDUG DB2 2016 SQL Challenge Week 2

IDUG DB2 2016 SQL Challenge Week 2

A RANDOM PARTICIPANT IN THIS MONTH’S CHALLENGE WILL RECEIVE A $200USD COUPON TO USE TOWARDS TAKING IBM CERTIFICATION EXAMS! POST YOUR ANSWERS IN THE COMMENTS OF THIS ARTICLE FOR A CHANCE. A winner will be chosen at random from the participants at the end of the month.

Whether or not you have participated in week one of our online SQL challenge you can certainly participate this week. We are going to stick with the system-period temporal theme. You can use the tables you created last week, or use the DDL at the end of this article to make a table this week. Actually, you don’t even need to create a table if you don’t want to!

Let’s query a system-period temporal table relative to a point in time. In other words, let’s time travel? In order to keep things simple we’re going to query our table using the point in time represented by the CURRENT TIMESTAMP special register. Now, if you don’t think that is not really time travel please keep in mind that with system-period temporal tables the only “current” is to select from the base table without specifying any sort of time travel in the query. Remember, we are traveling at the speed of light, and in the database right now was right now just then! With that in mind we are going to query the table four different ways.

  • SELECT the current data (remember what I just said about how NOT to time travel).
  • SELECT the data as of the CURRENT TIMESTAMP special register using a period-specification.
  • SELECT the data as of the CURRENT TIMESTAMP special register and also utilizing a special register to enable the AS OF time travel specification.
  • SELECT the data as of the CURRENT TIMESTAMP special register by manually coding the necessary table access and predicates.

Post your answers in the comments for this article or email them to kurt.struyf@lone-star.be . Don’t be shy! You can be a winner regardless of your response! You can do this in DB2 for z/OS or DB2 for LUW. If you are trying this on DB2 for z/OS you will need DB2 11 for z/OS for the solution to #3.

Temporal Table References

Are you new to system-period temporal tables? Use these references:

How to Leverage DB2’s Automated Time Travel Queries and Temporal Tables

Using the new DB2 for z/OS Temporal Special Registers

Temporal and Archive - How to Manage Data Over Time

Understanding the Complexity of Time Travel

Temporal Table DDL

This is DDL should work for either DB2 for z/OS or DB2 for LUW.


CREATE TABLE EMP2 (
EMPNO                                CHAR(6) NOT NULL,
ROW_START      TIMESTAMP(12)
GENERATED ALWAYS AS ROW BEGIN NOT NULL,
ROW_END          TIMESTAMP(12)
GENERATED ALWAYS AS ROW END   NOT NULL,
RW_ID TIMESTAMP(12)
GENERATED ALWAYS
AS TRANSACTION START ID IMPLICITLY HIDDEN,
LASTNAME         VARCHAR(15) NOT NULL,
WORKDEPT         CHAR(3) WITH DEFAULT NULL,
PERIOD SYSTEM_TIME(START_TS, END_TS));
 
CREATE TABLE EMP2_HIST LIKE EMP2;


ALTER TABLE EMP2
ADD VERSIONING USE HISTORY TABLE EMP2_HIST;

1 Comment

Select

April 21, 2016 02:29 AM by Anantha Narayanan

As per the select statements.

-- I get all the values for current timestamp

-- based on the system time (period) we see only a limited number of rows fetched

Recent Stories
Lessons Learned of Locking and Latches by Adrian Burke

Db2 for z/OS locking basics

A Beginners Guide to Locks, Latches, Claims and Drains in DB2 for z/OS by Steve Thomas