IDENTITY COLUMNS - one more question

syed asif

IDENTITY COLUMNS - one more question
Hi Group,
Thanks for the valuable information regarding the
merits and demerits of Identity Column. I have one
more question regarding the Identity column.
We have created table with Primary key as Identity
column and also have populated data into it from UDB
on AIX. Now the development team wants to start the
entering the data from the next maximum value. I want
to know can we use the ALTER TABLE command to alter
the the Identity column as say it to START WITH. If so
then what should be the value of START with. Is it the
maximum value of the records in the column or
MAXASSIGNEDVAL.
Hope to hear from the group.
Regards,
Syed
--- Walter Janissen <[login to unmask email]>
wrote:
> Because you have RI relationship using an identity
> column, you have to
> create a unique index on that column. Therefore you
> can use the option
> GENERATED BY DEFAULT. In this case you can unload
> the values of the
> identity column and reload them. So they remain the
> same. If you have to
> drop and recreate the table, you must alter your
> starting value in the
> CREATE TABLE. Before dropping look at the column
> MAXASSIGNEDVAL in the
> SYSSEQUENCES catalog table.
>
> HTH
>
>
> To change your subscription options or to cancel
> your subscription visit the DB2-L webpage at
> http://www.ryci.com/db2-l. The owners of the list
> can


__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com



James Campbell

Re: IDENTITY COLUMNS - one more question
(in response to syed asif)
As has been mentioned several times, DB2 for OS/390 and z/OS
does not permit the START (or any other) value to be changed -
except by dropping and re-creating the table.

DB2 V7 for Windows etc at fp4 do allow an ALTER TABLE to alter
these values. So perhaps it will come to the OS/390/z/OS world.

You seem to have a number of choices
- use BY DEFAULT, and unload/reload data. Make the START
value at least as large as the largest current value
- use ALWAYS - which means inserted rows will have their values
changed. You can use whatever you like.
- use BY DEFAULT INCREMENT BY 2, allow data from one source
to have automatically assigned numbers, while data from another
has imbedded numbers.

No easy answers without a lot more information.

James Campbell

On 17 Dec 2001, at 9:40, syed asif wrote:

Date sent: Mon, 17 Dec 2001 09:40:17 -0800
Send reply to: DB2 Data Base Discussion List <DB2-
[login to unmask email]>
From: syed asif <[login to unmask email]>
Subject: Re: [DB2-L] IDENTITY COLUMNS - one more
question
To: [login to unmask email]

<snip>
> I want
> to know can we use the ALTER TABLE command to alter
> the the Identity column as say it to START WITH. If so
> then what should be the value of START with. Is it the
> maximum value of the records in the column or
> MAXASSIGNEDVAL.
> Hope to hear from the group.
> Regards,
> Syed
<rest snipped>

James A Campbell



syed asif

Re: IDENTITY COLUMNS - one more question
(in response to James Campbell)
Thanks James for the valueable suggestion. We have set
up an test environment with identity column. so I was
concerned with the use of Identity column.
Well the best option to do a load is to go ahead with
Droping and creating the table with a START option.
Thanks again the informaion.
Regards,
Syed

--- James Campbell <[login to unmask email]> wrote:
> As has been mentioned several times, DB2 for OS/390
> and z/OS
> does not permit the START (or any other) value to be
> changed -
> except by dropping and re-creating the table.
>
> DB2 V7 for Windows etc at fp4 do allow an ALTER
> TABLE to alter
> these values. So perhaps it will come to the
> OS/390/z/OS world.
>
> You seem to have a number of choices
> - use BY DEFAULT, and unload/reload data. Make the
> START
> value at least as large as the largest current value
> - use ALWAYS - which means inserted rows will have
> their values
> changed. You can use whatever you like.
> - use BY DEFAULT INCREMENT BY 2, allow data from one
> source
> to have automatically assigned numbers, while data
> from another
> has imbedded numbers.
>
> No easy answers without a lot more information.
>
> James Campbell
>
> On 17 Dec 2001, at 9:40, syed asif wrote:
>
> Date sent: Mon, 17 Dec 2001 09:40:17
> -0800
> Send reply to: DB2 Data Base Discussion
> List <DB2-
> [login to unmask email]>
> From: syed asif
> <[login to unmask email]>
> Subject: Re: [DB2-L] IDENTITY COLUMNS
> - one more
> question
> To: [login to unmask email]
>
> <snip>
> > I want
> > to know can we use the ALTER TABLE command to
> alter
> > the the Identity column as say it to START WITH.
> If so
> > then what should be the value of START with. Is it
> the
> > maximum value of the records in the column or
> > MAXASSIGNEDVAL.
> > Hope to hear from the group.
> > Regards,
> > Syed
> <rest snipped>
>
> James A Campbell
>
>
> To change your subscription options or to cancel
> your subscription visit the DB2-L webpage at
> http://www.ryci.com/db2-l. The owners of the list
> can


__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com