|
Problem IC63864
|
Status: Closed |
DB2 ENGINE MAY PANIC, MARK DATABASE AS BAD, WHILE EXPLAINING SQL STATEMENT IF EXPLAIN_DIAGNOSTIC_DATA TABLE WAS DROPPED. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
DB2 engine may panic, mark database bad while explaining SQL
statement, if EXPLAIN_DIAGNOSTIC_DATA table was dropped. This
may happen when explain statement has run repeatedly and was
cached, then, table was removed and then explain was attempted
to run again.
Typical stack trace back of the trapped EDU:
<StackTrace>
-------Frame------ ------Function + Offset------
0x09000000003636BC pthread_kill + 0x88
0x0900000006496FB8 sqloDumpEDU + 0x48
0x09000000066EE1AC MarkDBBad__16sqeLocalDatabaseFi + 0x280
0x09000000050C8FFC sqldDumpContext__FP8sqeAgentiN42PCcPvT2 +
0x208
0x090000000605AF74
sqldRowInsert__FP8sqeAgentUsT2UcUliPP10SQLD_VALUEP13SQLD_TDATARE
CP8SQLZ_RID + 0xCC8
0x0900000005FE48E8
sqlrinsr__FP8sqlrr_cbUsT2iT2PP10SQLD_VALUEUlP8SQLZ_RID + 0xA0
0x0900000005473E58
sqlnx_exp_insert_row__FP16sqlnx_exp_fidtidPP10SQLD_VALUE + 0x250
0x09000000077953A4
sqlrkexp__FP8sqlrr_cbP16sqlkdRqstRplyFmtiPP15SQLR_RPCMESSAGE +
0x71C
0x09000000054CFE50 sqlrr_rpc_router__FP8sqlrr_cb + 0x634
0x09000000054CE544
sqlrr_subagent_router__FP8sqeAgentP12SQLE_DB2RA_T + 0xE50
0x09000000054C2D7C sqleSubRequestRouter__FP8sqeAgentPUiT2 +
0x9EC
0x09000000054C2334 sqleProcessSubRequest__FP8sqeAgent + 0x680
0x090000000647DB6C RunEDU__8sqeAgentFv + 0x2CC
0x090000000647F944 EDUDriver__9sqzEDUObjFv + 0x84
0x090000000647F880 sqlzRunEDU__FPcUi + 0xC
0x09000000064947B8 sqloEDUEntry + 0x224
</StackTrace>
Steps to reproduce:
1. Create all objects for the explain facility:
$ db2 connect to sample
$ cd ~/sqllib/misc
$ db2 -tvf EXPLAIN.DDL
2. Run explain statement:
$ db2 "EXPLAIN PLAN SET QUERYNO = 17 SET QUERYTAG =
'B999999999899' FOR select * from employee"
DB20000I The SQL command completed successfully.
3. Remove the table (and underlying function that depends on
it):
$ db2 drop function DB2INST1.EXPLAIN_GET_MSGS
DB20000I The SQL command completed successfully.
$ db2 drop table "EXPLAIN_DIAGNOSTIC_DATA"
DB20000I The SQL command completed successfully.
4. Rerun the same explain statement:
$ db2 "EXPLAIN PLAN SET QUERYNO = 17 SET QUERYTAG =
'B999999999899' FOR select * from employee"
DB21034E The command was processed as an SQL statement because
it was not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1034C The database is damaged. All applications processing
the database
have been stopped. SQLSTATE=58031
The workaround is not to drop any tables from the explain
facility manually.
As an alternative, SYSINSTALLOBJECTS stored procedure could be
used to remove all objects for explain facility in one
transaction. Example:
db2 "call sysinstallobjects ('EXPLAIN', 'D', CAST (NULL AS
VARCHAR(128)), <schema-name> )" |
| Problem Summary: |
|
| Local Fix: |
The workaround is not to drop any tables from the explain
facility manually.
As an alternative, SYSINSTALLOBJECTS stored procedure could be
used to remove all objects for explain facility in one
transaction. Example:
db2 "call sysinstallobjects ('EXPLAIN', 'D', CAST (NULL AS
VARCHAR(128)), <schema-name> )" |
| available fix packs: |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
|
| Workaround |
not known / see Local fix |
| Comment |
This APAR is a duplicate of IC63870 |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 21.10.2009
28.10.2009
28.10.2009 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
