CREATE MULTISET TABLE JOB ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( ID BIGINT NOT NULL, JOB_CD VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
HOURS_PER_WEEK INTEGER, EMP_ID BIGINT, VLD_DT PERIOD(DATE) NOT NULL AS VALIDTIME, SYS_VLD_DT PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL AS TRANSACTIONTIME SEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME PRIMARY KEY ( ID ), PARTITION BY ( CASE_N( (END(VLD_DT )) IS UNTIL_CHANGED, (END(VLD_DT )) IS NOT UNTIL_CHANGED),CASE_N( (END(SYS_VLD_DT )) IS UNTIL_CLOSED, (END(SYS_VLD_DT )) IS NOT UNTIL_CLOSED) );
which tracks which Job code with the hours per week assigned to an employee at that point of time.
Currently I am doing sequnced validtime delete to close the record and nonsequenced validtime insert to update the emp with any change in the hours per week.
When I am doing that my old record is getting logically deleted in transactiontime but it still show open in validtime.
But, I want to close the old record's vld_to_date with new record's vld_from_dt -1.