Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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 column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts Confusion b/w index and subscript Deepak kumar25 Mainframe Interview Questions 7 Thu Aug 31, 2017 6:50 am
No new posts Select numeric portion from CHAR data... balaji81_k DB2 6 Sat Aug 19, 2017 1:51 am
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us