|
View previous topic :: View next topic
|
| Author |
Message |
JayC
New User
Joined: 27 Sep 2008
Posts: 70
|
|
|
|
Hi,
I got a -803 in one of my production jobs, the SQLCODE explaination says that the Index in the given index-space contrains the columns of the table-No two rows can have duplicate values because of this constraint.
My question is how do I know whats the COnstraint, whats the column that makes up the Unique Index. This way I can try and check the data that tried to insert into the table and caused the abend.
Right now, I know that the Table has 2 primary key. Would I be correct if
I assume that the unique index is made up of these 2 primary keys only ?
Thanks in advance.[/b] |
|
| Back to top |
|
 |
muthuvel
Active User
Joined: 29 Nov 2005
Posts: 217
Location: Canada
|
|
|
|
If there is an utility like BMCDB2 in your shop,then you can use that for checking the indexes.If you don't have this please ask your DBAs if any of the available tools to check index or ask them to do for the concerned table.
It is not correct or to assume only that 2 keys that form index.It can have some more fields also. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
JayC,
You can try checking the SYSIBM.SYSINDEXES table and list out all the indexes for the table in question. |
|
| Back to top |
|
|
JayC
New User
Joined: 27 Sep 2008
Posts: 70
|
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
select uniquerule from sysibm.sysindexes where tbname ='table name' ;
if uniquerule is U then it is a unique index ... later chk for the index definition to get your cols .... |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| in addition if the value is P its unique and primary index |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
if your programs where written to include a call to dsntiar to provide info on the neg sql code, you would have found the name of the index that was violated (caused the -803 to be invoked).
this query will provide you with index names if you know the table name:
| Code: |
SELECT NAME
, UNIQUERULE
, COLCOUNT
, INDEXTYPE
, REMARKS
FROM SYSIBM.SYSINDEXES
WHERE TBNAME = '<your table name>'
AND TBCREATOR = '<your creator name>'
ORDER BY TBNAME, NAME; |
then, when you have the index names, this query will provide the columns and rules for each index:
| Code: |
SELECT K.IXNAME
, I.UNIQUERULE AS UNIQ
, K.COLNO
, K.COLSEQ
, K.ORDERING
, K.COLNAME
FROM SYSIBM.SYSKEYS K
, SYSIBM.SYSINDEXES I
WHERE IXNAME IN (
'XOTHPAR0'
, 'XOTHPAR1'
)
AND IXCREATOR = '<your creator name>'
AND IXCREATOR = I.TBCREATOR
AND IXNAME = I.NAME
ORDER BY IXNAME, COLSEQ, COLNO
; |
|
|
| Back to top |
|
|
JayC
New User
Joined: 27 Sep 2008
Posts: 70
|
|
|
|
Hi Dick,
I executed the first query that you showed and got 3 indexes as the result.
Now using the second query I should be able to get the columns and the rules for each of the 3 indexes. But , then I excute the following query (as guided by you)
Could you kindly tell me whats the mean:
| Code: |
SELECT K.IXNAME
, I.UNIQUERULE AS UNIQ
, K.COLNO
, K.COLSEQ
, K.ORDERING
, K.COLNAME
FROM SYSIBM.SYSKEYS K
, SYSIBM.SYSINDEXES I
--WHERE IXNAME IN ('XOTHPAR0','XOTHPAR1')
WHERE IXNAME = 'MYINDEXNAME'
AND IXCREATOR = 'MYINDEXCREATORNAME' |
I seem to get a single column in the result set and they all are "myindexname' which I had found out from the first query.
Could you tell me whats the meaning of
| Code: |
WHERE IXNAME IN ('XOTHPAR0','XOTHPAR1')
|
Kindly help,
thank you |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| the NAME result from the first query should be cut/pasted in the IXNAME in clause of the second query. |
|
| Back to top |
|
|
JayC
New User
Joined: 27 Sep 2008
Posts: 70
|
|
|
|
Ok,
Do I need to include the condition 'AND IXNAME = I.NAME' also ?: |
|
| Back to top |
|
|
|
|
