|
Problem IC86158
|
Status: Closed |
SYSIBMADM.ALL_IND_COLUMNS HAS DUPLICATE ROWS FOR ONE TABLE |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
SYSIBMADM.ALL_IND_COLUMNS may return duplicate rows for a table
e.g.
db2 select * from all_ind_columns where table_name='T1'
INDEX_OWNER
INDEX_SCHEMA
INDEX_NAME
TABLE_OWNER
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
COLUMN_POSITION DESCEND
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
--------------- -------
CONNIE
CONNIE
IND1
CONNIE
CONNIE
T1
COL1
1 N
CONNIE
CONNIE
IND1
CONNIE
CONNIE
T1
COL1
1 N
This problem only happens if there are multiple rows in
syscat.tabauth where grantee is the table owner.
e.g. CONNIE is the table owner. There is one row with grantor
'TEST' and another row with grantor 'AAA', both have grantee as
'CONNIE'.
db2 select * from syscat.tabauth where tabname='T1'
GRANTOR
GRANTORTYPE GRANTEE
GRANTEETYPE TABSCHEMA
TABNAME
CONTROLAUTH ALTERAUTH DELETEAUTH INDEXAUTH INSERTAUTH REFAUTH
SELECTAUTH UPDATEAUTH
----------------------------------------------------------------
----------------------------------------------------------------
-----------
----------------------------------------------------------------
----------------------------------------------------------------
-----------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------- --------- ---------- --------- ---------- -------
---------- ----------
SYSIBM
S CONNIE
U CONNIE
T1
Y G G G G G G
G
TEST
U CONNIE
U CONNIE
T1
N Y Y Y Y Y Y
Y
AAA
U CONNIE
U CONNIE
T1
N G G G G G G
G |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* DB2 Users *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.7 Fix pack 8 *
**************************************************************** |
| Local Fix: |
To avoid the duplicate rows, include the keyword DISTINCT when
querying SYSIBMADM.ALL_IND_COLUMNS. E.g. select distinct * from
sysibmadm.all_ind_columns. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Problem first fix in Version 9.7 Fix pack 8 |
| Workaround |
To avoid the duplicate rows, include the keyword DISTINCT when
querying SYSIBMADM.ALL_IND_COLUMNS. E.g. select distinct * from
sysibmadm.all_ind_columns. |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC88045
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 27.08.2012
08.04.2013
08.04.2013 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP8
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.8
|
 |
