SEO

Hello, I need to merge a small table (of rows less than 100,sometimes
even 0 rows) to a big table (of rows around 4 billion). I used the PK
of the big table as merge key but merge does a table scan so it runs
for ever.

I checked the table and PK statistics of the big table and it looks
good. Please let me know if I need to check for something else.

Here are more details
Small table - Non Partitioned ( Node 0)
Big table - Partitioned across 7 logical nodes (Node 1 - 8)

Query:
MERGE INTO CARD.VIN_VEH_OPTNS AS A USING (
SELECT VEH_IDENT_NBR, OPTN_CD, ERR_FLAG
FROM ETL.STG_NEWS_VIN_VEH_OPTNS2
WHERE ERR_FLAG IN ('N', 'Y'))AS B ON (A.VEH_IDENT_NBR =
B.VEH_IDENT_NBR AND A.OPTN_CD =B.OPTN_CD AND
A.VEH_OPTN_CD_CSI =13)
WHEN NOT MATCHED
THEN
INSERT (VEH_IDENT_NBR, OPTN_CD, VEH_OPTN_CD_CSI, OPTN_INSTLT_TYP_CD,
PART_SERIAL_NBR, BROADCAST_CD, SOURCE_SYSTEM_CD, SOURCE_DT,
DWH_EFCTV_TIMSTM, DWH_UPD_TIMSTM)VALUES (B.VEH_IDENT_NBR,
B.OPTN_CD, 13, '', '', '', 34, Current Timestamp, '
2007-02-20 10:39:53', '2007-02-20 10:39:53')
ELSE IGNORE


Reorgchk stats of big table is as below
Table statistics:
SCHEMA NAME CARD OV NP FP ACTBLK
TSIZE F1 F2 F3 REORG
----------------------------------------------------------------------------------------
Table: CARD.VIN_VEH_OPTNS
CARD VIN_VEH_OPTNS 4.6e+09 0 3e+07 3e+07 - 4.96e
+11 0 97 97 ---
----------------------------------------------------------------------------------------

Index statistics:

SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL
KEYS F4 F5 F6 F7 F8 REORG
-------------------------------------------------------------------------------------------------
Table: CARD.VIN_VEH_OPTNS
CARD VVO_IX1 5e+09 7e+06 6916 4 10 13755
1000 100 80 0 0 0 -----
CARD VVO_IX2 5e+09 7e+06 287 4 12 8e+05 1e
+07 100 78 0 0 0 -----
SYSIBM SQL061028160336900 5e+09 6e+07 0 5 27 84 5e
+09 98 68 1 0 0 -----
-------------------------------------------------------------------------------------------------

Indexes on Big table:
CARD VVO_IX1
D 1
+DWH_EFCTV_TIMSTM
CARD VVO_IX2
D 2 +DWH_UPD_TIMSTM
+VEH_OPTN_CD_CSI
SYSIBM SQL061028160336900
P 3 +VEH_IDENT_NBR+OPTN_CD
+VEH_OPTN_CD_CSI

Access Plan:
Optimizer Plan:

INSERT
( 2)
/ \
DTQ Table:
( 3) CARD
| VIN_VEH_OPTNS
TBSCAN
( 4)
|
TEMP
( 5)
|
FILTER
( 6)
|
FILTER
( 7)
|
TBSCAN
( 8)
|
SORT
( 9)
|
NLJOIN
( 10)
/ \
TBSCAN TBSCAN
( 11) ( 12)
| |
Table: TEMP
ETL ( 13)
STG_NEWS_VIN_VEH_OPTNS2 |
BTQ
( 14)
|
TBSCAN
( 15)
|
Table:
CARD
VIN_VEH_OPTNS


Please share your thoughts.

Sam Durai wrote:
> Hello, I need to merge a small table (of rows less than 100,sometimes
> even 0 rows) to a big table (of rows around 4 billion). I used the PK
> of the big table as merge key but merge does a table scan so it runs
> for ever.
>
> I checked the table and PK statistics of the big table and it looks
> good. Please let me know if I need to check for something else.
>
> Here are more details
> Small table - Non Partitioned ( Node 0)
> Big table - Partitioned across 7 logical nodes (Node 1 - 8)
>
> Query:
> MERGE INTO CARD.VIN_VEH_OPTNS AS A USING (
> SELECT VEH_IDENT_NBR, OPTN_CD, ERR_FLAG
> FROM ETL.STG_NEWS_VIN_VEH_OPTNS2
> WHERE ERR_FLAG IN ('N', 'Y'))AS B ON (A.VEH_IDENT_NBR =
> B.VEH_IDENT_NBR AND A.OPTN_CD =B.OPTN_CD AND
> A.VEH_OPTN_CD_CSI =13)
> WHEN NOT MATCHED
> THEN
> INSERT (VEH_IDENT_NBR, OPTN_CD, VEH_OPTN_CD_CSI, OPTN_INSTLT_TYP_CD,
> PART_SERIAL_NBR, BROADCAST_CD, SOURCE_SYSTEM_CD, SOURCE_DT,
> DWH_EFCTV_TIMSTM, DWH_UPD_TIMSTM)VALUES (B.VEH_IDENT_NBR,
> B.OPTN_CD, 13, '', '', '', 34, Current Timestamp, '
> 2007-02-20 10:39:53', '2007-02-20 10:39:53')
> ELSE IGNORE
>
>
> Reorgchk stats of big table is as below
> Table statistics:
> SCHEMA NAME CARD OV NP FP ACTBLK
> TSIZE F1 F2 F3 REORG
> ----------------------------------------------------------------------------------------
> Table: CARD.VIN_VEH_OPTNS
> CARD VIN_VEH_OPTNS 4.6e+09 0 3e+07 3e+07 - 4.96e
> +11 0 97 97 ---
> ----------------------------------------------------------------------------------------
>
> Index statistics:
>
> SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL
> KEYS F4 F5 F6 F7 F8 REORG
> -------------------------------------------------------------------------------------------------
> Table: CARD.VIN_VEH_OPTNS
> CARD VVO_IX1 5e+09 7e+06 6916 4 10 13755
> 1000 100 80 0 0 0 -----
> CARD VVO_IX2 5e+09 7e+06 287 4 12 8e+05 1e
> +07 100 78 0 0 0 -----
> SYSIBM SQL061028160336900 5e+09 6e+07 0 5 27 84 5e
> +09 98 68 1 0 0 -----
> -------------------------------------------------------------------------------------------------
>
> Indexes on Big table:
> CARD VVO_IX1
> D 1
> +DWH_EFCTV_TIMSTM
> CARD VVO_IX2
> D 2 +DWH_UPD_TIMSTM
> +VEH_OPTN_CD_CSI
> SYSIBM SQL061028160336900
> P 3 +VEH_IDENT_NBR+OPTN_CD
> +VEH_OPTN_CD_CSI
>
> Access Plan:
> Optimizer Plan:
>
> INSERT
> ( 2)
> / \
> DTQ Table:
> ( 3) CARD
> | VIN_VEH_OPTNS
> TBSCAN
> ( 4)
> |
> TEMP
> ( 5)
> |
> FILTER
> ( 6)
> |
> FILTER
> ( 7)
> |
> TBSCAN
> ( 8)
> |
> SORT
> ( 9)
> |
> NLJOIN
> ( 10)
> / \
> TBSCAN TBSCAN
> ( 11) ( 12)
> | |
> Table: TEMP
> ETL ( 13)
> STG_NEWS_VIN_VEH_OPTNS2 |
> BTQ
> ( 14)
> |
> TBSCAN
> ( 15)
> |
> Table:
> CARD
> VIN_VEH_OPTNS
>
>
> Please share your thoughts.
>
Do/Can you have dups in the source?
SELECT VEH_IDENT_NBR, OPTN_CD, ERR_FLAG
FROM ETL.STG_NEWS_VIN_VEH_OPTNS2
WHERE ERR_FLAG IN ('N', 'Y'))AS B

assuming you don't have a UNIQUE key on VEH_IDENT_NBR, OPTN_CD
I recommend SELECT VEH_IDENT_NBR, OPTN_CD, MAX(ERR_FLAG)
FROM ETL.STG_NEWS_VIN_VEH_OPTNS2
WHERE ERR_FLAG IN ('N', 'Y')
GROUP BY VEH_IDENT_NBR, OPTN_CD) AS B

Do you have statistics on the small table?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

On Feb 20, 12:44 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> Sam Durai wrote:
> > Hello, I need to merge a small table (of rows less than 100,sometimes
> > even 0 rows) to a big table (of rows around 4 billion). I used the PK
> > of the big table as merge key but merge does a table scan so it runs
> > for ever.
>
> > I checked the table and PK statistics of the big table and it looks
> > good. Please let me know if I need to check for something else.
>
> > Here are more details
> > Small table - Non Partitioned ( Node 0)
> > Big table - Partitioned across 7 logical nodes (Node 1 - 8)
>
> > Query:
> > MERGE INTO CARD.VIN_VEH_OPTNS AS A USING (
> > SELECT VEH_IDENT_NBR, OPTN_CD, ERR_FLAG
> > FROM ETL.STG_NEWS_VIN_VEH_OPTNS2
> > WHERE ERR_FLAG IN ('N', 'Y'))AS B ON (A.VEH_IDENT_NBR =
> > B.VEH_IDENT_NBR AND A.OPTN_CD =B.OPTN_CD AND
> > A.VEH_OPTN_CD_CSI =13)
> > WHEN NOT MATCHED
> > THEN
> > INSERT (VEH_IDENT_NBR, OPTN_CD, VEH_OPTN_CD_CSI, OPTN_INSTLT_TYP_CD,
> > PART_SERIAL_NBR, BROADCAST_CD, SOURCE_SYSTEM_CD, SOURCE_DT,
> > DWH_EFCTV_TIMSTM, DWH_UPD_TIMSTM)VALUES (B.VEH_IDENT_NBR,
> > B.OPTN_CD, 13, '', '', '', 34, Current Timestamp, '
> > 2007-02-20 10:39:53', '2007-02-20 10:39:53')
> > ELSE IGNORE
>
> > Reorgchk stats of big table is as below
> > Table statistics:
> > SCHEMA NAME CARD OV NP FP ACTBLK
> > TSIZE F1 F2 F3 REORG
> > ----------------------------------------------------------------------------------------
> > Table: CARD.VIN_VEH_OPTNS
> > CARD VIN_VEH_OPTNS 4.6e+09 0 3e+07 3e+07 - 4.96e
> > +11 0 97 97 ---
> > ----------------------------------------------------------------------------------------
>
> > Index statistics:
>
> > SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL
> > KEYS F4 F5 F6 F7 F8 REORG
> > -------------------------------------------------------------------------------------------------
> > Table: CARD.VIN_VEH_OPTNS
> > CARD VVO_IX1 5e+09 7e+06 6916 4 10 13755
> > 1000 100 80 0 0 0 -----
> > CARD VVO_IX2 5e+09 7e+06 287 4 12 8e+05 1e
> > +07 100 78 0 0 0 -----
> > SYSIBM SQL061028160336900 5e+09 6e+07 0 5 27 84 5e
> > +09 98 68 1 0 0 -----
> > -------------------------------------------------------------------------------------------------
>
> > Indexes on Big table:
> > CARD VVO_IX1
> > D 1
> > +DWH_EFCTV_TIMSTM
> > CARD VVO_IX2
> > D 2 +DWH_UPD_TIMSTM
> > +VEH_OPTN_CD_CSI
> > SYSIBM SQL061028160336900
> > P 3 +VEH_IDENT_NBR+OPTN_CD
> > +VEH_OPTN_CD_CSI
>
> > Access Plan:
> > Optimizer Plan:
>
> > INSERT
> > ( 2)
> > / \
> > DTQ Table:
> > ( 3) CARD
> > | VIN_VEH_OPTNS
> > TBSCAN
> > ( 4)
> > |
> > TEMP
> > ( 5)
> > |
> > FILTER
> > ( 6)
> > |
> > FILTER
> > ( 7)
> > |
> > TBSCAN
> > ( 8)
> > |
> > SORT
> > ( 9)
> > |
> > NLJOIN
> > ( 10)
> > / \
> > TBSCAN TBSCAN
> > ( 11) ( 12)
> > | |
> > Table: TEMP
> > ETL ( 13)
> > STG_NEWS_VIN_VEH_OPTNS2 |
> > BTQ
> > ( 14)
> > |
> > TBSCAN
> > ( 15)
> > |
> > Table:
> > CARD
> > VIN_VEH_OPTNS
>
> > Please share your thoughts.
>
> Do/Can you have dups in the source?
> SELECT VEH_IDENT_NBR, OPTN_CD, ERR_FLAG
> FROM ETL.STG_NEWS_VIN_VEH_OPTNS2
> WHERE ERR_FLAG IN ('N', 'Y'))AS B
>
> assuming you don't have a UNIQUE key on VEH_IDENT_NBR, OPTN_CD
> I recommend SELECT VEH_IDENT_NBR, OPTN_CD, MAX(ERR_FLAG)
> FROM ETL.STG_NEWS_VIN_VEH_OPTNS2
> WHERE ERR_FLAG IN ('N', 'Y')
> GROUP BY VEH_IDENT_NBR, OPTN_CD) AS B
>
> Do you have statistics on the small table?
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab

Thanks for looking into the issue. We cannot have duplicates on
VEH_IDENT_NBR, OPTN_CD, ERR_FLAG. I do have updated stats on the small
table and most of the time this table would be empty so I'm not sure
whether Unique Index would be of help.

Sam Durai wrote:
> Thanks for looking into the issue. We cannot have duplicates on
> VEH_IDENT_NBR, OPTN_CD, ERR_FLAG. I do have updated stats on the small
> table and most of the time this table would be empty so I'm not sure
> whether Unique Index would be of help.
Not on all three, but that doesn't help. Imporatnt is whether you could
have two (VEH_IDENT_NBR, OPTN_CD) with different ERR_FLAG.
because you join on (VEH_IDENT_NBR, OPTN_CD).
Anyway: try my rewrite and see what it does to the plan. It should
become much simpler. All the goo above the JOIN should go away (and
hopefully in the process remove the tablescan).


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

On Feb 20, 1:18 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> Sam Durai wrote:
> > Thanks for looking into the issue. We cannot have duplicates on
> > VEH_IDENT_NBR, OPTN_CD, ERR_FLAG. I do have updated stats on the small
> > table and most of the time this table would be empty so I'm not sure
> > whether Unique Index would be of help.
>
> Not on all three, but that doesn't help. Imporatnt is whether you could
> have two (VEH_IDENT_NBR, OPTN_CD) with different ERR_FLAG.
> because you join on (VEH_IDENT_NBR, OPTN_CD).
> Anyway: try my rewrite and see what it does to the plan. It should
> become much simpler. All the goo above the JOIN should go away (and
> hopefully in the process remove the tablescan).
>
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab

Here is the latest plan as per modified query.


INSERT
( 2)
/ \
DTQ Table:
( 3) CARD
| VIN_VEH_OPTNS
TBSCAN
( 4)
|
TEMP
( 5)
|
FILTER
( 6)
|
NLJOIN
( 7)
/--/ \--\
GRPBY TBSCAN
( 8) ( 12)
| |
TBSCAN TEMP
( 9) ( 13)
| |
SORT BTQ
( 10) ( 14)
| |
TBSCAN TBSCAN
( 11) ( 15)
| |
Table: Table:
ETL CARD
STG_NEWS_VIN_VEH_OPTNS2 VIN_VEH_OPTNS


Query :
MERGE INTO CARD.VIN_VEH_OPTNS AS A USING (
SELECT VEH_IDENT_NBR, OPTN_CD, MAX(ERR_FLAG)
FROM ETL.STG_NEWS_VIN_VEH_OPTNS2
WHERE ERR_FLAG IN ('N', 'Y')
GROUP BY VEH_IDENT_NBR, OPTN_CD)AS B ON (A.VEH_IDENT_NBR =
B.VEH_IDENT_NBR AND A.OPTN_CD =B.OPTN_CD AND
A.VEH_OPTN_CD_CSI =13)
WHEN NOT MATCHED
THEN
INSERT (VEH_IDENT_NBR, OPTN_CD, VEH_OPTN_CD_CSI, OPTN_INSTLT_TYP_CD,
PART_SERIAL_NBR, BROADCAST_CD, SOURCE_SYSTEM_CD, SOURCE_DT,
DWH_EFCTV_TIMSTM, DWH_UPD_TIMSTM)VALUES (B.VEH_IDENT_NBR,
B.OPTN_CD, 13, '', '', '', 34, Current Timestamp, '
2007-02-20 10:39:53', '2007-02-20 10:39:53')
ELSE IGNORE

The initial query which I posted earlier uses the PK index on other
DPF enabled environment but not on this. Not sure what the reason
eventhough the stat looks good :-(

Hi Sam, what version of DB2 are you using?

Miro

On Feb 20, 8:01 pm, "mirof007" <mirof...@gmail.com> wrote:
> Hi Sam, what version of DB2 are you using?
>
> Miro

It is DB2 UDB V8.1 FP 10
DB21085I Instance "cardp1in" uses "64" bits and DB2 code release
"SQL08023"
with level identifier "03040106".
Informational tokens are "DB2 v8.1.1.96", "s050811", "U803920", and
FixPak
"10".
Product is installed at "/usr/opt/db2_08_01".

As I said it is a logically partitioned db on AIX 5.3 having 8 logical
nodes. The big table is split across 7 partitions while the other
small table is on partition 1.

Is there is something which I need to enable to make the optimizer use
the index.

On Feb 21, 2:07 am, "Sam Durai" <reachsamdu...@gmail.com> wrote:
> Hello, I need to merge a small table (of rows less than 100,sometimes
> even 0 rows) to a big table (of rows around 4 billion). I used the PK
> of the big table as merge key but merge does a table scan so it runs
> for ever.
>
> I checked the table and PK statistics of the big table and it looks
> good. Please let me know if I need to check for something else.
>
> Here are more details
> Small table - Non Partitioned ( Node 0)
> Big table - Partitioned across 7 logical nodes (Node 1 - 8)
>
> Query:
> MERGE INTO CARD.VIN_VEH_OPTNS AS A USING (
> SELECT VEH_IDENT_NBR, OPTN_CD, ERR_FLAG
> FROM ETL.STG_NEWS_VIN_VEH_OPTNS2
> WHERE ERR_FLAG IN ('N', 'Y'))AS B ON (A.VEH_IDENT_NBR =
> B.VEH_IDENT_NBR AND A.OPTN_CD =B.OPTN_CD AND
> A.VEH_OPTN_CD_CSI =13)
> WHEN NOT MATCHED
> THEN
> INSERT (VEH_IDENT_NBR, OPTN_CD, VEH_OPTN_CD_CSI, OPTN_INSTLT_TYP_CD,
> PART_SERIAL_NBR, BROADCAST_CD, SOURCE_SYSTEM_CD, SOURCE_DT,
> DWH_EFCTV_TIMSTM, DWH_UPD_TIMSTM)VALUES (B.VEH_IDENT_NBR,
> B.OPTN_CD, 13, '', '', '', 34, Current Timestamp, '
> 2007-02-20 10:39:53', '2007-02-20 10:39:53')
> ELSE IGNORE
>

I thought that it is worth to try following index.
Because, to check MATCHED or NOT, data of columns A.VEH_IDENT_NBR,
A.OPTN_CD, A.VEH_OPTN_CD_CSI from Table "CARD.VIN_VEH_OPTNS AS A" are
necessary. If there is no appropriate index, DB2 need to see table
itself to acquire these values. Consequentry, tablespace scan may be
inevitable.
CREATE INDEX CARD_IDNBR_CD_CSI ON CARD.VIN_VEH_OPTNS
(VEH_IDENT_NBR, OPTN_CD, VEH_OPTN_CD_CSI)

On Feb 20, 9:32 pm, "Tonkuma" <tonk...@jp.ibm.com> wrote:
> On Feb 21, 2:07 am, "Sam Durai" <reachsamdu...@gmail.com> wrote:
>
>
>
> > Hello, I need to merge a small table (of rows less than 100,sometimes
> > even 0 rows) to a big table (of rows around 4 billion). I used the PK
> > of the big table as merge key but merge does a table scan so it runs
> > for ever.
>
> > I checked the table and PK statistics of the big table and it looks
> > good. Please let me know if I need to check for something else.
>
> > Here are more details
> > Small table - Non Partitioned ( Node 0)
> > Big table - Partitioned across 7 logical nodes (Node 1 - 8)
>
> > Query:
> > MERGE INTO CARD.VIN_VEH_OPTNS AS A USING (
> > SELECT VEH_IDENT_NBR, OPTN_CD, ERR_FLAG
> > FROM ETL.STG_NEWS_VIN_VEH_OPTNS2
> > WHERE ERR_FLAG IN ('N', 'Y'))AS B ON (A.VEH_IDENT_NBR =
> > B.VEH_IDENT_NBR AND A.OPTN_CD =B.OPTN_CD AND
> > A.VEH_OPTN_CD_CSI =13)
> > WHEN NOT MATCHED
> > THEN
> > INSERT (VEH_IDENT_NBR, OPTN_CD, VEH_OPTN_CD_CSI, OPTN_INSTLT_TYP_CD,
> > PART_SERIAL_NBR, BROADCAST_CD, SOURCE_SYSTEM_CD, SOURCE_DT,
> > DWH_EFCTV_TIMSTM, DWH_UPD_TIMSTM)VALUES (B.VEH_IDENT_NBR,
> > B.OPTN_CD, 13, '', '', '', 34, Current Timestamp, '
> > 2007-02-20 10:39:53', '2007-02-20 10:39:53')
> > ELSE IGNORE
>
> I thought that it is worth to try following index.
> Because, to check MATCHED or NOT, data of columns A.VEH_IDENT_NBR,
> A.OPTN_CD, A.VEH_OPTN_CD_CSI from Table "CARD.VIN_VEH_OPTNS AS A" are
> necessary. If there is no appropriate index, DB2 need to see table
> itself to acquire these values. Consequentry, tablespace scan may be
> inevitable.
> CREATE INDEX CARD_IDNBR_CD_CSI ON CARD.VIN_VEH_OPTNS
> (VEH_IDENT_NBR, OPTN_CD, VEH_OPTN_CD_CSI)

Thats the PK in big table.
SYSIBM SQL061028160336900
P 3 +VEH_IDENT_NBR+OPTN_CD
+VEH_OPTN_CD_CSI

Sam Durai wrote:
> On Feb 20, 9:32 pm, "Tonkuma" <tonk...@jp.ibm.com> wrote:
>> On Feb 21, 2:07 am, "Sam Durai" <reachsamdu...@gmail.com> wrote:
>>
>>
>>
>>> Hello, I need to merge a small table (of rows less than 100,sometimes
>>> even 0 rows) to a big table (of rows around 4 billion). I used the PK
>>> of the big table as merge key but merge does a table scan so it runs
>>> for ever.
>>> I checked the table and PK statistics of the big table and it looks
>>> good. Please let me know if I need to check for something else.
>>> Here are more details
>>> Small table - Non Partitioned ( Node 0)
>>> Big table - Partitioned across 7 logical nodes (Node 1 - 8)
>>> Query:
>>> MERGE INTO CARD.VIN_VEH_OPTNS AS A USING (
>>> SELECT VEH_IDENT_NBR, OPTN_CD, ERR_FLAG
>>> FROM ETL.STG_NEWS_VIN_VEH_OPTNS2
>>> WHERE ERR_FLAG IN ('N', 'Y'))AS B ON (A.VEH_IDENT_NBR =
>>> B.VEH_IDENT_NBR AND A.OPTN_CD =B.OPTN_CD AND
>>> A.VEH_OPTN_CD_CSI =13)
>>> WHEN NOT MATCHED
>>> THEN
>>> INSERT (VEH_IDENT_NBR, OPTN_CD, VEH_OPTN_CD_CSI, OPTN_INSTLT_TYP_CD,
>>> PART_SERIAL_NBR, BROADCAST_CD, SOURCE_SYSTEM_CD, SOURCE_DT,
>>> DWH_EFCTV_TIMSTM, DWH_UPD_TIMSTM)VALUES (B.VEH_IDENT_NBR,
>>> B.OPTN_CD, 13, '', '', '', 34, Current Timestamp, '
>>> 2007-02-20 10:39:53', '2007-02-20 10:39:53')
>>> ELSE IGNORE
>> I thought that it is worth to try following index.
>> Because, to check MATCHED or NOT, data of columns A.VEH_IDENT_NBR,
>> A.OPTN_CD, A.VEH_OPTN_CD_CSI from Table "CARD.VIN_VEH_OPTNS AS A" are
>> necessary. If there is no appropriate index, DB2 need to see table
>> itself to acquire these values. Consequentry, tablespace scan may be
>> inevitable.
>> CREATE INDEX CARD_IDNBR_CD_CSI ON CARD.VIN_VEH_OPTNS
>> (VEH_IDENT_NBR, OPTN_CD, VEH_OPTN_CD_CSI)
>
> Thats the PK in big table.
> SYSIBM SQL061028160336900
> P 3 +VEH_IDENT_NBR+OPTN_CD
> +VEH_OPTN_CD_CSI
>
Sam woudl you mind doing an EXPLAIN PLAN followed by db2exfmt rather
than db2expl.
db2exfmt produces has a lot more information. db2expln merely
disassembles the runtime into a plan.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab