IBM Mainframe Forum

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.

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

Sounds like you are running DB2 V8 in Compatability mode, and you haven't gone to new function mode yet. Is this the case?

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.

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).

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