I am looking for a catalog table in db2 which will provide the rowcount & index_id too. I can get the row_count as card from sysibm.systables.But there is no index_id in that. on sql server this can be directly done through sys.dm_db_partition_stats.
Is there any equivalent in db2luw .please let me know.
This is the query in sql server, looking to have the same functionality in db2luw
'SELECT @s_currval= SUM(ow.row_count) '+
' FROM sys.dm_db_partition_stats ow WHERE '+
' object_name(object_id) = @table_name AND (index_id < 2)'
Maybe I am mistaken, but isn't row count for the table the same as row count for all indexes on the table? So you can query syscat.tables with a join on syscat.indexes to get the row count and whatever index names exist for the table.
select t.card, i.FULLKEYCARD, i.uniquerule, i.iid, i.indname
from syscat.tables t
join syscat.indexes i on i.tabschema=t.tabschema and i.tabname=t.tabname
where i.tabschema='SYSIBM' and t.tabname='SYSTABLES'