IBM Support

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

Subscribe

You can track all active APARs for this component.

 

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 number

    LI74539

  • Reported component name

    DB2 UDE ESE LIN

  • Reported component ID

    5765F4104

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-05-27

  • Closed date

    2010-01-04

  • Last modified date

    2010-01-04

  • 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

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
04 January 2010