Differences

This shows you the differences between the selected revision and the current version of the page.

wiki:sysmaster 2012/06/27 16:55 wiki:sysmaster 2021/04/22 13:28 current
Line 5: Line 5:
The window into the entire system, an area of shared memory with an SQL like interface The window into the entire system, an area of shared memory with an SQL like interface
 +
 +=====  How to drop sysmaster =====
 +
 +
 +  dbaccess sysmaster -
 + 
 +  delete from systables where tabid > 99 and partnum < '0x100000'; 
 + 
 +  delete from systables where tabname = 'sysdbspartn';
 + 
 +  close database;
 + 
 +  drop database sysmaster;
=====  How long has the user been connected ===== =====  How long has the user been connected =====
Line 340: Line 353:
            group by  dbsname, tabname              group by  dbsname, tabname 
                       
 +
=====  List all the tables in the temporary dbspaces ===== =====  List all the tables in the temporary dbspaces =====
    select dbsname,tabname     select dbsname,tabname
Line 347: Line 361:
            order by  1             order by  1
                       
 +
 +
 +=====  List all the tables in the temporary dbspaces by session =====
 +
 +    SELECT q1.*, (100 * size_kb / dbs_size) :: DECIMAL(5,2) AS percent
 +      FROM
 +      (
 +        SELECT
 +            t2.owner [1,8],
 +            t2.dbsname [1,18] AS database,
 +            t2.tabname [1,22] AS table,
 +            t3.name [1,10] AS dbspace,
 +            (CURRENT - DBINFO('utc_to_datetime', ti_created)) :: INTERVAL DAY(4) TO SECOND AS life_time,
 +            (ti_nptotal * ti_pagesize/1024) :: INT AS size_kb
 +          FROM
 +            systabinfo AS t1,
 +            systabnames AS t2,
 +            sysdbspaces AS t3
 +          WHERE t2.partnum = ti_partnum
 +            AND t3.dbsnum = TRUNC(t2.partnum/1024/1024)
 +            AND TRUNC(MOD(ti_flags,256)/16) > 0
 +    ) AS q1,
 +    (
 +      SELECT name AS dbspace,
 +          SUM(chksize * d1.pagesize/1024) AS dbs_size
 +        FROM
 +            syschunks AS d1,
 +            sysdbspaces AS d2
 +        WHERE d1.dbsnum = d2.dbsnum
 +        GROUP BY 1
 +    ) AS q2
 +    WHERE q1.dbspace = q2.dbspace
 +    ORDER BY 6 DESC, 5 DESC;
 +
 +
=====  How big is a table ===== =====  How big is a table =====
Line 783: Line 832:
  3 SD secondary server   3 SD secondary server
  4 RS secondary server   4 RS secondary server
 +
Line 804: Line 854:
    where b.dbsnum = trunc(a.partnum/1048576)     where b.dbsnum = trunc(a.partnum/1048576)
      and c.idxname = a.tabname       and c.idxname = a.tabname
 +      and a.dbsname = '<database>'
      and tabid > 99       and tabid > 99
  order by 1   order by 1

Personal Tools