Informix

I've written one script for fetching all the table and then individual table row count. One part of the script is below :

=========================================================
for i in `grep -v "tabname" "${DIR}/t_tab1"`
do
cnt2=`expr $cnt2 + 1`
cnt=`echo "SELECT COUNT(*) FROM $i " | dbaccess ${DBNAME}`
echo $i " "$cnt >> $FILE1
if [[ $count1 -eq $cnt2 ]]
then
break
fi
done
=========================================================

I run the script in Test server in running well, and now I need to move in Production.

As multiple times script making connection with database and fetching count from table, So do I need to run UPDATE STATISTICS LOW before running this script.

Already there are multiple UPDATE STATISTICS running on few table with Medium and High mode. So if I run UPDATE STATISTICS LOW , will it impact all running UPDATE STATISTICS put on table?

Kindly suggest.


Thanks
Amit

------------------------------
AMIT PATEL
------------------------------

#Informix

Hi Amit,

use sysmaster for the nrows and some more tabinfo values (e.g. npused/nptotal...).
Play with this sql:
echo "select tabname,sum(ti_nrows) from systabinfo,systabnames where ti_partnum=partnum and dbsname='mydb' group by tabname order by tabname "|dbaccess sysmaster

..and the LOW update statistics works for LOW actions, only. You can build and run a generic script with the do_stat from Art K. to make this job easy.

Have fun,
Henri

------------------------------
Henri Cujass
leolo IT, CTO
Germany
------------------------------

Original Message:
Sent: Sun January 03, 2021 09:49 PM
From: AMIT PATEL
Subject: Update Statistics

I've written one script for fetching all the table and then individual table row count. One part of the script is below :

=========================================================
for i in `grep -v "tabname" "${DIR}/t_tab1"`
do
cnt2=`expr $cnt2 + 1`
cnt=`echo "SELECT COUNT(*) FROM $i " | dbaccess ${DBNAME}`
echo $i " "$cnt >> $FILE1
if [[ $count1 -eq $cnt2 ]]
then
break
fi
done
=========================================================

I run the script in Test server in running well, and now I need to move in Production.

As multiple times script making connection with database and fetching count from table, So do I need to run UPDATE STATISTICS LOW before running this script.

Already there are multiple UPDATE STATISTICS running on few table with Medium and High mode. So if I run UPDATE STATISTICS LOW , will it impact all running UPDATE STATISTICS put on table?

Kindly suggest.


Thanks
Amit

------------------------------
AMIT PATEL
------------------------------

#Informix

Amit

If all you need is the table name and row count for each table in your database, you can get away with...

$ echo "SELECT tabname, nrows FROM systables;" | dbaccess ${DBNAME}

...thus limiting the number of times you connect to the database to one. You don't need to worry about Update Statistics for this.

------------------------------
Kirit Rana
------------------------------

Original Message:
Sent: Sun January 03, 2021 09:49 PM
From: AMIT PATEL
Subject: Update Statistics

I've written one script for fetching all the table and then individual table row count. One part of the script is below :

=========================================================
for i in `grep -v "tabname" "${DIR}/t_tab1"`
do
cnt2=`expr $cnt2 + 1`
cnt=`echo "SELECT COUNT(*) FROM $i " | dbaccess ${DBNAME}`
echo $i " "$cnt >> $FILE1
if [[ $count1 -eq $cnt2 ]]
then
break
fi
done
=========================================================

I run the script in Test server in running well, and now I need to move in Production.

As multiple times script making connection with database and fetching count from table, So do I need to run UPDATE STATISTICS LOW before running this script.

Already there are multiple UPDATE STATISTICS running on few table with Medium and High mode. So if I run UPDATE STATISTICS LOW , will it impact all running UPDATE STATISTICS put on table?

Kindly suggest.


Thanks
Amit

------------------------------
AMIT PATEL
------------------------------

#Informix

Hi Kirit,

don't work for a new table. The correct value is in sysmaster everytime.

Regards
Henri

------------------------------
Henri Cujass
leolo IT, CTO
Germany
------------------------------

Original Message:
Sent: Mon January 04, 2021 06:22 AM
From: Kirit Rana
Subject: Update Statistics

Amit

If all you need is the table name and row count for each table in your database, you can get away with...

$ echo "SELECT tabname, nrows FROM systables;" | dbaccess ${DBNAME}

...thus limiting the number of times you connect to the database to one. You don't need to worry about Update Statistics for this.

------------------------------
Kirit Rana

Original Message:
Sent: Sun January 03, 2021 09:49 PM
From: AMIT PATEL
Subject: Update Statistics

I've written one script for fetching all the table and then individual table row count. One part of the script is below :

=========================================================
for i in `grep -v "tabname" "${DIR}/t_tab1"`
do
cnt2=`expr $cnt2 + 1`
cnt=`echo "SELECT COUNT(*) FROM $i " | dbaccess ${DBNAME}`
echo $i " "$cnt >> $FILE1
if [[ $count1 -eq $cnt2 ]]
then
break
fi
done
=========================================================

I run the script in Test server in running well, and now I need to move in Production.

As multiple times script making connection with database and fetching count from table, So do I need to run UPDATE STATISTICS LOW before running this script.

Already there are multiple UPDATE STATISTICS running on few table with Medium and High mode. So if I run UPDATE STATISTICS LOW , will it impact all running UPDATE STATISTICS put on table?

Kindly suggest.


Thanks
Amit

------------------------------
AMIT PATEL
------------------------------
#Informix

Hi Henri

Yes true. Assumed tables were present and correct. lol

------------------------------
Kirit Rana
------------------------------

Original Message:
Sent: Mon January 04, 2021 06:36 AM
From: Henri Cujass
Subject: Update Statistics

Hi Kirit,

don't work for a new table. The correct value is in sysmaster everytime.

Regards
Henri

------------------------------
Henri Cujass
leolo IT, CTO
Germany

Original Message:
Sent: Mon January 04, 2021 06:22 AM
From: Kirit Rana
Subject: Update Statistics

Amit

If all you need is the table name and row count for each table in your database, you can get away with...

$ echo "SELECT tabname, nrows FROM systables;" | dbaccess ${DBNAME}

...thus limiting the number of times you connect to the database to one. You don't need to worry about Update Statistics for this.

------------------------------
Kirit Rana

Original Message:
Sent: Sun January 03, 2021 09:49 PM
From: AMIT PATEL
Subject: Update Statistics

I've written one script for fetching all the table and then individual table row count. One part of the script is below :

=========================================================
for i in `grep -v "tabname" "${DIR}/t_tab1"`
do
cnt2=`expr $cnt2 + 1`
cnt=`echo "SELECT COUNT(*) FROM $i " | dbaccess ${DBNAME}`
echo $i " "$cnt >> $FILE1
if [[ $count1 -eq $cnt2 ]]
then
break
fi
done
=========================================================

I run the script in Test server in running well, and now I need to move in Production.

As multiple times script making connection with database and fetching count from table, So do I need to run UPDATE STATISTICS LOW before running this script.

Already there are multiple UPDATE STATISTICS running on few table with Medium and High mode. So if I run UPDATE STATISTICS LOW , will it impact all running UPDATE STATISTICS put on table?

Kindly suggest.


Thanks
Amit

------------------------------
AMIT PATEL
------------------------------
#Informix

Kirit:

The problem with using systables to determine the number of rows in a table is that the values in systables are ONLY accurately updated when UPDATE STATISTICS LOW is run (or LOW is implied by a HIGH without the DISTRIBUTIONS ONLY clause).

On the other hand, using SELECT COUNT(*) FROM tablename; is always accurate as is using sysmaster as Henri suggested (though I would go directly to sysptnhdr instead of the systabinfo view:

select tabname, nrows
from systabnames st, sysptnhdr sp
where st.partnum = sp.partnum
   and dbsname = 'mydatabase'
   and ncols > 0;

Amit:

If you are querying with COUNT(*) or through sysmaster, there is no need to run UPDATE STATISTICS LOW or any other way to get accurate counts! And, as Henri said, the LOW will not affect the data distributions generated by HIGH or MEDIUM as long as you do not include the DROP DISTRIBUTIONS clause!

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------

Original Message:
Sent: Mon January 04, 2021 07:16 AM
From: Kirit Rana
Subject: Update Statistics

Hi Henri

Yes true. Assumed tables were present and correct. lol

------------------------------
Kirit Rana

Original Message:
Sent: Mon January 04, 2021 06:36 AM
From: Henri Cujass
Subject: Update Statistics

Hi Kirit,

don't work for a new table. The correct value is in sysmaster everytime.

Regards
Henri

------------------------------
Henri Cujass
leolo IT, CTO
Germany

Original Message:
Sent: Mon January 04, 2021 06:22 AM
From: Kirit Rana
Subject: Update Statistics

Amit

If all you need is the table name and row count for each table in your database, you can get away with...

$ echo "SELECT tabname, nrows FROM systables;" | dbaccess ${DBNAME}

...thus limiting the number of times you connect to the database to one. You don't need to worry about Update Statistics for this.

------------------------------
Kirit Rana

Original Message:
Sent: Sun January 03, 2021 09:49 PM
From: AMIT PATEL
Subject: Update Statistics

I've written one script for fetching all the table and then individual table row count. One part of the script is below :

=========================================================
for i in `grep -v "tabname" "${DIR}/t_tab1"`
do
cnt2=`expr $cnt2 + 1`
cnt=`echo "SELECT COUNT(*) FROM $i " | dbaccess ${DBNAME}`
echo $i " "$cnt >> $FILE1
if [[ $count1 -eq $cnt2 ]]
then
break
fi
done
=========================================================

I run the script in Test server in running well, and now I need to move in Production.

As multiple times script making connection with database and fetching count from table, So do I need to run UPDATE STATISTICS LOW before running this script.

Already there are multiple UPDATE STATISTICS running on few table with Medium and High mode. So if I run UPDATE STATISTICS LOW , will it impact all running UPDATE STATISTICS put on table?

Kindly suggest.


Thanks
Amit

------------------------------
AMIT PATEL
------------------------------
#Informix

Original Message:
Sent: 1/3/2021 9:50:00 PM
From: AMIT PATEL
Subject: Update Statistics

I've written one script for fetching all the table and then individual table row count. One part of the script is below :

=========================================================
for i in `grep -v "tabname" "${DIR}/t_tab1"`
do
cnt2=`expr $cnt2 + 1`
cnt=`echo "SELECT COUNT(*) FROM $i " | dbaccess ${DBNAME}`
echo $i " "$cnt >> $FILE1
if [[ $count1 -eq $cnt2 ]]
then
break
fi
done
=========================================================

I run the script in Test server in running well, and now I need to move in Production.

As multiple times script making connection with database and fetching count from table, So do I need to run UPDATE STATISTICS LOW before running this script.

Already there are multiple UPDATE STATISTICS running on few table with Medium and High mode. So if I run UPDATE STATISTICS LOW , will it impact all running UPDATE STATISTICS put on table?

Kindly suggest.


Thanks
Amit

------------------------------
AMIT PATEL
------------------------------

#Informix