How many rows

Is there a query to know How many rows have each table on a database ? Thanks in advance. Enviado desde mi iPhone

Simplest is probably: update statistics; select tabname, nrows from systables where tabid>99; j. > On Mar 11, 2015, at 3:28 PM, Jorge Valenzuela <jorgervt@gmail.com> = wrote: >=20 > Is there a query to know=20 > How many rows have each table on a database ?=20 >=20 > Thanks in advance.=20 >=20 > Enviado desde mi iPhone=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

Thank you. Enviado desde mi iPhone > El 11/03/2015, a las 12:33, Jack Parker <jack.parker4@verizon.net> escribió: > > Simplest is probably: > > update statistics; > select tabname, nrows from systables where tabid>99;> > j. > >>> On Mar 11, 2015, at 3:28 PM, Jorge Valenzuela <jorgervt@gmail.com> = >> wrote: >> =20 >> Is there a query to know=20 >> How many rows have each table on a database ?=20 >> =20 >> Thanks in advance.=20 >> =20 >> Enviado desde mi iPhone=20 >> =20 >> =20 >> = > **************************************************************************= > *****=20 >> Forum Note: Use "Reply" to post a response in the discussion forum.=20= > >> =20 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

select st.tabname, sp.nrows from sysmaster:systabnames stn, sysmaster:sysptnhdr sp, systables st where stn.dbsname = "mydatabase" and stn.tabname = st.tabname and stn.partnum = sp.partnum and st.tabid > 99 and st.tabtype = 'T' ; Or, if you have my utils2_ak package, you could create a little script like this one and name it "countem": #!/bin/ksh dbs=$1 table=$2 dbaccess $dbs - <<EOF select "$table", count(*) from $table; EOF ### End of script: countem Then you can use my dbscript utility like this: dbscript -d mydatabase -c 'countem mydatabase %s' | ksh | egrep '[0-9]' document_contents_master 736454 document_contents_comments 921283 document_contents_extent 0 using_date 1000000 using_datetime 1000000 document_contents_wide 736454 document_contents 736454 with_datetime 1000000 with_date 1000000 yts_datetime 1000000 wyts_datetime 1000000 Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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 Wed, Mar 11, 2015 at 3:28 PM, Jorge Valenzuela <jorgervt@gmail.com> wrote: > Is there a query to know > How many rows have each table on a database ? > > Thanks in advance. > > Enviado desde mi iPhone > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0111d156ecebf8051108bc84

Bare in mind that in the query you can have more than one row per table if they are fragmented if that is not wath you want, you'll have to group by the tabname and sum the nrows: infx1210@infxsrv:informix-> dbaccess db - Database selected. > CREATE TABLE tab1( > col1 INT > ) FRAGMENT BY ROUND ROBIN > PARTITION tab1_p1 IN data_dbs, > PARTITION tab1_p2 IN data_dbs; Table created. > INSERT INTO tab1 VALUES(1); 1 row(s) inserted. > SELECTst.tabname, sp.nrows > FROM sysmaster:systabnames stn, sysmaster:sysptnhdr sp, systables st > WHERE stn.dbsname = DBINFO('dbname') > AND stn.tabname = st.tabname > AND stn.partnum = sp.partnum > AND st.tabid > 99 > AND st.tabtype = 'T'; tabname tab1 nrows 1 tabname tab1 nrows 0 2 row(s) retrieved. > SELECT st.tabname, SUM(sp.nrows) AS nrows > FROM sysmaster:systabnames stn, sysmaster:sysptnhdr sp, systables st > WHERE stn.dbsname = DBINFO('dbname') > AND stn.tabname = st.tabname > AND stn.partnum = sp.partnum > AND st.tabid > 99 > AND s t.tabtype = 'T' > GROUP BY 1; tabname tab1 nrows 1 1 row(s) retrieved. > Keen regards. On Wed, 11 Mar 2015 at 20:02 Art Kagel <art.kagel@gmail.com> wrote: > select st.tabname, sp.nrows > from sysmaster:systabnames stn, sysmaster:sysptnhdr sp, systables st > where stn.dbsname = "mydatabase"> > and stn.tabname = st.tabname > > and stn.partnum = sp.partnum > > and st.tabid > 99 > > and st.tabtype = 'T' > ; > > Or, if you have my utils2_ak package, you could create a little script like > this one and name it "countem": > #!/bin/ksh > dbs=$1 > table=$2 > dbaccess $dbs - <<EOF > select "$table", count(*) > from $table; > EOF > ### End of script: countem > > Then you can use my dbscript utility like this: > > dbscript -d mydatabase -c 'countem mydatabase %s' | ksh | egrep '[0-9]' > > document_contents_master 736454 > document_contents_comments 921283 > document_contents_extent 0 > using_date 1000000 > using_datetime 1000000 > document_contents_wide 736454 > document_contents 736454 > with_datetime 1000000 > with_date 1000000 > yts_datetime 1000000 > wyts_datetime 1000000 > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on 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 Wed, Mar 11, 2015 at 3:28 PM, Jorge Valenzuela <jorgervt@gmail.com> > wrote: > > > Is there a query to know > > How many rows have each table on a database ? > > > > Thanks in advance. > > > > Enviado desde mi iPhone > > > > > > > > > ************************************************************ > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --089e0111d156ecebf8051108bc84 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d04182558a6fabc05110b0eae

Fair enough. This version solves the problem with partitioned tables appearing multiple times: select st.tabname, sum( sp.nrows) from sysmaster:systabnames stn, sysmaster:sysptnhdr sp, systables st where stn.dbsname = "mydatabase" and stn.tabname = st.tabname and stn.partnum = sp.partnum and st.tabid > 99 and st.tabtype = 'T' group by 1; Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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 Wed, Mar 11, 2015 at 6:48 PM, Ricardo Henriques < ricardoaireshenriques@gmail.com> wrote: > Bare in mind that in the query you can have more than one row per table if > they are fragmented if that is not wath you want, you'll have to group by > the tabname and sum the nrows: > > infx1210@infxsrv:informix-> dbaccess db - > > Database selected. > > > CREATE TABLE tab1(> > > col1 INT > > > ) FRAGMENT BY ROUND ROBIN > > > PARTITION tab1_p1 IN data_dbs, > > > PARTITION tab1_p2 IN data_dbs; > > Table created. > > > INSERT INTO tab1 VALUES(1);> > 1 row(s) inserted. > > > SELECTst.tabname, sp.nrows > > > FROM sysmaster:systabnames stn, sysmaster:sysptnhdr sp, systables st > > > WHERE stn.dbsname = DBINFO('dbname') > > > AND stn.tabname = st.tabname > > > AND stn.partnum = sp.partnum > > > AND st.tabid > 99 > > > AND st.tabtype = 'T'; > > tabname tab1 > > nrows 1 > > tabname tab1 > > nrows 0 > > 2 row(s) retrieved. > > > SELECT st.tabname, SUM(sp.nrows) AS nrows> > > FROM sysmaster:systabnames stn, sysmaster:sysptnhdr sp, systables st > > > WHERE stn.dbsname = DBINFO('dbname') > > > AND stn.tabname = st.tabname > > > AND stn.partnum = sp.partnum > > > AND st.tabid > 99 > > > AND s t.tabtype = 'T' > > > GROUP BY 1; > > tabname tab1 > > nrows 1 > > 1 row(s) retrieved. > > > > > Keen regards. > > On Wed, 11 Mar 2015 at 20:02 Art Kagel <art.kagel@gmail.com> wrote: > > > select st.tabname, sp.nrows > > from sysmaster:systabnames stn, sysmaster:sysptnhdr sp, systables st > > where stn.dbsname = "mydatabase"> > > > and stn.tabname = st.tabname > > > > and stn.partnum = sp.partnum > > > > and st.tabid > 99 > > > > and st.tabtype = 'T' > > ; > > > > Or, if you have my utils2_ak package, you could create a little script > like > > this one and name it "countem": > > #!/bin/ksh > > dbs=$1 > > table=$2 > > dbaccess $dbs - <<EOF > > select "$table", count(*) > > from $table; > > EOF > > ### End of script: countem > > > > Then you can use my dbscript utility like this: > > > > dbscript -d mydatabase -c 'countem mydatabase %s' | ksh | egrep '[0-9]' > > > > document_contents_master 736454 > > document_contents_comments 921283 > > document_contents_extent 0 > > using_date 1000000 > > using_datetime 1000000 > > document_contents_wide 736454 > > document_contents 736454 > > with_datetime 1000000 > > with_date 1000000 > > yts_datetime 1000000 > > wyts_datetime 1000000 > > > > Art > > > > Art S. Kagel, President and Principal Consultant > > ASK Database Management > > www.askdbmgt.com > > > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on 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 Wed, Mar 11, 2015 at 3:28 PM, Jorge Valenzuela <jorgervt@gmail.com> > > wrote: > > > > > Is there a query to know > > > How many rows have each table on a database ? > > > > > > Thanks in advance. > > > > > > Enviado desde mi iPhone > > > > > > > > > > > > > > ************************************************************ > > ******************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --089e0111d156ecebf8051108bc84 > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --f46d04182558a6fabc05110b0eae > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bdc143862dc0f05110c9476

Thank you very much Enviado desde mi iPhone > El 11/03/2015, a las 17:37, Art Kagel <art.kagel@gmail.com> escribió: > > Fair enough. This version solves the problem with partitioned tables > appearing multiple times: > > select st.tabname, sum( sp.nrows) > from sysmaster:systabnames stn, sysmaster:sysptnhdr sp, systables st > where stn.dbsname = "mydatabase" > and stn.tabname = st.tabname > and stn.partnum = sp.partnum > and st.tabid > 99 > and st.tabtype = 'T' > group by 1> ; > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on 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 Wed, Mar 11, 2015 at 6:48 PM, Ricardo Henriques < > ricardoaireshenriques@gmail.com> wrote: > >> Bare in mind that in the query you can have more than one row per table if >> they are fragmented if that is not wath you want, you'll have to group by >> the tabname and sum the nrows: >> >> infx1210@infxsrv:informix-> dbaccess db - >> >> Database selected. >> >>> CREATE TABLE tab1(>> >>> col1 INT >> >>> ) FRAGMENT BY ROUND ROBIN >> >>> PARTITION tab1_p1 IN data_dbs, >> >>> PARTITION tab1_p2 IN data_dbs; >> >> Table created. >> >>> INSERT INTO tab1 VALUES(1);>> >> 1 row(s) inserted. >> >>> SELECTst.tabname, sp.nrows >> >>> FROM sysmaster:systabnames stn, sysmaster:sysptnhdr sp, systables st >> >>> WHERE stn.dbsname = DBINFO('dbname') >> >>> AND stn.tabname = st.tabname >> >>> AND stn.partnum = sp.partnum >> >>> AND st.tabid > 99 >> >>> AND st.tabtype = 'T'; >> >> tabname tab1 >> >> nrows 1 >> >> tabname tab1 >> >> nrows 0 >> >> 2 row(s) retrieved. >> >>> SELECT st.tabname, SUM(sp.nrows) AS nrows>> >>> FROM sysmaster:systabnames stn, sysmaster:sysptnhdr sp, systables st >> >>> WHERE stn.dbsname = DBINFO('dbname') >> >>> AND stn.tabname = st.tabname >> >>> AND stn.partnum = sp.partnum >> >>> AND st.tabid > 99 >> >>> AND s t.tabtype = 'T' >> >>> GROUP BY 1; >> >> tabname tab1 >> >> nrows 1 >> >> 1 row(s) retrieved. >> >> >> Keen regards. >> >>> On Wed, 11 Mar 2015 at 20:02 Art Kagel <art.kagel@gmail.com> wrote: >>> >>> select st.tabname, sp.nrows >>> from sysmaster:systabnames stn, sysmaster:sysptnhdr sp, systables st >>> where stn.dbsname = "mydatabase">>> >>> and stn.tabname = st.tabname >>> >>> and stn.partnum = sp.partnum >>> >>> and st.tabid > 99 >>> >>> and st.tabtype = 'T' >>> ; >>> >>> Or, if you have my utils2_ak package, you could create a little script >> like >>> this one and name it "countem": >>> #!/bin/ksh >>> dbs=$1 >>> table=$2 >>> dbaccess $dbs - <<EOF >>> select "$table", count(*) >>> from $table; >>> EOF >>> ### End of script: countem >>> >>> Then you can use my dbscript utility like this: >>> >>> dbscript -d mydatabase -c 'countem mydatabase %s' | ksh | egrep '[0-9]' >>> >>> document_contents_master 736454 >>> document_contents_comments 921283 >>> document_contents_extent 0 >>> using_date 1000000 >>> using_datetime 1000000 >>> document_contents_wide 736454 >>> document_contents 736454 >>> with_datetime 1000000 >>> with_date 1000000 >>> yts_datetime 1000000 >>> wyts_datetime 1000000 >>> >>> Art >>> >>> Art S. Kagel, President and Principal Consultant >>> ASK Database Management >>> www.askdbmgt.com >>> >>> Blog: http://informix-myview.blogspot.com/ >>> >>> Disclaimer: Please keep in mind that my own opinions are my own opinions >>> and do not reflect on 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 Wed, Mar 11, 2015 at 3:28 PM, Jorge Valenzuela <jorgervt@gmail.com> >>> wrote: >>> >>>> Is there a query to know >>>> How many rows have each table on a database ? >>>> >>>> Thanks in advance. >>>> >>>> Enviado desde mi iPhone >>> ************************************************************ >>> ******************* >>>> Forum Note: Use "Reply" to post a response in the discussion forum. >>> >>> --089e0111d156ecebf8051108bc84 >>> >>> >>> ************************************************************ >>> ******************* >>> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> --f46d04182558a6fabc05110b0eae > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bdc143862dc0f05110c9476 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

This 1-liner takes care of fragmented or not (DBASE is your database, TABLE your table of course)... echo "database $DBASE; set isolation dirty read; select count(*) from $TABLE;" \\\\ | dbaccess $DBASE 2>/dev/null|tail -2|head -1 | tr -d " " | awk '{print $1" rows for ""'$DBASE'"":""'$TABLE'"}' For a simple select like this, we're rolling up the row count of all fragments since we're using table name. I use this all the time when I just want total row count, regardless of fragments. Real example: box:/home/informix> nrows xx:big_table 1661619190 rows for xx:big_table The 1.6B rows is the accurate number of total rows across 3 fragments. ("oncheck -pt dbase:table | grep rows" below. First 3 fragments are data fragments - I've cut out the index fragments with row count of 0): Number of rows 553871992 Number of rows 553873934 Number of rows 553873300 Mark Mark Scranton www.markscranton.com The Mark Scranton Group