이 쿼리는 IDS instance의 모든 database의 개별 size를 보여주는 쿼리입니다.
여기에는 두개의 버전이 있는데 IDS 10.x 이전 버전은 결과를 세로로 스크롤해주고 (아시져 10 이전버전은 display 방식이 single line 만 지원이 되어서 그렇다는것을..) 10.x 이후 버전은 가로줄 방식(이게 보기가 훨 좋긴 하죠)으로 스크롤 해준다는 것 외에는 결과값 자체에는 변함이 없습니다.
다음은 쿼리입니다.
=================================================================
IDS 10 미만 버전
DATABASE sysmaster;
SELECT stn.dbsname db_name,
SUM
(
sti.ti_npused *
(
select sh_pagesize from sysshmvals
)/1024/1024
) mb_used,
SUM
(
sti.ti_nptotal *
(
select sh_pagesize from sysshmvals
)/1024/1024
) mb_total
FROM systabnames stn, systabinfo sti, sysdatabases sdb
WHERE stn.partnum = sti.ti_partnum
AND stn.dbsname = sdb.name
GROUP BY 1
ORDER BY 1;
IDS 10 이상 버전
DATABASE sysmaster;
SELECT stn.dbsname[1,35] db_name,
SUM
(
sti.ti_npused *
(
select pagesize
from sysdbspaces
where name = dbinfo('dbspace', sti.ti_partnum)
)/1024/1024
) mb_used,
SUM
(
sti.ti_nptotal *
(
select pagesize
from sysdbspaces
where name = dbinfo('dbspace', sti.ti_partnum)
)/1024/1024
) mb_total
FROM systabnames stn, systabinfo sti, sysdatabases sdb
WHERE stn.partnum = sti.ti_partnum
AND stn.dbsname = sdb.name
GROUP BY 1
ORDER BY 1;
===============================================================
다음은 당연히 원문입니다.
smi_seq_scans.sql
The purpose of this SMI query is to display the individual sizes of all databases in your IDS instance .
The smi_db_size.sql comes in two versions. Choose the one that fits your current IDS version. The purpose of the substrings (stn.dbsname[1,35] is only to make sure that the retrieved row is displayed on a single line within the dbaccess utility. You can remove it if you need the full database name.
IDS < 10
DATABASE sysmaster;
SELECT stn.dbsname db_name,
SUM
(
sti.ti_npused *
(
select sh_pagesize from sysshmvals
)/1024/1024
) mb_used,
SUM
(
sti.ti_nptotal *
(
select sh_pagesize from sysshmvals
)/1024/1024
) mb_total
FROM systabnames stn, systabinfo sti, sysdatabases sdb
WHERE stn.partnum = sti.ti_partnum
AND stn.dbsname = sdb.name
GROUP BY 1
ORDER BY 1;
IDS >= 10
DATABASE sysmaster;
SELECT stn.dbsname[1,35] db_name,
SUM
(
sti.ti_npused *
(
select pagesize
from sysdbspaces
where name = dbinfo('dbspace', sti.ti_partnum)
)/1024/1024
) mb_used,
SUM
(
sti.ti_nptotal *
(
select pagesize
from sysdbspaces
where name = dbinfo('dbspace', sti.ti_partnum)
)/1024/1024
) mb_total
FROM systabnames stn, systabinfo sti, sysdatabases sdb
WHERE stn.partnum = sti.ti_partnum
AND stn.dbsname = sdb.name
GROUP BY 1
ORDER BY 1;
|