Results 1 to 4 of 4

Thread: Top Tables in E1 /DB2

  1. #1

    Top Tables in E1 /DB2

    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

  2. #2
    Senior Member Larry_Jones's Avatar
    Join Date
    Nov 2000
    Location
    Spokane, WA, USA
    Posts
    3,073
    Define "Top".
    Largest tables in number of rows?
    Most transactions?
    Most used Master tables?
    Larry Jones
    E1 9.1 - TR 9.1.5 on Win 2012 R2. Oracle DB 11.2.04
    WebLogic 12.1.3, Wintel, BI Publisher

  3. #3
    Senior Member altquark's Avatar
    Join Date
    Oct 2000
    Location
    Kansas City, MO
    Posts
    2,636
    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 by altquark; 01-11-2017 at 03:31 PM.
    Jon Steel
    EnterpriseOne/SOA Technical Architect
    erpSOURCING LLC
    http://www.erpsourcing.com
    cto@spla.sh
    24/7 Assistance - (904) 382 5701

  4. #4
    Thanks JOn and Larry

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
The legal restrictions and terms of use applicable to this site are available here.
Use of this site signifies your agreement to the terms of use.
JDELIST is NOT affiliated with JD Edwards® & Company, Oracle or Peoplesoft. Contents of this site are neither endorsed nor approved by JD Edwards® & Company, Oracle or Peoplesoft.