There are 2 DB2 XML indexes that end with an underscore ‘_’ which is something we would prefer to avoid.
Looking at it in more detail I can see that these are generated by the system and their length is limited to 25 characters.
From the DDL script the following table is created:
CREATE TABLE "RXFTP1"."ORG_DATA_PROVIDER_H"
("DATA_PROVIDER_PARTY_ID" CHARACTER(32) FOR MIXED DATA
,"CREATED_DTTM" TIMESTAMP (6) WITHOUT TIME ZONE
,"CREATED_BY_USER_ID" CHARACTER(32) FOR MIXED DATA
,"LAST_UPDATED_DTTM" TIMESTAMP (6) WITHOUT TIME ZONE
,"LAST_UPDATED_BY_USER_ID" CHARACTER(32) FOR MIXED DATA
WITH DEFAULT 0
WITH DEFAULT 'ROW_STATUS_ACTIVE'
WITH DEFAULT NULL
,CONSTRAINT "DATA_PROVIDER_HK" PRIMARY KEY
NOT VOLATILE CARDINALITY
DATA CAPTURE NONE
PARTITION BY SIZE EVERY 4G;
The index I_DOCIDORG_DATA_PROVIDER_ is generated by the system & looks like this:
CREATE INDEX "RXFTP1"."I_DOCIDORG_DATA_PROVIDER_"
USING STOGROUP "SGSMS"
Ideally the index should end with a ‘_H’ to correspond with the table its relates to.
I did a test to explicitly create the XML index ending in ‘H’ in a test database but the system also generates the index ending in an underscore.
So you get two indexes:
I_DOCIDORG_DATA_PROVIDER_ generated by the system
I_DOCIDORG_DATA_PROVIDER_H explicitly defined by me
However, I did try and manually the system generated index but am prevented from doing so due to a -669 (0002) SQL error code which states that the object cannot be explicitly dropped due to the presence of an XML column defined on the table.
Dropping and redefining the XML objects makes no difference as we still get the same outcome. We may have to leave these ‘_’ indexes as they and accept it as a quirk of the system (but be aware of it) unless you know of an alternative.
How can the system generated XML index be removed?