LI74539: SQL ERROR "-901" MAY OCCUR WHEN USING AN XML INDEX DECLARED ON XMLPATTERNS THAT CONCATENATE TEXT VALUES FOR EXAMPLE "//*",

DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error 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.
  

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.
  

Problem summary

• See Error description field for more information.
  

Problem conclusion

• Problem was first fixed in DB2 UDB Version 9.5 FixPack 5.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

  LI74538

• APAR is sysrouted TO one or more of the following:

  IC62797 IC62798 IC62799 IC62800 IC62801

Fix information

• Fixed component name

  DB2 UDE ESE LIN

• Fixed component ID

  5765F4104

Applicable component levels

• R950 PSN

     UP