Start a new topic

displaying indexes for DB2 databases

[This topic is migrated from our old forums. The original author name has been removed]

As no one has replied yet, I'll edit this post (rather than reply to myself) with my better understanding of my question: I think this question is probably more about me not understanding DB2 views/tables, rather than a DBVisualizer question, but I'll persist: When I view Tables, the INDEXES tab is correctly populated. When I view Views, the INDEXES tab only shows: SCHEMA, NAME, 0, (null), (null), 0, 0, (null), (null), (null), (null), (null) Does a View not have access to all the Table's Indexes? If so, shouldn't dbvisualizer show them in the Indexes tab? (All our site's tables are redefined as views, and for some schemas I only have access to the views - it would be nice if the Indexes tab showed the Index info, rather than me having to look it up in SYSIBM.SYSINDEXES) My database is configured as DB2LUW. Driver name reports as IBM Data Server for JDBC and SQLJ, version 4.15.82 Edited by: MartinH on 24-Oct-2013 18:48

[This reply is migrated from our old forums.]

Re: displaying indexes for DB2 databases
Hi Martin, Can you post a screenshot? The Views node should not have any Indexes tab. What is the Database Type set to for the connection? Best Regards, Hans
[This reply is migrated from our old forums. The original author name has been removed]
[Attachment has been removed.]

Re: displaying indexes for DB2 databases
The database type is DB2 LUW. Unfortunately my newly installed 9.1.1 (installed over 9.0.7) has stopped working after the first reboot - it just times out on all database connections - i may have to uninstall and try again. But I have 8.0.11 on my other computer and it can reproduce the issue too. From what you are saying, it sounds like my views are being treated as tables. (Strangely I notice now that all my tables have a first indexes row of (null),SCHEMA,NAME,0,(null),(null),0,0,(null),(null),(null),(null),(null), followed by valid rows for the actual indexes - see attached table pic). Would it be considered 'inappropriate' to display the table indexes tab for a view?; I mean knowing the keys is crucial to coming up with efficient queries surely?
[This reply is migrated from our old forums.]

Re: displaying indexes for DB2 databases
Hi Martin, Can you please post a screenshot, from 8.0.11 if it takes you some time to get 9.1.1 reinstalled? Like I said earlier, there should not be any Index tab for a view, but maybe we are talking about different things. A screenshot should clarify that. Since a view cannot have any indexes, I don't see how we can show an Indexes tab for a view. Please see this page for more about views and indexes: http://bytes.com/topic/db2/answers/490119-can-i-create-index-view Best Regards, Hans
[This reply is migrated from our old forums. The original author name has been removed]

Re: displaying indexes for DB2 databases
I'm confused...are you not able to see the two screenshots I attached to the previous post? Is my post still in draft or something... I do understand that indexes are an attribute of the table not the view, but they are useful information when deciding on how to query the view and you may not have access to the table. But I can see that it is 'cleaner' to exclude the index info (the view might not even contain the indexes columns). So I guess my question is answered...I shouldn't be able to see an indexes tab for my views. There's just the tangential issue of why I do see such a tab.
[This reply is migrated from our old forums.]

Re: displaying indexes for DB2 databases
Hi Martin, My apologies, I missed that you had already posted screenshots. The screenshots show that you use DbVisualizer Free edition, not the Pro edition as I had assumed. With the Free edition, tables and views are shown with the same sub tabs in the Object View tab, because the JDBC driver does not distinguish between them. Hence the Indexes tab for the view without any real information. With the Pro edition, tables and views are shown with different sub tabs in the Object View tab, and there is no Indexes tab for a view. Showing indexes for the underlying tables used by a view would probably just be misleading, because there is virtually no way to know if they will be used when querying the view. I hope this clarifies the situation. Best Regards, Hans
[This reply is migrated from our old forums. The original author name has been removed]

Re: displaying indexes for DB2 databases
Perfect, that all makes sense. Thank you for talking it through with me. Martin.