"Fun with SQL" Challenge 2016!

A RANDOM PARTICIPANT IN THIS MONTH’S CHALLENGE WILL RECEIVE A $200USD COUPON TO USE TOWARDS TAKING IBM CERTIFICATION EXAMS!

IDUG is dedicated to the support of not only database administrators, but also developers who use our databases. This means that we’re dedicated to sharing information about the many features of SQL and of DB2 in particular. Why is this important? Well, for some very basic reasons.

  • Delivering SQL-based solutions can happen quickly

Developers can generally construct and test SQL quickly. Therefore, if the SQL can incorporate significant programming features then the resulting application processing can also be developed and tested quickly.

  • SQL is portable

SQL runs on the server to which it is directed. Therefore, if more application logic can be pushed into an SQL statement, then that statement can be placed into a variety of application programs or embedded in various tools, on a variety of platforms. This gives an enterprise leverage in selecting platforms.

  • Advanced SQL can be a performance advantage

This performance advantage is realized when a process that requires multiple database calls can be consolidated into a single database call. Reducing the calls to the database, the network message traffic, and quantity of data sent by the database and processed by the application can result in a dramatic performance improvement of a data intensive process.

SQL is a Programming Language

Have read you the SQL manuals lately? There is one for DB2 for z/OS and two for DB2 for LUW. Many people use these reference as just that, references. However, I frequently encourage people to actually read the manuals. In the very least you should read the “SQL Queries” chapters (chapter 6 in the DB2 for LUW SQL Reference Volume 1, chapter 4 in the DB2 for z/OS SQL Reference). It is absolutely amazing what you can do with the SQL language in DB2:

  • Subqueries, joins, anti-joins
  • Complex built-in functions and user-defined functions
  • Online analytical functionality
  • Aggregation
  • Recursion, looping
  • XML processing and generation
  • JSON processing
  • SQL PL programming for stored procedures, functions, and triggers

Getting a good SQL education can be a bit of a challenge. There are some good classes out there and plenty of examples to be found on the internet. However, some of the best education possible is to just give it a try. This is actually really easy for developers to do as DB2 Express-C and IBM Data Studio can both be downloaded for free from the IBM web site, and the DB2 sample database can be created for immediate testing of various SQL capabilities.

The Dojo Concept

The concept of the SQL Dojo was brought to IDUG and the IDUG Content Committee by Content Committee member Kurt Struyf. Kurt had attended a local application developer’s user group meeting and was blown away by the comradery and support brought out by the Dojo exercise. As IDUG was coming up with more ideas to support application developers, Kurt had the brilliant idea to bring the dojo concept to SQL and IDUG. It was an instant success and we’re expanding on it every year.

The concept of a dojo is quite simple. A group of developers (and/or DBAs and Sysprogs) gather together and are individually and randomly assigned to teams. Then a single computer is used on a single display with a portable keyboard and mouse to present a series of programming challenges. Teams are chosen randomly and each team is given just a few minutes, to solve the programming challenge. If the team solves the programming challenge, they move on to the next one; if they don’t solve the challenge before their time runs out, the same challenge (including previous teams’ unfinished work) moves on to the next randomly selected team. Once a team has been selected their number immediately goes back into the pool, and so they could be selected many times during the dojo and must stay engaged. Now comes the real fun; not only does a participant get to meet and work with other people in their group, but participants are also welcome to shout suggestions to the team working on the challenge in an effort to help them out. This can result in a very active and funny discussion with a timer running out, multiple people shouting out suggestions, and the team racing to implement the solution and beat the clock. The result is a fun and cooperative event where people with a common interest meet others, network, work cooperatively, and learn something about application development. It’s great fun and a great learning opportunity.

Kurt brought the SQL Dojo concept to the IDUG North America Technical Conference in 2013. We called it “Fun with SQL” and it was an instant hit! We’ve been expanding on it ever since. Last year at this time we did our first online SQL challenge. You can review those challenges here:

April 2015 Online SQL Challenge 

April 2015 “Fun with SQL” Challenge 2 

April 2015 “Fun with SQL” third Challenge 

April 2015 “Fun with SQL” fourth Challenge 

The April 2016 SQL challenge

This year we are going to focus on system-period temporal tables and time travel SQL. This feature was introduced in DB2 10 for LUW and z/OS and is quickly becoming an extremely popular feature for automating the historical and auditing requirements of modern applications. However, did you know that it is also possible to retrofit this feature into existing applications? I do, as I’ve done it! So, let’s get started, but to get started we’re going to have to create some system-period temporal tables! You can do all of this in either DB2 for LUW or DB2 for z/OS. Only the table names change a little bit. The employee table in DB2 for LUW is called “Employee” and in DB2 for z/OS it is called “EMP”. In the DDL examples here you can eliminate everything after  the column definitions from the DDL examples if you are working with DB2 for LUW.

A RANDOM PARTICIPANT IN THIS MONTH’S CHALLENGE WILL RECEIVE A $200USD COUPON TO USE TOWARDS TAKING IBM CERTIFICATION EXAMS!

During the first challenge we will be setting up our environment and make sure that everyone starts with the same structure and understanding. That’s why we will ask you to create 3 very similar tables, please follow the steps as described below as you will notice, (hopefully). There is a slight difference in behavior, this can impact your success rate in your real life environment.

Step 1: Create a table emp2 using the following DDL .

CREATE TABLE EMP2   
   (EMPNO                CHAR(6) FOR SBCS DATA NOT NULL,         
    FIRSTNME             VARCHAR(12) FOR SBCS DATA NOT NULL,     
    MIDINIT              CHAR(1) FOR SBCS DATA NOT NULL,         
    LASTNAME             VARCHAR(15) FOR SBCS DATA NOT NULL,     
    WORKDEPT             CHAR(3) FOR SBCS DATA WITH DEFAULT NULL,
    PHONENO              CHAR(4) FOR SBCS DATA WITH DEFAULT NULL,
    HIREDATE             DATE WITH DEFAULT NULL,                  
    JOB                  CHAR(8) FOR SBCS DATA WITH DEFAULT NULL,
    EDLEVEL              SMALLINT WITH DEFAULT NULL,             
    SEX                  CHAR(1) FOR SBCS DATA WITH DEFAULT NULL,
    BIRTHDATE            DATE WITH DEFAULT NULL,                 
    SALARY               DECIMAL(9, 2) WITH DEFAULT NULL,        
    BONUS                DECIMAL(9, 2) WITH DEFAULT NULL,        
    COMM                 DECIMAL(9, 2) WITH DEFAULT NULL)        
  PARTITION BY SIZE EVERY 4 G                                     
  AUDIT NONE                                                     
  DATA CAPTURE NONE                                              
  CCSID      EBCDIC                                              
  NOT VOLATILE                                               
  APPEND NO  ;              

Step 2: turn table emp2 into a system temporal table, the extra column names we want you to use are :

ROW_START
ROW_END
RW_ID

Hint you will need 4 alter statements. Add the history table to it (hint you need  a create statement and another alter statement).

Step 3: Create a table emp3 using the full create DDL (don’t use create like emp2) BUT make sure that you create it with the system-period temporal columns included. So the difference between emp2 and emp3 is that emp2 was created as a “normal” table and then altered into a temporal table. Where emp3 will immediately be created as a system-period temporal table.

Add the history table to it ( hint you need a create statement and another alter statement). At this moment emp2 and emp3 look the same and are both system-period temporal

Step 4: Insert data into emp2 and emp3 (use these provided insert statements at the end of this article).

Step 5: Do you notice any difference between emp2 and emp3 behavior? Describe your observations as answer 1 send together with other questions to kurt.struyf@lone-star.be or post in the comments for this article.

Step 6: Create a table emp4 using the following DDL.

CREATE TABLE EMP4   
   (EMPNO                CHAR(6) FOR SBCS DATA NOT NULL,         
    FIRSTNME             VARCHAR(12) FOR SBCS DATA NOT NULL,     
    MIDINIT              CHAR(1) FOR SBCS DATA NOT NULL,         
    LASTNAME             VARCHAR(15) FOR SBCS DATA NOT NULL,     
    WORKDEPT             CHAR(3) FOR SBCS DATA WITH DEFAULT NULL,
    PHONENO              CHAR(4) FOR SBCS DATA WITH DEFAULT NULL,
    HIREDATE             DATE WITH DEFAULT NULL,                 
    JOB                  CHAR(8) FOR SBCS DATA WITH DEFAULT NULL,
    EDLEVEL              SMALLINT WITH DEFAULT NULL,             
    SEX                  CHAR(1) FOR SBCS DATA WITH DEFAULT NULL,
    BIRTHDATE            DATE WITH DEFAULT NULL,                 
    SALARY               DECIMAL(9, 2) WITH DEFAULT NULL,        
    BONUS                DECIMAL(9, 2) WITH DEFAULT NULL,        
    COMM                 DECIMAL(9, 2) WITH DEFAULT NULL)        
  PARTITION BY SIZE EVERY 4 G                                    
  AUDIT NONE                                                     
  DATA CAPTURE NONE                                              
  CCSID      EBCDIC                                              
  NOT VOLATILE                                                    
  APPEND NO  ;                 

Step 7: Insert data into emp4 (using provide insert statements at the end of this article).

Step 7: Turn table emp4 into a system-period temporal table, similar to what you did for emp2, the extra column names we want you to use are :

ROW_START
ROW_END
RW_ID

 Hint you will need 4 alter statements. Add the history table to it (hint you need  a create statement and another alter statement).

Step 9: Do you notice any difference between emp2 and emp4 behavior? What happens if you try to select from emp2 and emp4? Any differences in behavior or results? Describe your observations as answer 2 and send together with other questions to kurt.struyf@lone-star.be or post in the comments of this article.

INSERTS example:

INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000010','CHRISTINE','I','HAAS','A00','3978','01.01.1965','PRES ',18,'F','14.08.1933',52750.00,1000.00,4220.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000020','MICHAEL','L','THOMPSON','B01','3476','10.10.1973','MANAGER ',18,'M','02.02.1948',41250.00,800.00,3300.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000030','SALLY','A','KWAN','C01','4738','05.04.1975','MANAGER ',20,'F','11.05.1941',38250.00,800.00,3060.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000050','JOHN','B','GEYER','E01','6789','17.08.1949','MANAGER ',16,'M','15.09.1925',40175.00,800.00,3214.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000060','IRVING','F','STERN','D11','6423','14.09.1973','MANAGER ',16,'M','07.07.1945',32250.00,600.00,2580.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000070','EVA','D','PULASKI','D21','7831','30.09.1980','MANAGER ',16,'F','26.05.1953',36170.00,700.00,2893.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000090','EILEEN','W','HENDERSON','E11','5498','15.08.1970','MANAGER ',16,'F','15.05.1941',29750.00,600.00,2380.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000100','THEODORE','Q','SPENSER','E21','0972','19.06.1980','MANAGER ',14,'M','18.12.1956',26150.00,500.00,2092.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000110','VINCENZO','G','LUCCHESI','A00','3490','16.05.1958','SALESREP',19,'M','05.11.1929',46500.00,900.00,3720.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000120','SEAN',' ','O''CONNELL','A00','2167','05.12.1963','CLERK ',14,'M','18.10.1942',29250.00,600.00,2340.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000130','DOLORES','M','QUINTANA','C01','4578','28.07.1971','ANALYST ',16,'F','15.09.1925',23800.00,500.00,1904.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000140','HEATHER','A','NICHOLLS','C01','1793','15.12.1976','ANALYST ',18,'F','19.01.1946',28420.00,600.00,2274.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000150','BRUCE',' ','ADAMSON','D11','4510','12.02.1972','DESIGNER',16,'M','17.05.1947',25280.00,500.00,2022.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000160','ELIZABETH','R','PIANKA','D11','3782','11.10.1977','DESIGNER',17,'F','12.04.1955',22250.00,400.00,1780.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000170','MASATOSHI','J','YOSHIMURA','D11','2890','15.09.1978','DESIGNER',16,'M','05.01.1951',24680.00,500.00,1974.00);
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000180','MARILYN','S','SCOUTTEN','D11','1682','07.07.1973','DESIGNER',17,'F','21.02.1949',21340.00,500.00,1707.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000190','JAMES','H','WALKER','D11','2986','26.07.1974','DESIGNER',16,'M','25.06.1952',20450.00,400.00,1636.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000200','DAVID',' ','BROWN','D11','4501','03.03.1966','DESIGNER',16,'M','29.05.1941',27740.00,600.00,2217.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000210','WILLIAM','T','JONES','D11','0942','11.04.1979','DESIGNER',17,'M','23.02.1953',18270.00,400.00,1462.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000220','JENNIFER','K','LUTZ','D11','0672','29.08.1968','DESIGNER',18,'F','19.03.1948',29840.00,600.00,2387.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000230','JAMES','J','JEFFERSON','D21','4265','21.11.1966','CLERK ',14,'M','30.05.1935',22180.00,400.00,1774.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000240','SALVATORE','M','MARINO','D21','3780','05.12.1979','CLERK ',17,'M','31.03.1954',28760.00,600.00,2301.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000250','DANIEL','S','SMITH','D21','0961','30.10.1969','CLERK ',15,'M','12.11.1939',19180.00,400.00,1534.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000260','SYBIL','V','JOHNSON','D21','8953','11.09.1975','CLERK ',16,'F','05.10.1936',17250.00,300.00,1380.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000270','MARIA','L','PEREZ','D21','9001','30.09.1980','CLERK ',15,'F','26.05.1953',27380.00,500.00,2190.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000280','ETHEL','R','SCHNEIDER','E11','8997','24.03.1967','OPERATOR',17,'F','28.03.1936',26250.00,500.00,2100.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000290','JOHN','R','PARKER','E11','4502','30.05.1980','OPERATOR',12,'M','09.07.1946',15340.00,300.00,1227.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000300','PHILIP','X','SMITH','E11','2095','19.06.1972','OPERATOR',14,'M','27.10.1936',17750.00,400.00,1420.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000310','MAUDE','F','SETRIGHT','E11','3332','12.09.1964','OPERATOR',12,'F','21.04.1931',15900.00,300.00,1272.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000320','RAMLAL','V','MEHTA','E21','9990','07.07.1965','FIELDREP',16,'M','11.08.1932',19950.00,400.00,1596.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000330','WING',' ','LEE','E21','2103','23.02.1976','FIELDREP',14,'M','18.07.1941',25370.00,500.00,2030.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'000340','JASON','R','GOUNOT','E21','5698','05.05.1947','FIELDREP',16,'M','17.05.1926',23840.00,500.00,1907.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'200010','DIAN','J','HEMMINGER','A00','3978','01.01.1965','SALESREP',18,'F','14.08.1933',46500.00,1000.00,4220.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'200120','GREG',' ','ORLANDO','A00','2167','05.05.1972','CLERK ',14,'M','18.10.1942',29250.00,600.00,2340.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'200140','KIM','N','NATZ','C01','1793','15.12.1976','ANALYST ',18,'F','19.01.1946',28420.00,600.00,2274.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'200170','KIYOSHI',' ','YAMAMOTO','D11','2890','15.09.1978','DESIGNER',16,'M','05.01.1951',24680.00,500.00,1974.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'200220','REBA','K','JOHN','D11','0672','29.08.1968','DESIGNER',18,'F','19.03.1948',29840.00,600.00,2387.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'200240','ROBERT','M','MONTEVERDE','D21','3780','05.12.1979','CLERK ',17,'M','31.03.1954',28760.00,600.00,2301.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'200280','EILEEN','R','SCHWARTZ','E11','8997','24.03.1967','OPERATOR',17,'F','28.03.1936',26250.00,500.00,2100.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'200310','MICHELLE','F','SPRINGER','E11','3332','12.09.1964','OPERATOR',12,'F','21.04.1931',15900.00,300.00,1272.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'200330','HELENA',' ','WONG','E21','2103','23.02.1976','FIELDREP',14,'F','18.07.1941',25370.00,500.00,2030.00 ) ;
INSERT INTO EMP2 (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM )
VALUES (
'200340','ROY','R','ALONZO','E21','5698','05.05.1947','FIELDREP',16,'M','17.05.1926',23840.00,500.00,1907.00 ) ;

5 Comments

answer 1

April 5, 2016 11:06 AM by Peter Vanroose

I'm using DB2 10 for z/OS.

No differences whatsoever between contents of emp2 and emp3, except of course for column ROW_START which contains the INSERT timestamp, and these are different (between rows of the same table, and also between identical rows of the two tables; I've done the inserts with "autocommit")

Only a very minimal difference between the catalog information of those two tables (apart from the table names, tablespace names, and database names & OBIDs/DBIDs): sysibm.systables.version is 1 for EMP2 and 0 for EMP3.

--       Peter Vanroose.

answer 2

April 5, 2016 11:18 AM by Peter Vanroose

I'm using DB2 10 for z/OS.

The only difference between contents of emp2 and emp4 is, as expected, the content of the auto-generated columns. Since the inserts were done before the table became versioned, ROW_START contains the earliest possible timestamp in EMP4. As if the rows have been there "since ever before", which is correct logically speaking since the table became only time-aware after the rows were there.

No differences in the catalog between emp2 and emp4, nor between their history talbes (apart from the table names, tablespace names, and database names & OBIDs/DBIDs).

--       Peter Vanroose.

Answer 1 & 2

April 6, 2016 03:25 PM by Darren Ashworth

Answer 1:
DB2 10.0 on LUW
I created Employee2 & Employee3 as directed with their associated history tables. I then loaded the data into each base table. The only differences noticed between these two tables are with columns (RW_ID & ROW_END). As these are timestamp columns as designed. When updating a record in Employee2 & Employee3 then the value of the record before the update is automatically written to its associated history table by design.

Answer 2:
I created Employee4 and inserted records as directed. I then made EMPLOYEE4 a system-period temporal table. The differences between EMPLOYEE2 and EMPLOYEE4 are with columns (RW_ID & ROW_END). Both of the columns on EMPLOYEE4 have a default value of (0001-01-01) as expected. That is because the data already existed in this table before it became a system-period table. Great exercise - I had a lot of fun with this.

Answer 1 & 2

April 21, 2016 02:00 AM by Anantha Narayanan

No difference in EMP2 and EMP2.

Coming to EMP2 and EMP4 we could see since the insert happened before converting the table to system temporary table. The ROW_START & ROW_ID has year staring from 0001 in EMP4. 

And the winner is ... ??? ;-)

May 19, 2016 08:10 AM by Peter Vanroose

.

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