Find Index & its dbspaces
Question: how to list indexes together with the dbspace each one lives in. Suggested answers: join sysmaster:systabnames with sysindexes using DBINFO('DBSPACE', partnum), or better, join systables, sysindexes and sysfragments with DBINFO('DBSPACE', c.partn) for the index and systables.partnum for the table — the latter worked for the poster. Caveat raised: for fragmented tables systables.partnum is 0 (error -727), so partnums must come from sysfragments for both table and index, using a UNION to cover both cases. oncheck -pt was offered as a non-SQL alternative, and filtering by a specific dbspace is done by adding predicates/joining sysmaster:sysdbspaces.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, How to find Index & its dbspace name? (IN systabnames,indexes & tables are combined,but here i need only index) rgds Schillache
What version of IDS are you using?
If you are using V9 or greater you should be able to run the following
query against the database with indexes of interest.
SELECT idxname , DBINFO ( "DBSPACE" , partnum )
FROM sysmaster:systabnames , sysindexes
WHERE dbsname = 'sdecad'
AND tabname = idxname ;
Stuart McCann
Integrated Spatial Services Unit
Information Communication & Technology
Department of Lands, Bathurst
Phone: (02) 63328284
stuart.mccann@lands.nsw.gov.au
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
SCHIL ACHE
Sent: Tuesday, 30 June 2009 2:29 PM
To: ids@iiug.org
Subject: Find Index & its dbspaces [16184]
Hi,
How to find Index & its dbspace name?
(IN systabnames,indexes & tables are combined,but here i need only
index)
rgds
Schillache
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
***************************************************************
This message is intended for the addressee named and may contain confidential
information. If you are not the intended recipient, please delete it and
notify the sender. Views expressed in this message are those of the individual
sender, and are not necessarily the views of the Department of Lands. This
email message has been swept by MIMEsweeper for the presence of computer
viruses.
***************************************************************
Please consider the environment before printing this email.
ALthough if your table/indices are fragmented/partitioned, the partnum will
be in sysfragments not systables.
j.
Sane ego te vocavi. Forsitan capedictum tuum desit.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of
Stuart McCann
Sent: Tuesday, June 30, 2009 1:01 AM
To: ids@iiug.org
Subject: RE: Find Index & its dbspaces [16185]
What version of IDS are you using?
If you are using V9 or greater you should be able to run the following
query against the database with indexes of interest.
SELECT idxname , DBINFO ( "DBSPACE" , partnum )
FROM sysmaster:systabnames , sysindexes
WHERE dbsname = 'sdecad'
AND tabname = idxname ;
Stuart McCann
Integrated Spatial Services Unit
Information Communication & Technology
Department of Lands, Bathurst
Phone: (02) 63328284
stuart.mccann@lands.nsw.gov.au
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
SCHIL ACHE
Sent: Tuesday, 30 June 2009 2:29 PM
To: ids@iiug.org
Subject: Find Index & its dbspaces [16184]
Hi,
How to find Index & its dbspace name?
(IN systabnames,indexes & tables are combined,but here i need only
index)
rgds
Schillache
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
***************************************************************
This message is intended for the addressee named and may contain
confidential
information. If you are not the intended recipient, please delete it and
notify the sender. Views expressed in this message are those of the
individual
sender, and are not necessarily the views of the Department of Lands. This
email message has been swept by MIMEsweeper for the presence of computer
viruses.
***************************************************************
Please consider the environment before printing this email.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi,
I believe the following works for 9.40 and later. sysfragments is
populated even if the index is not fragmented.
select first 5
a.tabname[1,16] as table
, b.idxname[1,16] as index_name
, dbinfo ("DBSPACE", c.partn) as index_dbspace
, dbinfo ("DBSPACE", a.partnum) as table_dbspace
from systables a
, sysindexes b
, sysfragments c
where a.tabid = b.tabid
and b.tabid = c.tabid
and b.idxname = c.indexname
;
Cheers,
Dick Snoke
IBM Data Management - ChannelWorks
dsnoke@us.ibm.com
(404) 487-1595
From:
"SCHIL ACHE" <penfriend5@yahoo.co.uk>
To:
ids@iiug.org
Date:
06/30/09 12:29 AM
Subject:
Find Index & its dbspaces [16184]
Sent by:
ids-bounces@iiug.org
Hi,
How to find Index & its dbspace name?
(IN systabnames,indexes & tables are combined,but here i need only index)
rgds
Schillache
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Richard ...
This seems to break when a table is fragmented ...It gets a -727 Any
suggestions on getting around that .....
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
From:
"Richard Snoke" <dsnoke@us.ibm.com>
To:
ids@iiug.org
Date:
06/30/2009 08:07 AM
Subject:
Re: Find Index & its dbspaces [16190]
Sent by:
ids-bounces@iiug.org
Hi,
I believe the following works for 9.40 and later. sysfragments is
populated even if the index is not fragmented.
select first 5
a.tabname[1,16] as table
, b.idxname[1,16] as index_name
, dbinfo ("DBSPACE", c.partn) as index_dbspace
, dbinfo ("DBSPACE", a.partnum) as table_dbspace
from systables a
, sysindexes b
, sysfragments c
where a.tabid = b.tabid
and b.tabid = c.tabid
and b.idxname = c.indexname
;
Cheers,
Dick Snoke
IBM Data Management - ChannelWorks
dsnoke@us.ibm.com
(404) 487-1595
From:
"SCHIL ACHE" <penfriend5@yahoo.co.uk>
To:
ids@iiug.org
Date:
06/30/09 12:29 AM
Subject:
Find Index & its dbspaces [16184]
Sent by:
ids-bounces@iiug.org
Hi,
How to find Index & its dbspace name?
(IN systabnames,indexes & tables are combined,but here i need only index)
rgds
Schillache
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
That's normal. For a fragmented table, the query needs to be different
since systables.partnum is zero. In that case, you have to use
sysfragments for both the table partnum and index partnum. If you want to
get both fragmented and unfragmented objects, you have to use a union.
Dick Snoke
IBM Data Management - ChannelWorks
dsnoke@us.ibm.com
(404) 487-1595
From:
"Peter_Logan@spartanstores.com" <Peter_Logan@spartanstores.com>
To:
ids@iiug.org
Date:
06/30/09 09:51 AM
Subject:
Re: Find Index & its dbspaces [16191]
Sent by:
ids-bounces@iiug.org
Richard ...
This seems to break when a table is fragmented ...It gets a -727 Any
suggestions on getting around that .....
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
From:
"Richard Snoke" <dsnoke@us.ibm.com>
To:
ids@iiug.org
Date:
06/30/2009 08:07 AM
Subject:
Re: Find Index & its dbspaces [16190]
Sent by:
ids-bounces@iiug.org
Hi,
I believe the following works for 9.40 and later. sysfragments is
populated even if the index is not fragmented.
select first 5
a.tabname[1,16] as table
, b.idxname[1,16] as index_name
, dbinfo ("DBSPACE", c.partn) as index_dbspace
, dbinfo ("DBSPACE", a.partnum) as table_dbspace
from systables a
, sysindexes b
, sysfragments c
where a.tabid = b.tabid
and b.tabid = c.tabid
and b.idxname = c.indexname
;
Cheers,
Dick Snoke
IBM Data Management - ChannelWorks
dsnoke@us.ibm.com
(404) 487-1595
From:
"SCHIL ACHE" <penfriend5@yahoo.co.uk>
To:
ids@iiug.org
Date:
06/30/09 12:29 AM
Subject:
Find Index & its dbspaces [16184]
Sent by:
ids-bounces@iiug.org
Hi,
How to find Index & its dbspace name?
(IN systabnames,indexes & tables are combined,but here i need only index)
rgds
Schillache
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
If you wanna avoid the sql you can use oncheck -pt...
MM
Thanks for your response.But the output of the query returns 0 rows.
Hi, Thanks for your response. I got the answer from the query output given by you.It gives all indexes from all dbspaces. Have any way to tune somemore,so that i get the list of particular dbspaces's indexes only ? (eg.By specify like dbspace="dbspace10" ) rgds schillache
I am back in US now. --- On Mon, 6/29/09, SCHIL ACHE <penfriend5@yahoo.co.uk> wrote: > From: SCHIL ACHE <penfriend5@yahoo.co.uk> > Subject: Find Index & its dbspaces [16184] > To: ids@iiug.org > Date: Monday, June 29, 2009, 9:28 PM > Hi, > > How to find Index & its dbspace name? > (IN systabnames,indexes & tables are combined,but here > i need only index) > > rgds > Schillache > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the > discussion forum. > >
Hi, Sure, you can add or change predicates in that query to get things for a single dbspace, group of dbspaces, single table, set of tables, etc. Just take a look at the sysindices, sysindexes and systables tables in the database catalogs and sysdbspaces table in the sysmaster database. You'll have to join with sysdbspaces or use the DBINFO ("dbspace", <partnum>) function to choose what you want. If you're not comfortable with the SQL language, please get to know your local DBA. If you're the DBA, find a good book on the SQL language. Cheers, Dick Snoke IBM Data Management - ChannelWorks dsnoke@us.ibm.com (404) 487-1595 From: "SCHIL ACHE" <penfriend5@yahoo.co.uk> To: ids@iiug.org Date: 06/30/09 11:34 PM Subject: Re: Find Index & its dbspaces [16201] Sent by: ids-bounces@iiug.org Hi, Thanks for your response. I got the answer from the query output given by you.It gives all indexes from all dbspaces. Have any way to tune somemore,so that i get the list of particular dbspaces's indexes only ? (eg.By specify like dbspace="dbspace10" ) rgds schillache ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.