ALTER TABLE ALTER COLUMN

[Jonathan Ball]

I want to alter a table to set an existing non-identity column as an
identity column. Is it possible? (V6R1) If so, I can't figure out the
syntax. SQL References suggests it can be done.

[CRPence]

Post by Jonathan Ball
I want to alter a table to set an existing non-identity column
as an identity column. Is it possible? (V6R1) If so, I can't
figure out the syntax. SQL References suggests it can be done.

Hmmm. I did not recall that doing so, was unsupported. But even at
v7r1, the DB2 for IBM i SQL InfoCenter docs says the capability does
*not* exist to effect that change from non-identity to identity; not
sure what was the quote in the SQL Reference implying the capability.
The DB2 LUW apparently allows syntax SET GENERATED ALWAYS AS IDENTITY,
if done after DROP DEFAULT for a column that had a default specified.
The DB2 for i SQL allows SET GENERATED ALWAYS [without AS IDENTITY], but
only to modify that specific clause; e.g. from GENERATED BY DEFAULT

I do seem to recall however, that the Record Format Level Identifier
does not change if the *only* change is to be an identity [i.e. not also
a change to have NOT NULL or DROP DEFAULT], so a new file created with
the /same/ format but with the column since-change to be an IDENTITY,
can have the data from the old file restored into the new file. That
allows one to minimize the ALTER work; i.e. to avoid doing the three phases:

ALTER TABLE The_Table ADD COLUMN THE_IDENTITY data-type
GENERATED ALWAYS AS IDENTITY (START WITH <max(Not_Id_Col)+1>)
;
UPDATE The_Table OVERRIDING SYSTEM VALUE
SET THE_IDENTITY = Not_Id_Col
;
ALTER TABLE The_Table DROP COLUMN Not_Id_Col
;

<http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/sqlp/rbafysqlpidentity.htm>
IBM i 7.1 Information Center -> Database -> Programming -> SQL
programming -> Data definition language
_Creating and altering an identity column_
"...
Only columns of type SMALLINT, INTEGER, BIGINT, DECIMAL, or NUMERIC can
be created as identity columns. You are allowed only one identity column
per table. When you are changing a table definition, only a *column that
you are adding can be specified as an identity column*; existing columns
cannot.
..."

[Jonathan Ball]

Post by CRPence

Post by Jonathan Ball
I want to alter a table to set an existing non-identity column
as an identity column. Is it possible? (V6R1) If so, I can't
figure out the syntax. SQL References suggests it can be done.

Hmmm. I did not recall that doing so, was unsupported. But even at
v7r1, the DB2 for IBM i SQL InfoCenter docs says the capability does
*not* exist to effect that change from non-identity to identity; not
sure what was the quote in the SQL Reference implying the capability.
The DB2 LUW apparently allows syntax SET GENERATED ALWAYS AS IDENTITY,
if done after DROP DEFAULT for a column that had a default specified.
The DB2 for i SQL allows SET GENERATED ALWAYS [without AS IDENTITY], but
only to modify that specific clause; e.g. from GENERATED BY DEFAULT

It wasn't an explicit quote or instruction; it was the elements of the
ALTER COLUMN information the SQL reference. It led me to believe it's
possible. I also found a reference on the web to being able to do this
in DB2 for Windows 9.7:
http://www.dbforums.com/db2/1650358-adding-identity-column-existing-table.html

The person posting the reply to someone's inquiry said he succeeded in
doing the following:


alter table test2 add column id integer not null with default 0;
alter table test2 alter column id drop default;
alter table test2 alter column id set generated always as identity;

So, the third statement altered an existing column in the table, which
was not originally added to the table as an identity column, and turned
it into one. I tried that on my table and it failed.

Post by CRPence
I do seem to recall however, that the Record Format Level Identifier
does not change if the *only* change is to be an identity [i.e. not also
a change to have NOT NULL or DROP DEFAULT], so a new file created with
the /same/ format but with the column since-change to be an IDENTITY,
can have the data from the old file restored into the new file. That
ALTER TABLE The_Table ADD COLUMN THE_IDENTITY data-type
GENERATED ALWAYS AS IDENTITY (START WITH <max(Not_Id_Col)+1>)
;
UPDATE The_Table OVERRIDING SYSTEM VALUE
SET THE_IDENTITY = Not_Id_Col
;
ALTER TABLE The_Table DROP COLUMN Not_Id_Col
;
<http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/sqlp/rbafysqlpidentity.htm>
IBM i 7.1 Information Center -> Database -> Programming -> SQL
programming -> Data definition language
_Creating and altering an identity column_
"...
Only columns of type SMALLINT, INTEGER, BIGINT, DECIMAL, or NUMERIC can
be created as identity columns. You are allowed only one identity column
per table. When you are changing a table definition, only a *column that
you are adding can be specified as an identity column*; existing columns
cannot.
..."