Unable to create Part index with DATE



IBM's flagship relational database management system

Unable to create Part index with DATE

Postby scochran » Wed May 06, 2009 6:49 pm

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.
scochran
 
Posts: 2
Joined: Fri Jan 16, 2009 12:04 am
Has thanked: 0 time
Been thanked: 0 time

Re: Unable to create Part index with DATE

Postby scochran » Wed May 06, 2009 7:24 pm

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;
scochran
 
Posts: 2
Joined: Fri Jan 16, 2009 12:04 am
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post