Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Unique Index and -803

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
JayC

New User


Joined: 27 Sep 2008
Posts: 70

PostPosted: Mon Jan 05, 2009 2:12 pm    Post subject: Unique Index and -803
Reply with quote

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
View user's profile Send private message

muthuvel

Active User


Joined: 29 Nov 2005
Posts: 217
Location: Chennai

PostPosted: Mon Jan 05, 2009 2:21 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2180
Location: @my desk

PostPosted: Mon Jan 05, 2009 2:40 pm    Post subject:
Reply with quote

JayC,

You can try checking the SYSIBM.SYSINDEXES table and list out all the indexes for the table in question.
Back to top
View user's profile Send private message
JayC

New User


Joined: 27 Sep 2008
Posts: 70

PostPosted: Mon Jan 05, 2009 7:10 pm    Post subject:
Reply with quote

Hi Arun,

How do I check this table, I mean do I execute a query ? I got the following link : http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.ugref/db2z_utl_checkindex.htm
Would you have an easier way to check what are the columns that constitue this unique index constraint.

thanks.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jan 06, 2009 3:57 pm    Post subject:
Reply with quote

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
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jan 06, 2009 3:57 pm    Post subject:
Reply with quote

in addition if the value is P its unique and primary index
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Jan 06, 2009 4:26 pm    Post subject:
Reply with quote

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
View user's profile Send private message
JayC

New User


Joined: 27 Sep 2008
Posts: 70

PostPosted: Wed Jan 07, 2009 4:23 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jan 07, 2009 5:27 pm    Post subject:
Reply with quote

the NAME result from the first query should be cut/pasted in the IXNAME in clause of the second query.
Back to top
View user's profile Send private message
JayC

New User


Joined: 27 Sep 2008
Posts: 70

PostPosted: Wed Jan 07, 2009 7:40 pm    Post subject:
Reply with quote

Ok,
Do I need to include the condition 'AND IXNAME = I.NAME' also ?:
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Several errors during building altern... Andi1982 JCL & VSAM 11 Wed Jul 06, 2016 7:39 pm
No new posts Drop building Alternate Index for a p... bhavin.mehta JCL & VSAM 6 Mon Jul 04, 2016 3:47 pm
No new posts VSAM Alternate Index in CICS jacobdng CICS 2 Fri May 06, 2016 1:41 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us