Joined: 11 Jan 2006 Posts: 66 Location: Lower Saxony (DE)
some months ago I created a set of tables (in one tablespace).
After having loss of data we cannot figure out, we'd like to migrate these to temporal tables (on DB2 v10.? z/OS).
I created a similar history table (currently the only one in it's tablepace) including the same columns as the base table and the three necessary timestamp cols.
After that, I added the timestamp cols (including the NOT NULL GENERATED ALWAYS AS ... clauses) to the base table as well as the PERIOD SYSTEM_TIME option.
When trying to ADD VERSIONING USE HISTORY TABLE, I receive the SQL-code -766 / SQL-State 560A4 which says "THE OBJECT OF A STATEMENT IS A TABLE FOR WHICH THE REQUESTED OPERATION IS NOT PERMITTED"
My german manual (basing on v9.0 z/OS) points out the statement references a auxiliary table.
But both, the prepared history table as well as the base table do not contain any LOB- or XML-columns.
Before executing the Alter Table Add Versioning statement, in SysTalbles the history table is marked as a ordinary table.
I've got two ideas what could be the reason for this problem. But in order to leave you unprejudiced, today I don't talk about them.
I'd be very thankful for any hints solving my problem.
Are you compliant to below clauses,(though few of them you listed above)?
Table with system-period data versioning
A table with system-period data versioning must be the only table in the table space, and it must not be any of the following types of tables:
•An incomplete table
•An auxiliary table
•A clone table or a table with a clone defined on it
•A table that was implicitly created for an XML column
•A table that contains a security label column
A security label column cannot be added to a system-period temporal table.