Weird behaviour during DB2 REORG for some Jazz Tables
Hi all,
the REORG Operation seems to take for ever for the following tables:
For each one of the REORG db2 sleeps: the db2sync process doesn't show anymore in the top window (Linux), the iostat show that nothing happens on the database.
the most amazing thing is that for example the DB2INST1.CONNHEADER_JAZZ_DEADLOCKS table had only 101 records.
the REORG hanged for about two hours and when we decided to stop it, the db2diag.log actually logged that the process was interrupted by the user, like if he was still running it.
Does anyone shares the same experience or have some advices regarding that issue?
Are the DEADLOCK related tables important tables to index?
Thanks,
the REORG Operation seems to take for ever for the following tables:
REORG TABLE DB2INST1.CONNHEADER_JAZZ_DEADLOCKS;
REORG TABLE DB2INST1.CONTROL_JAZZ_DEADLOCKS;
REORG TABLE DB2INST1.DEADLOCK_JAZZ_DEADLOCKS;
REORG TABLE DB2INST1.DLCONN_JAZZ_DEADLOCKS;
REORG TABLE DB2INST1.DLLOCK_JAZZ_DEADLOCKS;
For each one of the REORG db2 sleeps: the db2sync process doesn't show anymore in the top window (Linux), the iostat show that nothing happens on the database.
the most amazing thing is that for example the DB2INST1.CONNHEADER_JAZZ_DEADLOCKS table had only 101 records.
the REORG hanged for about two hours and when we decided to stop it, the db2diag.log actually logged that the process was interrupted by the user, like if he was still running it.
2009-06-12-20.42.43.625018-240 I7609830G572 LEVEL: Warning
PID : 17320 TID : 2370825120 PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : JAZZ
APPHDL : 0-1083 APPID: *LOCAL.db2inst1.090612214414
AUTHID : DB2INST1
EDUID : 46 EDUNAME: db2agent (JAZZ)
FUNCTION: DB2 UDB, trace services, sqlt_logerr_string (secondary logging fu, probe:0
MESSAGE : Reorg table failed.
DATA #1 : String, 89 bytes
Table(2:5)=DB2INST1.CONNHEADER_JAZZ_DEADLOCKS, Flags=x00004091, IID=0, Temp=0, LongTemp=0
2009-06-12-20.42.43.625246-240 I7610403G538 LEVEL: Warning
PID : 17320 TID : 2370825120 PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : JAZZ
APPHDL : 0-1083 APPID: *LOCAL.db2inst1.090612214414
AUTHID : DB2INST1
EDUID : 46 EDUNAME: db2agent (JAZZ)
FUNCTION: DB2 UDB, data management, sqldTableReorgObject, probe:4296
RETCODE : ZRC=0x80100003=-2146435069=SQLP_LINT "Interrupt from application"
DIA8003C The interrupt has been received.
2009-06-12-20.42.43.625461-240 I7610942G527 LEVEL: Severe
PID : 17320 TID : 2370825120 PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : JAZZ
APPHDL : 0-1083 APPID: *LOCAL.db2inst1.090612214414
AUTHID : DB2INST1
EDUID : 46 EDUNAME: db2agent (JAZZ)
FUNCTION: DB2 UDB, relation data serv, sqlrreorg_table, probe:550
MESSAGE : Reorg: DMS return rc =
DATA #1 : Hexdump, 4 bytes
0x8D4F3884 : 0300 1080 ....
2009-06-12-20.42.43.625595-240 I7611470G505 LEVEL: Severe
PID : 17320 TID : 2370825120 PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : JAZZ
APPHDL : 0-1083 APPID: *LOCAL.db2inst1.090612214414
AUTHID : DB2INST1
EDUID : 46 EDUNAME: db2agent (JAZZ)
FUNCTION: DB2 UDB, relation data serv, sqlrreorg_table, probe:600
DATA #1 : String, 73 bytes
Table Schema : DB2INST1
Table Name : CONNHEADER_JAZZ_DEADLOCKS
Does anyone shares the same experience or have some advices regarding that issue?
Are the DEADLOCK related tables important tables to index?
Thanks,
One answer
Hi all,
I did open WI 86744 https://jazz.net/jazz/web/projects/Rational%20Team%20Concert#action=com.ibm.team.workitem.viewWorkItem&id=86744 for RTC and here's the response I got from IBM support on that issue:
Hi Adil, we do not reorg the DEADLOCK related tables. Attaching a sample of the reorganization, which we do in the dev lab as best practice.
https://jazz.net/jazz/service/com.ibm.team.workitem.service.internal.rest.IAttachmentRestService/repo/csid/Attachment/RTCDB2TablesWithReorg.txt?itemId=_H1axsGWDEd6HZLaaq4FsDg&id=26643
Thanks for you all for your time!
I did open WI 86744 https://jazz.net/jazz/web/projects/Rational%20Team%20Concert#action=com.ibm.team.workitem.viewWorkItem&id=86744 for RTC and here's the response I got from IBM support on that issue:
Hi Adil, we do not reorg the DEADLOCK related tables. Attaching a sample of the reorganization, which we do in the dev lab as best practice.
https://jazz.net/jazz/service/com.ibm.team.workitem.service.internal.rest.IAttachmentRestService/repo/csid/Attachment/RTCDB2TablesWithReorg.txt?itemId=_H1axsGWDEd6HZLaaq4FsDg&id=26643
Thanks for you all for your time!