Q
Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Can I turn a 'generated by default' identity column into a 'generated always' column with DB2 V8.1?

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?

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

Dig Deeper on DB2 UDB (universal databases)

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Join the conversation

2 comments

Send me notifications when other members comment.

Please create a username to comment.

set generated always restart with 64243 ...worked for me. We have version 7.1
Cancel
For fixed length character strings of 254 bytes or under use char data for more than the above use varchar data supported by DB2 supports decimal data too
Cancel

-ADS BY GOOGLE

SearchDataCenter

Close