Differences

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

wiki:sysmaster 2010/04/07 14:38 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 45: Line 58:
            where systabnames.partnum = sysptntab.partnum             where systabnames.partnum = sysptntab.partnum
                       
 +
=====  How long has the database engine been running ===== =====  How long has the database engine been running =====
       
-            select dbinfo('UTC_TO_DATETIME', sh_curtime) +            select dbinfo('UTC_TO_DATETIME', sh_curtime) - dbinfo('UTC_TO_DATETIME', sh_boottime)
-            - dbinfo('UTC_TO_DATETIME', sh_boottime)+
            from sysshmvals             from sysshmvals
                       
Line 75: Line 88:
            and syschunks.dbsnum = sysdbspaces.dbsnum             and syschunks.dbsnum = sysdbspaces.dbsnum
                       
 +
 +
 +
=====  What is the current version of the engine ===== =====  What is the current version of the engine =====
    select owner     select owner
-            from sysmaster:systables .+            from sysmaster:systables
            where tabid = 99             where tabid = 99
                       
 +
 +Works fine on the earlier engines, not so good on the later ones as the Official Release doesn't tally back  i.e. 10.00.FC6 reports 9.50C1, and 11.50.FC6 reports 9.53C1 etc. However,
 +
 +  DBINFO('version','server-type')
 +  DBINFO('version','major')
 +  DBINFO('version','minor')
 +  DBINFO('version','os')
 +  DBINFO('version','full')
 +
 +All should work
 +
 +
=====  How to list the users ===== =====  How to list the users =====
    select unique username     select unique username
Line 222: Line 250:
            from sysdatabases             from sysdatabases
                       
 +
=====  What is the database to dbspace mapping ===== =====  What is the database to dbspace mapping =====
       
Line 241: Line 270:
            or             or
                       
-            select DBINFO("DBSPACE",partnum") dbspace+            select DBINFO("DBSPACE",partnum) dbspace,
            name,             name,
            owner             owner
            from sysdatabases             from sysdatabases
                       
 +
=====  What is the blobspace page size ===== =====  What is the blobspace page size =====
    select prtpage/PAGESIZE     select prtpage/PAGESIZE
Line 269: Line 299:
            group by 1             group by 1
                       
 +
Line 290: 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 301: 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 308: 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 434: Line 522:
            order by 1,5             order by 1,5
                       
 +
=====  How much space is left in a chunk ===== =====  How much space is left in a chunk =====
Line 446: Line 535:
            and syschunks.chknum = syschfree.chknum             and syschunks.chknum = syschfree.chknum
            order by 1,2             order by 1,2
-            Alternatively, this information is contained in  +             
-            <a href="/onstat/onstat_d.html">onstat -d</a>+Alternatively, this information is contained in [[http://www.oninit.com/onstat/onstat_d.html|onstat d]]  
                       
 +
=====  What is the disk IO for the chunk ===== =====  What is the disk IO for the chunk =====
       
Line 699: Line 790:
            AND e.dbsname NOT IN ('rootdbs', 'sysmaster', 'sysadmin', 'sysuser', 'sysutils')             AND e.dbsname NOT IN ('rootdbs', 'sysmaster', 'sysadmin', 'sysuser', 'sysutils')
        ORDER BY 1, 2         ORDER BY 1, 2
 +
 +
=====  What mode is the engine running in ===== =====  What mode is the engine running in =====
Line 704: Line 797:
  select sh_mode   select sh_mode
    from sysmaster:sysshmvals;     from sysmaster:sysshmvals;
 +
 +IDS 7,9,10,11
 +
 +  -1 Offline (-1 = 255)
 +  0 Initialisation
 +  1 Quiescent
 +  2 Recovery
 +  3 Backup
 +  4 Shutdown
 +  5 Online
 +  6 Abort
 +
 +IDS 8
 +
 +  0 Initialisation
 +  1 Quiescent
 +  2 Micro kernel
 +  3 Recovery
 +  4 Backup
 +  5 Shutdown
 +  6 Online
 +  7 Abort
 +
 +
 +=====  What type of instance is running =====
 +
 +  select ha_type
 +    from sysmaster:sysha_type
 +
 +
 +  0 Not part of a high-availability environment
 +  1 Primary server
 +  2 HDR secondary server
 +  3 SD secondary server
 +  4 RS secondary server
 +
 +
 +
 +
 +
 +===== What dbspaces are being used by a database =====
 +
 +  select distinct dbinfo("dbspace",b.partnum)
 +    from <database>:sysfragments a, sysmaster:systabnames b
 +    where a.partn = b.partnum
 +      and tabid > 99
 +  union
 +  select distinct dbinfo("dbspace",b.partnum)
 +    from <database>:systables a, sysmaster:systabnames b
 +    where a.partnum = b.partnum
 +      and a.partnum != 0
 +      and tabid > 99
 +  union
 +  select b.name dbs_name
 +    from sysmaster:systabnames a, sysmaster:sysdbspaces b, <database>:sysindexes c
 +    where b.dbsnum = trunc(a.partnum/1048576)
 +      and c.idxname = a.tabname
 +      and a.dbsname = '<database>'
 +      and tabid > 99
 +  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