pajama

db2top is a monitoring tool included in DB2 for LUW on AIX, Linux, and Solaris platforms. Sometimes this utility is not shown in colors, Even though the monochromatic view of db2top is very useful, db2top can use colors when using the interactive screen mode to highlight some outputs and that can be more useful and attractive to some users.

The most common reasons to see db2top without colors are the following:

Your term emulator and/or your TERM environment setting cannot manage colors. In this case using xterm or dtterm at your TERM environment setting and use a term emulator capable to emulate either xterm or dtterm would be enough to show db2top in colors.

Your db2top configuration is not configured to show the output in colors, In order to check if db2top is configured to manage colors just execute the utility "db2top -d <database>" (where database is any database active on your instance), and once you are seeing the db2top main screen, just press "w" key, this writes the db2top configuration file and shows its current values:

Ensure the parameter "colors" is set to "ON". If it is configured to "OFF" you can edit directly the configuration file indicated by the first line of the above screen ("/home/db2inst1/.db2toprc" in this case) in order to set this parameter to "ON".

The db2updv8 command is a program that will define new user defined functions (UDFs) and stored procedures (SPs) that have been added to DB2 UDB in a Version 8.x FixPak.

The UDFs SYSFUN.GET_DBM_CFG and SYSFUN.APPLICATION_ID are supported in DB2 UDB Version 8.1 FixPak 7. Run db2updv8 to use these new functions. 

e.g. db2 "select application_id() from sysibm.sysdummy1"

Also note that other tools (for example, the Activity Monitor and its stored procedures) may not be fully functional until these UDFs are defined.

When you connect to a database you receive error -952 "User's password is not correct for the database server"

The error message indicates the authentication failed due to an invalid password. Under certain circumstances, error -952 could be returned even when the password is correct.

Possible causes are:

• password is not correct or it has expired
• user does not have read permission on file /etc/passwd
• wrong permission on the executable $INFORMIXDIR/bin/oninit, where INFORMIXDIR is the environment variable pointing to the directory where the product is installed.
• the password is greater than 8 characters
• the database server was not started as user informix or root

• Operating System is AIX, and file /etc/security/passwd is not readable by user root
• Operating System is Linux (RedHat), Solaris or HP 11i, and file /etc/shadow is readable by user root
• if your platform is DEC-Alpha, you might be facing the following bug:

Here are some hints on what you must consider when receiving error -952:

• Log on to the system and verify that the userid/password have not expired and need to be set. Do this by using the telnet utility from the Unix prompt on a different machine, to establish a connection to the machine where Informix is installed, and go through the authentication process.

Example:

   telnet <informix server hostname>

Renew the password if you get an error saying that the password expired.

• Make sure that all users, including user informix have read permission on /etc/passwd

• Change directory to where Informix, installed and make sure that permission on the executable $INFORMIXDIR/bin/oninit are -rwsr-sr--, where INFORMIXDIR is the environment variable pointing to the directory where the product is installed.

Example:

cd $INFORMIXDIR
ls ./bin/oninit

-rwsr-sr--   1 root     informix 8163428 Jul 23  2003 oninit

• Do the following to correct any permission problem:
  
  • If you have Informix version 7, run the script installserver as user root
  • If you have Informix version 9, run the script installserver as user informix and then execute script RUN_AS_ROOT as user root

• the password is greater than 8 characters, reduce the password length to 8 characters

• Make sure you start the database server as user informix or root

• If Operating System is AIX, make sure /etc/security/passwd is readable by user root

• If Operating System is Linux (RedHat), Solaris or HP 11i, make sure file /etc/shadow is readable by user root

• If your platform is DEC-Alpha, make sure you have the fix for defect 154706 by doing one of the following:
  
  • Install a version of the product where this defect is fixed: 7.31.FD5, 9.40.UC1 or later
  • Contact Informix Technical Support Team and request a fix for this defect

Example:

informix:x:200:200:InformixAdministrator:/home/informix:/bin/ksh

where x represents that the password is a shadow password

Example:

informix:ow3/GUDb8r2rk:12103::::::

Example:

After the update, the entry in the /etc/passwd file will look like the following:

informix:ow3/GUDb8r2rk:200:200:InformixAdministrator:/home/informix:/bin/ksh

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

[回答]
データベース 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.