IDS Forum

Re:Table/index space

Posted By: ART KAGEL, BLOOMBERG/ 731 LEXIN
Date: Wednesday, 10 October 2007, at 9:32 a.m.

In Response To: Re:Table/index space (Long Nguyen)

It should work regardless of where the index and table partitions are stored.
They all share the same lockid so that links them to the core table's partnum
and the partnum on each sysptnhdr row for each table or index fragment will
identify the dbspace that it resides in. Have you tried it on such a table?
What are the results?

Art S. Kagel

----- Original Message -----
From: Long Nguyen <ids@iiug.org>
To: ids@iiug.org
At: 10/09 22:14:48

Hi Art,
This sql works well for a fragmented table where data and index are in the
same dbspace.
However in case the fragmented indexes are allocated on different dbspaces
how can we find their dbspaces and page usages with SQL statements?
(currently I run oncheck -pT and browse all the data and indexes sections).
Long Nguyen
==============

"ART KAGEL,

BLOOMBERG/ 731 To: ids@iiug.org

LEXIN" cc:

<kagel@bloomberg. Subject: Re:Table/index space [9898]

net>

Sent by:

ids-bounces@iiug.

org

05/09/2007 05:22

AM

Please respond to

ids

<SIGH> I type too fast. Final version of the second query corrected here:

select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname database,

st2.tabname partition, nptotal, npused,

npdata, (npused - npdata) npindex
from systabnames st1, systabnames st2, sysptnhdr sph
where st1.partnum = sph.lockid and st2.partnum = sph.partnum
.....
order by 2, 3, 1;

----- Original Message -----
From: ART KAGEL (BLOOMBERG/ 731 LEXIN)
To: ids@iiug.org
At: 9/04 9:59:48

In sysmaster:

select dbinfo( 'dbspace', sph.partnum ) dbspace, dbsname database, tabname,

nptotal, npused, npdata
from systabnames st, sysptnhdr sph
where st.partnum = sph.partnum
.....
order by 2, 3, 1;

If you want this for a specific database and/or table add filters on
dbsname
and tabname in place of the elipsis.

This will return only table partitions. If you want to include index
partitions also you have to include systabnames twice, once for the table
level
filter and once to link all of the table and index partitions by lockid:

select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname database,

st2.tabname, nptotal, npused, npdata
from systabnames st1, systabnames st2, sysptnhdr sph
where st1.partnum = st2.lockid and st2.partnum = sph.partnum
.....
order by 2, 3, 1;

Here if you want to filter for specific database(s) and/or table(s) you
need
to
add filters on st1.dbsname and/or st2.tabname.

Art S. Kagel

----- Original Message -----
From: Tony Demeis <ids@iiug.org>
To: ids@iiug.org
At: 9/04 8:38:49

Hi,
I'm looking for some SQL that will accurately display the number of
pages allocated and used for a fragmented table with its indexes
(IDS9.4, AIX-4KB page size). The report should show the total number of
pages (data & index) in each dbspace.

E.g.
Table x is fragmented (by expression) across 10 dbspaces and has 5
indexes.
Dbspace 1:
Pages allocated: 100,000
Pages used: 98,000

Dbspace 2:
Pages allocated: 100,000
Pages used: 99,000
......
......

Thank you.

*******************************************************************************

Forum Note: Use "Reply" to post a response in the discussion forum.

*******************************************************************************

Forum Note: Use "Reply" to post a response in the discussion forum.

Disclaimer:
This correspondence is for the named person's use only. It may contain
confidential or legally privileged information or both. No confidentiality
or privilege is waived or lost by any mistransmission. If you receive this
correspondence in error, please immediately delete it together with any
attachments from your system and notify the sender. You must not disclose,
copy or rely on any part of this correspondence if you are not the intended
recipient.
Any opinions expressed in this message are those of the individual sender,
except where the sender expressly, and with authority, states them to be
the opinions of Ruralco Holdings Limited or any of its subsidiaries
(collectively "Ruralco").
Although all care has been taken to screen this communication for viruses,
neither the sender nor Ruralco warrants that any communication via the
Internet is free of errors, viruses, interception or interference.
Information is distributed without warranties of any kind.

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• Table/index space
  Demeis, Tony (MOH) -- Tuesday, 4 September 2007, at 8:38 a.m.
  • Re:Table/index space
    ART KAGEL, BLOOMBERG/ 731 LEXIN -- Tuesday, 4 September 2007, at 9:59 a.m.
    • Re:Table/index space
      ART KAGEL, BLOOMBERG/ 731 LEXIN -- Tuesday, 4 September 2007, at 10:04 a.m.
  • Re:Table/index space
    ART KAGEL, BLOOMBERG/ 731 LEXIN -- Tuesday, 4 September 2007, at 3:22 p.m.
    • Re:Table/index space
      Long Nguyen -- Tuesday, 9 October 2007, at 10:13 p.m.
      • Re:Table/index space
        ART KAGEL, BLOOMBERG/ 731 LEXIN -- Wednesday, 10 October 2007, at 9:32 a.m.
        • Re:Table/index space
          Long Nguyen -- Thursday, 11 October 2007, at 3:57 a.m.
          • Re: Table/index space
            Obnoxio The Clown -- Thursday, 11 October 2007, at 4:02 a.m.
          • Re:Table/index space
            ART KAGEL, BLOOMBERG/ 731 LEXIN -- Thursday, 11 October 2007, at 10:54 a.m.
            • Re:Table/index space
              Long Nguyen -- Monday, 15 October 2007, at 7:16 p.m.
              • Re:Table/index space
                ART KAGEL, BLOOMBERG/ 731 LEXIN -- Tuesday, 16 October 2007, at 9:39 a.m.
                • Re:Table/index space
                  Long Nguyen -- Thursday, 18 October 2007, at 7:19 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.