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

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).

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
)

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