DB2 FOR LUW / DB2FORLUW / 970 - DB2

The error can happen in both scenarios if we are creating and 
index as DATE, TIMESTAMP, DOUBLE and the index value generated 
from the xmlpattern is very large. 
 
Scenario A) 
If you create index and then import data, the import statement 
will fail with -901 error 
 
db2 "CREATE INDEX IND1 ON COL1 ("DATA" ASC) GENERATE KEY 
USING XMLPATTERN '//*' AS SQL DATE ALLOW REVERSE SCANS" 
DB20000I  The SQL command completed successfully. 
 
db2 "import from dat1.del of del messages test.log 
insert_update into tab1" 
SQL3306N  An SQL error "-901" occurred while inserting a row 
into the table. 
 
 
Scenario B) 
If you import the data and then try to create index, the create 
index statement will fail with -901 error 
 
db2 "import from dat1.del of del messages test.log 
insert_update into tab1" 
 
Number of rows read         = 1 
Number of rows skipped      = 0 
Number of rows inserted     = 1 
Number of rows updated      = 0 
Number of rows rejected     = 0 
Number of rows committed    = 1 
 
db2 "CREATE INDEX IND1 ON COL1 ("DATA" ASC) GENERATE KEY 
USING XMLPATTERN '//*' AS SQL DATE ALLOW REVERSE SCANS" 
DB21034E  The command was processed as an SQL statement because 
it was not a valid Command Line Processor command.  During SQL 
processing it returned: 
SQL0901N  The SQL statement failed because of a non-severe 
system error.

See Error description field for more information.

As a work around the user can provide a more descriptive 
xmlpattern expression as part of their CREATE INDEX statement. 
Instead of using something like "//*" which tries to index 
everything, the following pattern expression can be used to 
efficiently identify the nodes to be indexed: 
 
db2 "CREATE INDEX IDX1 ON COL1 ("DATA" ASC) GENERATE KEY 
USING XMLPATTERN '/FistName/LastName/' AS SQL DATE ALLOW 
REVERSE SCANS" 
 
Please NOTE that when using xmlpattern "//*" to create the 
index, the generated index key value could contain entries from 
every text node in every xml document in the xml column which 
essentially could result in indexing everything. Such heavy 
indexes are not recommended since they are very costly to 
maintain during insert/update/delete operations and they cosume 
a lot of storage space. Also, using such xml patterns can 
generate an index with key values the customer might not want.

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
 

Problem was first fixed in DB2 UDB Version 9.7 FixPack 1.

keiner bekannt / siehe Local-Fix

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

26.08.2009
04.01.2010
04.01.2010

9.7.FP1