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.

suspect index after upgrade

2 posts in General Discussion Last posting was on 2010-02-02 14:28:43.0Z
Taylor Posted on 2010-02-02 06:56:03.0Z
From: Taylor <heroxulei@hotmail.com>
Newsgroups: sybase.public.ase.general
Subject: suspect index after upgrade
Date: Mon, 1 Feb 2010 22:56:03 -0800 (PST)
Organization: http://groups.google.com
Lines: 13
Message-ID: <f3d73f8f-bb01-4ed3-a515-eef7f4e9cddf@e37g2000yqn.googlegroups.com>
NNTP-Posting-Host: 205.181.240.194
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1265093763 14392 127.0.0.1 (2 Feb 2010 06:56:03 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 2 Feb 2010 06:56:03 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: e37g2000yqn.googlegroups.com; posting-host=205.181.240.194; posting-account=CZamOQoAAAAUHTdhCDOUmRkybCZwE5Ph
User-Agent: G2/1.0
X-HTTP-Via: 1.0 nowbdcpx01b.statestr.com:80 (IronPort-WSA/5.6.2-102)
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.0.3705; InfoPath.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!news-raspail.gip.net!news.gsl.net!gip.net!aotearoa.belnet.be!news.belnet.be!feed.xsnews.nl!border-1.ams.xsnews.nl!news.glorb.com!news2.glorb.com!postnews.google.com!e37g2000yqn.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28928
Article PK: 78168

Hi,

I just upgraded a 12.5.0.3 ASE database on Windows to ASE 12.5.3 on
Solaris. After the upgrade, a lot of indexes in one database are
marked as suspect and can't be used. Those indexes are created by
constraint primary key when create the table. I need to run 'alter
table drop constraint' and 'alter table add constraint' to make those
indexes normal.
Is there any other ways to do this since I need to run it one by one
and cost a lot of time? And is there a SQL that can find all the
objects with primary key?

Thanks for advice.


"Mark A. Parsons" <iron_horse Posted on 2010-02-02 14:28:43.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: suspect index after upgrade
References: <f3d73f8f-bb01-4ed3-a515-eef7f4e9cddf@e37g2000yqn.googlegroups.com>
In-Reply-To: <f3d73f8f-bb01-4ed3-a515-eef7f4e9cddf@e37g2000yqn.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 100126-1, 01/26/2010), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b68369b$1@forums-1-dub>
Date: 2 Feb 2010 06:28:43 -0800
X-Trace: forums-1-dub 1265120923 10.22.241.152 (2 Feb 2010 06:28:43 -0800)
X-Original-Trace: 2 Feb 2010 06:28:43 -0800, vip152.sybase.com
Lines: 26
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28931
Article PK: 78173

See the source code for sp_helpconstraint for ideas on how to obtain a list of tables with primary keys.

Have you looked at how sp_post_xpload rebuilds suspect indexes after a cross-platform dump-n-load scenario? (eg, see
ASE 12.5.3 New Features manual, Chapter 1: Dumping and Loading Databases Across Platforms)

I haven't actually used sp_post_xpload, but the source code may give you some ideas on how to roll your own code if you
don't want to use sp_post_xpload.

Depending on your available resources, there's no reason why you couldn't rebuild multiple indexes in parallel.
Obviously (?) a clustered index should be built 'serially' before attempting to build non-clustered indexes (on the same
table).

Taylor wrote:
> Hi,
>
> I just upgraded a 12.5.0.3 ASE database on Windows to ASE 12.5.3 on
> Solaris. After the upgrade, a lot of indexes in one database are
> marked as suspect and can't be used. Those indexes are created by
> constraint primary key when create the table. I need to run 'alter
> table drop constraint' and 'alter table add constraint' to make those
> indexes normal.
> Is there any other ways to do this since I need to run it one by one
> and cost a lot of time? And is there a SQL that can find all the
> objects with primary key?
>
> Thanks for advice.