Informix

nested-group-icon.png

DB2

Expand all | Collapse all

Update Statistics

  • 1.  Update Statistics

    Posted 14 days ago
    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
    ------------------------------


  • 2.  RE: Update Statistics

    Posted 14 days ago
    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
    ------------------------------



  • 3.  RE: Update Statistics

    Posted 14 days ago
    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
    ------------------------------



  • 4.  RE: Update Statistics

    Posted 14 days ago
    Edited by Henri Cujass 14 days ago
    Hi Kirit,

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

    Regards
    Henri

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



  • 5.  RE: Update Statistics

    Posted 14 days ago
    Hi Henri

    Yes true. Assumed tables were present and correct. lol

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



  • 6.  RE: Update Statistics

    Posted 13 days ago

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



  • 7.  RE: Update Statistics

    Posted 13 days ago
    Amit,

    In my experience, even with statistics updated, select count(*) can take an awfully long time. AFAIK it is counting rows, not depending on the updated statistics.

    In many of my scripts, I go directly to the systabinfo or sysptnhdr  view:

    select trim(tnt.dbsname) dbsname,       -- Table Partitions
           trim(tnt.owner)   owner,
           trim(tnt.tabname) tabname,
           trim(tnt.dbsname) || ':' || trim(tnt.owner) || '.' || trim(tnt.tabname)
                             full_name,
           'T'               partn_type,
           hex(tnt.partnum)  partition,
           hex(ph.lockid)    lockid,
           ds.name dbspace,
           ph.rowsize,
           ph.nrows,  --*** This is what Amit is looking for *** (I think)
           ph.nextns,
           (ph.pagesize/1024)::int pagesize,
           ph.nptotal,
           ph.npused
      from systabnames tnt,
           sysptnhdr ph,
           sysdbspaces ds
     where (   ph.partnum = ph.lockid
            or ph.nkeys  != 1)
       and tnt.partnum = ph.partnum
       and ph.dbsnum = ds.dbsnum
       and mod(tnt.partnum, 1048576) != 1
       and tnt.dbsname = "whatever"

    I hope the Courier font stays with this; the SQL is so neatly aligner.

    Art, now's the time for you to pipe up: Is the sysptnhdr.nrows always up to date?  I strongly suspect it is.

    -- Jacob S.