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.

Adding columns to a sybase table with unique auto_identity index option

5 posts in General Discussion Last posting was on 2010-11-11 09:06:43.0Z
Tony Ayres Posted on 2010-11-09 16:44:46.0Z
Sender: 1d3c.4cd97a15.1804289383@sybase.com
From: Tony Ayres
Newsgroups: sybase.public.ase.general
Subject: Adding columns to a sybase table with unique auto_identity index option
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4cd97a7d.1d4d.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 9 Nov 2010 08:44:46 -0800
X-Trace: forums-1-dub 1289321086 10.22.241.41 (9 Nov 2010 08:44:46 -0800)
X-Original-Trace: 9 Nov 2010 08:44:46 -0800, 10.22.241.41
Lines: 15
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29689
Article PK: 78918

I've inherited a Sybase database that has the 'unique
auto_identity index' option enabled on it. As part of an
upgrade process I need to add a few extra columns to the
tables in this database i.e.

alter table mytable add <newcol> float default -1 not null

When I try to do this I get the follow error:

Column names in each table must be unique, column name
SYB_IDENTITY_COL in table #syb__altab....... is specifed
more than once

Is it possible to alter a table to add columns with this
property enabled on it?


Bret Halford Posted on 2010-11-10 16:43:57.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.12) Gecko/20101027 Thunderbird/3.1.6
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Adding columns to a sybase table with unique auto_identity index option
References: <4cd97a7d.1d4d.1681692777@sybase.com>
In-Reply-To: <4cd97a7d.1d4d.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4cdacbcd$1@forums-1-dub>
Date: 10 Nov 2010 08:43:57 -0800
X-Trace: forums-1-dub 1289407437 10.22.241.152 (10 Nov 2010 08:43:57 -0800)
X-Original-Trace: 10 Nov 2010 08:43:57 -0800, vip152.sybase.com
Lines: 29
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29691
Article PK: 78919


On 11/9/2010 9:44 AM, Tony Ayres wrote:
> I've inherited a Sybase database that has the 'unique
> auto_identity index' option enabled on it. As part of an
> upgrade process I need to add a few extra columns to the
> tables in this database i.e.
>
> alter table mytable add<newcol> float default -1 not null
>
> When I try to do this I get the follow error:
>
> Column names in each table must be unique, column name
> SYB_IDENTITY_COL in table #syb__altab....... is specifed
> more than once
>
> Is it possible to alter a table to add columns with this
> property enabled on it?

It should be possible.

What version are you on?

By any chance has "auto identity" or "unique auto_identity index"
been turned on for the tempdb used by this session (i.e. regular tempdb
or a multiple tempdb if you are using that feature?)

Cheers,
-bret


Tony Ayres Posted on 2010-11-10 22:25:14.0Z
Sender: 245b.4cd9a9dd.1804289383@sybase.com
From: Tony Ayres
Newsgroups: sybase.public.ase.general
Subject: Re: Adding columns to a sybase table with unique auto_identity index option
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4cdb1bca.5a55.1681692777@sybase.com>
References: <4cdacbcd$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 10 Nov 2010 14:25:14 -0800
X-Trace: forums-1-dub 1289427914 10.22.241.41 (10 Nov 2010 14:25:14 -0800)
X-Original-Trace: 10 Nov 2010 14:25:14 -0800, 10.22.241.41
Lines: 37
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29695
Article PK: 78923


> It should be possible.
>
> What version are you on?

Hi Brett, thanks for the response, I'm using ASE 15.0

I've tried the following simple test on 15 and 15.5 and both
versions produce the error...

use master
sp_dboption 'esmdb', 'unique auto_identity indexoption',
true

use esmdb

create table test_unique_ids (
test_col char
);

alter table test_unique_ids add new_col float default -1 not
null

> By any chance has "auto identity" or "unique auto_identity
> index" been turned on for the tempdb used by this session
> (i.e. regular tempdb or a multiple tempdb if you are using
> that feature?)

No neither of these are enabled for tempdb. The manual
indicates unique auto_identity index is ignored for tempdb.

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sag1/html/sag1/sag1408.htm

Apart from that I haven't found anything on the pros/cons of
enabling this option.

Cheers,
Tony


Bret Halford Posted on 2010-11-10 23:10:31.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.12) Gecko/20101027 Thunderbird/3.1.6
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Adding columns to a sybase table with unique auto_identity index option
References: <4cdacbcd$1@forums-1-dub> <4cdb1bca.5a55.1681692777@sybase.com>
In-Reply-To: <4cdb1bca.5a55.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4cdb2667$1@forums-1-dub>
Date: 10 Nov 2010 15:10:31 -0800
X-Trace: forums-1-dub 1289430631 10.22.241.152 (10 Nov 2010 15:10:31 -0800)
X-Original-Trace: 10 Nov 2010 15:10:31 -0800, vip152.sybase.com
Lines: 96
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29696
Article PK: 78925


On 11/10/2010 3:25 PM, Tony Ayres wrote:
>> It should be possible.
>>
>> What version are you on?
>
> Hi Brett, thanks for the response, I'm using ASE 15.0
>
> I've tried the following simple test on 15 and 15.5 and both
> versions produce the error...
>
> use master
> sp_dboption 'esmdb', 'unique auto_identity indexoption',
> true
>
> use esmdb
>
> create table test_unique_ids (
> test_col char
> );
>
> alter table test_unique_ids add new_col float default -1 not
> null
>
>> By any chance has "auto identity" or "unique auto_identity
>> index" been turned on for the tempdb used by this session
>> (i.e. regular tempdb or a multiple tempdb if you are using
>> that feature?)
>
> No neither of these are enabled for tempdb. The manual
> indicates unique auto_identity index is ignored for tempdb.
>
> http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sag1/html/sag1/sag1408.htm
>
> Apart from that I haven't found anything on the pros/cons of
> enabling this option.
>
> Cheers,
> Tony

Hi Tony,

Well, [to quote Dilbert] it works find on *my* machine... :-)


1> select @@version
2> go


------------------------------------------------------------------------------------------------------------------------
Adaptive Server Enterprise/15.5/EBF 17785 SMP ESD#1/P/Sun_svr4/OS
5.8/asear155/2495/32-bit/FBO/Thu May 27 07:53:56 2010

1> 2>
3> sp_helpdb test9
4> go
name db_size owner dbid created durability status
----- ------------- ----- ---- ------------ ----------
-------------------------------------------------------------------------------------------
test9 6.0 MB sa 20 Aug 20, 2010 full select
into/bulkcopy/pllsort, unique auto_identity index, mixed log and data

(1 row affected)
device_fragments size usage
created free kbytes
------------------------------ ------------- --------------------
------------------------- ----------------
datadev1 6.0 MB data and log Aug
20 2010 11:45AM 2552

1> dbcc traceon(3604)
2> go
DBCC execution completed. If DBCC printed error messages, contact a user
with System Administrator (SA) role.
1> dbcc traceflags
2> go
Active traceflags: 3604

DBCC execution completed. If DBCC printed error messages, contact a user
with System Administrator (SA) role.


1> create table test_unique_ids ( test_col char )
2> go
1> alter table test_unique_ids add new_col float default -1 not null
2> go
Non-clustered index (index id = 2) is being rebuilt.
(0 rows affected)

Could you post:
a) output of "select @@version"
b) output of sp_helpdb esmdb
c) output of dbcc traceflags (after turning on traceflag 3604)

Cheers,
-bret


Tony Ayres Posted on 2010-11-11 09:06:43.0Z
Sender: 6be9.4cdbabbb.1804289383@sybase.com
From: Tony Ayres
Newsgroups: sybase.public.ase.general
Subject: Re: Adding columns to a sybase table with unique auto_identity index option
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4cdbb223.6cd1.1681692777@sybase.com>
References: <4cdb2667$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 11 Nov 2010 01:06:43 -0800
X-Trace: forums-1-dub 1289466403 10.22.241.41 (11 Nov 2010 01:06:43 -0800)
X-Original-Trace: 11 Nov 2010 01:06:43 -0800, 10.22.241.41
Lines: 39
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29697
Article PK: 78926


> Well, [to quote Dilbert] it works find on *my* machine...
> :-)

Thats good, at least I know this is a (mis)configuration
issue.
esmdb is just a test database, heres the output from the
real one:

select @@version:

Adaptive Server Enterprise/15.0.2/EBF 15686 ESD#5/P/Solaris
AMD64/OS 5.10/ase1502/2528/64-bit/FBO/Tue Jun 17 19:27:04
2008

----------
sp_helpdb navdb

name,db_size,owner,dbid,created,status
'navdb',' 1500.0 MB','nsi',' 6','Nov 04, 2010','select
into/bulkcopy/pllsort, trunc log on chkpt, unique
auto_identity index'

device_fragments,size,usage,created,free kbytes
'datadevn',' 1000.0 MB','data only ','Nov 4
2010 12:16PM',' 1015682'
'logdevn',' 500.0 MB','log only ','Nov 4
2010 12:16PM','not applicable'

log only free kbytes = 509998

----------

Active traceflags: 3604

DBCC execution completed. If DBCC printed error messages,
contact a user with System Administrator (SA) role.
Execution time: 0 seconds

/Tony