pajama

Session in "Commit Active" means it's performing the commit of a Unit of Work, but not being able to finish the same yet for some reasons.

Out of many possible reasons following could be some common ones :

- The database is using HADR and at the standby side it's hitting a momentary HADR log receive buffer full situation.

- Network issues affecting HADR communications between primary and standby

- The database has very large history file

- Disk bottleneck at the storage side where the database transaction logs are kept

To identify HADR receive buffer full situation just run "db2pd -hadr" at the standby side while the "Commit Active" state is present at the primary side of the HADR and see what the output of StandByRcvBufUsed field shows. If it's near 100% then it can cause the "Commit Active" situation in the primary side. Consider increasing DB2_HADR_BUF_SIZE registry setting to address the issue.

If a network issue is suspected between two sides of HADR pair then that should be taken to the network administrator to check on possible network bottleneck or any other kinds of network issues.

To check whether history file have become too big. Check for the file db2rhist.asc file under database directory. If it's big (example, more than 100MB or so) then try to prune it down.

Bottleneck in disk containing the active log directory or, mirror log path can cause the "Commit Active" situation most. Usually, iostat output collected while the "Commit Active" is in place can show if the disk bottleneck is in place. As for example, under AIX operating system collecting "iostat -TDla 1 30" might show the column "%tm" reaching near 100% for the disk containing log paths. If the %tm is 100% and all the data flow in that disk shows zero, that will indicate a momentary stalled disk situation.

Some snapshot monitoring commands or administrative views acquire DB2 internal latches for resource serialization. These latches might be owned by EDUs on the problematic database.

The MON_GET_* table functions, such as the MON_GET_TABLESPACE and MONREPORT module, don't require a DB2 internal latch.

Unfortunately, the MON_GET_TRANSACTION_LOG table function is not available in releases prior to DB2 Version 10.1. However, you can calculate used log space with the db2pd -db DBNAME -dbcfg -logs output.

The following represents the calculation of the total available log space.

LOGDATAPAGE = 4096 (LOGPAGE) - 20 (OVERHEADBYTES) = 4076

Total available log space
= LOGFILSIZ * LOGDATAPAGE * ( LOGPRIMARY + LOGSECOND )

The following represents the calculation of the total used log space.

Total used log space
= Current LSO - StartLSO of First active log file + Compensation log space

Where "First active log Number" is the number in the "First active log file" name, found in the db2pd -dbcfg output, and "Compensation log space" is the total of all "LogSpace" values in db2pd -transactions output.

The following is an example of how to query the "Total available log space" and "Total used log space" with the SYSIBMADM.LOG_UTILIZATION administrative view.

$ date; db2 "select char(DB_NAME,16) DB_NAME, LOG_UTILIZATION_PERCENT, TOTAL_LOG_USED_KB, TOTAL_LOG_AVAILABLE_KB, TOTAL_LOG_USED_TOP_KB, DBPARTITIONNUM from SYSIBMADM.LOG_UTILIZATION"

Wed Mar 6 02:38:04 EST 2013

DB_NAME LOG_UTILIZATION_PERCENT TOTAL_LOG_USED_KB TOTAL_LOG_AVAILABLE_KB TOTAL_LOG_USED_TOP_KB DBPARTITIONNUM
---------------- ----------------------- -------------------- ---------------------- --------------------- --------------
DB01 90.80 7402 749 7402 0

1 record(s) selected.


In the following example, the Total available log space calculation equates to 8152K and Total used log space equates to 7402K, based on thedb2pd output.

The following is the "Total available log space" and "Total used log space", calculated from the db2pd output.

LOGDATAPAGE = 4096 - 20 = 4076

Total available log space
= LOGFILSIZ * LOGDATAPAGE * ( LOGPRIMARY + LOGSECOND )
= 256 * 4076 * 8
= 8347648 = 8152K

Total used log space
= Current LSO - StartLSO of First active log file + Compensation log space
= Current LSO 46082460 - StartLSO 41803457 + all LogSpace (1320458 + 1980634)
= 4279003 + (1320458 + 1980634) = 7580095 = 7402.4365234375K = 7402K

TOTAL_LOG_AVAILABLE_KB = 8152K - 7402K = 750K


$ date; db2pd -db DB01 -dbcfg -logs -transactions

Wed Mar 6 02:38:06 EST 2013
...

Database Member 0 -- Database DB01 -- Active -- Up 0 days 00:11:51 -- Date 03/06/2013 02:38:07

Database Configuration Settings:
...

LOGBUFSZ (4KB) 64 64
LOGFILSIZ (4KB) 256 256
LOGPRIMARY 5 5
LOGSECOND 3 3
First active log file S0000001.LOG S0000001.LOG
...

Database Member 0 -- Database DB01 -- Active -- Up 0 days 00:11:52 -- Date 03/06/2013 02:38:08

Logs:
Current Log Number 5
Pages Written 25
Cur Commit Disk Log Reads 0
Cur Commit Total Log Reads 0
Method 1 Archive Status Success
Method 1 Next Log to Archive 5
Method 1 First Failure n/a
Method 2 Archive Status n/a
Method 2 Next Log to Archive n/a
Method 2 First Failure n/a
Log Chain ID 0
Current LSO 46082460
Current LSN 0x0000000000046772

Address StartLSN StartLSO State Size Pages Filename
0x0700000063BB0940 000000000003A183 41803457 0x00000000 256 256 S0000001.LOG
0x0700000063BB11C0 000000000003D0A4 42846913 0x00000000 256 256 S0000002.LOG
0x0700000063BB1A40 0000000000040146 43890369 0x00000000 256 256 S0000003.LOG
0x0700000063BB22C0 00000000000431E9 44933825 0x00000000 256 256 S0000004.LOG
0x0700000063BB00C0 000000000004628B 45977281 0x00000000 256 256 S0000005.LOG
0x0700000070FBC580 0000000000000000 47020737 0x00000000 256 256 S0000006.LOG
0x0700000070F44100 0000000000000000 48064193 0x00000000 256 256 S0000007.LOG
0x0700000070FD00C0 0000000000000000 49107649 0x00000000 256 256 S0000008.LOG

Database Member 0 -- Database DB01 -- Active -- Up 0 days 00:11:52 -- Date 03/06/2013 02:38:08

Transactions:
Address AppHandl [nod-index] TranHdl Locks State Tflag Tflag2 Firstlsn Lastlsn Firstlso Lastlso LogSpace SpaceReserved TID AxRegCnt GXID ClientUserID ClientWrkstnName ClientApplName ClientAccntng
0x07000000606AF380 28 [000-00028] 3 20002 WRITE 0x00000000 0x00000000 0x000000000003A2C0 0x000000000003F16D 41862316 43550312 1320458 3008538 0x000000000139 1 0 n/a n/a n/a n/a
0x07000000606B4480 29 [000-00029] 4 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0 0 0x0000000000F2 1 0 n/a n/a n/a n/a
0x07000000606B9580 30 [000-00030] 5 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0 0 0x000000000150 1 0 n/a n/a n/a n/a
0x07000000606BE680 31 [000-00031] 6 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0 0 0x0000000000F4 1 0 n/a n/a n/a n/a
0x07000000606C3780 32 [000-00032] 7 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0 0 0x0000000000F6 1 0 n/a n/a n/a n/a
0x07000000606C8880 33 [000-00033] 8 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0 0 0x0000000000F7 1 0 n/a n/a n/a n/a
0x07000000606CD980 34 [000-00034] 9 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0 0 0x0000000000F8 1 0 n/a n/a n/a n/a
0x07000000606D2A80 35 [000-00035] 10 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0 0 0x0000000000F9 1 0 n/a n/a n/a n/a
0x07000000606D7B80 36 [000-00036] 11 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0 0 0x0000000000FA 1 0 n/a n/a n/a n/a
0x07000000606DCC80 37 [000-00037] 12 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0 0 0x0000000000FC 1 0 n/a n/a db2evml_DB2DETAILDEADLOCK n/a
0x07000000606E1D80 39 [000-00039] 13 30003 WRITE 0x00000000 0x00000000 0x000000000003F16E 0x0000000000046771 43550396 46082376 1980634 4512698 0x000000000101 1 0 n/a n/a n/a n/a

References:
Space requirements for log files http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.dbobj.doc/doc/c0004930.html

db2pd - Monitor and troubleshoot DB2 database commandhttp://pic.dhe.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0011729.html

During the Informix instance start you will see following types of error messages in the instance message log and the instance will not come online:

13:23:26  Assert Failed: No Exception Handler
13:23:26  IBM Informix Dynamic Server Version 11.70.FC7W2
13:23:26   Who: Thread(134, kaio, 0, 16)
  File: mtex.c Line: 490
13:23:26   Results: Exception Caught. Type: MT_EX_OS, Context: mem
13:23:26   Action: Please notify IBM Informix Technical Support.
13:23:26   See Also: /tmp/af.46e0eae, shmem.46e0eae.0

The af.<xxxxxx> file will contain an empty stack trace of the kaio thread; the si_signo value of 11 will indicate the SEGV signal received by the oninit process:

13:23:26  Stack for thread: 134 kaio

 base: 0x0000000146f02000
  len:   69632
   pc: 0x0000000101103350
  tos: 0x00000001019ff751
state: running
   vp: 22

 ucontext: 0x0000000101a00a20
  siginfo: 0x0000000101a00d00
           si_signo: 11  si_code: 1   si_errno: 0 si_pid: 0 si_uid: 0
           si_addr: 0x0000000001057d68 si_value: 0x0000000000000000  si_fd: 0

One of the possible causes of the problem is following Oracle Solaris defect:

15815250 : SUNBT7197575 AUTOMOUNTD FAILS TO MOUNT LDAP USERS HOME DIR on SOLARIS 10 BRANDED

Contact the Oracle Solaris support and ask them for following fix:

IDR-fix idr781.1 for Solaris 11.1.8.4

INTRODUCTION

This document describes a technique that can help you rebuild a table based on the data that might be still accessible. It uses the concept of the ROWIDs and the UNLOAD command.

Attention: This technique only saves rows that are still accessible. Damaged rows are lost.

Note: The technique will not work for fragmented tables, because they do not have rowids.

1. Use the UNLOAD command to extract the accessible rows from the table. This command will fail when the first corrupted row is encountered:

UNLOAD TO file1 SELECT rowid, * FROM table ;

file1  
File name where the data is going to the stored

table
Name of the source table

2. Open file1 and take note of the last ROWID that was unloaded successfully:

last_rowid = Last ROWID in file1

3. Add two units to the last_rowid. You will use this value in the next step:

next_rowid = last_rowid + 2

4. Run the UNLOAD command again, this time to retrieve the remaining rows of the table using the next_rowid as the filtering argument:

UNLOAD TO file2
SELECT rowid, * FROM table where rowid >= next_rowid

Note: If UNLOAD fails without producing any data, skip another rowid by increasing next_rowid. You might have to repeat steps 2, 3 and 4 many times depending on the quantity of corrupted rows. There are some scenarios where this technique can be very time-consuming.

5. After unloading all possible rows, repeat steps 1 and 2, this time without selecting the rowid in the column list of the SELECT statement.

UNLOAD TO file1 SELECT * FROM table ;
UNLOAD TO file2
SELECT * FROM table where rowid >= next_rowid

6. Recreate the table using the data that was just unloaded.

LOAD FROM file1 INSERT INTO table;
LOAD FROM file2 INSERT INTO table;

1. oncheck reported an error in the table customer. Run the first UNLOAD command until it fails.

UNLOAD TO file1 SELECT rowid, * FROM customer;

2. Suppose the last rowid found in the last record on file1 is decimal 1038:

last_rowid = 1038

3. Add two units to last_rowid :

next_rowid = (last_rowid + 2) = 1040

4. Using this calculation, restart the unload on rowid 1040:

UNLOAD TO file2
SELECT rowid, * FROM customer where rowid >= 1040;

5. Repeat this process until you scan the whole table. Assuming that the second UNLOAD command finished without errors, the next step is to run the same commands again without the rowid.

UNLOAD TO file1 SELECT * FROM customer;
UNLOAD TO file2
SELECT * FROM customer where rowid >=1040;

6. Recreate the table and load the data using file1 and file2 as input files:

LOAD from file1 insert into customer;
LOAD from file2 insert into customer;

DB2 Enterprise Server Edition v9.5 and higher is only supported in Test and Development mode on 32-bit Linux systems.

A DB2 Enterprise Server Edition permanent license may only be applied on a 64-bit Linux system.

Here are your options: 

• Upgrade the OS to 64-bit Linux and then install Enterprise Server Edition and license.
  
• Purchase another DB2 edition such as DB2 Workgroup Server Edition or DB2 Express Edition. You will need to also obtain and apply the corresponding permanent license.
  
• Purchase DB2 Developer's Edition for 32-bit Linux. The Developer's Edition license certificate (db2dede.lic) can be applied on top of a Enterprise Server Edition install without any problems.

Messages similar to the following will be displayed in the Message.log on Primary HDR server.

R: Needed to send a ping message but failed. 1
12:31:25 Logical Log 358 Complete, timestamp: 0x20c592c5.
12:31:27 DR: Needed to send a ping message but failed. 1
12:32:28 DR: Needed to send a ping message but failed. 1
12:33:11 Logical Log 359 Complete, timestamp: 0x20ca1170.
12:33:28 DR: Needed to send a ping message but failed. 1
12:33:59 DR: Needed to send a ping message but failed. 1
12:34:29 DR: Needed to send a ping message but failed. 1
12:35:06 Logi

This is strictly a new Warning message developed and released in IDS 11.70.xC7.

This message is displayed when the HDR buffer starts to backup and fill. This is usually evident of a very Busy system and no cause for alarm.

Monitor these Warnings. If the numeric value in the Warning message reaches a value of 3 or greater, possible Ping Time-out errors could start to occur.

INTRODUCTION

You can use a SQL statement to obtain information regarding each of the most recent UPDATE STATISTICS HIGH and UPDATE STATISTICS MEDIUM commands that ran against a table.

The type of information you can obtain from the most recent update statistics is the following:

• The date on which it ran
• The mode used (high or medium)
• The column(s) it ran against
• The resolution used
• The confidence level used

SELECT UNIQUE tabname,colname,constructed,mode, 
       resolution,confidence 
FROM  systable s t, syscolumns c, sysdistrib d 
WHERE d.tabid = c.tabi d  AND d.colno = c.colno A ND 
       d.tabid = t.tabid AND 
       t.tabname=' tablename ' 
ORDER BY 1,2;

tablename
The name of the table you want the distribution information for.

Note: The confidence level for distributions run in high mode is irrelevant so it should always be set to zero.

tabname      state 
colname      code 
constructed  07/08/2003 
mode         H 
resolution   0.5 
confidence   0.00 

tabname      state 
colname      sname 
constructed  07/08/2003 
mode         M 
resolution   2.500000000000 
confidence   0.94999999

No rows found is returned.

INTRODUCTION

This article shows a SQL statement which can be executed to retrieve when and for which columns the distributions were last built. However, if a column does not have a distribution it will not appear in the query output.

STEPS

select systables.tabname, syscolumns.colname, 
      sysdistrib.constructed,mode
from sysdistrib,systables,syscolumns
where systables.tabid > 99
and systables.tabid = syscolumns.tabid
and sysdistrib.tabid=systables.tabid
and sysdistrib.colno = syscolumns.colno
group by 1,2,3,4
order by tabname,colname;

Assuming that 

UPDATE STATISTICS HIGH for table customer (customer_num) 

 
tabname      customer 
colname      customer_num 
constructed  12/15/2006 
mode         H 

Certain env variables set

determine if any of the following environment variables are set and unset them

• IFX_AUTOFREE
• IFX_DEFERRED_PREPARE
• OPTOFC

System generated names for primary and foreign key indexes and constraints.

There are times that you may want to specify the name of the index associated with a foreign key constraint or a primary key constraint rather than having one generated by the engine. You may, for instance, want to use the index name as part of an optimizer directive to be used on the same table in more than one instance.

To specify a name for the index associated with a primary key constraint or a foreign key constraint do the following:

1. Create the table without including the PRIMARY KEY or FOREIGN KEY clause. 

2. Create a unique index on the columns used by the primary key or foreign key you want to create. This index will be the one that will be used by your primary key or foreign key constraint. 


Example: 

If you are going to create a primary key on the columns col1 and col2 of a table named tab2 and you want the index to be named ixtab2 you would use this statement to create the index: 

CREATE UNIQUE INDEX ixtab2 on tab2(col1,col2);


3. Alter the table to add the primary key or foreign key constraint. The engine will detect that a unique index already exists on the columns and use that index automatically.


Example: 

Continuing the previous example, the following ALTER TABLE statement would create primary key constraint that uses the index ixtab2: 

ALTER TABLE tab2 ADD CONSTRAINT PRIMARY KEY(col1,col2);