Index Expression question

Mark Stone

Index Expression question
Does anyone know if the CAST function can be used in an index
expression, I'm getting a -628 (SQL0628N Multiple or conflicting
keywords involving the " " clause are present.) when trying to create
an index with the following:

CREATE UNIQUE INDEX "FDBEL"."XRGMSTC"
ON "FDBEL"."REG_MASTER"
( FAMILY_ID, CAST(RELATION_ID AS INTEGER))
USING STOGROUP "FSGALLP"
PRIQTY 7500 SECQTY 3000 ERASE NO
FREEPAGE 0
PCTFREE 10
GBPCACHE CHANGED
DEFINE YES
COMPRESS NO
CLUSTER
BUFFERPOOL BP11
CLOSE NO
PIECESIZE 2G
COPY YES;

RELATION_ID is a SMALLINT, but it's defined as INTEGER in some other
older tables and is causing some join query issues that I thought may
be resolved with an index expression.

Thanks

Mark.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Vidya Attuluri

Re: Index Expression question
(in response to Mark Stone)
You don't need to cast SMALLINT as INTEGER. Optimizer starting from V8
treats it as TYPE 1 predicate and it is indexable.



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Mark Stone
Sent: Tuesday, January 25, 2011 12:06 PM
To: [login to unmask email]
Subject: [DB2-L] Index Expression question

Does anyone know if the CAST function can be used in an index
expression, I'm getting a -628 (SQL0628N Multiple or conflicting
keywords involving the " " clause are present.) when trying to create
an index with the following:

CREATE UNIQUE INDEX "FDBEL"."XRGMSTC"
ON "FDBEL"."REG_MASTER"
( FAMILY_ID, CAST(RELATION_ID AS INTEGER))
USING STOGROUP "FSGALLP"
PRIQTY 7500 SECQTY 3000 ERASE NO
FREEPAGE 0
PCTFREE 10
GBPCACHE CHANGED
DEFINE YES
COMPRESS NO
CLUSTER
BUFFERPOOL BP11
CLOSE NO
PIECESIZE 2G
COPY YES;

RELATION_ID is a SMALLINT, but it's defined as INTEGER in some other
older tables and is causing some join query issues that I thought may
be resolved with an index expression.

Thanks

Mark.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is
it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Mark Stone

Re: Index Expression question
(in response to Vidya Attuluri)
It turns out the -628 was due to some other clause in the create
statement (I generated it from a beta of Toad, so the jokes on me). I
would normally agree with your conclusion, but I have an explain that
is performing a TBSCAN. I have an index on family_id, relation_id, but
it's not using it and OSC is showing the cast comparison which is why
I'm "experimenting"

On Tue, Jan 25, 2011 at 9:14 AM, Attuluri, Vidya
<[login to unmask email]> wrote:
> You don't need to cast SMALLINT as INTEGER. Optimizer starting from V8
> treats it as TYPE 1 predicate and it is indexable.
>
>
>
> -----Original Message-----
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Mark Stone
> Sent: Tuesday, January 25, 2011 12:06 PM
> To: [login to unmask email]
> Subject: [DB2-L] Index Expression question
>
> Does anyone know if the CAST function can be used in an index
> expression, I'm getting a -628 (SQL0628N  Multiple or conflicting
> keywords involving the " " clause are present.) when trying to create
> an index with the following:
>
> CREATE UNIQUE INDEX "FDBEL"."XRGMSTC"
>  ON "FDBEL"."REG_MASTER"
>    ( FAMILY_ID, CAST(RELATION_ID AS INTEGER))
>  USING STOGROUP "FSGALLP"
>    PRIQTY 7500 SECQTY 3000     ERASE NO
>  FREEPAGE 0
>  PCTFREE 10
>  GBPCACHE CHANGED
>  DEFINE YES
>  COMPRESS NO
>  CLUSTER
>  BUFFERPOOL BP11
>  CLOSE NO
>  PIECESIZE 2G
>  COPY YES;
>
> RELATION_ID is a SMALLINT, but it's defined as INTEGER in some other
> older tables and is causing some join query issues that I thought may
> be resolved with an index expression.
>
> Thanks
>
> Mark.
>
> _____________________________________________________________________
> * IDUG North America * Anaheim, California * May 2-6 2011 *
> http://IDUG.ORG/NA *
> *   If you are going to attend only one conference this year, this is
> it!   *
> _____________________________________________________________________
> http://www.IDUG.org/mentor
> Mentoring should be a rewarding experience for everyone...
> IDUG is offering up to 80% off when you both come to the conference!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
> is the home of IDUG's Listserv
>
> _____________________________________________________________________
> * IDUG North America * Anaheim, California * May 2-6 2011 *  http://IDUG.ORG/NA *
> *   If you are going to attend only one conference this year, this is it!   *
> _____________________________________________________________________
> http://www.IDUG.org/mentor
> Mentoring should be a rewarding experience for everyone...
> IDUG is offering up to 80% off when you both come to the conference!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv
>

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv