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.

Index

3 posts in General Discussion Last posting was on 2011-10-13 18:21:03.0Z
RajV Posted on 2011-10-13 17:54:38.0Z
Sender: 438e.4e970008.1804289383@sybase.com
From: RajV
Newsgroups: sybase.public.ase.general
Subject: Index
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e9725de.4ea6.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 13 Oct 2011 10:54:38 -0700
X-Trace: forums-1-dub 1318528478 10.22.241.41 (13 Oct 2011 10:54:38 -0700)
X-Original-Trace: 13 Oct 2011 10:54:38 -0700, 10.22.241.41
Lines: 7
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30587
Article PK: 73478

Hi

Does anyone have a query for selecting only nonclustered
indexes from data row level locking tables. Wish to exclude
clustered indexes.

Thanks


Bret Halford Posted on 2011-10-13 18:21:03.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Index
References: <4e9725de.4ea6.1681692777@sybase.com>
In-Reply-To: <4e9725de.4ea6.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: <4e972c0f$1@forums-1-dub>
Date: 13 Oct 2011 11:21:03 -0700
X-Trace: forums-1-dub 1318530063 10.22.241.152 (13 Oct 2011 11:21:03 -0700)
X-Original-Trace: 13 Oct 2011 11:21:03 -0700, vip152.sybase.com
Lines: 23
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30589
Article PK: 73479


On 10/13/2011 11:54 AM, RajV wrote:
> Hi
>
> Does anyone have a query for selecting only nonclustered
> indexes from data row level locking tables. Wish to exclude
> clustered indexes.
>
> Thanks

sysindexes.indid 0 is the data chain, 1 is only used for allpages
clustered, 2-250 are possible nonclustered index indids

sysindexes.status2 bit 0x0200 (decimal 512) is on for DOL clustered
indexes. (the doc wording of "Table is a data-only-locked table with a
clustered index" is a little bit off).

So...

select * from sysindexes
where
indid between 2 and 250
and status2 & 512 = 0
and lockscheme(id) != "allpages"


Rob V Posted on 2011-10-13 18:08:44.0Z
From: Rob V <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Index
References: <4e9725de.4ea6.1681692777@sybase.com>
In-Reply-To: <4e9725de.4ea6.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: <4e97292c$1@forums-1-dub>
Date: 13 Oct 2011 11:08:44 -0700
X-Trace: forums-1-dub 1318529324 10.22.241.152 (13 Oct 2011 11:08:44 -0700)
X-Original-Trace: 13 Oct 2011 11:08:44 -0700, vip152.sybase.com
Lines: 39
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30588
Article PK: 73480


On 13-Oct-2011 19:54, RajV wrote:
> Hi
>
> Does anyone have a query for selecting only nonclustered
> indexes from data row level locking tables. Wish to exclude
> clustered indexes.
>
> Thanks

select object_name(id), index_name=name from sysindexes
where lockscheme(id) = 'datarows'
and id > 100
and indid > 0
and status2 & 512 = 0


The system table definitions for the sysindexes catalog table are at
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36274.1570/html/tables/X22611.htm
.

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------