+ echo DB2 version, evaluated on DB2 version, evaluated on + date Tue Jun 9 14:03:58 MST 1998 + echo Populate, since all subsequent modifications will execute on one day Populate, since all subsequent modifications will execute on one day + db2 DROP TABLE WDS_TT DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0204N "RTS.WDS_TT" is an undefined name. SQLSTATE=42704 + db2 CREATE TABLE WDS_TT(RA_Hour INT, RA_Min INT, RA_Sec INT, Dec_Degree INT, Dec_Minute INT, Discoverer CHAR(7), Mag_First DECIMAL(5,2), Trans_Start TIMESTAMP, Trans_Stop TIMESTAMP) DB20000I The SQL command completed successfully. + db2 INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First, Trans_Start, Trans_Stop) VALUES (05, 57, 40, 00, 02, 'BU 1190', 6.5, TIMESTAMP('1988-11-08-00.00.00'), TIMESTAMP('9999-12-31-00.00.00')) DB20000I The SQL command completed successfully. + db2 INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First, Trans_Start, Trans_Stop) VALUES (00, 00, 00, 75, 30, 'A 1248', 12.0, TIMESTAMP('1989-03-12-00.00.00'), TIMESTAMP('1992-11-15-00.00.00')) DB20000I The SQL command completed successfully. + db2 INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First, Trans_Start, Trans_Stop) VALUES (04, 13, 20, 50, 32, 'CHR 15', 15.5, TIMESTAMP('1990-02-09-00.00.00'), TIMESTAMP('9999-12-31-00.00.00')) DB20000I The SQL command completed successfully. + db2 INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First, Trans_Start, Trans_Stop) VALUES (01, 23, 70, -09, 55, 'HJ 3433', 10.5, TIMESTAMP('1991-03-25-00.00.00'), TIMESTAMP('9999-12-31-00.00.00')) DB20000I The SQL command completed successfully. + db2 INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First, Trans_Start, Trans_Stop) VALUES (00, 00, 09, 75, 30, 'A 1248', 12.0, TIMESTAMP('1992-11-15-00.00.00'), TIMESTAMP('1994-05-18-00.00.00')) DB20000I The SQL command completed successfully. + db2 INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First, Trans_Start, Trans_Stop) VALUES (02, 33, 10, -09, 25, 'LDS3402', 10.6, TIMESTAMP('1993-12-19-00.00.00'), TIMESTAMP('1996-07-09-00.00.00')) DB20000I The SQL command completed successfully. + db2 INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First, Trans_Start, Trans_Stop) VALUES (00, 00, 09, 75, 30, 'A 1248', 10.5, TIMESTAMP('1994-05-18-00.00.00'), TIMESTAMP('1995-07-23-00.00.00')) DB20000I The SQL command completed successfully. + db2 INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First, Trans_Start, Trans_Stop) VALUES (00, 00, 08, 75, 30, 'A 1248', 10.5, TIMESTAMP('1995-07-23-00.00.00'), TIMESTAMP('9999-12-31-00.00.00')) DB20000I The SQL command completed successfully. + db2 SELECT * FROM WDS_TT RA_HOUR RA_MIN RA_SEC DEC_DEGREE DEC_MINUTE DISCOVERER MAG_FIRST TRANS_START TRANS_STOP ----------- ----------- ----------- ----------- ----------- ---------- --------- -------------------------- -------------------------- 5 57 40 0 2 BU 1190 6.50 1988-11-08-00.00.00.000000 9999-12-31-00.00.00.000000 0 0 0 75 30 A 1248 12.00 1989-03-12-00.00.00.000000 1992-11-15-00.00.00.000000 4 13 20 50 32 CHR 15 15.50 1990-02-09-00.00.00.000000 9999-12-31-00.00.00.000000 1 23 70 -9 55 HJ 3433 10.50 1991-03-25-00.00.00.000000 9999-12-31-00.00.00.000000 0 0 9 75 30 A 1248 12.00 1992-11-15-00.00.00.000000 1994-05-18-00.00.00.000000 2 33 10 -9 25 LDS3402 10.60 1993-12-19-00.00.00.000000 1996-07-09-00.00.00.000000 0 0 9 75 30 A 1248 10.50 1994-05-18-00.00.00.000000 1995-07-23-00.00.00.000000 0 0 8 75 30 A 1248 10.50 1995-07-23-00.00.00.000000 9999-12-31-00.00.00.000000 8 record(s) selected. + db2 DROP TABLE WDS DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0204N "RTS.WDS" is an undefined name. SQLSTATE=42704 + db2 CREATE TABLE WDS(RA_Hour INT, RA_Min INT, RA_Sec INT, Dec_Degree INT, Dec_Minute INT, Discoverer CHAR(7), Mag_First DECIMAL(5,2)) DB20000I The SQL command completed successfully. + db2 INSERT INTO WDS(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First) SELECT RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First FROM WDS_TT WHERE Trans_Stop = TIMESTAMP('9999-12-31-00.00.00') DB20000I The SQL command completed successfully. + db2 SELECT * FROM WDS RA_HOUR RA_MIN RA_SEC DEC_DEGREE DEC_MINUTE DISCOVERER MAG_FIRST ----------- ----------- ----------- ----------- ----------- ---------- --------- 5 57 40 0 2 BU 1190 6.50 4 13 20 50 32 CHR 15 15.50 1 23 70 -9 55 HJ 3433 10.50 0 0 8 75 30 A 1248 10.50 4 record(s) selected. + echo trigger defs trigger defs + db2 DROP TRIGGER INSERT_WDS DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0204N "RTS.INSERT_WDS" is an undefined name. SQLSTATE=42704 + db2 DROP TRIGGER DELETE_WDS DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0204N "RTS.DELETE_WDS" is an undefined name. SQLSTATE=42704 + db2 DROP TRIGGER UPDATE_WDS DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0204N "RTS.UPDATE_WDS" is an undefined name. SQLSTATE=42704 + db2 CREATE TRIGGER INSERT_WDS AFTER INSERT ON WDS REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First, Trans_Start, Trans_Stop) VALUES (N.RA_Hour, N.RA_Min, N.RA_Sec, N.Dec_Degree, N.Dec_Minute, N.Discoverer, N.Mag_First, CURRENT TIMESTAMP, TIMESTAMP('9999-12-31-00.00.00')) DB20000I The SQL command completed successfully. + db2 CREATE TRIGGER DELETE_WDS AFTER DELETE ON WDS REFERENCING OLD AS O FOR EACH ROW MODE DB2SQL UPDATE WDS_TT SET Trans_Stop = CURRENT TIMESTAMP WHERE WDS_TT.Discoverer = O.Discoverer AND WDS_TT.Trans_Stop = TIMESTAMP('9999-12-31-00.00.00') DB20000I The SQL command completed successfully. + db2 CREATE TRIGGER UPDATE_WDS AFTER UPDATE ON WDS REFERENCING OLD AS O NEW AS N FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE WDS_TT SET Trans_Stop = CURRENT TIMESTAMP WHERE WDS_TT.Discoverer = O.Discoverer AND WDS_TT.Trans_Stop = TIMESTAMP('9999-12-31-00.00.00'); INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First, Trans_Start, Trans_Stop) VALUES (N.RA_Hour, N.RA_Min, N.RA_Sec, N.Dec_Degree, N.Dec_Minute, N.Discoverer, N.Mag_First, CURRENT TIMESTAMP, TIMESTAMP('9999-12-31-00.00.00')); END DB20000I The SQL command completed successfully. + echo some updates, to test triggers (not in article) some updates, to test triggers (not in article) + db2 INSERT INTO WDS (RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First) VALUES (00, 02, 00, 60, 57, 'ES 1933', 2.4) DB20000I The SQL command completed successfully. + db2 INSERT INTO WDS (RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First) VALUES (00, 08, 00, 31, 23, 'STT 256', 7.9) DB20000I The SQL command completed successfully. + db2 DELETE FROM WDS WHERE Discoverer = 'ES 1933' DB20000I The SQL command completed successfully. + db2 UPDATE WDS SET Dec_Minute = 33 WHERE Discoverer = 'STT 256' DB20000I The SQL command completed successfully. + db2 SELECT * FROM WDS RA_HOUR RA_MIN RA_SEC DEC_DEGREE DEC_MINUTE DISCOVERER MAG_FIRST ----------- ----------- ----------- ----------- ----------- ---------- --------- 5 57 40 0 2 BU 1190 6.50 4 13 20 50 32 CHR 15 15.50 1 23 70 -9 55 HJ 3433 10.50 0 0 8 75 30 A 1248 10.50 0 8 0 31 33 STT 256 7.90 5 record(s) selected. + db2 SELECT * FROM WDS_TT RA_HOUR RA_MIN RA_SEC DEC_DEGREE DEC_MINUTE DISCOVERER MAG_FIRST TRANS_START TRANS_STOP ----------- ----------- ----------- ----------- ----------- ---------- --------- -------------------------- -------------------------- 5 57 40 0 2 BU 1190 6.50 1988-11-08-00.00.00.000000 9999-12-31-00.00.00.000000 0 0 0 75 30 A 1248 12.00 1989-03-12-00.00.00.000000 1992-11-15-00.00.00.000000 4 13 20 50 32 CHR 15 15.50 1990-02-09-00.00.00.000000 9999-12-31-00.00.00.000000 1 23 70 -9 55 HJ 3433 10.50 1991-03-25-00.00.00.000000 9999-12-31-00.00.00.000000 0 0 9 75 30 A 1248 12.00 1992-11-15-00.00.00.000000 1994-05-18-00.00.00.000000 2 33 10 -9 25 LDS3402 10.60 1993-12-19-00.00.00.000000 1996-07-09-00.00.00.000000 0 0 9 75 30 A 1248 10.50 1994-05-18-00.00.00.000000 1995-07-23-00.00.00.000000 0 0 8 75 30 A 1248 10.50 1995-07-23-00.00.00.000000 9999-12-31-00.00.00.000000 0 2 0 60 57 ES 1933 2.40 1998-06-09-14.04.09.396460 1998-06-09-14.04.10.414382 0 8 0 31 23 STT 256 7.90 1998-06-09-14.04.09.932791 1998-06-09-14.04.10.984633 0 8 0 31 33 STT 256 7.90 1998-06-09-14.04.10.984633 9999-12-31-00.00.00.000000 11 record(s) selected. + echo get the current state from the audit log get the current state from the audit log + db2 SELECT RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First FROM WDS_TT WHERE Trans_Stop = TIMESTAMP('9999-12-31-00.00.00') RA_HOUR RA_MIN RA_SEC DEC_DEGREE DEC_MINUTE DISCOVERER MAG_FIRST ----------- ----------- ----------- ----------- ----------- ---------- --------- 5 57 40 0 2 BU 1190 6.50 4 13 20 50 32 CHR 15 15.50 1 23 70 -9 55 HJ 3433 10.50 0 0 8 75 30 A 1248 10.50 0 8 0 31 33 STT 256 7.90 5 record(s) selected. + echo define a prior state as a view define a prior state as a view + db2 CREATE VIEW WDS_April_1 AS SELECT RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First FROM WDS_TT WHERE Trans_Start <= TIMESTAMP('1994-04-01-00.00.00') AND TIMESTAMP('1994-04-01-00.00.00') < Trans_Stop SQL0280W View or trigger "RTS.WDS_APRIL_1" has replaced an existing, inoperative view or trigger. SQLSTATE=01595 + db2 SELECT * FROM WDS_April_1 RA_HOUR RA_MIN RA_SEC DEC_DEGREE DEC_MINUTE DISCOVERER MAG_FIRST ----------- ----------- ----------- ----------- ----------- ---------- --------- 5 57 40 0 2 BU 1190 6.50 4 13 20 50 32 CHR 15 15.50 1 23 70 -9 55 HJ 3433 10.50 0 0 9 75 30 A 1248 12.00 2 33 10 -9 25 LDS3402 10.60 5 record(s) selected. + echo Which stars were of magnitude 11 or brighter, as currently known? Which stars were of magnitude 11 or brighter, as currently known? + db2 SELECT Discoverer FROM WDS WHERE Mag_First <= 11.0 DISCOVERER ---------- BU 1190 HJ 3433 A 1248 STT 256 4 record(s) selected. + echo Asking the same question, as best known on April 1, 1994 Asking the same question, as best known on April 1, 1994 + db2 SELECT Discoverer FROM WDS_April_1 WHERE Mag_First <= 11.0 DISCOVERER ---------- BU 1190 HJ 3433 LDS3402 3 record(s) selected. + echo When was it recorded that A1248 had a magnitude other than 10.5? When was it recorded that A1248 had a magnitude other than 10.5? + db2 SELECT Mag_First, Trans_Start, Trans_Stop FROM WDS_TT WHERE Discoverer = 'A 1248' AND Mag_First <> 10.5 MAG_FIRST TRANS_START TRANS_STOP --------- -------------------------- -------------------------- 12.00 1989-03-12-00.00.00.000000 1992-11-15-00.00.00.000000 12.00 1992-11-15-00.00.00.000000 1994-05-18-00.00.00.000000 2 record(s) selected. + echo When was it recorded that a star had a magnitude equal to that of A1248? When was it recorded that a star had a magnitude equal to that of A1248? + db2 SELECT W2.Discoverer, (CASE WHEN W1.Trans_Start > W2.Trans_Start THEN W1.Trans_Start ELSE W2.Trans_Start END), (CASE WHEN W1.Trans_Stop < W2.Trans_Stop THEN W1.Trans_Stop ELSE W2.Trans_Stop END) FROM WDS_TT W1, WDS_TT W2 WHERE W1.Discoverer = 'A 1248' AND W2.Discoverer <> W1.Discoverer AND W1.Mag_First = W2.Mag_First AND (CASE WHEN W1.Trans_Start > W2.Trans_Start THEN W1.Trans_Start ELSE W2.Trans_Start END) < (CASE WHEN W1.Trans_Stop < W2.Trans_Stop THEN W1.Trans_Stop ELSE W2.Trans_Stop END) DISCOVERER 2 3 ---------- -------------------------- -------------------------- HJ 3433 1994-05-18-00.00.00.000000 1995-07-23-00.00.00.000000 HJ 3433 1995-07-23-00.00.00.000000 9999-12-31-00.00.00.000000 2 record(s) selected. + echo When was the RA_Sec position of a double star corrected? When was the RA_Sec position of a double star corrected? + db2 SELECT W1.Discoverer, W1.RA_Sec AS Old_Value, W2.RA_Sec AS New_Value, W1.Trans_Stop AS When_Changed FROM WDS_TT AS W1, WDS_TT AS W2 WHERE W1.Discoverer = W2.Discoverer AND W1.Trans_Stop = W2.Trans_Start AND W1.RA_Sec <> W2.RA_Sec DISCOVERER OLD_VALUE NEW_VALUE WHEN_CHANGED ---------- ----------- ----------- -------------------------- A 1248 0 9 1992-11-15-00.00.00.000000 A 1248 9 8 1995-07-23-00.00.00.000000 2 record(s) selected. + db2 DROP TABLE WDS_TT DB20000I The SQL command completed successfully. + db2 DROP TRIGGER INSERT_WDS DB20000I The SQL command completed successfully. + db2 DROP TRIGGER DELETE_WDS DB20000I The SQL command completed successfully. + db2 DROP TRIGGER UPDATE_WDS DB20000I The SQL command completed successfully. + echo bitemporal tables bitemporal tables + db2 DROP TABLE WDS_B DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0204N "RTS.WDS_B" is an undefined name. SQLSTATE=42704 + db2 CREATE TABLE WDS_B(Discoverer CHAR(7), Mag_First DECIMAL(5,2), Trans_Start TIMESTAMP, Trans_Stop TIMESTAMP, Valid_From DATE, Valid_To DATE) DB20000I The SQL command completed successfully. + db2 INSERT INTO WDS_B(Discoverer, Mag_First, Trans_Start, Trans_Stop, Valid_From, Valid_To) VALUES ('A 1248', 12.0, TIMESTAMP('1989-03-12-00.00.00'), TIMESTAMP('1995-11-15-00.00.00'), DATE('1922-05-14'), DATE('9999-12-31')) DB20000I The SQL command completed successfully. + db2 INSERT INTO WDS_B(Discoverer, Mag_First, Trans_Start, Trans_Stop, Valid_From, Valid_To) VALUES ('A 1248', 12.0, TIMESTAMP('1995-11-15-00.00.00'), TIMESTAMP('9999-12-31-00.00.00'), DATE('1922-05-14'), DATE('1994-10-16')) DB20000I The SQL command completed successfully. + db2 INSERT INTO WDS_B(Discoverer, Mag_First, Trans_Start, Trans_Stop, Valid_From, Valid_To) VALUES ('A 1248', 10.5, TIMESTAMP('1995-11-15-00.00.00'), TIMESTAMP('9999-12-31-00.00.00'), DATE('1994-10-16'), DATE('9999-12-31')) DB20000I The SQL command completed successfully. + db2 SELECT * FROM WDS_B DISCOVERER MAG_FIRST TRANS_START TRANS_STOP VALID_FROM VALID_TO ---------- --------- -------------------------- -------------------------- ---------- ---------- A 1248 12.00 1989-03-12-00.00.00.000000 1995-11-15-00.00.00.000000 05/14/1922 12/31/9999 A 1248 12.00 1995-11-15-00.00.00.000000 9999-12-31-00.00.00.000000 05/14/1922 10/16/1994 A 1248 10.50 1995-11-15-00.00.00.000000 9999-12-31-00.00.00.000000 10/16/1994 12/31/9999 3 record(s) selected. + echo What was the history recorded as of January 1, 1994? What was the history recorded as of January 1, 1994? + db2 CREATE VIEW WDS_VT_AS_OF_Jan_1 AS SELECT Discoverer, Mag_First, Valid_From, Valid_To FROM WDS_B WHERE Trans_Start <= TIMESTAMP('1994-01-01-00.00.00') AND TIMESTAMP('1994-01-01-00.00.00') < Trans_Stop SQL0280W View or trigger "RTS.WDS_VT_AS_OF_JAN_1" has replaced an existing, inoperative view or trigger. SQLSTATE=01595 + db2 SELECT * FROM WDS_VT_AS_OF_Jan_1 DISCOVERER MAG_FIRST VALID_FROM VALID_TO ---------- --------- ---------- ---------- A 1248 12.00 05/14/1922 12/31/9999 1 record(s) selected. + echo List the corrections made on plates taken in the 1920's. List the corrections made on plates taken in the 1920's. + db2 SELECT B1.Discoverer, B1.Trans_Stop AS When_Changed, (CASE WHEN B1.Valid_From > B2.Valid_From THEN B1.Valid_From ELSE B2.Valid_From END) AS Valid_From, (CASE WHEN B1.Valid_To < B2.Valid_To THEN B1.Valid_To ELSE B2.Valid_To END) AS Valid_To FROM WDS_B AS B1, WDS_B AS B2 WHERE B1.Discoverer = B2.Discoverer AND B1.Trans_Stop = B2.Trans_Start AND (CASE WHEN B1.Valid_From > B2.Valid_From THEN B1.Valid_From ELSE B2.Valid_From END) < DATE('1929-12-31') AND DATE('1920-01-01') < (CASE WHEN B1.Valid_To < B2.Valid_To THEN B1.Valid_To ELSE B2.Valid_To END) AND (CASE WHEN B1.Valid_From > B2.Valid_From THEN B1.Valid_From ELSE B2.Valid_From END) < (CASE WHEN B1.Valid_To < B2.Valid_To THEN B1.Valid_To ELSE B2.Valid_To END) DISCOVERER WHEN_CHANGED VALID_FROM VALID_TO ---------- -------------------------- ---------- ---------- A 1248 1995-11-15-00.00.00.000000 05/14/1922 10/16/1994 1 record(s) selected. + db2 DROP TABLE WDS_B DB20000I The SQL command completed successfully. + db2 DROP TABLE WDS DB20000I The SQL command completed successfully.