Page 2 of 2

Re: Defining case insensitive Primary Key in DB2

PostPosted: Wed Nov 04, 2009 6:38 pm
by GuyC
The create would fail as long as there are duplicates.
You could still define it with DEFER = YES (which is good practice in most cases when creating an index). The index will be in Rebuild Pending.
The rebuild will fail as long as there are duplicates.

Re: Defining case insensitive Primary Key in DB2

PostPosted: Fri Nov 06, 2009 9:16 pm
by Neelu_Soni
Hi,

I tried UCASE with Create Index. Its not valid for the current release that we are using. Throws the following error

DSNT408I SQLCODE = -4700, ERROR: ATTEMPT TO USE NEW FUNCTION BEFORE NEW
FUNCTION MODE
DSNT418I SQLSTATE = 56038 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHSM5X SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 75 0 0 -1 60 507 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'0000004B' X'00000000' X'00000000' X'FFFFFFFF'
X'0000003C' X'000001FB' SQL DIAGNOSTIC INFORMATION

Neelu

Re: Defining case insensitive Primary Key in DB2

PostPosted: Fri Nov 06, 2009 10:06 pm
by swd
Sounds like you are running DB2 V8 in Compatability mode, and you haven't gone to new function mode yet. Is this the case?

Re: Defining case insensitive Primary Key in DB2

PostPosted: Sat Nov 07, 2009 8:34 am
by Neelu_Soni
Looks like it. I myself do not have DDL access to Database. I had my DBA run the Create Index. He sent me the error thown.

Re: Defining case insensitive Primary Key in DB2

PostPosted: Sat Nov 07, 2009 9:01 am
by dick scherrer
Hello,

Now would probably be a good time to correct the duplicates.

Even when the new feature is available, the data will prevent a successful CREATE. . . (See the info from GuyC above).

Re: Defining case insensitive Primary Key in DB2

PostPosted: Tue Nov 10, 2009 4:36 pm
by GuyC
swd wrote:Sounds like you are running DB2 V8 in Compatability mode, and you haven't gone to new function mode yet. Is this the case?

More likely DB2 9 in Conversion mode ;)