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

DB2 Version 11.1 Mod 2 Fix Pack 2 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod2 Fix Pack2 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod2 Fix Pack2 iFix002 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod4 Fix Pack4 iFix001 for Linux, UNIX, and Windows

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