PM82173: HOURGLASS DOES NOT MODIFY TIME FOR DB2 COLUMNS DEFINED WITH TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE

APAR status

• Closed as user error.

Error description

• Hourglass does not modify time for DB2 Columns defined with
  TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE
  
  Testcase:
  
  
        CREATE TABLE
          Q039395.TYUTSHG
           (
           TEST  CHAR(5) NOT NULL FOR SBCS DATA
          ,TS_HG  TIMESTAMP NOT NULL WITH DEFAULT
          ,UPDATED_TS_HG  TIMESTAMP NOT NULL GENERATED ALWAYS
            FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
           )
            IN DQ039395.SQ039395
            CCSID EBCDIC
            NOT VOLATILE
            APPEND NO
        ;
  
  INSERT INTO Q039395.TYUTSHG
              (TEST)
  VALUES      ( 'AAAAA' );
  INSERT INTO Q039395.TYUTSHG
              (TEST)
  VALUES      ( 'BBBBB' );
  INSERT INTO Q039395.TYUTSHG
              (TEST)
  VALUES      ( 'CCCCC' );
  INSERT INTO Q039395.TYUTSHG
              (TEST)
  VALUES      ( 'DDDDD' );
  INSERT INTO Q039395.TYUTSHG
              (TEST)
  VALUES      ( 'EEEEE' );
  
  As you can see the Timestamp value by UPDATED_TS_HG is wrong
  by INSERT too.
  

Local fix

Problem summary

Problem conclusion

Temporary fix

Comments

• As we understand it, you believe that HourGlass should intercept
  time requests related to the retrieval of the timestamp value
  that DB2 stores in Row Change Timestamp Column (RCTC).  After
  much internal discussion, we don't believe that we should be so.
  It seems to us as though the RCTC value, especially when
  supplied by DB2 itself, should always contain the actual,
  unmodified system time-of-day value.  One very good reason for
  this is the fact that HourGlass availability is not necessarily
  guaranteed.  This raises the possibility that some RCTC
  values could contain HourGlass-modified values, and other RCTC
  values, even in the same row, could contain the normal,
  unmodified system time-of-day value, which would create an
  inconsistency in the meaning of the RCTC column itself.
  
  If we understand it correctly, DB2 does provide an option for
  the user application to supply the RCTC value to DB2.  In that
  case, the application could retrieve the timestamp value via the
  Current Timestamp special register, which HourGlass does
  currently modify, examine the value just retrieved to ensure
  that it is what the application is expecting, and either give
  the timestamp value to DB2 for the RCTC, or handle the error
  condition.  It seems to me that this procedure would
  minimize problems for both the client and IBM.
  

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

Applicable component levels