IBM Support

[Db2] How to load data into system-period temporal tables.

How To


Summary

Users might see one of the following errors to load data into system-period temporal tables.

SQL2437N The data movement command failed because the utility was unable to
resolve how implicitly hidden columns should be processed. No data was moved.

SQL3550W The field value in row "x" is not NULL, but the target column has
been defined as GENERATED ALWAYS.

SQL20535N The data change operation "LOAD" is not supported for the target
object "<table_name>" because of an implicit or explicit period specification
involving "SYSTEM_TIME". Reason code: "1".

SQL3016N An unexpected keyword "includeimplicitlyhidden" was found in the
filetmod parameter for the filetype.

Objective

This article guides a user on how to load exported data into system-period temporal tables.

Environment

Db2 10.1 or later with time travel query functionality.

Steps

This article assumes the followings
  • The data is exported with data in sys* columns in system-period temporal tables.
  • The sys* columns are defined as implicit hidden.
  1. Set DB2_DMU_DEFAULT in case the input file type is IXF format because includeimplicitlyhidden file type modifier is not supported in IXF. 
    db2set -im DB2_DMU_DEFAULT=IMPLICITLYHIDDENINCLUDE
  2. Connect to the database, then set CURRENT TEMPORAL SYSTEM_TIME to NULL 
    db2 connect to <db_name>
db2 set current temporal system_time null
  3. LOAD the data with periodoverride transactionidoverride file type modifiers. 
    db2 "load from <input_file> of ixf modified by periodoverride transactionidoverride insert into <table_name>"
  4. (Optional) Disconnect from the database, and remove DB2_DMU_DEFAULT to reset the configuration changes. 
    db2 connect reset
db2set -im DB2_DMU_DEFAULT=
See the following pages for details of the LOAD options.
  • Creating a system-period temporal table
    The LOAD, IMPORT, and EXPORT commands can use the includeimplicitlyhidden modifier to work with implicitly hidden columns.
  • Utilities and tools
    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.
  • Restrictions for system-period temporal tables
    IMPORT and LOAD operations into system-period temporal tables are blocked if the CURRENT TEMPORAL SYSTEM_TIME special register is set to a non-null value.

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PlJAAU","label":"Data Movement-\u003ELoad"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1.0;10.5.0;11.1.0;11.5.0"}]

Document Information

Modified date:
17 July 2022

UID

ibm16604311

Page Feedback