Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2007
    Posts
    16

    Unanswered: synchronizing sequence numbers for a new schema

    howdy db2 users,

    anybody out there with a strategy for resetting a sequence number in Schema B equal to a sequence number in Schema A?

    i am re-creating sequences in schema B that exist in schema A.

    when i try to alter the sequence, a sub-select returns SQL0104N (syntax error):

    DB2 v8.1.0.64
    FixPak 7
    Type ESE

    alter sequence schema_b.mysequence
    restart with (
    select max(sequenced column) from schema_a.table_a
    )

    thanks,
    bill

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    you can use a query like that. have to give a constant in with clause

    RESTART
    Restarts the sequence. If numeric-constant is not specified, the sequence is restarted at the value specified implicitly or explicitly as the starting value on the CREATE SEQUENCE statement that originally created the sequence.

    WITH numeric-constant
    Restarts the sequence with the specified value. This value can be any positive or negative value that could be assigned to a column of the data type associated with the sequence (SQLSTATE 42815), without non-zero digits existing to the right of the decimal point (SQLSTATE 428FA).
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    sorry ;-( , i didnt read it properly...... u can

    just try removing with ....

    alter sequence schema_b.mysequence
    restart (
    select max(sequenced column) from schema_a.table_a
    )
    Last edited by rahul_s80; 08-30-07 at 11:13.
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  4. #4
    Join Date
    Jul 2007
    Posts
    16

    synchronizing sequence numbers for a new schema

    thanks rahul, but neither syntax worked for me. examples and error messages below:

    alter schema_b.action_pk_seq
    restart with (
    select max(action_sid)
    from
    schema_a.action
    )

    alter schema_b.action_pk_seq
    restart with (
    select max(action_sid)
    from
    schema_a.action
    )

    I received this error:

    DB21007E End of file reached while reading the command.

    so i added a semi-colon and then i got this error:
    alter schema_b.action_pk_seq restart with ( select max(action_sid) from schema_a.action )

    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command.

    During SQL processing it returned:
    SQL0104N An unexpected token "alter ecore.action_pk_seq restart with" was
    found following "BEGIN-OF-STATEMENT".

    Expected tokens may include:
    "<space>". SQLSTATE=42601

Posting Permissions

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