Re:Table/index space
Someone wanted SQL (instead of oncheck -pT) to report allocated/used pages per dbspace for a table fragmented by expression, including its indexes. Art Kagel supplied a sysmaster query joining systabnames twice to sysptnhdr via lockid/partnum, using dbinfo('dbspace', partnum). A user reported it only returned the data dbspaces, not the separate index dbspaces. Resolution: the filter was on the wrong alias — it must be st1.tabname = 'tablename' (the lockid side), not st2.tabname, so all table and index fragments appear.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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.
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.
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.
Long Nguyen wrote:
> 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)
>
You can't attach files.
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
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.
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
............................................................
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
TBLs
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
@@N