ozburt
New User
Joined: 05 Nov 2009
Posts: 5
Location: UK
|
|
|
|
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:
| Code: |
CREATE TABLE "RXFTP1"."ORG_DATA_PROVIDER_H"
("DATA_PROVIDER_PARTY_ID" CHARACTER(32) FOR MIXED DATA
NOT NULL
,"CREATED_DTTM" TIMESTAMP (6) WITHOUT TIME ZONE
NOT NULL
WITH DEFAULT
,"CREATED_BY_USER_ID" CHARACTER(32) FOR MIXED DATA
NOT NULL
,"LAST_UPDATED_DTTM" TIMESTAMP (6) WITHOUT TIME ZONE
NOT NULL
WITH DEFAULT
,"LAST_UPDATED_BY_USER_ID" CHARACTER(32) FOR MIXED DATA
NOT NULL
,"ROW_VERSION_SEQ" INTEGER
NOT NULL
WITH DEFAULT 0
,"ROW_STATUS_CD" VARGRAPHIC(48)
NOT NULL
WITH DEFAULT 'ROW_STATUS_ACTIVE'
,"DATA_PROVIDER_TYPE_CD" VARGRAPHIC(48)
NOT NULL
,"DATA_PROVIDER_NAME" VARGRAPHIC(60)
NOT NULL
,"DATA_PROVIDER_DESC" VARGRAPHIC(255)
WITH DEFAULT NULL
,"DATA_PROVIDER_XCD" VARGRAPHIC(40)
NOT NULL
,"OPERATING_COUNTRY_CD" VARGRAPHIC(3)
NOT NULL
,"CREATED_BY_MODULE_CD" VARGRAPHIC(16)
NOT NULL
WITH DEFAULT
,"EXTENSION_XML" XML
,CONSTRAINT "DATA_PROVIDER_HK" PRIMARY KEY
("DATA_PROVIDER_PARTY_ID"
,"ROW_VERSION_SEQ"
)
)
IN "RXFTDBP1"."ORGR0006"
APPEND NO
NOT VOLATILE CARDINALITY
DATA CAPTURE NONE
AUDIT NONE
CCSID UNICODE
PARTITION BY SIZE EVERY 4G;
|
The index I_DOCIDORG_DATA_PROVIDER_ is generated by the system & looks like this:
| Code: |
CREATE INDEX "RXFTP1"."I_DOCIDORG_DATA_PROVIDER_"
ON "RXFTP1"."ORG_DATA_PROVIDER_H"
("DB2_GENERATED_DOCID_FOR_XML" ASC
)
NOT CLUSTER
DEFINE YES
COMPRESS NO
BUFFERPOOL "BP5"
CLOSE YES
DEFER NO
COPY NO
USING STOGROUP "SGSMS"
PRIQTY -1
SECQTY -1
ERASE NO
FREEPAGE 0
PCTFREE 10
GBPCACHE CHANGED
PIECESIZE 2097152K; |
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?
Any advice would be appreciated.
Code'd |
|
