Differences

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

wiki:sysmaster 2010/04/20 14:47 wiki:sysmaster 2014/04/30 20:59 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 286: Line 299:
            group by 1             group by 1
                       
 +
Line 307: Line 321:
            or sqx_executions > 1)             or sqx_executions > 1)
            and sqx_bufreads / sqx_executions > 50000             and sqx_bufreads / sqx_executions > 50000
 +
 +The following SQL identifies the unused indexes
 +
 +  SELECT
 +    x1.dbsname,
 +    x3.tabname,
 +    x1.tabname AS idxname,
 +    (x4.npused * x4.pagesize / 1024) :: INT AS size_kb
 +  FROM
 +    systabnames AS x1, sysptntab AS x2,
 +    systabnames AS x3, sysptnhdr AS x4
 +  WHERE x2.partnum = x1.partnum
 +    AND x2.partnum != x2.tablock
 +    AND x3.partnum = x2.tablock
 +    AND x4.partnum = x1.partnum
 +    AND x1.dbsname MATCHES '[a-z]*'
 +    AND x3.tabname MATCHES '[a-z]*'
 +    AND x2.pf_isread = 0
 +    AND x2.pf_iswrite > 0
 +  ORDER BY 4 DESC
 +
In the later engines, or databases with detached indices this information is also available via Table IO stats but the indexname as the table filter In the later engines, or databases with detached indices this information is also available via Table IO stats but the indexname as the table filter
Line 761: Line 796:
  3 SD secondary server   3 SD secondary server
  4 RS secondary server   4 RS secondary server
 +
Line 782: Line 818:
    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
 +
 +===== What are the unused indexes =====
 +
 +  select sysindexes.idxname index, sysindexes.idxtype as type, sysptprof.isreads reads
 +    from sysindexes, sysmaster:sysptprof sysptprof
 +    where sysindexes.idxname = sysptprof.tabname
 +      and sysptprof.isreads = 0
 +    order by reads desc, idxname ;

Personal Tools