|
View previous topic :: View next topic
|
| Author |
Message |
soumen2255
New User
Joined: 25 Jun 2008
Posts: 24
Location: Pune
|
|
|
|
Hi,
Can anyone please help me in how to find out the unique index column name in a table? I am getting and 803 error while table insert so want to find the column name that has the unique index and causing the error.I have access to teh SYSIBM tables. |
|
| Back to top |
|
 |
manikawnth
New User
Joined: 07 Feb 2007
Posts: 61
Location: Mumbai
|
|
|
|
Go to SYSIBM.SYSINDEXES, get your name of the unique index for the table.
Then go to SYSIBM.SYSCOLUMNS and get the corresponding columns related to that index.
Thanks,
Manikanth
P.S: Do I make sense or Am I completely absurd about sys tables? |
|
| Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hello Soumen,
You cna use below query for this.It will be better if you read about SYSIBM table
| Code: |
SELECT NAME,COLNAME
FROM SYSIBM.SYSINDEXES,SYSIBM.SYSKEYS
WHERE TBNAME='table name'
AND TBCREATOR='creator'
AND UNIQUERULE ='U'
AND NAME= IXNAME
AND IXCREATOR=CREATOR with ur; |
Remember from the FM
| Quote: |
UNIQUERULE CHAR(1)
NOT NULL
Whether the index is unique:
D
No (duplicates are allowed)
U
Yes
P
Yes, and it is a primary index (As in prior releases of DB2, a value of P is used for primary keys that are used to enforce a referential constraint.)
C
Yes, and it is an index used to enforce UNIQUE constraint
N
Yes, and it is defined with UNIQUE WHERE NOT NULL
R
Yes, and it is an index used to enforce the uniqueness of a non-primary parent key
G
Yes, and it is an index used to enforce the uniqueness of values in a column defined as ROWID GENERATED BY DEFAULT |
|
|
| Back to top |
|
|
manikawnth
New User
Joined: 07 Feb 2007
Posts: 61
Location: Mumbai
|
|
|
|
It is SYSKEYS table instead of SYSCOLUMNS.
I correct my mistake.
THanks guptae |
|
| Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hello manikawnth,
Please refer the manuals before posting your reply. Would you please advise which column of SYSIBM.SYSCOLUMNS table relates the column name with defined index name. |
|
| Back to top |
|
|
manikawnth
New User
Joined: 07 Feb 2007
Posts: 61
Location: Mumbai
|
|
|
|
I am sorry for mistake boss.
I know it is SYSIBM.SYSKEYS. In a hurry i made that mistake.
Dont kill a dead snake. |
|
| Back to top |
|
|
soumen2255
New User
Joined: 25 Jun 2008
Posts: 24
Location: Pune
|
|
|
|
| Thank You Everyone for their valuable help. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| In a hurry i made that mistake. |
And there is no reason for the hurry. . . Admitting to a vice does not make it a virtue. . . 
Suggest you actually test solutions before posting them. . . |
|
| Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA
|
|
|
|
I love killing dead snakes.  |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
It is surely less dangerous. . .
Oooopps - i thought it was dead. . . |
|
| Back to top |
|
|
|
|
