Differences
This shows you the differences between the selected revision and the current version of the page.
wiki:sysmaster 2010/04/19 17:53 | 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 271: | Line 299: | ||
group by 1 | group by 1 | ||
+ | |||
Line 292: | 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 303: | 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 310: | 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 436: | 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 448: | 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 730: | Line 819: | ||
6 Online | 6 Online | ||
7 Abort | 7 Abort | ||
+ | |||
===== What type of instance is running ===== | ===== What type of instance is running ===== | ||
Line 742: | Line 832: | ||
3 SD secondary server | 3 SD secondary server | ||
4 RS 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 ; | ||
+ | |||