Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2009
    Location
    USA
    Posts
    53

    Unanswered: row count in the table

    Looking for a way to get the row count in the table without using SELECT or executing RUNSTATS. Any options?

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    How about MQT
    it NEEDS your base table must have a unique index or pk....
    and still have to excute select from mqt.....
    But it will be much more efficient than count(*) from basetable.....

    create table mqt as ( select pk,count(*) as cnt from basetable group by pk) data initially deferred refresh immediate

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    MQT seems good idea.
    But, I doubt wheather it is effective or not.

    The MQT has same number of rows as basetable.
    (because, pk is also unique, then group by pk results all count(*) are one.)
    So, if compared index scan of pk of basetable and table space scan of MQT,
    I'm not sure the MQT is more effective.

  4. #4
    Join Date
    Jul 2009
    Location
    USA
    Posts
    53
    I cannot query the table or use RUNSTATS. Any other options?

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2aix View Post
    I cannot query the table or use RUNSTATS. Any other options?
    Try a psychic.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jul 2009
    Location
    USA
    Posts
    53
    Quote Originally Posted by Marcus_A View Post
    Try a psychic.

    Already did. What else can you suggest?

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2aix View Post
    Already did. What else can you suggest?
    I don't think you can count the rows unless you count them.

    However, you might want to look at using sampling with runstats if you think a full runstats takes too long (see manual). Obviously, the number many not be exact.

    Also, if your table has a transaction date of some kind, or a PK that always gets larger (like an indentity column) you could only count the new rows since the last time you counted them, to save some time.

    If using an SQL statement, rather than runstats, use count(*) and not count (column-name). This will let DB2 to use whichever method it thinks is fastest to count, and will propbably use an index only and and actually touch the data pages.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Db2pd -tcbstats

    Sshows the number of inserts and deletes since the last runstats or since last db activation, whichever is later. If there has been a runstats since db activation, you can add/subtract to card in the catalog.



    I know, the next condtion is, you dont want to issue a command

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by sathyaram_s View Post
    I know, the next condtion is, you dont want to issue a command
    I wonder what is the ultimate goal of db2aix. I have a feeling that it is different from "counting table rows without counting".

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    If you don't want any access to table/indexes, don't want to execute runstats and willing to unload all data, then you can use db2dart /ddel and count the lines in a file it creates.

  11. #11
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    If you track all inserts & deletes (e.g. through a trigger) you'll never need to issue count(*) [unless maybe initially, when the triggers are created] to obtain an exact row count.

    That's essentially what the "real-time statistics" (RTS) do (at least, on DB2 for z/OS; don't know whether this exists on DB2 for LUW). The counts are stored in catalog table SYSIBM.TABLESPACESTATS (per tablespace partition, but that's per table for non-partitioned single-table tablespaces).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •