|
Problem IT15265
|
Status: Closed |
DB2TS CLEAR EVENTS DOES NOT WORK WHEN EVENTTABLLE HAS ROWS > 1000. |
| product: |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 |
| Problem description: |
Trying to clear events from the text search events table results
in SQL0607N.
db2 "CALL SYSPROC.SYSTS_CLEAR_EVENTS('ONSITE',
'FILES_FILE_NAME_TSINDEX','locale', ?) "
SQL20427N An error occurred during a text search administration
procedure or
command. The error message is "" SQL0607N "DROP" is not defined
for
system objects. SQLSTAT". SQL". SQLSTATE=38H14
This only occurs if the number of rows to remove is > 1000
rows. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Users of Text Indexes *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 11 Fix Pack 1 or higher *
**************************************************************** |
| Local Fix: |
Get eventtableschemaName and eventtableName using :
db2 "select EVENTTABLESCHEMA,EVENTTABLENAME from
sysibmts.systsindexes where indexname =
'FILES_FILE_NAME_TSINDEX'"
Lock index to block other admin commands on index during change
of eventview
db2ts set command locks for index
onsite.FILES_FILE_NAME_TSINDEX for text
Trim the eventtable as below:
db2 "delete from
<eventtableschemaName>.<eventtableName>"
Unlock the index for other admin operatin on index.
db2ts clear command locks for index
ionsite.FILES_FILE_NAME_TSINDEX for text |
| available fix packs: |
DB2 Version 11.1 Mod1 Fix Pack1 iFix001 for Linux, UNIX, and Windows
DB2 Version 11.1 Mod 2 Fix Pack 2 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod2 Fix Pack2 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod2 Fix Pack2 iFix002 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
|
| Solution |
First fixed in DB2 Version 11 Fix Pack 1 or higher |
| Workaround |
Get eventtableschemaName and eventtableName using :
db2 "select EVENTTABLESCHEMA,EVENTTABLENAME from
sysibmts.systsindexes where indexname =
'FILES_FILE_NAME_TSINDEX'"
Lock index to block other admin commands on index during change
of eventview
db2ts set command locks for index
onsite.FILES_FILE_NAME_TSINDEX for text
Trim the eventtable as below:
db2 "delete from
<eventtableschemaName>.<eventtableName>"
Unlock the index for other admin operatin on index.
db2ts clear command locks for index
onsite.FILES_FILE_NAME_TSINDEX for text |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 12.05.2016
10.10.2017
10.10.2017 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 11.1.1.1
|
 |
