subsequent add versioning results SQL-Code -766...

Auryn · Posted: Thu Apr 17, 2014 6:02 pm

Hi there,

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.

Rohit Umarjikar · Posted: Fri Apr 18, 2014 12:11 am

Hello,

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.

GuyC · Posted: Fri Apr 18, 2014 1:51 pm