IBM Support

IT20242: SQL20288N "INDEX WITH XPTH TYPE". REASON CODE = "3" USING DB2LOOK -M OUPUT

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • db2look -m will generate update SQL for Indexes with XML
    columns. When this output is used this SQL will fail with
    
    SQL20288N  Statistics could not be updated for object "xxxxxxxx"
    of
    type "Index with XPTH type". Reason code = "3".  SQLSTATE=428DY
    
    db2sampl
    db2look -d -e -m :
    
    Has:
    
    CREATE TABLE "MARIAJ  "."PRODUCT"  (
                      "PID" VARCHAR(10) NOT NULL ,
                      "NAME" VARCHAR(128) ,
                      "PRICE" DECIMAL(30,2) ,
                      "PROMOPRICE" DECIMAL(30,2) ,
                      "PROMOSTART" DATE ,
                      "PROMOEND" DATE ,
                      "DESCRIPTION" XML )
                     IN "IBMDB2SAMPLEXML" ;
    
    
    CREATE INDEX "MARIAJ  "."PROD_DETAIL_XMLIDX" ON "MARIAJ
    "."PRODUCT"
                    ("DESCRIPTION" ASC)
                    GENERATE KEY USING XMLPATTERN
    '/product/description/detail'
                      AS SQL VARCHAR  HASHED IGNORE INVALID VALUES
                    COMPRESS NO ALLOW REVERSE SCANS;
    
    
    UPDATE SYSSTAT.INDEXES
    SET NLEAF=-1,
        NLEVELS=-1,
        FIRSTKEYCARD=-1,
        FIRST2KEYCARD=-1,
        FIRST3KEYCARD=-1,
        FIRST4KEYCARD=-1,
        FULLKEYCARD=-1,
        SEQUENTIAL_PAGES=-1,
        DENSITY=-1,
        AVERAGE_SEQUENCE_GAP=-1.000000,
        AVERAGE_SEQUENCE_PAGES=-1.000000,
        AVERAGE_RANDOM_PAGES=-1.000000,
        NUM_EMPTY_LEAFS=-1,
        INDCARD=-1
    WHERE COLNAMES = '+DESCRIPTION'
          AND TABNAME = 'PRODUCT' AND TABSCHEMA = 'MARIAJ  ';
    
    
    When using the db2look mimic output produced (db2look -d sample
    -m -o lookm.out, running db2 -tvf lookm.out)
    
    UPDATE SYSSTAT.INDEXES SET NLEAF=-1, NLEVELS=-1,
    FIRSTKEYCARD=-1, FIRST2KEYCARD=-1, FIRST3KEYCARD=-1,
    FIRST4KEYCARD=-1, FULLKEYCARD=-1, SEQUENTIAL_PAGES=-1,
    DENSITY=-1, AVERAGE_SEQUENCE_GAP=-1.000000,
    AVERAGE_SEQUENCE_PAGES=-1.000000,
    AVERAGE_RANDOM_PAGES=-1.000000, NUM_EMPTY_LEAFS=-1, INDCARD=-1
    WHERE COLNAMES = '+DESCRIPTION' AND TABNAME = 'PRODUCT' AND
    TABSCHEMA = 'MARIAJ  '
    DB21034E  The command was processed as an SQL statement because
    it was not a
    valid Command Line Processor command.  During SQL processing it
    returned:
    SQL20288N  Statistics could not be updated for object
    "SQL170330082340110" of
    type "Index with XPTH type". Reason code = "3".  SQLSTATE=428DY
    
    This error does not affect the updating of the statistics and
    can be safely ignored
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 Version 11.1 Mod1 and Fix Pack 2 (11.1m2fp2)  *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in DB2 Version 11.1 Mod2 and Fix Pack 2
    (11.1m2fp2)
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT20242

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2017-04-18

  • Closed date

    2017-06-23

  • Last modified date

    2017-06-23

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

    IT20057

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
29 June 2020