Table of Contents

Sysmaster

What is Sysmaster

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

          select sid, username,
          dbinfo('UTC_TO_DATETIME',connected) conection_time,
          current - dbinfo('UTC_TO_DATETIME',connected)
          connected_since
          from syssessions
          order by 2,3
           

How long has the user been idle

          SELECT s.sid, s.username, q.odb_dbname database, 
          dbinfo('UTC_TO_DATETIME',s.connected) conection_time,
          dbinfo('UTC_TO_DATETIME',t.run_time) last_run_time, 
          current - dbinfo('UTC_TO_DATETIME',t.run_time) idle_time
          FROM syssessions s, systcblst t,  sysrstcb r, sysopendb q
          WHERE t.tid = r.tid AND s.sid = r.sid AND s.sid = q.odb_sessionid 
          ORDER BY 6 DESC

How to link a session to a transaction

  select tx_id 
     from systrans  
     where tx_addr =  select us_txp  
          from sysuserthreads  
          where us_sid = SESSIONID) 

Historically, this table could not tell you if you are in a real transaction ie a BEGIN WORK unless that transaction had done real work as the flags where not changed when the transaction is started.

How to find the busy tables

  select systabnames.tabname,
          sysptntab.pf_isread, 
          sysptntab.pf_iswrite, 
          sysptntab.pf_isrwrite, 
          sysptntab.pf_isdelete, 
          sysptntab.pf_seqscans  
          from systabnames, sysptntab  
          where systabnames.partnum = sysptntab.partnum 
           

How long has the database engine been running

  
          select dbinfo('UTC_TO_DATETIME', sh_curtime) - dbinfo('UTC_TO_DATETIME', sh_boottime)
          from sysshmvals
          

How to find the current SQL statement

  select sqs_statement  
          from syssqlstat 
          where sqs_statement; 
          or
          select sqx_sessionid, 
          sqx_conbno, 
          sqx_sqlstatement 
          from syssqexplain, 
          sysscblst  
          where sqx_sessionid = sid  
          order by 1,2  
           

Where are the logs

  select number, 
          name  
          from syslogfil, 
          syschunks, 
          sysdbspaces 
          where syslogfil.chunk = syschunks.chknum
          and syschunks.dbsnum = sysdbspaces.dbsnum 
          

What is the current version of the engine

  select owner 
          from sysmaster:systables
          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

  select unique username 
          from  syssessions

What is the disk IO for a session

  select syssesprof.sid,
          isreads, 
          iswrites, 
          isrewrites, 
          isdeletes 
          from syssesprof, syssessions  
          where syssesprof.sid = syssessions.sid 

How to convert a PID to a session ID

  select sid 
          fromsyssessions 
          where pid = 'pid'
          

How to find the sequential scans for a table

  select dbsname, tabname, seqscans 
          from sysptprof;
          

How to list the logging status of a database

  select name,
          is_logging,
          is_buff_log,
          is_ansi,
          is_nls
          from sysdatabases
  The flags are:
          0:  Not logged
          1:  Buffered Logging
          2:  Unbuffered Logging
          4:  Ansi
          8:  NLS
          

How to list the locks

  select dbsname,
          tabname,
          rowidlk,
          keynum,
          type  
          from syslocks,
          syssessions 
          where owner = "sid"
          
        
      B   Byte lock
      IS  Intent shared lock
      S   Shared lock
      XS  Repeatable read shared lock
      U   Update lock
      IX  Intent exclusive lock
      SIX Shared intent exclusive
      X   Exclusive lock
      XR  Repeatble read exclusive
          
          

How to list the database locks

SELECT "database lock" table_name, l.type lock_type, l.keynum index_num,
  HEX(l.rowidlk) rowid, s.sid session_id, s.username, s.pid, s.hostname,
  q.sqs_statement statement 
FROM syslocks l, sysdatabases d, syssessions s, syssqlstat q 
WHERE d.name = ' database_name ' 
  AND l.rowidlk = d.rowid
  AND l.owner = s.sid 
  AND dbsname = 'sysmaster' 
  AND tabname = 'sysdatabases' 
  AND s.sid = q.sqs_sessionid 
 
UNION ALL 

SELECT l.tabname, l.type, l.keynum, HEX(l.rowidlk), s.sid, s.username,
  s.pid, s.hostname, q.sqs_statement 
FROM syslocks l, syssessions s, syssqlstat q 
WHERE l.dbsname = ' database_name ' 
  AND l.owner = s.sid 
  AND s.sid = q.sqs_sessionid 
  AND dbsname = 'sysmaster' 
  AND tabname = 'sysdatabases' 
ORDER BY 5; 

How to display free log space

  select number, 
          uniqid,
          size, 
          used, 
          (used/size*100)  
          from syslogs 
          where uniqid >= 
          (select min(tx_loguniq) 
          from systrans 
          where tx_loguniq > 0) 
           
          union  
          select number, 
          uniqid, 
          size,
          0, 
          0.00  
          from syslogs 
          where uniqid < 
          (select min(tx_loguniq) 
          from systrans 
          where tx_loguniq > 0) 
           

What is the page size

  select sh_pagesize 
          from sysshmvals

How many rows are there in the table

The nrows column on systables is only maintained as a result of update statistics but the current number of rows is always maintained accurately within sysmaster.

          
          select ti_nrows  
          from systabnames, 
          systabinfo 
          where systabn.tabname = TABLENAME
          and dbsname = DATABASE 
          and systabinfo.ti_partnum = systabnmes.partnum 
           

How to get the username

  select username, uid
          from sysmaster:syssessions 
          where sid = 'session id'

How to list all the databases

  select name 
          from sysdatabases
          

What is the database to dbspace mapping

  
          select name 
          from  sysdbspaces 
          where dbsnum in 
          (select trunc(partnum / 1048576)  
          from sysdatabases
          where name = "DATABASE") 
          In the later engines
          
          select sysdatabases.name, 
          sysdatabases.owner,
          sysdbspaces.name  
          from sysdbspaces,sysdatabases  
          where partdbsnum(sysdatabases.partnum) = 
          sysdbspaces.dbsnum 
          or
          
          select DBINFO("DBSPACE",partnum) dbspace,
          name,
          owner
          from sysdatabases
           

What is the blobspace page size

  select prtpage/PAGESIZE
          from sysdbstab</br>
          where bitval(flags,'0x10') = 1
           

What is the table to dbspace mapping

  select tabname,  
          trunc(systabnames.partnum/1048576) dbspace,
          sysdbspaces.name 
          from systabnames,
          sysdbspaces 
          where sysdbspaces.dbsnum = trunc(systabnames.partnum/1048576) 
          

What is the total size of the dbspaces

  select name, 
          count(*) chunks, 
          sum(chksize), 
          sum(nfree)  
          from syschunks,
          sysdbspaces  
          where syschunks.dbsnum = sysdbspaces.dbsnum 
          group by 1 
           

What are the poor indexes

The following sql can identify sql using poor indexes

          
   select sqx_sessionid, 
          sqx_executions,
          round(sqx_bufreads/sqx_executions),
          round(sqx_pagereads/sqx_executions), 
          sqx_estcost, 
          sqx_estrows, 
          sqx_index, 
          sqx_sqlstatement  
     from  syssqexplain 
     where sqx_executions > 3000
          and (sqx_bufreads / sqx_executions > 50
          or sqx_executions > 100)
          and (sqx_bufreads / sqx_executions > 1000
          or sqx_executions > 1)
          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

What is the disk IO for the table

  select dbname, 
          tabname, 
          sum(pagreads), 
          sum(pagwrites)  
          from sysptprof  
          group by  dbsname, tabname  
          

List all the tables in the temporary dbspaces

  select dbsname,tabname 
          from sysptprof, sysdbspaces 
          where dbsnum = trunc(partnum/1048576)
          and name = TEMPDBS 
          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

This check does not need any locks unlike oncheck, therefore on a live system might not be accurate.

   select dbsname, 
          tabname,  
          ti_nextns, 
          ti_nptotal, 
          ti_npused, 
          ti_npdata, 
          ti_nrows, 
          ti_rowsize 
      from systabnames, systabinfo 
      where partnum = ti_partnum 
          

List the table extent usage

  select tabname, 
          count(*)  
          from sysextents  
          where dbsname = 'DATABASE'  
          group by  1  
          
          Or summarised by dbspaces
          
          
          select substr(DBINFO("DBSPACE",partnum),1,10) DBSpace,
          dbsname[1,10] Database,
          tabname[1,15] Table,
          sum(pe_size) tot_space,
          count(*) no_of_exts
          from sysptnext, systabnames
          where pe_partnum = partnum
          and tabname != "TBLSpace"
          group by 1,2,3
          order by 1,4 desc
           

What is the current engine configuration

  
          select *
          from sysconfig
          For example, to extract the current server name
          
          select *
          from sysconfig
          where cf_name = "DBSERVERNAME"
          

What is the current profile information

  
          select *
          from sysprofile
          Alternatively, this information can be found using
          <a href="/onstat/onstat_p.html">onstat -p</a> 

What can I find out about the logs

  
   select *
      from syslogs
   
As this is just a view onto syslogfil but with the flags interpreted the same data can be seen just by selecting from the syslogfil table, therefore avoiding the bitval operations. So the syslogfil select would
          
   select number ,uniqid ,size ,used ,
          bitval(syslogfil.flags ,'0x1' ) is_used,
          bitval(syslogfil.flags ,'0x2' ) is_current,
          bitval(syslogfil.flags ,'0x4' ) is_backed_up,
          bitval(syslogfil.flags ,'0x8' ) is_new,
          bitval(syslogfil.flags ,'0x10' ) is_archived,
          bitval(syslogfil.flags ,'0x20' ) is_temp,
          syslogfil.flags,name
       from syslogfil,syschunks,sysdbstab where (syslogfil.number > 0)
          and trunc((hex(physloc)/1048576))=chknum
          and syschunks.dbsnum = sysdbstab.dbsnum
          

How to summarise the VP statistics

  
          select vpid,
          txt,
          usecs_user,
          usecs_sys,
          num_ready
          from sysvplst,
          flags_text
          where sysvplst.flags != 6
          and sysvplst.class = flags_text.flags
          and flags_text.tabname = "sysvplst"
          
           

How much space is left in the DBSpace

  
          select name,                                 
          sum(chksize) pages,
          sum(nfree) free
          from sysdbspaces, syschunks
          where sysdbspaces.dbsnum = syschunks.dbsnum
          group by 1                                    
          

What is the current chunk status

  
          select name,
          is_mirrored,
          is_blobspace,
          is_temp,
          chknum,
          fname,
          offset,
          is_offline,
          is_recovering,
          is_blobchunk,
          is_inconsistent,
          chksize,
          nfree,
          mfname,
          moffset,
          mis_offline,
          mis_recovering
          from sysdbspaces, syschunks
          where sysdbspaces.dbsnum = syschunks.dbsnum
          order by 1,5
          

How much space is left in a chunk

  
          select name,
          syschfree.chknum,
          syschfree.extnum,
          syschfree.start,
          syschfree.leng
          from sysdbspaces, syschunks, syschfree
          where sysdbspaces.dbsnum = syschunks.dbsnum
          and syschunks.chknum = syschfree.chknum
          order by 1,2
          
Alternatively, this information is contained in onstat d
           

What is the disk IO for the chunk

  
          select name,
          chknum,
          "Primary",
          reads,
          writes,
          pagesread,
          pageswritten
          from syschktab, sysdbstab
          where syschktab.dbsnum = sysdbstab.dbsnum
          union all
          select name,
          chknum,
          "Mirror",
          reads,
          writes,
          pagesread,
          pageswritten
          from sysmchktab, sysdbstab
          where sysmchktab.dbsnum = sysdbstab.dbsnum
          order by 1,2
          Alternatively, this information can be seen from 
          <a href="/onstat/onstatu_d.html">onstat -D</a>
          	 

What is the percentage IO for the chunks

  
          select name,
          chktype,
          "Primary" chktype,
          reads,
          writes,
          pagesread,
          pageswritten
          from syschktab, sysdbstab
          where syschktab.dbsnum = sysdbstab.dbsnum
          union all
          select name,
          chktype,
          "Mirror" chktype,
          reads,
          writes,
          pagesread,
          pageswritten
          from sysmchktab, sysdbstab
          where sysmchktab.dbsnum = sysdbstab.dbsnum
          into temp t_io with no log;
          select sum(reads) t_reads,
          sum(writes) t_writes,
          sum(pagesread) t_pagesread,
          sum(pageswritten) t_pageswritten
          from t_io
          into temp t_sum with no log;
          select name,
          chknum,
          chktype,
          reads,
          writes,
          pagesread,
          pageswritten,
          round((reads/t_reads)*100,2) p_reads,
          round((writes/t_writes)*100,2) p_writes,
          round((pagesread/t_pagesread)*100,2) p_pagesread,
          round((pageswritten/t_pageswritten)*100,2) p_pageswritten
          from t_io, t_sum
           

Where are the tables in the dbspace

  
          select DBINFO("DBSPACE",partnum),
          dbsname,
          tabname,
          pe_phys,
          pe_size
          from sysptnext, systabnames
          where pe_partnum = partnum
          order by 1
           

What is the IO for the table

  
    select dbsname,
          tabname,
          isreads,
          bufreads,
          pagreads,
          iswrites,
          bufwrites,
          pagwrites,
          lockreqs,
          lockwts,
          deadlks
       from sysptprof
       order by 3
If the columns read 0 then you may need to set TBLSPACE_STATS in the your ONCONFIG file

How much space is occupied by a table.

  select  dbsname,
          tabname,
          count(*) num_of_extents,
          sum( pe_size ) total_size
          from systabnames, sysptnext
          where partnum = pe_partnum
          and tabname not matches "sys*"
          group by 1, 2
          order by 3 desc, 4 desc;
          
          	

What are the busiest tables

The following SQL will identify the most used tables

          
    select fname, name, dbsname, tabname, 
          pagreads+pagwrites pagtots 
       from sysptprof, syschunks, sysdbspaces, sysptnhdr 
       where trunc(hex(sysptprof.partnum)/1048576) = chknum 
          and syschunks.dbsnum = sysdbspaces.dbsnum 
          and sysptprof.partnum = sysptnhdr.partnum 
          and (pagreads+pagwrites) != 0 
       order by 5 desc 
          
          

For Online versions 4 and 5 is not as straightforward. tbstat -t will show all the active tables. This will give the tblnum in hex which can then be used in the following sql

          
    select tabname 
       from systables
       where hex(partnum) = tblnum
          
          

What is the total size of the database

  
          select sum(ti_nptotal), 
          sum(ti_npused)
          from systabnames, systabinfo
          where partnum = ti_partnum
          order by 1
          	

How were the last UPDATE STATISTICS run

  
          select systables.tabname, 
          syscolumns.colname, 
          sysdistrib.constructed,mode
          from sysdistrib,systables,syscolumns
          where systables.tabid > 99
          and systables.tabid = syscolumns.tabid
          and sysdistrib.tabid=systables.tabid
          and sysdistrib.colno = syscolumns.colno
          group by 1,2,3,4
          order by tabname,colname; 

When was the last onstat -z run

  This can be determined via the following SQL:
           
          SELECT sh_pfclrtime
          FROM sysshmvals;
          

When was Update Stats last run

  
          select distinct tabname, b.constructed, b.mode
          from systables a, sysdistrib b
          where a.tabid=b.tabid
          order by 1
          

What are all the DBINFO options

          DBINFO('dbspace', partn)
          DBINFO('sqlca.sqlerrd1')
          DBINFO('sqlca.sqlerrd2')
          DBINFO('utc_to_datetime', <column>)
          DBINFO('utc_current')
          DBINFO('get_tz')
          DBINFO('serial8')
          DBINFO('sessionid')
          DBINFO('dbhostname')
          DBINFO('version','server-type')
          DBINFO('version','major')
          DBINFO('version','minor')
          DBINFO('version','os')
          DBINFO('version','full')
          
Which are available very much depends on the IDS version

How full is the physical log

    select pl_physize, pl_phyused, round ((pl_phyused * 100.0)/pl_physize,2)
       from sysplog
           

Which database am I connected to

  
          select sqc_currdb
          from syssqlcurall 
          where sqc_sessionid = dbinfo('sessionid');
           

Where are the indexes

  Find detached indexes
          select
                  sysmaster:sysdbspaces.name dbs_name,
                  sysmaster:systabnames.tabname,
                  app_db:sysindexes.idxname
          from sysmaster:systabnames, sysmaster:sysdbspaces,
                  app_db:sysindexes
          where sysmaster:sysdbspaces.dbsnum =
                  trunc(sysmaster:systabnames.partnum/1048576)
          and sysmaster:sysdbspaces.name != "rootdbs"
          and sysmaster:sysdbspaces.name != "database_dbs"
          and app_db:sysindexes.idxname = sysmaster:systabnames.tabname 
          
          union
          
          -- Find other indexes
          select
                  sysmaster:sysdbspaces.name dbs_name,
                  sysmaster:systabnames.tabname,
                  app_db:sysindexes.idxname
          from sysmaster:systabnames, sysmaster:sysdbspaces,
                  app_db:systables, app_db:sysindexes
          where sysmaster:sysdbspaces.dbsnum =
                  trunc(sysmaster:systabnames.partnum/1048576)
          and sysmaster:sysdbspaces.name != "rootdbs"
          and sysmaster:sysdbspaces.name != "database_dbs"
          and app_db:systables.tabname = sysmaster:systabnames.tabname
          and app_db:sysindexes.tabid = app_db:systables.tabid
          -- Exclude detached indexes
          and app_db:sysindexes.idxname not in
                  (select tabname from sysmaster:systabnames )
          
          order by 1, 2, 3

What tables are in rootdbs

  SELECT UNIQUE e.dbsname, e.tabname
      FROM sysdbspaces d, syschunks c, sysextents e
      WHERE d.name = 'rootdbs'
          AND c.dbsnum = d.dbsnum
          AND e.chunk = c.chknum
          AND e.dbsname NOT IN ('rootdbs', 'sysmaster', 'sysadmin', 'sysuser', 'sysutils')
      ORDER BY 1, 2

What mode is the engine running in

select sh_mode 
  from sysmaster:sysshmvals;

IDS 7,9,10,11

  1. 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 ;