Table & Index report

Hi Gurus, Is there a way to find out that tables & indexs usage. Say, which db space it belongs to, and how many pages it is using. I try to get a report for this. I know oncheck -pe will generate a report, but you have to calculate. Thanks for any help. Denny ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques .

oncheck -pt Art S. Kagel ----- Original Message ----- From: Denny Guo <ids@iiug.org> At: 4/26 11:51 Hi Gurus, Is there a way to find out that tables & indexs usage. Say, which db space it belongs to, and how many pages it is using. I try to get a report for this. I know oncheck -pe will generate a report, but you have to calculate. Thanks for any help. Denny ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques . ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

www.advancedatatools.com Lester has some useful scripts for pulling that info out of the sysmaster catalogs. Bob Roussey -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Guo, Denny Sent: Wednesday, April 26, 2006 11:47 AM To: ids@iiug.org Subject: Table & Index report [6596] Hi Gurus, Is there a way to find out that tables & indexs usage. Say, which db space it belongs to, and how many pages it is using. I try to get a report for this. I know oncheck -pe will generate a report, but you have to calculate. Thanks for any help. Denny ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques . ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

I have a script which you can run oncheck -pe output through and get what your asking for. You can download it from www.artentech.com/downloads.htm . cheers j. >From: "Guo, Denny" <DGuo@livingstonintl.com> >Date: Wed Apr 26 10:47:27 CDT 2006 >To: ids@iiug.org >Subject: Table & Index report [6596] > >Hi Gurus, > >Is there a way to find out that tables & indexs usage. >Say, which db space it belongs to, and how many pages it is using. >I try to get a report for this. >I know oncheck -pe will generate a report, but you have to calculate. > >Thanks for any help. > >Denny > >******************************************* >The information contained in this e-mail message may >contain privileged and confidential information. >If you are not the intended recipient, you are >hereby notified that any review, dissemination, >distribution or duplication of this communication >is strictly prohibited. If you have received this >message in error, please notify the sender by return >e-mail, delete this message and destroy any copies. >Internet e- mail is not guaranteed to be secure or >error-free. Messages could be intercepted, corrupted, >lost, arrive late or contain viruses. >The sender will not be liable for >these risks. > >******************************************* >Ce message electronique pourrait contenir des >informations privilegiees et confidentielles. Si vous >n'en etes pas le recipiendaire prevu, nous vous >signalons qu'il est strictement interdit d'examiner, >de diffuser, de distribuer et de reproduire le >present message. Si vous l'avez recu par erreur, >veuillez prevenir l'expediteur par courriel, puis >effacer ce message et en detruire toute copie. >Le courrier electronique n'est pas garanti >securitaire ni exempt d'erreurs. Les messages >pourraient etre interceptes, corrompus, egares, >retardes ou contamines par des virus. >L'exp'editeur n'est pas >responsable de ces risques . > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.

The following script will report all in a dbspace number( you input as parameter) Frank QU #!/usr/local/bin/perl -w # #------------------------------------------------------------- use DBI; $connect = 'dbi:Informix:sysmaster'; $dbh = DBI->connect($connect) or die "could not connect to database\\ "; $dbh->{ChopBlanks} = 1; $dbh->{RaiseError} = 1; $output = *STDOUT; # get dbspace number $dbsnum = $ARGV[0]; # check Usage $argNum=@ARGV; if ($argNum!=1) { print "Usage: tbchunk dbsnum\\ "; exit; } $stmt= "select d.name,c.dbsnum, c.chknum, e.tabname, sum(e.size) as size from sysdbspaces d, syschunks c, sysextents e where d.dbsnum=c.dbsnum and c.chknum=e.chunk and d.dbsnum=$dbsnum group by d.name,c.dbsnum,c.chknum,e.tabname order by 5 desc"; $reptab = $dbh->prepare($stmt); $reptab->bind_columns(undef,\\\\$dbsname, \\\\$dbsnum, \\\\$chknum,\\\\$tabname,\\\\$total_size ); $reptab->execute; printf $output "\\ %20s %8s %8s %30s %20s \\ ", "dbsname","dbsnum", "chknum", "tabname", "total_size(pages)"; while ($reptab->fetch) { printf $output "\\ %20s %8s %8s %30s %20s \\ ", $dbsname, $dbsnum, $chknum, $tabname, $total_size; } Guo, Denny wrote: >Hi Gurus, > >Is there a way to find out that tables & indexs usage. >Say, which db space it belongs to, and how many pages it is using. >I try to get a report for this. >I know oncheck -pe will generate a report, but you have to calculate. > >Thanks for any help. > >Denny > >******************************************* >The information contained in this e-mail message may >contain privileged and confidential information. >If you are not the intended recipient, you are >hereby notified that any review, dissemination, >distribution or duplication of this communication >is strictly prohibited. If you have received this >message in error, please notify the sender by return >e-mail, delete this message and destroy any copies. >Internet e- mail is not guaranteed to be secure or >error-free. Messages could be intercepted, corrupted, >lost, arrive late or contain viruses. >The sender will not be liable for >these risks. > >******************************************* >Ce message electronique pourrait contenir des >informations privilegiees et confidentielles. Si vous >n'en etes pas le recipiendaire prevu, nous vous >signalons qu'il est strictement interdit d'examiner, >de diffuser, de distribuer et de reproduire le >present message. Si vous l'avez recu par erreur, >veuillez prevenir l'expediteur par courriel, puis >effacer ce message et en detruire toute copie. >Le courrier electronique n'est pas garanti >securitaire ni exempt d'erreurs. Les messages >pourraient etre interceptes, corrompus, egares, >retardes ou contamines par des virus. >L'exp'editeur n'est pas >responsable de ces risques . > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > -- Yunyao "Frank" Qu Computer Sciences Corporation(CSC) NOAA/CLASS, (301)817-4696

Thanks Frank, it is great. Denny -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Yunyao (Fra.... Sent: April 27, 2006 10:37 AM To: ids@iiug.org Subject: Re: Table & Index report [6607] The following script will report all in a dbspace number( you input as parameter) Frank QU #!/usr/local/bin/perl -w # #------------------------------------------------------------- use DBI; $connect = 'dbi:Informix:sysmaster'; $dbh = DBI->connect($connect) or die "could not connect to database\\ "; $dbh->{ChopBlanks} = 1; $dbh->{RaiseError} = 1; $output = *STDOUT; # get dbspace number $dbsnum = $ARGV[0]; # check Usage $argNum=@ARGV; if ($argNum!=1) { print "Usage: tbchunk dbsnum\\ "; exit; } $stmt= "select d.name,c.dbsnum, c.chknum, e.tabname, sum(e.size) as size from sysdbspaces d, syschunks c, sysextents e where d.dbsnum=c.dbsnum and c.chknum=e.chunk and d.dbsnum=$dbsnum group by d.name,c.dbsnum,c.chknum,e.tabname order by 5 desc"; $reptab = $dbh->prepare($stmt); $reptab->bind_columns(undef,\\\\$dbsname, \\\\$dbsnum, \\\\$chknum,\\\\$tabname,\\\\$total_size ); $reptab->execute; printf $output "\\ %20s %8s %8s %30s %20s \\ ", "dbsname","dbsnum", "chknum", "tabname", "total_size(pages)"; while ($reptab->fetch) { printf $output "\\ %20s %8s %8s %30s %20s \\ ", $dbsname, $dbsnum, $chknum, $tabname, $total_size; } Guo, Denny wrote: >Hi Gurus, > >Is there a way to find out that tables & indexs usage. >Say, which db space it belongs to, and how many pages it is using. >I try to get a report for this. >I know oncheck -pe will generate a report, but you have to calculate. > >Thanks for any help. > >Denny > >******************************************* >The information contained in this e-mail message may contain privileged >and confidential information. >If you are not the intended recipient, you are hereby notified that any >review, dissemination, distribution or duplication of this >communication is strictly prohibited. If you have received this message >in error, please notify the sender by return e-mail, delete this >message and destroy any copies. >Internet e- mail is not guaranteed to be secure or error-free. Messages >could be intercepted, corrupted, lost, arrive late or contain viruses. >The sender will not be liable for >these risks. > >******************************************* >Ce message electronique pourrait contenir des informations privilegiees >et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous >vous signalons qu'il est strictement interdit d'examiner, de diffuser, >de distribuer et de reproduire le present message. Si vous l'avez recu >par erreur, veuillez prevenir l'expediteur par courriel, puis effacer >ce message et en detruire toute copie. >Le courrier electronique n'est pas garanti securitaire ni exempt >d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, >retardes ou contamines par des virus. >L'exp'editeur n'est pas >responsable de ces risques . > > >*********************************************************************** >******** Forum Note: Use "Reply" to post a response in the discussion >forum. > > > > -- Yunyao "Frank" Qu Computer Sciences Corporation(CSC) NOAA/CLASS, (301)817-4696 ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques .