Utilities and tools

There are a number of tools and utilities available for you to work with and manage the data in your temporal tables.

The following tools are available to work with and manage temporal tables:

Import

When importing data into system-period temporal tables, you use file type modifiers to ignore any content in the external file that might be applied to the database manager generated columns in the system-period temporal table. The following modifiers are available when importing data into a system-period temporal table.

periodignore
Use this modifier to inform the import utility that data for the SYSTEM_TIME period columns is present in the external file, but should be ignored. When this modifier is specified, all time period column values are generated by the utility.
periodmissing
Use this modifier to advise the import utility that the external data file does not contain any data for the SYSTEM_TIME period columns. When this modifier is specified, all time period column values are generated by the utility.
transactionidignore
Use this modifier to inform the import utility that data for the transaction start-ID column is present in the external file, but should be ignored. When this modifier is specified, the value for the transaction start-ID column is generated by the utility.
transactionidmissing
Use this modifier to advise the import utility that the external data file does not contain any data for the transaction start-ID column. When this modifier is specified, the value for the transaction start-ID column is generated by the utility.

Unlike the load utility, the import utility does not have modifiers that override the GENERATED ALWAYS columns.

Load

When loading data into system-period temporal tables, you use file type modifiers to either ignore any data in the external file that might be applied to the database manager generated columns, or to load user-supplied values to those generated columns. The following modifiers are available when loading data into a system-period temporal table. LOAD REPLACE is blocked on system-period temporal tables.
periodignore
Use this modifier to inform the load utility that data for the SYSTEM_TIME period columns is present in the external file, but should be ignored. When this modifier is specified, all time period column values are generated by the utility.
periodmissing
Use this modifier to advise the load utility that the external data file does not contain any data for the SYSTEM_TIME period columns. When this modifier is specified, all time period column values are generated by the utility.
periodoverride
Use this modifier to instruct the load utility to accept user-supplied values for the SYSTEM_TIME period row-begin and row-end columns. This modifier overrides the GENERATED ALWAYS clause. This modifier can be useful when you want to maintain history data and load data that includes time stamps into a system-period temporal table. When this modifier is used, any rows with no data or NULL data in the row-begin and row-end columns are rejected.
transactionidignore
Use this modifier to inform the load utility that data for the transaction start-ID column is present in the external file, but should be ignored. When this modifier is specified, the value for the transaction start-ID column is generated by the utility.
transactionidmissing
Use this modifier to advise the load utility that the external data file does not contain any data for the transaction start-ID column. When this modifier is specified, the value for the transaction start-ID column is generated by the utility.
transactionidoverride
Use this modifier to instruct the load utility to accept user-supplied values for the transaction start-ID column. This modifier overrides the GENERATED ALWAYS clause. When this modifier is used, any rows with no data or NULL data in a transaction start-ID column are rejected.

ADMIN_MOVE_TABLE procedure

When using the ADMIN_MOVE_TABLE stored procedure to move data in an active system-period temporal table into a new table with the same name, the following actions are blocked.
  • Alter table operations that change the definition of the system-period temporal table or the associated history table are blocked during online move operations.
  • The KEEP option of ADMIN_MOVE_TABLE is unavailable for system-period temporal tables
The online-table-move operation is not supported for history tables.

QUIESCE TABLESPACES FOR TABLE command

When running the QUIESCE TABLESPACES FOR TABLE command on a system-period temporal table, all the table spaces associated with the system-period temporal table and its history table are quiesced. When running the command against a history table, all the table spaces associated with the history table and the associated system-period temporal table are quiesced.

Replication

When replicating a system-period temporal table, columns with following generated attributes cannot participate in the replication if the target is another system-period temporal table:
  • GENERATED ALWAYS AS ROW BEGIN
  • GENERATED ALWAYS AS ROW END
  • GENERATED ALWAYS AS TRANSACTION START ID

Similarly, when replicating a bitemporal table, columns with following generated attributes cannot participate in the replication if the target is another bitemporal table:

Roll forward

When the table space for a system-period temporal table or a bitemporal table is rolled-forward to a point in time, the table space for the associated history table also must be rolled-forward to the same point in time in the same ROLLFORWARD statement. Similarly when the table space for a history table is rolled-forward to a point in time, the table space for the system-period temporal table or a bitemporal table also must be rolled-forward to the same point in time. You can, however, recover the table space for the system-period temporal table or the table space for the history table to end of logs individually.

ADMIN_COPY_SCHEMA procedure

The ADMIN_COPY_SCHEMA procedure is used to copy a specific schema and all objects contained in it. The new target schema objects are created using the same object names as the objects in the source schema, but with the target schema qualifier. The ADMIN_COPY_SCHEMA procedure is supported for system-period temporal tables. The procedure requires that both system-period temporal table and the history table are in the same schema, otherwise neither table is copied and an error is recorded.