Posted: Mon Apr 25, 2016 4:35 pm Post subject: Ordering columns for expression-based indexes
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?
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
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
Posted: Tue Apr 26, 2016 2:34 pm Post subject:
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
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.
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
Posted: Wed Apr 27, 2016 1:00 pm Post subject:
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
;
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
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
