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