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
 

 

Determine Unique Index column name

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

New User


Joined: 25 Jun 2008
Posts: 24
Location: Pune

PostPosted: Thu Mar 18, 2010 1:20 pm    Post subject: Determine Unique Index column name
Reply with quote

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

manikawnth

New User


Joined: 07 Feb 2007
Posts: 61
Location: Mumbai

PostPosted: Thu Mar 18, 2010 1:35 pm    Post subject:
Reply with quote

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

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Thu Mar 18, 2010 1:46 pm    Post subject:
Reply with quote

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

New User


Joined: 07 Feb 2007
Posts: 61
Location: Mumbai

PostPosted: Thu Mar 18, 2010 1:48 pm    Post subject: Sorry for misleading info
Reply with quote

It is SYSKEYS table instead of SYSCOLUMNS.
I correct my mistake.
THanks guptae
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Thu Mar 18, 2010 1:49 pm    Post subject:
Reply with quote

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

New User


Joined: 07 Feb 2007
Posts: 61
Location: Mumbai

PostPosted: Thu Mar 18, 2010 2:00 pm    Post subject: @Guptae
Reply with quote

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

New User


Joined: 25 Jun 2008
Posts: 24
Location: Pune

PostPosted: Thu Mar 18, 2010 3:44 pm    Post subject: Reply to: Determine Unique Index column name
Reply with quote

Thank You Everyone for their valuable help.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Mar 18, 2010 8:57 pm    Post subject:
Reply with quote

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. . . icon_smile.gif

Suggest you actually test solutions before posting them. . .
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Fri Mar 19, 2010 1:28 am    Post subject:
Reply with quote

I love killing dead snakes. icon_lol.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Mar 19, 2010 1:47 am    Post subject: Reply to: Determine Unique Index column name
Reply with quote

It is surely less dangerous. . .

Oooopps - i thought it was dead. . .
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 Determine Region with max bandwidth f... seahawk789 COBOL Programming 3 Mon Feb 27, 2017 8:26 pm
No new posts setting new limit Key values for inde... srilata83 DB2 1 Fri Feb 10, 2017 9:24 pm
This topic is locked: you cannot edit posts or make replies. DB2 query Using Recursion, Converting... smilewithashu2 DB2 3 Tue Jan 03, 2017 12:50 pm
No new posts Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 6 Sun Nov 06, 2016 8:11 pm


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