Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2010
    Posts
    99

    Unanswered: MQT with identity column or sequence object

    Hello,
    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
    context. SQLSTATE=428F9


    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.

    thanks!,
    James

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    A MQT is a query with the data persisted to a table. It cannot have an identity column. Now if you just want to number each row, then use the ROW_NUMBER function in the query.

    Andy

  3. #3
    Join Date
    Nov 2010
    Posts
    99
    got it thanks!

  4. #4
    Join Date
    Nov 2010
    Posts
    99
    here's how that worked:

    FYI base table has 2 rows

    create table testnextval(seqid,id) as (select row_number() over(),id from test) DATA INITIALLY DEFERRED REFRESH DEFERRED ENABLE QUERY OPTIMIZATION MAINTAINED BY SYSTEM"

    select * from testnextval

    SEQID ID
    -------------------- -----------
    1 1
    2 2


    thanks again Andy!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •