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 firstname.lastname@example.org . 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:
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,
GENERATED ALWAYS AS ROW BEGIN NOT NULL,
GENERATED ALWAYS AS ROW END NOT NULL,
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;