#!/usr/bin/sh -x DB2.populaterestricted echo current insertion db2 "INSERT INTO LOT VALUES (433, 'h', CURRENT DATE, DATE ('9999-12-31'))" db2 "SELECT * FROM LOT" echo a logical current deletion db2 "UPDATE LOT SET TO_DATE = CURRENT DATE WHERE LOT_ID_NUM = 101 AND TO_DATE = DATE ('9999-12-31')" db2 "SELECT * FROM LOT" echo a logical current update db2 "INSERT INTO LOT (LOT_ID_NUM, GNDR_CODE, FROM_DATE, TO_DATE) SELECT DISTINCT 799, 's', CURRENT DATE, DATE ('9999-12-31') FROM LOT WHERE EXISTS (SELECT * FROM LOT WHERE LOT_ID_NUM = 799 AND TO_DATE = DATE ('9999-12-31'))" db2 "UPDATE LOT SET TO_DATE = CURRENT DATE WHERE LOT_ID_NUM = 799 AND GNDR_CODE <> 's' AND TO_DATE = DATE ('9999-12-31')" db2 "SELECT * FROM LOT" DB2.populategeneral echo A logical current deletion, in the general scenario db2 "UPDATE LOT SET TO_DATE = CURRENT DATE WHERE LOT_ID_NUM = 234 AND TO_DATE >= CURRENT DATE AND FROM_DATE < CURRENT DATE" db2 "DELETE FROM LOT WHERE LOT_ID_NUM = 234 AND FROM_DATE > CURRENT DATE" db2 "SELECT * FROM LOT" echo logical current update, in the general scenario DB2.populategeneral db2 "INSERT INTO LOT SELECT LOT_ID_NUM, 's', CURRENT DATE, TO_DATE FROM LOT WHERE LOT_ID_NUM = 799 AND FROM_DATE <= CURRENT DATE AND TO_DATE > CURRENT DATE" db2 "UPDATE LOT SET TO_DATE = CURRENT DATE WHERE LOT_ID_NUM = 799 AND GNDR_CODE <> 's' AND FROM_DATE < CURRENT DATE AND TO_DATE > CURRENT DATE" db2 "UPDATE LOT SET GNDR_CODE = 's' WHERE LOT_ID_NUM = 799 AND FROM_DATE >= CURRENT DATE" db2 "SELECT * FROM LOT" echo sequenced insertion DB2.populategeneral db2 "INSERT INTO LOT VALUES (426, 'h', DATE('1998-03-26'), DATE('1998-04-14'))" db2 "SELECT * FROM LOT" echo a sequenced deletion db2 "INSERT INTO LOT SELECT LOT_ID_NUM, GNDR_CODE, DATE('1998-10-22'), TO_DATE FROM LOT WHERE LOT_ID_NUM = 234 AND FROM_DATE <= DATE('1998-10-01') AND TO_DATE > DATE('1998-10-22')" db2 "UPDATE LOT SET TO_DATE = DATE('1998-10-01') WHERE LOT_ID_NUM = 234 AND FROM_DATE < DATE('1998-10-01') AND TO_DATE >= DATE('1998-10-01')" db2 "UPDATE LOT SET FROM_DATE = DATE('1998-10-22') WHERE LOT_ID_NUM = 234 AND FROM_DATE < DATE('1998-10-22') AND TO_DATE >= DATE('1998-10-22')" db2 "DELETE FROM LOT WHERE LOT_ID_NUM = 234 AND FROM_DATE >= DATE('1998-10-01') AND TO_DATE <= DATE('1998-10-22')" db2 "SELECT * FROM LOT" echo a sequenced update DB2.populategeneral db2 "INSERT INTO LOT SELECT LOT_ID_NUM, GNDR_CODE, FROM_DATE, DATE('1998-03-01') FROM LOT WHERE LOT_ID_NUM = 799 AND FROM_DATE < DATE('1998-03-01') AND TO_DATE > DATE('1998-03-01')" db2 "INSERT INTO LOT SELECT LOT_ID_NUM, GNDR_CODE, DATE('1998-04-01'), TO_DATE FROM LOT WHERE LOT_ID_NUM = 799 AND FROM_DATE < DATE('1998-04-01') AND TO_DATE > DATE('1998-04-01')" db2 "UPDATE LOT SET GNDR_CODE = 's' WHERE LOT_ID_NUM = 799 AND FROM_DATE < DATE('1998-04-01') AND TO_DATE > DATE('1998-03-01')" db2 "UPDATE LOT SET FROM_DATE = DATE('1998-03-01') WHERE LOT_ID_NUM = 799 AND FROM_DATE < DATE('1998-03-01') AND TO_DATE > DATE('1998-03-01')" db2 "UPDATE LOT SET TO_DATE = DATE('1998-04-01') WHERE LOT_ID_NUM = 799 AND FROM_DATE < DATE('1998-04-01') AND TO_DATE > DATE('1998-04-01')" db2 "SELECT * FROM LOT" echo a nonsequenced modification # db2 "DELETE FROM LOT WHERE LOT_ID_NUM = 234 AND TIMESTAMPDIFF(64, CHAR(TO_DATE - FROM_DATE)) > 3" db2 "SELECT LOT_ID_NUM, FROM_DATE, TO_DATE, TIMESTAMPDIFF(64, CHAR(TO_DATE - FROM_DATE)) AS T FROM LOT" db2 "DELETE FROM LOT WHERE LOT_ID_NUM = 234 AND TIMESTAMPDIFF(64, CHAR(TO_DATE - FROM_DATE)) > 3" db2 "SELECT * FROM LOT"