Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

ALTER TABLE ... MODIFY id IDENTITY

5 posts in Product Futures Discussion Last posting was on 2003-06-16 07:38:49.0Z
Ilya Zvyagin Posted on 2003-06-03 07:57:28.0Z
Reply-To: "Ilya Zvyagin" <masterziv@mail.ru>
From: "Ilya Zvyagin" <masterziv@mail.ru>
Subject: ALTER TABLE ... MODIFY id IDENTITY
Date: Tue, 3 Jun 2003 11:57:28 +0400
Lines: 37
Organization: FCT
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
FL-Build: Fidolook Express 2001 UIExt. BuildID: 3BC00FAD (7/10/2001 12:17:49).
X-Comment-To: All
Message-ID: <1054627048.750270@gatekeeper.fct.ru>
Cache-Post-Path: gatekeeper.fct.ru!unknown@dream.int.fct.ru
X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/)
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: gatekeeper.fct.ru 212.113.103.2
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1251
Article PK: 95491

Hello, All!
briefly :
It would be nice if one could attach IDENTITY property to an existing column
with unique IDs.

In latest ASE releases there is a possibility to add not null
columns, including IDENTITY, to an existing table.
Then you add a new column with IDENTITY property to a table,
ASE recreates the table physically (as the column is not null)
and generates sequentially id-s for all old rows.
It takes long for considerably big tables. I suspect it would
take much shorter if it didn't generate IDENTITY column values.
For some our tables, we already have a row identifier generated
sequentially and we would like to replace this identifier with
IDENTITY so that IDENTITY would continue generating from the
biggest existing identifier.

In current ASE (12.5.0.3) I can neither modify existing ID column
to add IDENTITY property no add an IDENTITY with some default
value (actually any value would do) and update it from the previous ID
column.

I tried
ALTER TABLE ... ADD newid numeric(10) IDENTITY
on some of our tables and it took about 45 min. In our production
system it would take about 5 hours (I approximate linearly) as it
contains more rows. As ALTER TABLE actually locks the database exclusively
we will have 5 hours out of work of our enterprise which we can't
afford.

--------------------
Ilya Zvyagin, First Container Terminal of SPb Sea Port
E-mail: masterziv@*KILLSPAM*mail.ru - include HP in subject
ICQ UID: 29427861(MasterZIV)


Lindsey White <whitelw Posted on 2003-06-10 15:32:29.0Z
Date: Tue, 10 Jun 2003 11:32:29 -0400
From: Lindsey White <whitelw@*NO_SPAM*michigan.gov>
X-Mailer: Mozilla 4.72 [en]C-CCK-MCD (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: ALTER TABLE ... MODIFY id IDENTITY
References: <1054627048.750270@gatekeeper.fct.ru>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Message-ID: <upCYLY2LDHA.348@forums-1-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 204.22.154.232
Lines: 58
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1256
Article PK: 95496

I believe that you can bcp the data out of the table andBCP it back in and
acheive the results that you desire. I think you have to set the "set identity
insert on" command, but I am not sure. Then drop and re-create the table
replacing the original datatype with an identity column and then BCP the data
back in using the -E option. There is some useful information that is (by far)
more detailed at www.sypron.nl . Navigate to the "identity gap" page, and use
the information there to complete the puzzle. I realize that this isn't as nice
as teh alter table statement, but it is fairly fast, even when using slow BCP...

HTH

Lindsey White
Michigan Dept. of Information Technology

Ilya Zvyagin wrote:

> Hello, All!
> briefly :
> It would be nice if one could attach IDENTITY property to an existing column
> with unique IDs.
>
> In latest ASE releases there is a possibility to add not null
> columns, including IDENTITY, to an existing table.
> Then you add a new column with IDENTITY property to a table,
> ASE recreates the table physically (as the column is not null)
> and generates sequentially id-s for all old rows.
> It takes long for considerably big tables. I suspect it would
> take much shorter if it didn't generate IDENTITY column values.
> For some our tables, we already have a row identifier generated
> sequentially and we would like to replace this identifier with
> IDENTITY so that IDENTITY would continue generating from the
> biggest existing identifier.
>
> In current ASE (12.5.0.3) I can neither modify existing ID column
> to add IDENTITY property no add an IDENTITY with some default
> value (actually any value would do) and update it from the previous ID
> column.
>
> I tried
> ALTER TABLE ... ADD newid numeric(10) IDENTITY
> on some of our tables and it took about 45 min. In our production
> system it would take about 5 hours (I approximate linearly) as it
> contains more rows. As ALTER TABLE actually locks the database exclusively
> we will have 5 hours out of work of our enterprise which we can't
> afford.
>
> --------------------
> Ilya Zvyagin, First Container Terminal of SPb Sea Port
> E-mail: masterziv@*KILLSPAM*mail.ru - include HP in subject
> ICQ UID: 29427861(MasterZIV)


Ilya Zvyagin Posted on 2003-06-16 07:38:49.0Z
Reply-To: "Ilya Zvyagin" <masterziv@mail.ru>
From: "Ilya Zvyagin" <masterziv@mail.ru>
References: <1054627048.750270@gatekeeper.fct.ru> <upCYLY2LDHA.348@forums-1-dub>
Subject: Re: ALTER TABLE ... MODIFY id IDENTITY
Date: Mon, 16 Jun 2003 11:38:49 +0400
Lines: 14
Organization: FCT
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
FL-Build: Fidolook Express 2001 UIExt. BuildID: 3BC00FAD (7/10/2001 12:17:49).
X-Comment-To: Lindsey White
Message-ID: <1055749130.185516@gatekeeper.fct.ru>
Cache-Post-Path: gatekeeper.fct.ru!unknown@dream.int.fct.ru
X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/)
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: gatekeeper.fct.ru 212.113.103.2
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1261
Article PK: 95501

Hello, Lindsey!
You wrote on Tue, 10 Jun 2003 11:32:29 -0400:

LW> I believe that you can bcp the data out of the table andBCP it back in and acheive the
LW> results that you desire. I think you have to set the "set identity insert on" command, but I

I emphasize that we have to finish all literally in minutes, not more.

--------------------
Ilya Zvyagin, First Container Terminal of SPb Sea Port
E-mail: masterziv@*KILLSPAM*mail.ru - include HP in subject
ICQ UID: 29427861(MasterZIV)


Dilip_Wadhwa Posted on 2003-06-15 15:24:14.0Z
From: Dilip_Wadhwa
Date: Sun, 15 Jun 2003 11:24:14 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: ALTER TABLE ... MODIFY id IDENTITY
Message-ID: <CAA5D11897A87D8000549DB085256D46.00317B4F85256D3A@webforums>
References: <1054627048.750270@gatekeeper.fct.ru>
Lines: 80
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1258
Article PK: 95497

Hi,

We also had a tough time while adding an identity column to an existing
huge table. I have provided the following tech note to the developers and
it is being used heavily in our environment. It might help you!!!. Let us
say that the test table name is 'lismst' with millions of records.

==============================================================

[1]: Create a temporary replica copy of the lismst table with no records.
select * into lismst_tmp from lismst where 1=2

[2]: Alter the temporary created table (i.e: lismst_tmp) to add an
identity column
alter table lismst_tmp add rid numeric(10,0) identity

[3]: bcp out the temporary table lismst_tmp and create a format file. Let
us assume that the name of the format file is lismst_tmp.fmt
bcp samba..lismst_tmp out lismst_tmp.bcp -Udilip
(Note: The output file size would be zero in size since this is an empty
table Our main purpose here is just to create a format file with an
identity column)

[4]: The last few lines of the format file would be as follows:
21 SYBCHAR 1 10 "" 21 lis_hijri_due_dt
22 SYBFLT8 1 8 "" 22 lis_unacc_mfee
23 SYBFLT8 1 8 "" 23 lis_acc_mfee
24 SYBNUMERIC 1 8 "" 24 rid 10 0

Modify the last line in format file by using unix vi editor and change the
last line as follows:

24 SYBNUMERIC 0 0 "" 24 rid 10 0

[5]: download the data of the actual table(i;e: lismst) by using Sybase bcp
utility and create one more format file.
bcp samba..lismst out lismst.bcp -Udilip

Note: Please ensure that downloading of the data is perfect and you had
downloaded the whole table.

[6]: Truncate the original table (i.e: lismst)
truncate table lismst

[7]: Drop all the indices on the original table. Dropping of the indices is
required to speed up the uploading process later on.
drop index lismst.<index_name>

[8]: alter the original table and add an identity column.
alter table lismst add rid numeric(10,0) identity

Note: At this stage you are adding an identity column to an empty table
since we have truncated the table.

[9]: upload the data which we had downloaded in Step (5) and use the
format file which we had modified in Step (4). Do
not forget to specify the name of the format file with -f option. (-f
lismst_tmp.fmt)
bcp dilip..lismst in lismst.bcp -Udilip -f lismst_tmp.fmt

[10]: Ensure that uploading is completed properly by checking the number
of records in original table.
select count(*) from lismst

[11]: re-create all the indices on the original table.
create index <index_name> on lismst(column list)

Summary:

With this approach we will achieve the following:
[1]: System resources consumption would be minimal.
[2]: It would not block any of the system tables
[3]: It would not impact any of the other processes
[4]: It would not block the whole database.
[5]: It would not degrade the Server performance
[6]: It would not occupy the log space
[7]: The whole exercise may take only 15-20 minutes to finish.

Cheers,
Dilip


Dilip_Wadhwa Posted on 2003-06-15 15:36:18.0Z
From: Dilip_Wadhwa
Date: Sun, 15 Jun 2003 11:36:18 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: ALTER TABLE ... MODIFY id IDENTITY
Message-ID: <FA00979CCF8D3AF50055B89085256D46.0055187C85256D46@webforums>
References: <1054627048.750270@gatekeeper.fct.ru> <CAA5D11897A87D8000549DB085256D46.00317B4F85256D3A@webforums>
Lines: 15
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1259
Article PK: 95499

Hi,

You can also use the following command to add an identity column on huge
table, with less impact.

select *,rid=identity(10) into lismst_new from lismst

[1]: Initially,this approach requires additional space in the database.
[2]: truncate and drop lismst table.
[3]: rename lismst_new to lismst.

HIH,
dilip