MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Ordering columns for expression-based indexes

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
jim haire
Beginner


Joined: 30 Dec 2002
Posts: 140
Topics: 40

PostPosted: Mon Apr 25, 2016 4:35 pm    Post subject: Ordering columns for expression-based indexes Reply with quote

I used to run a query to see what indexes are defined for a table and bring back the results in an index name, colseq order to see the order of the columns on the index.

However, since DB2 v10 if you have an expression based index defined for one or more of the columns in your index, the column sequence does not work.

Does anyone have a work-around for this? Are there different catalog tables that can be looked at to determine the column order and the expression that was defined in the index?
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Mon Apr 25, 2016 6:07 pm    Post subject: Reply with quote

jim haire,

Did you try using SYSIBM.SYSKEYTARGETS catalog table?
Code:

SELECT IXNAME                 
      ,KEYSEQ                 
      ,COLNO                   
  FROM SYSIBM.SYSKEYTARGETS   
 WHERE IXNAME = 'your index name' 
;


Check this link for explanation of SYSIBM.SYSKEYTARGETS table
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
jim haire
Beginner


Joined: 30 Dec 2002
Posts: 140
Topics: 40

PostPosted: Tue Apr 26, 2016 8:40 am    Post subject: Reply with quote

Thanks Kolusu. This will give me what I need!
Back to top
View user's profile Send private message
jim haire
Beginner


Joined: 30 Dec 2002
Posts: 140
Topics: 40

PostPosted: Tue Apr 26, 2016 2:11 pm    Post subject: Reply with quote

Here is a query that will help you return all indexes for a table with the column sequence even if you have indexes which are expression-based.

The upper part of the union returns indexes without expressions and the lower part of the union returns indexes that are expression-based.

Kolusu may have something better, but this seems to return the correct results.

Code:
SELECT IX.NAME, IX.CREATOR, UNIQUERULE, CLUSTERING,
       FULLKEYCARD, COLNAME, COLSEQ AS KEYSEQ, ORDERING
   FROM SYSIBM.SYSINDEXES IX, SYSIBM.SYSKEYS KY
  WHERE IX.TBCREATOR = '???????'
    AND IX.TBNAME = '????????????????'
    AND IX.CREATOR = KY.IXCREATOR
    AND IX.NAME = KY.IXNAME
    AND NOT EXISTS (SELECT 1 FROM SYSIBM.SYSKEYTARGETS KT
                     WHERE IX.CREATOR = KT.IXSCHEMA
                       AND IX.NAME    = KT.IXNAME)
 UNION
SELECT KT.IXNAME, KT.IXSCHEMA, UNIQUERULE, CLUSTERING,
       FULLKEYCARD, DERIVED_FROM, KEYSEQ, KT.ORDERING
   FROM SYSIBM.SYSINDEXES IX,
        SYSIBM.SYSKEYTARGETS KT
  WHERE IX.TBCREATOR = '???????'
    AND IX.TBNAME = '????????????????'
    AND IX.CREATOR = KT.IXSCHEMA
    AND IX.NAME     = KT.IXNAME
  ORDER BY 1,7
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Tue Apr 26, 2016 2:34 pm    Post subject: Reply with quote

jim haire,

Can you try this SQL and see if the results matches the query you had(Untested)
Code:

SELECT IX.NAME                                 
      ,IX.CREATOR                               
      ,UNIQUERULE                               
      ,CLUSTERING                               
      ,FULLKEYCARD                             
      ,COLNAME                                 
      ,COLSEQ                   AS KEYSEQ       
      ,KT.ORDERING                             
  FROM SYSIBM.SYSINDEXES    IX                 
 INNER JOIN                                     
       SYSIBM.SYSKEYS       KY                 
    ON IX.CREATOR    = KY.IXCREATOR             
 INNER JOIN                                     
       SYSIBM.SYSKEYTARGETS KT                 
    ON IX.CREATOR    = KT.IXSCHEMA             
   AND IX.NAME       = KT.IXNAME               
  WHERE IX.TBCREATOR = '???????'               
    AND IX.TBNAME    = '????????????????'       
  ORDER BY 1,7                 

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
jim haire
Beginner


Joined: 30 Dec 2002
Posts: 140
Topics: 40

PostPosted: Wed Apr 27, 2016 11:48 am    Post subject: Reply with quote

When I executed this I am getting a large number of rows. Not sure why?

When I saw the query, I was pretty sure it wasn't going to work. The reason being that the table I am trying to find the indexes for has 2 indexes: One is an expression-based index and one is not.

The KEYTARGETS table only has rows for the expression-based index. In order to get the information for the index that was not expression-based, there would need to be an outer join to the KEYTARGETS table.

It gets a little tricky, because for the expression-based index you need to use the KEYSEQ on the KEYTARGETS table and for the index that is not expression-based you need to use the COLSEQ from SYSKEYS.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Wed Apr 27, 2016 1:00 pm    Post subject: Reply with quote

jim haire wrote:
The KEYTARGETS table only has rows for the expression-based index. In order to get the information for the index that was not expression-based, there would need to be an outer join to the KEYTARGETS table.

It gets a little tricky, because for the expression-based index you need to use the KEYSEQ on the KEYTARGETS table and for the index that is not expression-based you need to use the COLSEQ from SYSKEYS.


Jim,

It is easy to pick the columns we want from different tables. Untested SQL

Code:

 SELECT IX.NAME                                     
       ,CASE WHEN IX.CREATOR  = KT.IXSCHEMA         
              AND IX.NAME     = KT.IXNAME           
             THEN KT.IXSCHEMA                       
             ELSE                                   
                  IX.CREATOR                         
              END                                   
       ,UNIQUERULE                                   
       ,CLUSTERING                                   
       ,FULLKEYCARD                                 
       ,COLNAME                                     
       ,CASE WHEN IX.CREATOR  = KT.IXSCHEMA         
              AND IX.NAME     = KT.IXNAME           
             THEN KT.DERIVED_FROM                   
             ELSE                                   
                  KY.COLNAME                         
              END                                   
       ,CASE WHEN IX.CREATOR  = KT.IXSCHEMA         
              AND IX.NAME     = KT.IXNAME           
             THEN KT.KEYSEQ                         
             ELSE                                   
                  KY.COLSEQ                         
              END                       AS KEYSEQ   
       ,CASE WHEN IX.CREATOR  = KT.IXSCHEMA         
              AND IX.NAME     = KT.IXNAME           
             THEN KT.ORDERING                       
             ELSE                                   
                  KY.ORDERING                       
              END                                   
   FROM SYSIBM.SYSINDEXES    IX                     
       ,SYSIBM.SYSKEYS       KY                     
       ,SYSIBM.SYSKEYTARGETS KT                     
  WHERE IX.CREATOR    = KY.IXCREATOR                 
    AND IX.CREATOR    = KT.IXSCHEMA                 
    AND IX.NAME       = KT.IXNAME                   
    AND IX.TBNAME     = '????????????????'           
    AND IX.TBCREATOR  = '???????'                   
   ORDER BY 1,7                                     
   ;

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
jim haire
Beginner


Joined: 30 Dec 2002
Posts: 140
Topics: 40

PostPosted: Thu Apr 28, 2016 3:40 pm    Post subject: Reply with quote

Yes, but will still need to have a left outer join on KEYTARGETS or the indexes that are not expression-based will not be returned from the SELECT.

It can't be done with all INNER JOINS because there is no row on the KEYTARGETS table for indexes that are not expression-based.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group