Thanks very much, Art.
It is easier to find and analyze the dbspace information with this sql
statement than oncheck.
Long N
==========================================================
"ART KAGEL,
BLOOMBERG/ 731 To: ids@iiug.org
LEXIN" cc:
<kagel@bloomberg. Subject: Re:Table/index space [10148]
net>
Sent by:
ids-bounces@iiug.
org
16/10/2007 11:39
PM
Please respond to
ids
Adjust the query:
select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname database,
st2.tabname tabname, nptotal, npused, npdata, (npused - npdata) npindex
from systabnames st1, systabnames st2, sysptnhdr sph
where st1.partnum = sph.lockid and st2.partnum = sph.partnum
and st1.tabname = "web_indext" -- <<< Here's the problem st1. not st2.
order by 2, 3, 1;
Art S. Kagel
----- Original Message -----
From: Long Nguyen <ids@iiug.org>
To: ids@iiug.org
At: 10/15 19:17:44
Hi Art,
This is the result list:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>
A) select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname database,
st2.tabname tabname, nptotal, npused, npdata, (npused - npdata) npindex
from systabnames st1, systabnames st2, sysptnhdr sph
where st1.partnum = sph.lockid and st2.partnum = sph.partnum
and st2.tabname = "web_indext"
order by 2, 3, 1;
Results:
dbspace wi_d1dbs dbspace wi_d2dbs
database v4prod database v4prod
tabname web_indext tabname web_indext
nptotal 148000 nptotal 17500
npused 148000 npused 17500
npdata 147838 npdata 17495
npindex 162 npindex 5
dbspace wi_d3dbs dbspace wi_d4dbs
database v4prod database v4prod
tabname web_indext tabname web_indext
nptotal 9000 nptotal 287941
npused 9000 npused 287941
npdata 8996 npdata 287326
npindex 4 npindex 615
B) oncheck -pT v4prod:web_indext
Results:
TBLspace Report for v4prod:informix.web_indext
The dbspaces for data (wi_d#dbs):
Table fragment partition wi_d1dbs in DBspace wi_d1dbs
Physical Address 12:5
Creation date 01/09/2002 12:17:50
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 110
Number of special columns 0
Number of keys 0
Number of extents 2
Current serial value 1
First extent size 500
Next extent size 500
Number of pages allocated 148000
Number of pages used 148000
Number of data pages 147838
Number of rows 2495239
Partition partnum 12582914
Partition lockid 12582914
...................................................
Table fragment partition wi_d2dbs in DBspace wi_d2dbs
Physical Address 13:5
Creation date 01/09/2002 12:17:50
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 110
Number of special columns 0
Number of keys 0
Number of extents 1
Current serial value 1
First extent size 500
Next extent size 500
Number of pages allocated 17500
Number of pages used 17500
Number of data pages 17495
Number of rows 291912
Partition partnum 13631490
Partition lockid 12582914
..................................................
Table fragment partition wi_d3dbs in DBspace wi_d3dbs
Physical Address 14:5
Creation date 01/09/2002 12:17:50
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 110
Number of special columns 0
Number of keys 0
Number of extents 1
Current serial value 1
First extent size 500
Next extent size 500
Number of pages allocated 9000
Number of pages used 9000
Number of data pages 8996
Number of rows 144202
Partition partnum 14680066
Partition lockid 12582914
...................................................................
Table fragment partition wi_d4dbs in DBspace wi_d4dbs
Physical Address 15:5
Creation date 01/09/2002 12:17:50
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 110
Number of special columns 0
Number of keys 0
Number of extents 6
Current serial value 1
First extent size 500
Next extent size 500
Number of pages allocated 287941
Number of pages used 287941
Number of data pages 287326
Number of rows 4864017
Partition partnum 15728642
Partition lockid 12582914
..........................................................
The Dbspaces for indexes starts from here (wi_x##dbs)
Index web_indext_i1 fragment partition wi_x4dbs in DBspace wi_x4dbs
Physical Address 19:6
Creation date 08/04/2007 23:55:49
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 110
Number of special columns 0
Number of keys 1
Number of extents 18
Current serial value 1
First extent size 250
Next extent size 500
Number of pages allocated 33000
Number of pages used 32680
Number of data pages 0
Number of rows 0
Partition partnum 19922947
Partition lockid 12582914
Index web_indext_i1 fragment partition wi_x3dbs in DBspace wi_x3dbs
Physical Address 18:6
Creation date 08/04/2007 23:55:49
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 110
Number of special columns 0
Number of keys 1
Number of extents 3
Current serial value 1
First extent size 250
Next extent size 250
Number of pages allocated 2750
Number of pages used 2539
Number of data pages 0
Number of rows 0
Partition partnum 18874371
Partition lockid 12582914
.............................................
Index web_indext_i1 fragment partition wi_x2dbs in DBspace wi_x2dbs
Physical Address 17:6
Creation date 08/04/2007 23:55:49
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 110
Number of special columns 0
Number of keys 1
Number of extents 1
Current serial value 1
First extent size 250
Next extent size 250
Number of pages allocated 1750
Number of pages used 1750
Number of data pages 0
Number of rows 0
Partition partnum 17825795
Partition lockid 12582914
...............................................
Index web_indext_i1 fragment partition wi_x1dbs in DBspace wi_x1dbs
Physical Address 16:6
Creation date 08/04/2007 23:55:49
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 110
Number of special columns 0
Number of keys 1
Number of extents 24
Current serial value 1
First extent size 250
Next extent size 500
Number of pages allocated 40500
Number of pages used 40193
Number of data pages 0
Number of rows 0
Partition partnum 16777219
Partition lockid 12582914
..................................................................
Index web_indext_i2 fragment partition wi_x4dbs in DBspace wi_x4dbs
Physical Address 19:7
Creation date 08/05/2007 00:14:50
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 110
Number of special columns 0
Number of keys 1
Number of extents 38
Current serial value 1
First extent size 265
Next extent size 1060
Number of pages allocated 127465
Number of pages used 127465
Number of data pages 0
Number of rows 0
Partition partnum 19922948
Partition lockid 12582914
...................................................................
Index web_indext_i2 fragment partition wi_x3dbs in DBspace wi_x3dbs
Physical Address 18:7
Creation date 08/05/2007 00:14:50
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 110
Number of special columns 0
Number of keys 1
Number of extents 13
Current serial value 1
First extent size 265
Next extent size 265
Number of pages allocated 15370
Number of pages used 15370
Number of data pages 0
Number of rows 0
Partition partnum 18874372
Partition lockid 12582914
.......................................................
Index web_indext_i2 fragment partition wi_x2dbs in DBspace wi_x2dbs
Physical Address 17:7
Creation date 08/05/2007 00:14:50
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 110
Number of special columns 0
Number of keys 1
Number of extents 8
Current serial value 1
First extent size 265
Next extent size 265
Number of pages allocated 8480
Number of pages used 8480
Number of data pages 0
Number of rows 0
Partition partnum 17825796
Partition lockid 12582914
...............................................
Index web_indext_i2 fragment partition wi_x1dbs in DBspace wi_x1dbs
Physical Address 16:7
Creation date 08/05/2007 00:14:50
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 110
Number of special columns 0
Number of keys 1
Number of extents 53
Current serial value 1
First extent size 265
Next extent size 2120
Number of pages allocated 232670
Number of pages used 232670
Number of data pages 0
Number of rows 0
Partition partnum 16777220
Partition lockid 12582914
...............................................................
Index web_indext_i3 fragment partition wi_x4dbs in DBspace wi_x4dbs
Physical Address 19:8
Creation date 08/05/2007 00:35:13
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 110
Number of special columns 0
Number of keys 1
Number of extents 25
Current serial value 1
First extent size 105
Next extent size 210
Number of pages allocated 26670
Number of pages used 26584
Number of data pages 0
Number of rows 0
Partition partnum 19922949
Partition lockid 12582914
..............................................
Index web_indext_i3 fragment partition wi_x3dbs in DBspace wi_x3dbs
Physical Address 18:8
Creation date 08/05/2007 00:35:13
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 110
Number of special columns 0
Number of keys 1
Number of extents 7
Current serial value 1
First extent size 105
Next extent size 105
Number of pages allocated 3255
Number of pages used 3255
Number of data pages 0
Number of rows 0
Partition partnum 18874373
Partition lockid 12582914
.........................................................
Index web_indext_i3 fragment partition wi_x2dbs in DBspace wi_x2dbs
Physical Address 17:8
Creation date 08/05/2007 00:35:13
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 110
Number of special columns 0
Number of keys 1
Number of extents 3
Current serial value 1
First extent size 105
Next extent size 105
Number of pages allocated 1680
Number of pages used 1626
Number of data pages 0
Number of rows 0
Partition partnum 17825797
Partition lockid 12582914
...............................................
Index web_indext_i3 fragment partition wi_x1dbs in DBspace wi_x1dbs
Physical Address 16:8
Creation date 08/05/2007 00:35:13
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 110
Number of special columns 0
Number of keys 1
Number of extents 38
Current serial value 1
First extent size 105
Next extent size 420
Number of pages allocated 48195
Number of pages used 48195
Number of data pages 0
Number of rows 0
Partition partnum 16777221
Partition lockid 12582914
...............................................
Index web_indext_i4 fragment partition wi_x4dbs in DBspace wi_x4dbs
Physical Address 19:5
Creation date 08/04/2007 23:45:51
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 110
Number of special columns 0
Number of keys 1
Number of extents 30
Current serial value 1
First extent size 47
Next extent size 94
Number of pages allocated 14852
Number of pages used 14852
Number of data pages 0
Number of rows 0
Partition partnum 19922946
Partition lockid 12582914
....................................................
Index web_indext_i4 fragment partition wi_x3dbs in DBspace wi_x3dbs
Physical Address 18:5
Creation date 08/04/2007 23:45:52
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 110
Number of special columns 0
Number of keys 1
Number of extents 17
Current serial value 1
First extent size 47
Next extent size 94
Number of pages allocated 3008
Number of pages used 3000
Number of data pages 0
Number of rows 0
Partition partnum 18874370
Partition lockid 12582914
.....................................................
Index web_indext_i4 fragment partition wi_x2dbs in DBspace wi_x2dbs
Physical Address 17:5
Creation date 08/04/2007 23:45:52
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 110
Number of special columns 0
Number of keys 1
Number of extents 8
Current serial value 1
First extent size 47
Next extent size 47
Number of pages allocated 1410
Number of pages used 1410
Number of data pages 0
Number of rows 0
Partition partnum 17825794
Partition lockid 12582914
.........................................................
Index web_indext_i4 fragment partition wi_x1dbs in DBspace wi_x1dbs
Physical Address 16:5
Creation date 08/04/2007 23:45:52
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 110
Number of special columns 0
Number of keys 1
Number of extents 48
Current serial value 1
First extent size 47
Next extent size 376
Number of pages allocated 29798
Number of pages used 29798
Number of data pages 0
Number of rows 0
Partition partnum 16777218
Partition lockid 12582914
............................................................
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>
Regards,
Long N.
================================================================================
============================
"ART KAGEL,
BLOOMBERG/ 731 To: ids@iiug.org
LEXIN" cc:
<kagel@bloomberg. Subject: Re:Table/index space [10116]
net>
Sent by:
ids-bounces@iiug.
org
12/10/2007 12:54
AM
Please respond to
ids
the forumns don't support attachments. Send to me directly.
Art S. Kagel
----- Original Message -----
From: Long Nguyen <ids@iiug.org>
To: ids@iiug.org
At: 10/11 3:59:22
Hi Art,
The list is long . So I attach it for your reference.
The wi_d#dbs dbspaces are for the fragmented by selecton data.
The wi_x#dbs dbspaces are for the fragmented by selection indexes.
The sql results (part A) only show wi_d#dbs names.
The "oncheck" results (part B) show both wi_d#dbs (for data) and wi_x#dbs
(for indexes).
(See attached file: web_indext.doc)
Regards,
Long N
============================================================================
"ART KAGEL,
BLOOMBERG/ 731 To: ids@iiug.org
LEXIN" cc:
<kagel@bloomberg. Subject: Re:Table/index space [10097]
net>
Sent by:
ids-bounces@iiug.
org
10/10/2007 11:32
PM
Please respond to
ids
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.
*******************************************************************************
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.
*******************************************************************************
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.
*******************************************************************************
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.
