pajama

INTRODUCTION

You can use a SQL statement to obtain information regarding each of the temporary tables that are currently created.


STEPS

You can run the following SELECT statement from any application:

SELECT t.tabname ,t.dbsname,t.owner,
       DECODE(d.is_logging,1,"Y","N") AS db_with_log,
       s.name AS dbspace,
       DBINFO("UTC_TO_DATETIME",ti_created) AS created,
       DECODE(hex(mod(ti_flags,256)/16),6,"Y","N") AS
       table_using_log,ti_npused AS num_usedpages,
       ti_nptotal AS num_pages
FROM   sysmaster:systabnames t, sysmaster:systabinfo i,
       sysmaster:sysdbspaces s, sysmaster:sysdatabases d
WHERE  t.partnum=ti_partnum AND
       d.name=t.dbsname AND
       s.dbsnum=TRUNC(t.partnum/1048576) AND
       hex(mod(ti_flags,256)/16) IN ( 6,2 )

tabname          temp1
dbsname          stores_demo
owner            informix
db_with_log      Y
dbspace          rootdbs
created          2005-05-23 16:44:24
table_using_log  Y
num_usedpages    1
num_pages        8

tabname          temp2
dbsname          stores_demo
owner            informix
db_with_log      Y
dbspace          rootdbs
created          2005-05-23 16:44:24
table_using_log  N
num_usedpages    1
num_pages        8

No rows found.

The query in the CODE section is used to allow the database administrator to use system catalog tables to find how much space is being used by each database in an instance.

The results are interpreted as follows:

dbspace
name of the dbspace

database
name of the database

total
total pages used for a database

used
total pages used for both data and indexes

data
total pages used for data

idx
total pages used for indexes

DATABASE sysmaster;
SELECT s.name dbspace,
n.dbsname database,
SUM(ti_nptotal) total, 
SUM(ti_npused) used,
SUM(ti_npdata) data, 
SUM(ti_npused) - SUM(ti_npdata) idx
FROM systabinfo i,systabnames n, sysdbspaces s
WHERE i.ti_partnum = n.partnum 
AND partdbsnum(i.ti_partnum)=s.dbsnum 
GROUP BY 1,2
ORDER BY 1,2

[質問]
作成したすべてのテーブルの、現在のエクステント数および追加可能なエクステント数を、容易に取得する方法はありますか。
(테이블의 현재 익스텐트 및 추가 가능한 익스텐트 개수를 쉽게 얻는 방법이 있습니까?)

[回答]
データベース sysmaster に対して、以下のSQL文を実行すると、現在のエクステント数および追加可能なエクステント数を、取得することができます。(sysmaster 데이터베이스에 접속하여 다음 SQL 문을 실행하면 현재 익스텐트 및 추가 가능한 익스텐트 개수를 검색할 수 있습니다.)

SELECT
st.dbsname[1,25] AS dbname,
st.tabname[1,35] AS dbobject,
count(*) num_of_extents,
trunc((pgh.pg_frcnt/8),0) additional_extents
FROM sysptnext ptn, sysptnhdr pth, syspaghdr pgh, systabnames st
WHERE st.partnum = ptn.pe_partnum AND st.partnum = pth.partnum 
AND pgh.pg_partnum = ((trunc(st.partnum/1048576,0)*1048576)+1)
AND pgh.pg_pagenum = (st.partnum-(trunc(st.partnum/1048576,0)*1048576))
AND st.dbsname NOT LIKE "sys%"
AND st.tabname NOT LIKE "sys%"
AND st.tabname NOT LIKE " %"
AND st.tabname NOT LIKE "TBLSpace%"
GROUP BY 1, 2, 4
ORDER BY 1, 2;


以下は出力例です。(다음은 출력 예입니다.)

dbname test_db　　<-- データベース名 (데이터베이스 이름)
dbobject test_tbl 　<-- テーブル名 (테이블 이름)
num_of_extents 2　　　　　<-- 現在のエクステント数 (현재 익스텐트 개수)
additional_extents 229　　　　<-- 追加可能なエクステント数 (추가 가능한 익스텐트 개수)

INTRODUCTION

You may need to determine the amount of space each table uses within a dbspace. The sysmaster table sysptnext contains information about the start and size of partitions within the database server. You can query this table to determine table usage within a dbspace.


STEPS

To retrieve space usage information about tables in a dbspace, execute the following sql statement:

SELECT DBINFO( "DBSPACE" , pe_partnum ) dbspace, dbsname databasename, tabname tablename, pe_size num_pages 
FROM  sysmaster:sysptnext a, sysmaster:systabnames b 
WHERE a.pe_partnum = b.partnum AND 
DBINFO( "DBSPACE" , pe_partnum ) IN ("dbspacename1", "dbspacename2" ) 
ORDER BY dbspace, num_pages DESC;

dbspacename1, dbspacename2

a comma separated list of the dbspace(s) for which you want the information

The following command can be executed from the UNIX command line on the sysmaster database to find out what are all the tables created on any specific dbspace. The <dbspace> should be replaced with the real dbspace name.

echo "select tabname from sysptnext, systabnames
where pe_partnum = partnum and dbinfo( 'DBSPACE' , pe_partnum ) = <dbspace> group by pe_partnum, dbsname, tabname
order by tabname"|dbaccess sysmaster

For example, to list out all the tables created on dbspace3, the above query will be like below:

echo "select tabname from sysptnext, systabnames
where pe_partnum = partnum and dbinfo( 'DBSPACE' , pe_partnum ) = 'dbspace3'
group by pe_partnum, dbsname, tabname
order by tabname"|dbaccess sysmaster

Sample output:

Database selected.



tabname TBLSpace

tabname alcatel_a_interface_bssap_week

tabname alcatel_bearer_bearer_channel_month

tabname alcatel_bsc_fabric_gch_week

tabname alcatel_bsc_fabric_tbf_monitoring_per_gpu_month

tabname alcatel_bsc_overviewchannel_week

tabname alcatel_cell_agch_month

tabname alcatel_cell_bvc_week

tabname alcatel_cell_directed_retry_month

tabname alcatel_cell_dl_tbf_establishments_1_week

tabname alcatel_cell_dl_tbf_releases_month

tabname alcatel_cell_dtap_week

tabname alcatel_cell_established_phase_tch_month

tabname alcatel_cell_external_directed_retry_week

tabname alcatel_cell_handover_sdcch_intra_day

tabname alcatel_cell_handover_sdcch_outgoing_month

tabname alcatel_cell_handover_sdcch_per_cause_week

tabname alcatel_cell_handover_tch_incoming_raw

tabname alcatel_cell_handover_tch_month

tabname alcatel_cell_handover_tch_per_cause_1_month


NOTE: Please do note to source your environment before running the above, so that you have access to the Informix Database.

Q. How do I calculate how much space is used on my instance?

A. You can use a SMI query to calculate how much space is used.

Note, however, that the output will only be accurate if you run update statistics HIGH for each table before running the query.

select  n.dbsname[1,20],
        n.tabname[1,20],
        count(*) no_extents,
        round(t.rowsize * t.nrows / 2048,0) used,
        sum( pe_size) allocated
from    sysmaster:systabnames n, sysmaster:sysptnext p, systables t
where   n.partnum = p.pe_partnum
and     n.partnum = t.partnum
and     t.tabtype = "T"
group by 1,2,4
order by 3 desc , 5 desc;

Note: This SQL assumes that you are using 2048 page size.

Here is a simple method to see information about additional extents, extent size and next size. The query can be easily modified to show the information for specific tables or dbspaces.

With this query you can avoid reaching Error -136 ISAM error: no more extents.

SET ISOLATION TO DIRTY READ;

SELECT dbsname as dbname,
tabname as dbobject,
TRUNC((pg_frcnt/8),0) additional_extents,
count(*) num_of_extents,
sum( pe_size ) total_size,
ti_nptotal pages_allocated,
ti_npused pages_used,
ti_npdata data_pages,
ti_nrows data_rows,
ti_rowsize as row_size,
ti_fextsiz first_extent_size,
ti_nextsiz next_extent_size,
hex(partnum) partnum
FROM systabnames, sysptnext, systabinfo, syspaghdr
WHERE
dbsname != "HASHTEMP"
AND tabname not like "sys%"
AND partnum = pe_partnum
AND partnum = ti_partnum
AND pg_partnum = ((TRUNC(partnum/1048576,0)*1048576)+1)
AND pg_pagenum = (partnum-(TRUNC(partnum/1048576,0)*1048576))
GROUP BY 1, 2, 3, 6, 7, 8, 9, 10, 11, 12, 13
ORDER BY 3 asc, 4 desc;