|
Problem IC62797
|
Status: Closed |
SQL ERROR "-901" MAY OCCUR WHEN USING AN XML INDEX DECLARED ON XMLPATTERNS THAT CONCATENATE TEXT VALUES FOR EXAMPLE "//*", |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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. |
| Problem Summary: |
See Error description field for more information. |
| Local Fix: |
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. |
| 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 |
Problem was first fixed in DB2 UDB Version 9.7 FixPack 1. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 26.08.2009
04.01.2010
04.01.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP1
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
