pajama

Errors -252/-113 are returned:

252: Cannot get system information for table.

113: ISAM error: the file is locked.

Isolation level Committed Read or Last Committed is used and there is an exclusive lock on the table

Using 'onstat -g sql' check the isolation level that sessions are using:

> onstat -g sql

IBM Informix Dynamic Server Version 11.70.FC3 -- on-Line -- Up 00:52:17 -- 182532 Kbytes

Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
39 - stores_demo CR Not Wait 0 0 9.24 Off
27 sysadmin DR Wait 5 0 0 - Off
26 sysadmin DR Wait 5 0 0 - Off
25 sysadmin DR Wait 5 0 0 - Off

In the above example session #39 is using an isolation level of Committed Read (CR).

Then check the table for exclusive locks. To do so, first get the partnum of the table. This can be done by running the 'oncheck -pt <dbname:tabname>' command:

> oncheck -pt stores_demo:customer

TBLspace Report for stores_demo:informix.customer

Physical Address 2:678
Creation date 08/05/2011 15:55:41
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 134
Number of special columns 0
Number of keys 0
Number of extents 1
Current serial value 129
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 8
Next extent size 8
Number of pages allocated 8
Number of pages used 3
Number of data pages 2
Number of rows 28
Partition partnum 1049059
Partition lockid 1049059
<...>

Next, convert the partnum value into HEX: 1049059 = 0x1001e3

Finally, grep the partnum value (without the leading 0x) from 'onstat -k' output:

> onstat -k | grep 1001e3
4ebef028 0 4b85c418 4ebeed28 HDR+X 1001e3 102 0 D
4ebef328 0 4b85c418 4ebef228 HDR+X 1001e3 103 0 D
4ebef528 0 4b85c418 4ebef428 HDR+X 1001e3 104 0 D
4ebef728 0 4b85c418 4ebef628 HDR+X 1001e3 105 0 D
4ebef828 0 4b85c418 4ebeefa8 HDR+X 1001e3 106 0 D
4ebefa28 0 4b85c418 4ebef9a8 HDR+X 1001e3 107 0 D
4ebefb28 0 4b85c418 4ebefaa8 HDR+X 1001e3 108 0 D
4ebefd28 0 4b85c418 4ebefca8 HDR+X 1001e3 109 0 D
4ebeff28 0 4b85c418 4ebefea8 HDR+X 1001e3 10a 0 D
4ebf0128 0 4b85c418 4ebf00a8 HDR+X 1001e3 10b 0 D
4ebf0328 0 4b85c418 4ebf02a8 HDR+X 1001e3 10c 0 D
4ebf0528 0 4b85c418 4ebf04a8 HDR+X 1001e3 10d 0 D
4ebf0728 0 4b85c418 4ebf06a8 HDR+X 1001e3 10e 0 D
4ebf0928 0 4b85c418 4ebf08a8 HDR+X 1001e3 201 0 D
4ebf0b28 0 4b85c418 4ebf0aa8 HDR+X 1001e3 202 0 D
4ebf0ca8 0 4b85c418 4ebf0c28 HDR+X 1001e3 203 0 D
4ebf0ea8 0 4b85c418 4ebf0e28 HDR+X 1001e3 204 0 D
4ebf10a8 0 4b85c418 4ebf1028 HDR+X 1001e3 205 0 D
4ebf12a8 0 4b85c418 4ebf1228 HDR+X 1001e3 206 0 D
4ebf1428 0 4b85c418 4ebf13a8 HDR+X 1001e3 207 0 D
4ebf1628 0 4b85c418 4ebf15a8 HDR+X 1001e3 208 0 D
4ebf1828 0 4b85c418 4ebf17a8 HDR+X 1001e3 209 0 D
4ebf1a28 0 4b85c418 4ebf19a8 HDR+X 1001e3 20a 0 D
4ebf1c28 0 4b85c418 4ebf1ba8 HDR+X 1001e3 20b 0 D
4ebf1e28 0 4b85c418 4ebf1da8 HDR+X 1001e3 20c 0 D
4ebf2028 0 4b85c418 4ebf1fa8 HDR+X 1001e3 20d 0 D
4ebf2228 0 4b85c418 4ebf21a8 HDR+X 1001e3 20e 0 D
4ebfeda8 0 4b85c418 4ebfee28 HDR+X 1001e3 101 0 D
4ebfee28 0 4b85c418 44452788 HDR+IX 1001e3 0 0

In the above example, the table has an intent-exclusive lock on the whole table (HDR+IX) and some exclusive locks on its rows (HDR+X).

Set the isolation level to Dirty Read at the session level before running the query:

> echo "set isolation to dirty read; select count(*) from customer;" | dbaccess stores_demo

Database selected.

Isolation level set.


(count(*))

28

1 row(s) retrieved.

Database closed.

Another option is to set lock mode to wait in order to allow the query to wait until the lock is released:

> echo "set lock mode to wait; select count(*) from customer;" | dbaccess stores_demo

Background

There are many flavours of index-related corruption problems, for instance:

• Unique index contains duplicates with different RID(s) or same RID(s)
• Multiple index entries pointing to the same RID
• Index key is out of place (wrong index key order)
• Row exists, but index keys do not exist in any or some of the indexes
• Index entry pointing to an empty data slot or unused data slot or RID is invalid
• Incorrect previous or next index page pointers, incorrect high key or other corruptions on index pages

1. An off-line db2dart /t (table inspection) would be able to detect corruption symptoms 1, 2, 3, 6 of the above list. For example:
   
   Table resides in table space id 4, and object id of table is 2, the database name is T30, the output is "inspTsi4oi2.rpt":
   db2dart T30 /t /tsi 4 /oi 2 /scr n /rptn inspTsi4oi2.rpt
   
   
2. The RUNSTATS command with a special registry variable set will collect statistics as well as detect corruption symptoms 4, 5 of the above list. For example:
   
   db2stop
   db2set DB2_KEYDATACHECK=IXSTATDATADEBUG
   db2start
   db2 connect to <database>
   db2 -v "runstats on table <tableschema>.<tabname> and detailed indexes all allow read access"
   
   NOTE: After each RUNSTATS, you need to save the contents of the sqllib/db2dump directory. You can do so by moving the entire content to another location. As long as the RUNSTATS does not return with DB2 completed successfully, you will need to report this to DB2 support.

You often need an advanced knowledge of DB2 in order to determine whether the problem is an index corruption or an index or data inconsistency problem. Therefore you should always contact IBM support organization to help you recover from these problems.

For index-related corruption or index or data inconsistency problems, the following information is typically required:

• the contents of the DB2DUMP directory
• the on-disk version of the related index pages (collected via the db2dart tool)
• related data page(s) for the index or data object (collected via the db2dart tool)
• database transaction log files, which are used to understand the past history of the index or table object that encountered the error.
  
  

1. Collect the standard diagnostic information using the db2support command. This should include all the files in DB2DUMP directory (or the path specified by DIAGPATH in the dbm configuration).
   
   There are cases where the corruptions are caused by a disk problem, and you do not care about root cause analysis of the problem. In these situations, skip step 2, and go directly to step 3.
   
   
2. Run db2dart as indicated below (note: if this is a multi-partition DPF environment, then you will need to run db2dart on the partition where failure occurred):
   
   db2dart <dbname> /t /tsi <pool id> /oi <object id> /rptn inspTsi<poolid>oi<objectid>_bcrashrec.rpt
   
   In general, this inspection should be fairly quick. If the database is mission critical database and needs to be brought up online immediately, then you may skip this step at this time.
   
   
3. Restart your database and verify crash recovery completes successfully.
   
   
   If crash recovery completes successfully and you cannot take the time needed to collect this data for root cause analysis of the problem, skip steps 4, 5 and 6 and go directly to step 7.
   
   
   If the restart fails, and it is determined to be a problem redoing or undoing an index record, then you can mark an index invalid (e.g. db2dart <dbname> /mi /oi <objectID> /tsi <tablespaceID>). INDEXREC will control when the invalid index object will be recreated.
   
4. To prevent the previous failing application from accessing the table again before the problem on the related index or table is resolved, you can lock the table in exclusive mode. Use the following command:
   
   
   db2 +c "LOCK TABLE <SCHEMA NAME>.<TABLENAME> IN EXCLUSIVE MODE"
   
   
   The "+c" turns off the auto commit, since LOCK TABLE is only effective within the unit of work.
   
   
   If you do care about the availability of the rest of the tables in the same table space as the problem table, instead you may also want to consider quiescing the table spaces for the table object. For example:
   
   
   db2 "QUIESCE TABLESPACES FOR TABLE <SCHEMA NAME>.<TABLE NAME> EXCLUSIVE"
   
   
   NOTE: QUIESCE command will also quiesce the index table space if the index object is in a separate table space. In a partitioned database environment, this command will only quiesce the portion on current node.
   
   
5. Run db2dart against the index and table object.
   
   
   If the problem is an index NOKEY error, then a number of db2dart commands will be printed in the db2diag.log file. Search for these commands (using eithergrep on UNIX® or find on Windows®) and save them in a file. Edit the file, replace "DBNAME" with the database name. If the problem has been hit multiple times, then there could be some duplicate entries. You only need to keep the latest set of db2dart commands.
   
   If it is the data manager that reports row not found or bad page, then NO db2dart commands are printed out. You can determine the pool id, object id, data page number from the db2diag.log entries. For example:
   
   
2006-06-17-11.32.59.941406+000 I235565A460 LEVEL: Severe
PID : 9224396 TID : 1 PROC : db2agent (SAMPLE) 0
INSTANCE: db2inst NODE : 000 DB : OB8
APPHDL : 0-78 APPID: ....
FUNCTION: DB2 UDB, data management, sqldDeleteRow, probe:1329
RETCODE : ZRC=0x87040001=-2029780991=SQLD_BADPAGE "Bad Data Page"
DIA8500C A data file error has occurred, record id is "".


2006-06-17-11.32.59.964241+000 I236403A380 LEVEL: Severe
PID : 9224396 TID : 1 PROC : db2agent (SAMPLE) 0
INSTANCE: db2inst NODE : 000 DB : OB8
APPHDL : 0-78 APPID: ...
MESSAGE : PAGE OBJECT IDENTIFIERS:
DATA #1 : String, 54 bytes
Tablespace ID = 15, Object ID = 8737, Object Type = 0


2006-06-17-11.32.59.964448+000 I236784A350 LEVEL: Severe
PID : 9224396 TID : 1 PROC : db2agent (SAMPLE) 0
INSTANCE: db2inst NODE : 000 DB : OB8
APPHDL : 0-78 APPID: ...
MESSAGE : PAGE NUMBERS:
DATA #1 : String, 35 bytes
Obj Page = 297, Pool Page = 787527

   
   In this case, the data page is 787527, pool id is 15 and object id is 8737.
   
   The commands that you need are:
   
   
   1. db2dart <dbname> /dd /tsi <pool id> /oi <object id> /ps <data page number>p /np 1 /v y /scr n /rptn dTsi<poolid>oi<objectid>.rpt
      
      ...where <data page number> is 787527p using the example above, "p" is used to indicate this is pool relative page which is important for DMS table space.
      
      
   2. db2dart <dbname> /di /tsi <pool id> /oi <object id> /ps 0 /np <large enough value to dump all index pages> /v y /scr n /rptn iTsi<poolid>oi<objectid>_0-<pagenum>.rpt.rpt
      
      
      Note: The command might take longer to run if a large /np value is specified. Ideally, you should collect as much data as possible. At minimum, it should be no less than 100 pages.
      
      If table is not too big, also dump the whole table. If the table is too big to dump, select a reasonably large number so that at least a part of the table can be dumped, for example:
      
      db2dart <dbname> /dd /tsi <pool id> /oi <object id> /ps <data page to start> /np <large enough number> /v y /scr n /rptn dTsi<pool id>oi<object id>_0-<page num>.rpt
      
      where <data page to start> would be "0" if you want to dump the whole table, or <data page number - 100>p if the table is too big to dump (using the example, that would be "787427p"). Replace the <pool id> and <object id> with the actual number.
      
      
6. Run RUNSTATS to check for any index or data inconsistency. For example:
   
   
   db2stop
   db2set DB2_KEYDATACHECK=IXSTATDATADEBUG
   db2start
   db2 connect to <db>
   db2 -v "SELECT INDSCHEMA, INDNAME FROM SYSCAT.INDEXES WHERE TABSCHEMA='<TABLE SCHENAME>' AND TABNAME='<TABLENAME>' "
   
   
   Then use the index schema name and index name from the above SELECT output for the following RUNSTATS command:
   
   db2 -v "runstats on table <tableschema>.<tabname> and index <index schema>.<index name> allow read access"
   
   
   NOTE: You need to perform the RUNSTATS on all of the indexes defined on the offending table; after each RUNSTATS command, you need to save the contents of the sqllib/db2dump directory (or DIAGPATH, if you're not using the default path).
   
   
   RUNSTATS on a large table could take a while and the table will not be available for updates while RUNSTATS is running. In a multi-partition DPF environment, RUNSTATS is done on a per partition basis.
   
   
   If you had quiesced the table space at step 4, then unquiesce the table spaces before you terminate the database connection. You will need to use the same user id for the QUIESCE command in order to reset:
   
   
   db2 -v "QUIESCE TABLESPACES FOR TABLE <SCHEMA NAME>.<TABLE NAME> RESET" 
   
   
   Once all the RUNSTATS commands are completed, you need to collect the db2diag.log along with the content of sqllib/db2dump (or content of DIAGPATH).
   
   
7. If it is determined that the index is corrupted (that is, if either RUNSTATS or db2dart /t inspection returns with an error), then proceed with rebuilding the index as follows:
   
   
   db2 "REORG INDEXES ALL FOR TABLE <SCHEMA NAME>.<TABLE NAME> ALLOW NO ACCESS"
   
   
   Applications cannot access your table while the above REORG is running. If you need the table to be available immediately, you can choose to run the above REORG INDEXES command with ALLOW READ ACCESS or ALLOW WRITE ACCESS options. Be aware, however, that the database is at risk of being brought down again if the previous failing application is run against the table before the REORG completes.
   
   
   If you are not concerned with root cause analysis of the problem, you do not need to collect any additional information and can skip the subsequent steps.
   
   
8. Collect table or index DDLs. You can use the db2look utility to do so. For example: 
   
   db2look -d <database> -l -e -a -t <table name> -o table.ddl
   
   
9. Collect transaction log files
   
   You will only know which log files to collect after a DB2 Support analyst examines the data collected thus far. In general, they will ask you to send a raw log file. In some special cases, they may ask you to format the file and only send the relevant log files. The number of log files required will depend on when the problem was introduced, so it might be necessary to retrieve older log files from archive. 
   
   
10. Additional information that may be important to root cause analysis or known APAR identification. For example:
    • What type of workload is typically running against the table?
    • Did you perform any actions that you don't normally do on this table, for instance any REORG INDEXES/TABLE commands, load or import operations, a high utilities workload, or restore or rollforward operations?
    • Were there any other unusual occurrences? For example, if there were recent hardware problems, provide the operating system error log. This would involve collecting the errpt -a output on AIX® or /var/adm/messages file on Solaris.

Tivoli Storage Manager does not start after crash

Disk failure on DB2 database files

During Tivoli Storage Manager start up, the following errors are reported:

ANR0172I rdbdb.c(1465): Error encountered performing action 
ActivateDatabase. 
ANR0162W Supplemental database diagnostic information: -1224:**No State
Information Available (128)**:-1224 (**No Other Information Available 
(206)**). 
ANR0172I rdbdb.c(1009): Error encountered performing action 
InstanceStop. 
ANR0162W Supplemental database diagnostic information: -1032:**No State
Information Available (89)**:-1032 (**No Other Information Available 
(72)**). 

The db2diag.log reports: 
. 
2010-02-19-15.29.08.326780+060 I289570185E438 LEVEL: Warning 
... 
MESSAGE : Crash Recovery is needed. 
2010-02-19-15.30.13.948634+060 I289572079E2547 LEVEL: Severe 
PID : 8492 TID : 47197773097280PROC : db2sysc 0 
INSTANCE: tsmb1 NODE : 000 DB : TSMDB1 
APPHDL : 0-7 APPID: *LOCAL.tsmb1.100219142908 
AUTHID : TSMB1 
EDUID : 65 EDUNAME: db2redow (TSMDB1) 0 
FUNCTION: DB2 UDB, buffer pool services, sqlbVerifyCBITS, probe:1110 
MESSAGE : ZRC=0x86020019=-2046689255=SQLB_CSUM "Bad Page, Checksum 
Error" 
DIA8426C A invalid page checksum was found for page "". 
DATA #1 : String, 64 bytes 
Error encountered trying to read a page - information follows : 
DATA #2 : String, 97 bytes 
CBIT verification error 
bitExpected is 0, userByte is 181, sector 63 (from head of page, 0 
based) 
DATA #3 : Page ID, PD_TYPE_SQLB_PAGE_ID, 4 bytes 
1196993 
DATA #4 : Object descriptor, PD_TYPE_SQLB_OBJECT_DESC, 72 bytes 
Obj: {pool:6;obj:5;type:1} Parent={5;5} 
... 

The db2diag messages indicate that the table ID 5 has corruption (see last message line: " {pool:6;obj:5;type:1} ".

You can do two different recoveries.

1) Restore the database to a point in time before the disk corruption occurred,

Or

2) Follow DB2 steps to determine if the corruption is only in the index tables and do a DB2 reorg.

Follow these steps to start the DB2 data collection:
$ db2stop 

SQL1064N DB2STOP processing was successful. 

Now run db2dart command for tablespaceid 6 or run for whole database. 
$ db2dart TSMDB1 /DB 
The requested DB2DART processing has completed successfully! 
Complete DB2DART report found in: 
/home/tsminst1/sqllib/db2dump/DART0000/TSMDB1.RPT 

Messages in RPT file:
Warning: The database state is not consistent.

Warning: Errors reported about reorg rows may be due to the inconsistent state of the database. 

Error: Duplicate found at same slot in unique index. Lvl(1) Page,slot,rid current:2311036p,1008,x6000050319000000 prev:2311036p,1008,x5100AC5E180000
Error: Duplicate found at same slot in unique index. Lvl(1) Page,slot,rid current:2249254p,344,x0D00071D19000000 prev:2249254p,344,x5700AF5E18000000
Error: Duplicate found at same slot in unique index. Lvl(1) Page,slot,rid current:2248938p,951,x4C00FF7419000000 prev:2248938p,951,x4100BC5E18000000
Error: Duplicate found at same slot in unique index. Lvl(1) Page,slot,rid current:2223926p,957,x55002C6A19000000 prev:2223926p,957,x2A00B95E18000000
Error: Duplicate found at same slot in unique index. Lvl(1) Page,slot,rid current:2223926p,958,x81002C6A19000000 prev:2223926p,958,x2900B95E18000000
Error: Duplicate found at same slot in unique index. Lvl(1) Page,slot,rid current:2223926p,959,x17002C6A19000000 prev:2223926p,959,x2800B95E18000000
Error: Duplicate found at same slot in unique index. Lvl(1) Page,slot,rid current:2223926p,960,x18002C6A19000000 prev:2223926p,960,x2700B95E18000000

Index page corruption can be corrected by running the following command below :

Get the table name (This example is using the message output provided in the message: "{pool:6;obj:5;type:1} "):
db2 "select tabname from syscat.tables where tableid=5 and tbspaceid=6" 

Get the schema name:
db2 list tables 

Run db2 reorg command ( substituting value of schema name. tablename ) 

db2 " REORG INDEXES ALL FOR TABLE schemaname. table_ame ALLOW NO ACCESS"

If there is still an issue, DB2 support will need the full db2dart output for further analysis.

INTRODUCTION

IBM® Informix® Dynamic Server (IDS) version 9.x introduced the ability to collect information for all active SQL sessions by using the command onstat -g sql 0. This functionality can be replicated for IDS version 7.31 databases by using the following awk script.

STEPS

Create an awk script that contains the following text:

BEGIN {system(":> onstat_g_sql_0")}
{
if ($NF > 0) {
my_string= onstat -g sql " $1 "  >> onstat_g_sql_0; echo \"----------------\" >> onstat_g_sql_0"
system(sprintf(my_string))
}
}
END {}

IBM Informix Dynamic Server Version 7.31.UD8     -- on-Line -- Up 00:10:54 -- 8912 Kbytes
Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers
13    SELECT         informix_log       CR  Not Wait   0    0    7.31
11    -              informix_log       CR  Not Wait   0    0    7.31

Sample output from awk script

IBM Informix Dynamic Server Version 7.31.UD8     -- on-Line -- Up 00:10:20 -- 8912 Kbytes
Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers
13    SELECT         informix_log       CR  Not Wait   0    0    7.31
Current statement name : slctcur
Current SQL statement :
  select * from test2
Last parsed SQL statement :
  select * from test2
----------------
IBM Informix Dynamic Server Version 7.31.UD8     -- on-Line -- Up 00:10:20 -- 8912 Kbytes
Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers
11    -              informix_log       CR  Not Wait   0    0    7.31
Last parsed SQL statement :
  UPDATE   test2 set str1="bbb" where key=0
----------------

Following errors may be seen in db2diag.log file.

2012-10-07-03.31.44.562773+480 I559988E520 LEVEL: Error

PID : 2160 TID : 140574011156224PROC : db2sysc 
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE 
APPHDL : 0-4470 APPID: 202.27.30.1.2101.121006192554 
AUTHID : PSOGALAG 
EDUID : 568 EDUNAME: db2agent (SAMPLE) 
FUNCTION: DB2 UDB, common communicati on, sqlcctest, probe:50 
MESSAGE : sqlcctest RC 
DATA #1 : Hexdump, 2 bytes 
0x00007FD9EFFF6780 : 3600 

2012-10-07-03.31.44.543149+480 I560509E538 LEVEL: Error 
PID : 2160 TID : 140574002767616PROC : db2sysc 
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE 
APPHDL : 0-4510 APPID: 202.27.30.1.2286.121006192634 
AUTHID : PSOGALAD 
EDUID : 570 EDUNAME: db2agent (SAMPLE) 
FUNCTION: DB2 UDB, common communication, sqlcctcptest, probe:11 
MESSAGE : Detected client termination 
DATA #1 : Hexdump, 2 bytes 
0x00007FD9EF7F6748 : 3600 

Function sqlcctcptest() tests if the client connection is active and function sqlcctest() tests whether the connection is still valid.

If you don't want such messages to be logged into db2diag.log file, you can set DB2CHECKCLIENTINTERVAL to zero.

For Example : Issue following:

1) db2set DB2CHECKCLIENTINTERVAL=0 
2) db2stop 
3) db2start