Table/index space

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.

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.

Oops, two things I forgot to add: - Obviously without specific tablename filters the elipsis are removed althogether. - In the second SELECT, there should be another required filter: AND st1.partnum = st1.lockid Art S. Kagel ----- Original Message ----- From: Art Kagel <ids@iiug.org> To: ids@iiug.org At: 9/04 10:00:21 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.

<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.