I need to create a table which has an identity column and some columns from another table.
I tried many ways, I searched the forums and of course I read the documentation, but I didn't succeed.

I tried something like:

create table t as (select 0 as id generated by default as identity, col1,..coln from table) definition only

it didn't work!

I tried to create first the new table from the old table, and after theat to alter the table an add the identity column, like this:

alter table t add column id int generated always as identity

but it didn't worlk also !

I don't know what to do anymore.
Please help!

Thanks in advance.

Couldn't you just db2look the original table def, save it to a file, add your column/change the name and run?

No, I can't use db2look, because I'm using Java, so I must create the table using JDBC.

I'm not sure there's an easy way to do this without the original table definition. As you said, ALTER won't work with IDENTITY, and you can't add columns when you're using the "AS" clause.

You might get away with using the MetaData classes JDBC provides, but that's fairly ugly... and won't give you the whole definition, probably just the data types.

ie. DatabaseMetaData's getColumns() method...

Anyone else have any ideas?

As it turns out, getColumns() gives a LOT of information beyond the data types...

Even so, not sure it'll make it easy to build a CREATE TABLE from.