Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 XML indexes (V10)

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
ozburt

New User


Joined: 05 Nov 2009
Posts: 5
Location: UK

PostPosted: Tue Jan 07, 2014 3:39 pm    Post subject: DB2 XML indexes (V10)
Reply with quote

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
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Jan 07, 2014 4:09 pm    Post subject:
Reply with quote

this ?
Code:
RENAME INDEX "RXFTP1"."I_DOCIDORG_DATA_PROVIDER_"  TO "I_DOCIDORG_DATA_PROVIDER_H"
Back to top
View user's profile Send private message
ozburt

New User


Joined: 05 Nov 2009
Posts: 5
Location: UK

PostPosted: Tue Jan 07, 2014 4:23 pm    Post subject: Reply to: DB2 XML indexes (V10)
Reply with quote

Great - I had forgotten about that. Many thanks, it has worked. icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Identify top 30 big tables or indexes... ashek15 DB2 0 Fri Jun 16, 2017 10:01 am
No new posts How to access SYSCAT.INDEXES ravikumar15 DB2 6 Thu Apr 25, 2013 1:08 am
No new posts Performance by DB2 with non-unique in... rakesh1155 DB2 10 Wed Mar 14, 2012 12:58 pm
No new posts Why Alternate indexes are not support... Phrzby Phil Mainframe Interview Questions 18 Mon Nov 28, 2011 12:19 pm
No new posts need some info on DB2 indexes raja1128 DB2 3 Sat Jul 30, 2011 6:25 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us