pajama

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.

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;