DB2 7.2 Fixpack 8 crashed

[Then]

Hi,
My database kept on crashing and recovering today and I have no
idea what caused it. However, I am able to narrow down a SQL that
causes the database to crash everytime it is ran. There are other
SQLs that is causing that problem too but I do not have the backend
code to know the details. Below are the db2diag.log output and a
sample of the SQL used. Any help would be much appreciated. Thanks.

db2diag.log
--------------------------------------------------------------------
2004-06-02-21.32.55.796000 Instance:DB2 Node:000
PID:2996(db2syscs.exe) TID:2980 Appid:41012220.1505.040531022053
base_sys_utilities sqledint Probe:0 Database:ICMS

Crash Recovery is needed.

2004-06-02-21.32.56.780000 Instance:DB2 Node:000
PID:2996(db2syscs.exe) TID:2980 Appid:41012220.1505.040531022053
recovery_manager sqlpresr Probe:1 Database:ICMS
DIA3908W Crash recovery has been initiated. Lowtran LSN is
"004664D3E00C",
Minbuff LSN is "004664D3E00C".

2004-06-02-21.32.56.796000 Instance:DB2 Node:000
PID:2996(db2syscs.exe) TID:2980 Appid:41012220.1505.040531022053
recovery_manager sqlprecm Probe:125 Database:ICMS

Using parallel recovery with 5 agents 4 QSets 28 queues and 4 chunks
2004-06-02-21.32.56.952000 Instance:DB2 Node:000
PID:2996(db2syscs.exe) TID:2980 Appid:41012220.1505.040531022053
recovery_manager sqlprecm Probe:400 Database:ICMS
DIA3916W Forward phase of crash recovery has completed. Next LSN is
"004664D4006D".

2004-06-02-21.32.56.968000 Instance:DB2 Node:000
PID:2996(db2syscs.exe) TID:2980 Appid:41012220.1505.040531022053
recovery_manager sqlpresr Probe:170 Database:ICMS
DIA3909W Crash recovery completed. Next LSN is "004664D4006D".

SQL
-------------------------------------------------------------
SELECT
TLESINST."EXTTRNID", TLESINST."INTERNAL",
TLESSCH."STARTTIME", TLESSCH."ENDTIME",
TEXTTRN."TRNNAME",
TTRNROLE."ROLEDESC",
HEMPLP."EMSTAFID", HEMPLP."EMFSTNM",
TCLSSCH."CLSCODE", TCLSSCH."STARTDATE", TCLSSCH."ENDDATE",
TCOURSE."DESCRIPTION",
TCOUTOPC."DESCRIPTION",
CREWCAT."LONGDESC",
FLEET."DESCRIP"
FROM
"DB2ADMIN"."TLESINST" TLESINST
left outer join "DB2ADMIN"."TLESSCH" TLESSCH on TLESINST."LESSONID" =
TLESSCH."LESSONID"
left outer join "DB2ADMIN"."TTRNROLE" TTRNROLE on TLESINST."ROLEID" =
TTRNROLE."ID"
left outer join "DB2ADMIN"."TEXTTRN" TEXTTRN on TLESINST."EXTTRNID" =
TEXTTRN."TRNID"
left outer join "DB2ADMIN"."HEMPLP" HEMPLP on TLESINST."TRAINERID" =
HEMPLP."EMCRID"
inner join "DB2ADMIN"."HEPOSITN" HEPOSITN on HEMPLP."EMCRID" =
HEPOSITN."PNCRID"
inner join "DB2ADMIN"."TCLSSCH" TCLSSCH on TLESSCH."CLASSID" =
TCLSSCH."CLASSID"
inner join "DB2ADMIN"."TCOUTOPC" TCOUTOPC on TLESSCH."TOPICID" =
TCOUTOPC."TOPICID"
inner join "DB2ADMIN"."TCOURSE" TCOURSE on TLESSCH."COURSEID" =
TCOURSE."COURSEID"
inner join "DB2ADMIN"."CREWCAT" CREWCAT on HEPOSITN."PNCRWCAT" =
CREWCAT."CATID"
inner join "DB2ADMIN"."FLEET" FLEET on HEPOSITN."PNFLTYPE" =
FLEET."FLTID"
WHERE
TCOURSE."DESCRIPTION" = 'A330 BASE CHECK ALL'

ORDER BY
TCOURSE."DESCRIPTION" ASC, TCLSSCH."CLSCODE" ASC FOR READ ONLY

[Jean-Marc Blaise]

Hi there,

It's the extract from the db2diag.log before the crash that is interesting.
You might check the temporary tablespace is not getting full ... if so, the
instance will crash in FP8. In that case, you need to apply another fixpack
(FP12).

Hth,

Jean-Marc

"Then" <ch*******@yahoo.com> a écrit dans le message de
news:4f**************************@posting.google.c om...

Hi,
My database kept on crashing and recovering today and I have no
idea what caused it. However, I am able to narrow down a SQL that
causes the database to crash everytime it is ran. There are other
SQLs that is causing that problem too but I do not have the backend
code to know the details. Below are the db2diag.log output and a
sample of the SQL used. Any help would be much appreciated. Thanks.

db2diag.log
--------------------------------------------------------------------
2004-06-02-21.32.55.796000 Instance:DB2 Node:000
PID:2996(db2syscs.exe) TID:2980 Appid:41012220.1505.040531022053
base_sys_utilities sqledint Probe:0 Database:ICMS

Crash Recovery is needed.

2004-06-02-21.32.56.780000 Instance:DB2 Node:000
PID:2996(db2syscs.exe) TID:2980 Appid:41012220.1505.040531022053
recovery_manager sqlpresr Probe:1 Database:ICMS
DIA3908W Crash recovery has been initiated. Lowtran LSN is
"004664D3E00C",
Minbuff LSN is "004664D3E00C".

2004-06-02-21.32.56.796000 Instance:DB2 Node:000
PID:2996(db2syscs.exe) TID:2980 Appid:41012220.1505.040531022053
recovery_manager sqlprecm Probe:125 Database:ICMS

Using parallel recovery with 5 agents 4 QSets 28 queues and 4 chunks
2004-06-02-21.32.56.952000 Instance:DB2 Node:000
PID:2996(db2syscs.exe) TID:2980 Appid:41012220.1505.040531022053
recovery_manager sqlprecm Probe:400 Database:ICMS
DIA3916W Forward phase of crash recovery has completed. Next LSN is
"004664D4006D".

2004-06-02-21.32.56.968000 Instance:DB2 Node:000
PID:2996(db2syscs.exe) TID:2980 Appid:41012220.1505.040531022053
recovery_manager sqlpresr Probe:170 Database:ICMS
DIA3909W Crash recovery completed. Next LSN is "004664D4006D".

SQL
-------------------------------------------------------------
SELECT
TLESINST."EXTTRNID", TLESINST."INTERNAL",
TLESSCH."STARTTIME", TLESSCH."ENDTIME",
TEXTTRN."TRNNAME",
TTRNROLE."ROLEDESC",
HEMPLP."EMSTAFID", HEMPLP."EMFSTNM",
TCLSSCH."CLSCODE", TCLSSCH."STARTDATE", TCLSSCH."ENDDATE",
TCOURSE."DESCRIPTION",
TCOUTOPC."DESCRIPTION",
CREWCAT."LONGDESC",
FLEET."DESCRIP"
FROM
"DB2ADMIN"."TLESINST" TLESINST
left outer join "DB2ADMIN"."TLESSCH" TLESSCH on TLESINST."LESSONID" =
TLESSCH."LESSONID"
left outer join "DB2ADMIN"."TTRNROLE" TTRNROLE on TLESINST."ROLEID" =
TTRNROLE."ID"
left outer join "DB2ADMIN"."TEXTTRN" TEXTTRN on TLESINST."EXTTRNID" =
TEXTTRN."TRNID"
left outer join "DB2ADMIN"."HEMPLP" HEMPLP on TLESINST."TRAINERID" =
HEMPLP."EMCRID"
inner join "DB2ADMIN"."HEPOSITN" HEPOSITN on HEMPLP."EMCRID" =
HEPOSITN."PNCRID"
inner join "DB2ADMIN"."TCLSSCH" TCLSSCH on TLESSCH."CLASSID" =
TCLSSCH."CLASSID"
inner join "DB2ADMIN"."TCOUTOPC" TCOUTOPC on TLESSCH."TOPICID" =
TCOUTOPC."TOPICID"
inner join "DB2ADMIN"."TCOURSE" TCOURSE on TLESSCH."COURSEID" =
TCOURSE."COURSEID"
inner join "DB2ADMIN"."CREWCAT" CREWCAT on HEPOSITN."PNCRWCAT" =
CREWCAT."CATID"
inner join "DB2ADMIN"."FLEET" FLEET on HEPOSITN."PNFLTYPE" =
FLEET."FLTID"
WHERE
TCOURSE."DESCRIPTION" = 'A330 BASE CHECK ALL'

ORDER BY
TCOURSE."DESCRIPTION" ASC, TCLSSCH."CLSCODE" ASC FOR READ ONLY