Is there a way to turn a "generated by default" identity column into a "generated always" column with DB2 V8.1?
I have a simple table I create executing.
create table tmp ( x_id INTEGER NOT NULL
GENERATED BY DEFAULT AS
IDENTITY( START WITH 1,
INCREMENT BY 1, NO CACHE )
When entering this command
alter table test alter column x_id
set expression as ( GENERATED ALWAYS )
DB2 gives me the following error message:
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 "ALWAYS" was found following "ssion as ( GENERATED". Expected tokens may include: "". SQLSTATE=42601
Even the following script, which I read in a developerWorks article does not work.
alter table tmp alter column x_id
set generated always restart with 64243
It produces this error:
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 "GENERATED ALWAYS" was found following "lter column x_id set". Expected tokens may include: "". QLSTATE=42601
How does the correct statement look?
QUESTION POSED ON: 07 FEB 2005
QUESTION ANSWERED BY: Craig Mullins, Years 2005-2006
You are running into one of the classic problems with using IDENTITY columns -- they are difficult to manage and modify. Once you have made the decision to use GENERATED BY DEFAULT, you can't easily change to GENERATED ALWAYS, and you can't do it using ALTER.
One approach would be to DROP the table and recreate it the way you want it to be. But then you have to worry about how to get the values back that are already there. This might be a good time to think about switching to Sequence Objects.
With Sequence Objects you create the SEQUENCE and grab values from it to supply to the column in the table. The column, which is now an IDENTITY column, just becomes an INTEGER column. You'll have to read the manuals regarding the implementation of Sequence Objects. Keep in mind, this will require a complex DROP/reCREATE process, as well as make changes to your application code (to get the next SEQUENCE value instead of just allowing the IDENTITY column to generate the value).
|
