Sysmaster

The window into the entire system, an area of shared memory with an SQL like interface

dbaccess sysmaster - 

delete from systables where tabid > 99 and partnum < '0x100000';   

delete from systables where tabname = 'sysdbspartn'; 

close database; 

drop database sysmaster; 

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

          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

  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.

  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 
           

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

  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  
           

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

  select owner 
          from sysmaster:systables
          where tabid = 99
          

DBINFO('version','server-type')
DBINFO('version','major')
DBINFO('version','minor')
DBINFO('version','os')
DBINFO('version','full')

All should work

  select unique username 
          from  syssessions

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

  select sid 
          fromsyssessions 
          where pid = 'pid'
          

  select dbsname, tabname, seqscans 
          from sysptprof;
          

  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
          

  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
          
          

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; 

• Replace database_name with the database you are querying
• Must be run as user informix

  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) 
           

  select sh_pagesize 
          from sysshmvals

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 
           

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

  select name 
          from sysdatabases
          

  
          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
           

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

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

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

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

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

  select dbsname,tabname 
          from sysptprof, sysdbspaces 
          where dbsnum = trunc(partnum/1048576)
          and name = TEMPDBS 
          order by  1 
           

  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;

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 
          

  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
           

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

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

  
   select *
      from syslogs
   

          
   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
          

  
          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"
          
           

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

  
          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
          

  
          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
          

  
          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>
          	 

  
          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
           

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

  
    select dbsname,
          tabname,
          isreads,
          bufreads,
          pagreads,
          iswrites,
          bufwrites,
          pagwrites,
          lockreqs,
          lockwts,
          deadlks
       from sysptprof
       order by 3

  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;
          
          	

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
          
          

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

  
          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; 

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

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

          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')
          

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

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

  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

  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

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 

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 

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

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 ;