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 |
