Top Tables in E1 /DB2

bhabeam

Member
By chance anyone has a SQL command in handy which pulls out the top 10 or top 30 tables on E1/DB2

I sense that these are the big ones F4211,F0101,F4101,F0911,F4311 But need more data

Thanks in advance
 
Define "Top".
Largest tables in number of rows?
Most transactions?
Most used Master tables?
 
On the iSeries :

select table_name, number_rows from QSYS2.SYSTABLESTAT

where table_schema = 'PRODDTA'
order by number_rows desc

On SQL Server :

SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8/1024 AS TotalSpaceMB,
SUM(a.used_pages) * 8/1024 AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8/1024 AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
TotalSpaceMB desc, t.Name

On Oracle :

select owner, table_name, numrows from sys.alltables where owner = 'PRODDTA' order by numrows desc;

 
Last edited:
Back
Top