tables\\\\row counts
The poster asked how to list all tables with their row counts. One reply suggested 'select tabname, nrows from systables', but others noted nrows is only accurate after UPDATE STATISTICS. Art Kagel gave live-count queries against the sysmaster SMI tables, joining systabnames to sysactptnhdr on partnum and using sum(nrows) to handle fragmented tables (indexes show as zero rows), plus a variant joining back to a database's systables. A shell/awk script generating 'select count(*)' per table was also offered. When asked how to restrict to one database, Art showed adding 'AND dbsname = 'aaa'' to the query, which resolved the question.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Please advise on a query to run to get a list of tables and their row counts in informix. help is highly appreciated.
LYNETTE OLIVIER wrote:
> Please advise on a query to run to get a list of tables and their row counts
> in informix.
select tabname, nrows from systables;
I think.
--
Cheers,
Obnoxio The Clown
http://obotheclown.blogspot.com
I will now proceed to pleasure myself with this fish.
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
That will work if you have recently run UPDATE STATISTICS, but the nrows
column in systables is not maintained live. For a live value, you can query
the SMI database sysmaster:
select dbsname, tabname, sum( nrows ) as rowcount
from systabnames stn, sysactptnhdr sp
where stn.partnum = sp.partnum
group by 1, 2
order by 1, 2;
The SUM() is there in case any of the tables are fragmented into multiple
partitions, so you get the table level total. This query will includes
indexes which will have a rowcount of zero, but I didn't filter them out
this way in case you actually have tables with zero rows. If you want to
filter out indexes and also want to see tables with zero rows, you can join
back to the database's systables records, but you'll have to do that query
for a single database, so:
select stn.dbsname, stn.tabname, sum( sp.nrows ) as rowcount
from systabnames stn, sysactptnhdr sp, mydatabase:systables st
where stn.dbsname = 'mydatabase'
and stn.partnum = sp.partnum
and stn.tabname = st.tabname
group by 1, 2
order by 1, 2
;
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Fri, Aug 6, 2010 at 12:03 PM, Obnoxio The Clown
<obnoxio@serendipita.com>wrote:
> LYNETTE OLIVIER wrote:
> > Please advise on a query to run to get a list of tables and their row
> counts
> > in informix.
>
> select tabname, nrows from systables;>
> I think.
>
> --
> Cheers,
> Obnoxio The Clown
>
> http://obotheclown.blogspot.com
> I will now proceed to pleasure myself with this fish.
>
> --
> This message has been scanned for viruses and
> dangerous content by OpenProtect(http://www.openprotect.com), and is
> believed to be clean.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016e6471848091af8048d29f47a
@Lynette,
At least under IDS 10.x, if you haven't run update statistics, the nrow value
can be off. Just my experience.
HTH
---
Jonathan Smaby
Pomona College
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Obnoxio
The Clown
Sent: Friday, August 06, 2010 9:04 AM
To: ids@iiug.org
Subject: Re: tables\\\\row counts [20769]
LYNETTE OLIVIER wrote:
> Please advise on a query to run to get a list of tables and their row counts
> in informix.
select tabname, nrows from systables;
I think.
--
Cheers,
Obnoxio The Clown
http://obotheclown.blogspot.com
I will now proceed to pleasure myself with this fish.
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
-------------------------------------------------------------
This message has been scanned by Postini anti-virus software.
or an overly complicated script will do the trick:
#!/bin/ksh
DBNAME=${1:-sysmaster}
(cat - << EOF | dbaccess ${DBNAME} 2>/dev/null | grep -v "^$" | awk '
BEGIN {
printf("set isolation dirty read;\\
\\
")
}
{
printf("output to pipe cat without headings select \\\\"%s\\\\", count(*) from
%s;\\
", $1, $1)
}
'
output to pipe cat without headings
select
rtrim(tabname)
from
systables
where
tabid > 99
order by
tabname asc;
EOF
) | dbaccess ${DBNAME} 2>/dev/null | grep -v "^$" | awk '
BEGIN {
printf("%25s%12s\\
\\
", "tabname", "nrows")
}
{
printf("%25s%12s\\
", $1, $2)
}
'
# end of script
> rowcount.ksh blog
tabname nrows
blog 3
blog_post 6
process_stat 0
Andrew
----- Original Message -----
From: "Art Kagel" <art.kagel@gmail.com>
To: <ids@iiug.org>
Sent: Friday, August 06, 2010 11:13 AM
Subject: Re: tables\\\\row counts [20771]
> That will work if you have recently run UPDATE STATISTICS, but the nrows
> column in systables is not maintained live. For a live value, you can
> query
> the SMI database sysmaster:
>
> select dbsname, tabname, sum( nrows ) as rowcount
> from systabnames stn, sysactptnhdr sp
> where stn.partnum = sp.partnum
> group by 1, 2
> order by 1, 2> ;
>
> The SUM() is there in case any of the tables are fragmented into multiple
> partitions, so you get the table level total. This query will includes
> indexes which will have a rowcount of zero, but I didn't filter them out
> this way in case you actually have tables with zero rows. If you want to
> filter out indexes and also want to see tables with zero rows, you can
> join
> back to the database's systables records, but you'll have to do that query
> for a single database, so:
>
> select stn.dbsname, stn.tabname, sum( sp.nrows ) as rowcount
> from systabnames stn, sysactptnhdr sp, mydatabase:systables st
> where stn.dbsname = 'mydatabase'>
> and stn.partnum = sp.partnum
>
> and stn.tabname = st.tabname
> group by 1, 2
> order by 1, 2
> ;
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or
> by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Fri, Aug 6, 2010 at 12:03 PM, Obnoxio The Clown
> <obnoxio@serendipita.com>wrote:
>
>> LYNETTE OLIVIER wrote:
>> > Please advise on a query to run to get a list of tables and their row
>> counts
>> > in informix.
>>
>> select tabname, nrows from systables;>>
>> I think.
>>
>> --
>> Cheers,
>> Obnoxio The Clown
>>
>> http://obotheclown.blogspot.com
>> I will now proceed to pleasure myself with this fish.
>>
>> --
>> This message has been scanned for viruses and
>> dangerous content by OpenProtect(http://www.openprotect.com), and is
>> believed to be clean.
>>
>>
>>
>>
>
*******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>
> --0016e6471848091af8048d29f47a
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
How will this work if i have two different databases, say aaa, and bbb...how will i run this query.. as i would like to run this for database aaa
PLEASE!!!! When you reply to a post, remember that most of us who follow
the forums DO NOT use the online forum reader. The majority use the email
gateway and even if the changes that the forum makes to the subject line
permitted the email readers we use to thread the posts, we delete posts once
we read them, even if we have responded. You must quote enough of the post
to which you are replying so that we know what you are referring to.
I'm going to assume that you are referring to the first query that I
posted. You can limit that to a single database by adding a filter on
dbsname:
select dbsname, tabname, sum( nrows ) as rowcount
from systabnames stn, sysactptnhdr sp
where stn.partnum = sp.partnum
AND dbsname = 'aaa'
group by 1, 2
order by 1, 2
;
My second query is already specific to a single database.
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Mon, Aug 9, 2010 at 9:17 AM, LYNETTE OLIVIER <logizmax84@gmail.com>wrote:
> How will this work if i have two different databases, say aaa, and
> bbb...how
> will i run this query..
>
> as i would like to run this for database aaa
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016e68ee95a40904c048d64cfcc