Differences
This shows you the differences between the selected revision and the current version of the page.
wiki:sysmaster 2012/06/27 16:55 | 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 340: | 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 347: | 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 783: | Line 832: | ||
3 SD secondary server | 3 SD secondary server | ||
4 RS secondary server | 4 RS secondary server | ||
+ | |||
Line 804: | Line 854: | ||
where b.dbsnum = trunc(a.partnum/1048576) | where b.dbsnum = trunc(a.partnum/1048576) | ||
and c.idxname = a.tabname | and c.idxname = a.tabname | ||
+ | and a.dbsname = '<database>' | ||
and tabid > 99 | and tabid > 99 | ||
order by 1 | order by 1 |