Native I/O considerations with a system-period temporal table

The native I/O processing of read, write, and update operations is, in general, similar to the corresponding SQL operations. However, there are some considerations to keep in mind.

Both SQL and native update and delete operations against a system-period temporal table causes historical rows to be added to a history table. Additionally, if the ON DELETE ADD EXTRA ROW clause is specified on the ALTER TABLE command, both SQL and native delete operations add an extra row when a record is deleted from the table. Similarly, the QAQQINI value for SYSTIME_PERIOD_ADJ has the same effect on concurrent native updates as it has on concurrent SQL update operations. For both native and SQL, the QAQQINI value causes the database to either raise an error or adjust to the row-begin and row-end timestamps. A native DB application receives a CPF503B exception, reason code 3, when the SYSTIME_PERIOD_ADJ value is set to *DEFAULT or *ERROR and a native UPDATE would have caused the value of the historical row's end time to be set to a value that is less than the historical row's begin time.

That said, several differences between native and SQL processing that are worth noting.

While native read requests work against either the temporal or the history table, they do not merge the results together. Only records from the file read are returned. For example, when the CURRENT TEMPORAL SYSTEM_TIME special register is set to a non-null value, an SQL read of the temporal table can return rows that are drawn from both the temporal table and its corresponding history table. However, for a native read of the temporal table, the database ignores the special register and return values only from the temporal table.

When the CURRENT TEMPORAL SYSTEM_TIME special register is set to a non-null value, SQL Data Manipulation (DML) statements fail with SQLCODE/SQLSTATE set to -20535/51046. An SQL user is unable to modify the temporal table’s data. However, no similar restriction exists for a native user. Native write, update, and delete operations modify the temporal table whether the special register is set or not.

Finally, a native write or update can specify values in the I/O buffer for both the SYSTEM_TIME period generated columns and other generated columns, if they exist. However, regardless of what values are supplied in the I/O buffer, DB2 for i substitutes the correct values for any generated columns.