Toad World® Forums

Bug Viewing Indexes


#1

Bug Viewing Indexes


#2

Scenario: When viewing indexes via MSAccess on a Access table, multiple columns
participating in an index are shown.

BUG: When viewing with TDA whether directly or via ODBC, only the first column
of that index is shown.

Scenario: When viewing indexes via MSAccess, on DB2V6R1, multiple columns
participating in an index are shown.

BUG: When viewing with TDA via ODBC, only the first column of that index is
shown.

SELECT qsys2.SYSINDEXES.TABLE_NAME,

qsys2.SYSINDEXES.INDEX_NAME,

qsys2.syskeys.ordinal_position,

qsys2.syskeys.column_name,

qsys2.syskeys.ordering

FROM qsys2.SYSINDEXES

INNER JOIN

qsys2.syskeys

ON qsys2.SYSINDEXES.INDEX_NAME = qsys2.syskeys.index_name

AND qsys2.SYSINDEXES.INDEX_schema = qsys2.syskeys.index_schema

WHERE qsys2.SYSINDEXES.table_schema = ‘Schema’

AND qsys2.SYSINDEXES.TABLE_NAME = ‘TableName’

ORDER BY qsys2.SYSINDEXES.INDEX_NAME, qsys2.syskeys.ordinal_position ;

Scenario: Similarly, but better, with SQL Server, a direct connection to the
database does show all columns participating in an index.

BUG: When viewing with TDA via ODBC, only the first column of that index is
shown.

USE ‘Schema’

GO

SELECT SYSOBJECTS.name AS table_name,

SYSINDEXES.name AS index_name,

sysindexkeys.keyno AS ordinal_position,

SYSCOLUMNS.name AS column_name

FROM SYSOBJECTS

INNER JOIN SYSINDEXES

ON SYSINDEXES.ID = SYSOBJECTS.ID

INNER JOIN sysindexkeys

ON sysindexkeys.id = SYSINDEXES.ID

AND sysindexkeys.indid = SYSINDEXES.INDID

INNER JOIN syscolumns

ON syscolumns.id = SYSOBJECTS.ID

AND syscolumns.colid = sysindexkeys.colid

LEFT OUTER JOIN syscomments

ON syscomments.id = SYSOBJECTS.ID

AND syscomments.colid = syscolumns.colid

WHERE SYSOBJECTS.NAME = ‘TableName’

ORDER BY SYSINDEXES.name, sysindexkeys.keyno


#3

TDA provides an explain plan for SQL Server connections, but could it also
provide that functionality when connected via ODBC to DB2 V6R1. There is a
Database Performance API that is available called Visual Explain (QQQVEXPL).


#4

Hi Tad,

I reproduced this issue, CR 79017 is created. Thanks for reporting this issue.

Jane