IBM Mainframe Forum

We are on DB2 V8 NFM (z/OS) and I recently had to add a new column to a unique/partitioned index. After extracting the DDL at the table level, the subsequent script to recreate everything (with new column in the unique index) failed with:

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "-". SOME SYMBOLS THAT MIGHT
BE LEGAL ARE: )

Here is the DDL for the index that is failing:

CREATE UNIQUE INDEX "TOSXC"."IITMRCV1"
ON "TOSXC"."TITMRCV"
("RCPT_DT" ASC,
"PO_NMBR" ASC,
"LN_NMBR" ASC,
"RCPT_NMBR" ASC,
"INVC_NMBR" ASC,
"RCPT_QNTY" ASC,
"RCPT_AMNT" ASC,
"VCHR_NMBR" ASC
)
DEFINE YES
CLUSTER
(PART 1
VALUES (1997-12-31)
USING STOGROUP "DB2T03"
PRIQTY 720
SECQTY 720
ERASE NO
FREEPAGE 0
PCTFREE 5
GBPCACHE CHANGED,
PART 2
VALUES (1998-12-31)
USING STOGROUP "DB2T02"
PRIQTY 720
SECQTY 720
ERASE NO
FREEPAGE 0
PCTFREE 5
GBPCACHE CHANGED
)
BUFFERPOOL BP2
CLOSE YES
DEFER NO
COPY NO;


I have tried several variations of date in the VALUES clause, but all have failed:

PART 2 VALUES ('1998-12-31')

DSNT408I SQLCODE = -20182, ERROR: PARTITIONING CLAUSE ENDING AT ON CREATE
STATEMENT FOR TOSXC.IITMRCV1 IS NOT VALID
DSNT418I SQLSTATE = 530A2 SQLSTATE RETURN CODE


PART 2 VALUES (1998-12-31)

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "-". SOME SYMBOLS THAT MIGHT
BE LEGAL ARE: ) ,
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE


PART 2 VALUES ('12/31/1998')

DSNT408I SQLCODE = -20182, ERROR: PARTITIONING CLAUSE ENDING AT ON CREATE
STATEMENT FOR TOSXC.IITMRCV1 IS NOT VALID
DSNT418I SQLSTATE = 530A2 SQLSTATE RETURN CODE


PART 2 VALUES (12/31/1998)

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "/". SOME SYMBOLS THAT MIGHT
BE LEGAL ARE: ) ,
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE


BTW, we are using the ISO DATE format, which is: yyyy-mm-dd

Does anyone have any ideas why I'm unable to get this index built?

Thanks.

Here is the fix...
Needed to include 'PARTITION BY RANGE' and enclose the actual date on the VALUES (or ENDING AT) clause, in single quotes...
CREATE UNIQUE INDEX "TOSXC"."IITMRCV1"
ON "TOSXC"."TITMRCV"
("RCPT_DT" ASC,
"PO_NMBR" ASC,
"LN_NMBR" ASC,
"RCPT_NMBR" ASC,
"INVC_NMBR" ASC,
"RCPT_QNTY" ASC,
"RCPT_AMNT" ASC,
"VCHR_NMBR" ASC
)
DEFINE YES
USING STOGROUP "DB2T03"
PRIQTY 720
SECQTY 720
ERASE NO
FREEPAGE 0
PCTFREE 5
GBPCACHE CHANGED
CLUSTER
PARTITION BY RANGE
(PARTITION 1 ENDING AT ('1997-12-31')
,PARTITION 2 ENDING AT ('1998-12-31'))
BUFFERPOOL BP2
CLOSE YES
DEFER NO
COPY NO;