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

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

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.

I cannot query the table or use RUNSTATS. Any other options?

Try a psychic.

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.

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

I wonder what is the ultimate goal of db2aix. I have a feeling that it is different from "counting table rows without counting".

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.

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