Unanswered: MQT with identity column or sequence object
I need to create an MQT that uses an identity column or sequence object. It can use any other method for creating sequential numbering but the number needs to start with 1 and increment by 1 and when the MQT is refreshed needs to restart with 1++...I suppose I could script the restart stmt in the refresh table script.
At any rate here is the SQL and error i am getting
create table testnextval(seq1,id) as (select nextval for test_seq, id from test) DATA INITIALLY DEFERRED REFRESH DEFERRED ENABLE QUERY OPTIMIZATION MAINTAINED BY SYSTEM"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0348N "NEXTVAL FOR DB2INST1.SONG_CHG_ID_SEQ" cannot be specified in this
I tried to alter the table to add an identify column but get a SQL0270N rc21 when I try drop the default constraint on the column:
A column cannot be dropped or have its length, data type,
security, or nullability altered on a table that is a base
table for a materialized query table.