Indexes view for DB2 sources - show if the index is partitioned or not

Using DBVis 8.0.12 - not sure if I am missing something, or whether this feature is implemented in V9.x, but.... Would be useful if the "Indexes" tab of the "Object View" was able to show if the index was a partitioned-index, when the data-source supports that range partitioning feature. I can find that information by querying the database catalog, but it would be more useful if the dbvis gui was able to show all of the information about the index that the catalog is able to store - even if this feature was not in the free version.

Hi Mike, We currently show all columns from syscat.indexes in the Indexes tab. If you can tell us which catalog tables/views to query to include the info you want, we can look into adding it. Best Regards, Hans

Hans - you can left join from syscat.indexes to syscat.indexpartitions. Non partitioned indexes will not have entries in syscat.indexpartitions. Partitioned indexes will have entries in syscat.indexpartitions, one row per range-partition per index.

Hi Mike, Thanks for your help with this. Do you mean like this: SELECT * FROM SYSCAT.INDEXES i LEFT OUTER JOIN SYSCAT.INDEXPARTITIONS p ON ( i.INDNAME = p.INDNAME) AND ( i.INDSCHEMA = p.INDSCHEMA) AND ( i.TABNAME = p.TABNAME) AND ( i.TABSCHEMA = p.TABSCHEMA) WHERE i.TABSCHEMA = '' AND i.TABNAME LIKE '' AND i.USER_DEFINED = 1 ; Not sure if all those columns must be part of the LEFT JOIN or if INDNAME and INDSCHEMA is enough to uniquely identify the index. Do you know in which DB2 version syscat.indexpartitions was added? Best Regards, Hans

Hans - the first db2 luw version that IBM documented this syscat.indexpartitions view was V9.7. I checked the v9.5 and v9.1 and v8.1 infocenters for linux/unix/windows flavours, and it is not mentioned there. I'm not sure of the other flavours of db2 (for as/400 or z-os etc) Four join columns is OK. Keep in mind that the only thing I'm suggesting as an enhancement, is an additional column in the current index object display stating whether the index is partitioned or not (if the back end database version supports partitioned indexes), so maybe the existence of a row in syscat.indexpartitions for a specific index is enough to give a value to that column. On larger RDBMS systems it is really useful to see "at one click" whether an index is partitioned or not Worth thinking about. Other customers might have different suggestions that might suggest a different approach or one that might be more generic (back end neutral etc).

Hi Mike, Thanks again for your help. I don't have access to an Enterprise Server Edition database to test this, but I believe this should produce the result your after. SELECT i.*, CASE WHEN p.INDPARTITIONOBJECTID IS NOT NULL THEN 'TRUE' ELSE 'FALSE' END PARTITIONED FROM SYSCAT.INDEXES i LEFT OUTER JOIN SYSCAT.INDEXPARTITIONS p ON ( i.INDNAME = p.INDNAME) AND ( i.INDSCHEMA = p.INDSCHEMA) AND ( i.TABNAME = p.TABNAME) AND ( i.TABSCHEMA = p.TABSCHEMA) WHERE i.TABSCHEMA = '' AND i.TABNAME LIKE '' AND i.USER_DEFINED = 1 ; If you can verify this, we can make the change in the next maintenance release. Maybe it also needs the DISTINCT keyword, in case there are multiple rows for the index in the SYSCAT.INDEXPARTITIONS view. Best Regards, Hans Edited by: Hans Bergsten on May 10, 2013 11:55 PM

Hans, I've been doing other things, but here are two suggestions, first gives basic information, second gives additional details. One way to do it is this: select i.* , case when ipt.indname is null then 'FALSE' else 'TRUE' end as PARTITIONED from syscat.indexes i left join ( select distinct indschema ,indname ,tabschema ,tabname from syscat.indexpartitions ) as ipt on ipt.indschema = i.indschema and ipt.indname = i.indname and ipt.tabschema = i.tabschema and ipt.tabname = i.tabname where i.tabschema = 'MIKE' and i.tabname = 'TEST_TABLE' ; Another way (giving more information i.e. the range partitioning expression)is this: select i.* , case when ipt.indname is null then 'FALSE' else 'TRUE' end as PARTITIONED , case when ipt.indname is not null then dpe.datapartitionexpression else null end as datapartitionexpression from syscat.indexes i left join ( select distinct indschema ,indname ,tabschema ,tabname from syscat.indexpartitions ) as ipt on ipt.indschema = i.indschema and ipt.indname = i.indname and ipt.tabschema = i.tabschema and ipt.tabname = i.tabname left join syscat.datapartitionexpression dpe on i.tabschema = dpe.tabschema and i.tabname = dpe.tabname where i.tabschema = 'MIKE' and i.tabname = 'TEST_TABLE' ;

Hi Mike, Thanks for the suggestions. Is there a reason for doing the LEFT JOIN on a subquery instead of just a LEFT JOIN on the syscat.indexpartitions table as I had it in my example? It seems like the sub query version may be harder for the database to optimize and it could therefore take longer to execute. Best Regards, Hans

As long as you have the DISTINCT on the syscat.indexpartitions you should be OK (there will be one row per range in syscat.indexpartitions per each partitioned index). I was not concerned about the optimizer behaviour here, as you can tell. Possibly because the number of ranges typically won't be vast.

Hi Mike, Thanks for your help with this. We will use the second suggestion in the next maintenance release. Best Regards, Hans