Prev: Much RAM or fast RAM?
Next: Stored Procedure performance issue - PLEASE HELP!
From: oddbande on 29 Jan 2010 09:01
I am new to using table partitioning, but now we want to use it on a
couple of tables. But when trying this we have experienced something I
consider strange, when db2 doesn't act as i would expect.

The environment is DB2 9.7 (fixpack 1) on Linux for z/OS (64-bits).

We have created a table like this:
CREATE TABLE "MLTEST01"."HENDELSE_I_FORSTE1" (
"KOLLI" VARCHAR(35) NOT NULL ,
"I_FORSTE_TIDSPUNKT" TIMESTAMP NOT NULL ,
"I_FORSTE_ENHET" VARCHAR(6) NOT NULL ,
"I_FORSTE_KODESELSKAPHEND" VARCHAR(3) ,
"I_FORSTE_POSTREF" VARCHAR(35) ,
"I_FORSTE_IDENTMVU" VARCHAR(10) ,
"I_FORSTE_KODELANDHEND" CHAR(2) ,
"I_FORSTE_POSTNR" VARCHAR(10) ,
"I_FORSTE_STATUSEMS" CHAR(1) ,
"I_FORSTE_STATUS" CHAR(1) ,
"I_FORSTE_KODEARSAKLM" VARCHAR(3) ,
"I_FORSTE_KODETILTAKLM" VARCHAR(3) ,
"I_FORSTE_SIGNMOTTAKER" VARCHAR(28)
)
IN "TSJAHEND" INDEX IN "IXJAHEND"
PARTITION BY RANGE (I_FORSTE_TIDSPUNKT)
( PARTITION UKE532009 STARTING ('2009-12-28-00.00.00.000000')
INCLUSIVE,
PARTITION UKE012010 STARTING ('2010-01-04-00.00.00.000000')
INCLUSIVE,
PARTITION UKE022010 STARTING ('2010-01-11-00.00.00.000000')
INCLUSIVE,
PARTITION UKE032010 STARTING ('2010-01-18-00.00.00.000000')
INCLUSIVE,
PARTITION UKE042010 STARTING ('2010-01-25-00.00.00.000000')
ENDING ('2010-02-01-00.00.00.000000') EXCLUSIVE
)
;

And one index:
CREATE INDEX "MLTEST01"."HENDELSE_I_FORSTE__TIDSPUNKT" ON
"MLTEST01"."HENDELSE_I_FORSTE1"
("I_FORSTE_TIDSPUNKT" ASC,
"KOLLI" ASC)
CLUSTER
PCTFREE 10 MINPCTUSED 10
COMPRESS NO ALLOW REVERSE SCANS;


We have tried to run db2expln on one statement and db2 doesn't seem to
be able to eliminate partitions from the scan if the sql statement is
like this:
select * from mltest01.hendelse_i_forste1 where i_forste_tidspunkt
>= current timestamp - 2 days

Then it says in the explain output:
List Prefetch Preparation
| Access Table Name = MLTEST01.HENDELSE_I_FORSTE1 ID = -6,-32767
| | #Columns = 13
| | Data-Partitioned Table
| | Skip Inserted Rows
| | Avoid Locking Committed Data
| | Currently Committed for Cursor Stability
| | RID List Fetch Scan
| | All data partitions will be accessed

Seems that there is no partitions excluded from the scan.

But if the search predicate is hard coded like this:
select * from mltest01.hendelse_i_forste1 where i_forste_tidspunkt
>= '2010-01-27-14.30.00.000000'

then the explain output says:
| Data Partition Elimination Info:
| | Range 1:
| | | #Key Columns = 1
| | | | Start Key: Inclusive Value
| | | | | 1: 2010-01-27 14:30:00.000000
| | | | Stop Key: End of Data Partition Range
| Active Data Partitions: 4

Then db2 only scans partition number 4 where the data we want is
located.

Why is it so? Is it not possible to use a computed search predicate as
we did (current timestamp - n days)? We gets this predicate from an
application - will it work as expected if the application performs the
computing and only sends in the computed value as a parameter?

Regards
Odd Bjørn Andersen
ErgoGroup AS
Oslo, Norway


From: whatever on 29 Jan 2010 16:28
On Jan 29, 9:01 am, oddbande <odd.ander...(a)ergo.no> wrote:
> I am new to using table partitioning, but now we want to use it on a
> couple of tables. But when trying this we have experienced something I
> consider strange, when db2 doesn't act as i would expect.
>
> The environment is DB2 9.7 (fixpack 1) on Linux for z/OS (64-bits).
>
> We have created a table like this:
> CREATE TABLE "MLTEST01"."HENDELSE_I_FORSTE1"  (
>                   "KOLLI" VARCHAR(35) NOT NULL ,
>                   "I_FORSTE_TIDSPUNKT" TIMESTAMP NOT NULL ,
>                   "I_FORSTE_ENHET" VARCHAR(6) NOT NULL ,
>                   "I_FORSTE_KODESELSKAPHEND" VARCHAR(3) ,
>                   "I_FORSTE_POSTREF" VARCHAR(35) ,
>                   "I_FORSTE_IDENTMVU" VARCHAR(10) ,
>                   "I_FORSTE_KODELANDHEND" CHAR(2) ,
>                   "I_FORSTE_POSTNR" VARCHAR(10) ,
>                   "I_FORSTE_STATUSEMS" CHAR(1) ,
>                   "I_FORSTE_STATUS" CHAR(1) ,
>                   "I_FORSTE_KODEARSAKLM" VARCHAR(3) ,
>                   "I_FORSTE_KODETILTAKLM" VARCHAR(3) ,
>                   "I_FORSTE_SIGNMOTTAKER" VARCHAR(28)
>                    )
>                  IN "TSJAHEND" INDEX IN "IXJAHEND"
>                  PARTITION BY RANGE (I_FORSTE_TIDSPUNKT)
>                    ( PARTITION UKE532009 STARTING ('2009-12-28-00.00.00.000000')
> INCLUSIVE,
>                      PARTITION UKE012010 STARTING ('2010-01-04-00.00.00.000000')
> INCLUSIVE,
>                      PARTITION UKE022010 STARTING ('2010-01-11-00.00.00.000000')
> INCLUSIVE,
>                      PARTITION UKE032010 STARTING ('2010-01-18-00.00.00.000000')
> INCLUSIVE,
>                      PARTITION UKE042010 STARTING ('2010-01-25-00.00.00.000000')
>                      ENDING ('2010-02-01-00.00.00.000000') EXCLUSIVE
>                    )
>                    ;
>
> And one index:
> CREATE INDEX "MLTEST01"."HENDELSE_I_FORSTE__TIDSPUNKT" ON
> "MLTEST01"."HENDELSE_I_FORSTE1"
>                 ("I_FORSTE_TIDSPUNKT" ASC,
>                  "KOLLI" ASC)
>                 CLUSTER
>                 PCTFREE 10 MINPCTUSED 10
>                 COMPRESS NO ALLOW REVERSE SCANS;
>
> We have tried to run db2expln on one statement and db2 doesn't seem to
> be able to eliminate partitions from the scan if the sql statement is
> like this:
> select * from mltest01.hendelse_i_forste1  where i_forste_tidspunkt
>
> >= current timestamp - 2 days
>
> Then it says in the explain output:
> List Prefetch Preparation
> |  Access Table Name = MLTEST01.HENDELSE_I_FORSTE1  ID = -6,-32767
> |  |  #Columns = 13
> |  |  Data-Partitioned Table
> |  |  Skip Inserted Rows
> |  |  Avoid Locking Committed Data
> |  |  Currently Committed for Cursor Stability
> |  |  RID List Fetch Scan
> |  |  All data partitions will be accessed
>
> Seems that there is no partitions excluded from the scan.
>
> But if the search predicate is hard coded like this:
> select * from mltest01.hendelse_i_forste1  where i_forste_tidspunkt
>
> >= '2010-01-27-14.30.00.000000'
>
> then the explain output says:
> |  Data Partition Elimination Info:
> |  |  Range 1:
> |  |  |  #Key Columns = 1
> |  |  |  |  Start Key: Inclusive Value
> |  |  |  |  |  1: 2010-01-27 14:30:00.000000
> |  |  |  |  Stop Key: End of Data Partition Range
> |  Active Data Partitions: 4
>
> Then db2 only scans partition number 4 where the data we want is
> located.
>
> Why is it so? Is it not possible to use a computed search predicate as
> we did (current timestamp - n days)? We gets this predicate from an
> application - will it work as expected if the application performs the
> computing and only sends in the computed value as a parameter?
>
> Regards
> Odd Bjørn Andersen
> ErgoGroup AS
> Oslo, Norway

Do u observer the actual Performance Problem or only db2expln is
reporting this ? Why i am asking is.... there were
some open APAR's with older versions of DB2 the db2expln is reporting
this funny stuff. That could be very well with V9.7 also.
Can u run db2exfmt in both cases and see the REAL Plan....

Cheers...
Shashi Mannepalli
From: whatever on 29 Jan 2010 16:32
On Jan 29, 4:28 pm, whatever <audheya2...(a)gmail.com> wrote:
> On Jan 29, 9:01 am, oddbande <odd.ander...(a)ergo.no> wrote:
>
>
>
>
>
> > I am new to using table partitioning, but now we want to use it on a
> > couple of tables. But when trying this we have experienced something I
> > consider strange, when db2 doesn't act as i would expect.
>
> > The environment is DB2 9.7 (fixpack 1) on Linux for z/OS (64-bits).
>
> > We have created a table like this:
> > CREATE TABLE "MLTEST01"."HENDELSE_I_FORSTE1"  (
> >                   "KOLLI" VARCHAR(35) NOT NULL ,
> >                   "I_FORSTE_TIDSPUNKT" TIMESTAMP NOT NULL ,
> >                   "I_FORSTE_ENHET" VARCHAR(6) NOT NULL ,
> >                   "I_FORSTE_KODESELSKAPHEND" VARCHAR(3) ,
> >                   "I_FORSTE_POSTREF" VARCHAR(35) ,
> >                   "I_FORSTE_IDENTMVU" VARCHAR(10) ,
> >                   "I_FORSTE_KODELANDHEND" CHAR(2) ,
> >                   "I_FORSTE_POSTNR" VARCHAR(10) ,
> >                   "I_FORSTE_STATUSEMS" CHAR(1) ,
> >                   "I_FORSTE_STATUS" CHAR(1) ,
> >                   "I_FORSTE_KODEARSAKLM" VARCHAR(3) ,
> >                   "I_FORSTE_KODETILTAKLM" VARCHAR(3) ,
> >                   "I_FORSTE_SIGNMOTTAKER" VARCHAR(28)
> >                    )
> >                  IN "TSJAHEND" INDEX IN "IXJAHEND"
> >                  PARTITION BY RANGE (I_FORSTE_TIDSPUNKT)
> >                    ( PARTITION UKE532009 STARTING ('2009-12-28-00.00.00.000000')
> > INCLUSIVE,
> >                      PARTITION UKE012010 STARTING ('2010-01-04-00.00.00.000000')
> > INCLUSIVE,
> >                      PARTITION UKE022010 STARTING ('2010-01-11-00.00.00.000000')
> > INCLUSIVE,
> >                      PARTITION UKE032010 STARTING ('2010-01-18-00.00.00.000000')
> > INCLUSIVE,
> >                      PARTITION UKE042010 STARTING ('2010-01-25-00.00.00.000000')
> >                      ENDING ('2010-02-01-00.00.00..000000') EXCLUSIVE
> >                    )
> >                    ;
>
> > And one index:
> > CREATE INDEX "MLTEST01"."HENDELSE_I_FORSTE__TIDSPUNKT" ON
> > "MLTEST01"."HENDELSE_I_FORSTE1"
> >                 ("I_FORSTE_TIDSPUNKT" ASC,
> >                  "KOLLI" ASC)
> >                 CLUSTER
> >                 PCTFREE 10 MINPCTUSED 10
> >                 COMPRESS NO ALLOW REVERSE SCANS;
>
> > We have tried to run db2expln on one statement and db2 doesn't seem to
> > be able to eliminate partitions from the scan if the sql statement is
> > like this:
> > select * from mltest01.hendelse_i_forste1  where i_forste_tidspunkt
>
> > >= current timestamp - 2 days
>
> > Then it says in the explain output:
> > List Prefetch Preparation
> > |  Access Table Name = MLTEST01.HENDELSE_I_FORSTE1  ID = -6,-32767
> > |  |  #Columns = 13
> > |  |  Data-Partitioned Table
> > |  |  Skip Inserted Rows
> > |  |  Avoid Locking Committed Data
> > |  |  Currently Committed for Cursor Stability
> > |  |  RID List Fetch Scan
> > |  |  All data partitions will be accessed
>
> > Seems that there is no partitions excluded from the scan.
>
> > But if the search predicate is hard coded like this:
> > select * from mltest01.hendelse_i_forste1  where i_forste_tidspunkt
>
> > >= '2010-01-27-14.30.00.000000'
>
> > then the explain output says:
> > |  Data Partition Elimination Info:
> > |  |  Range 1:
> > |  |  |  #Key Columns = 1
> > |  |  |  |  Start Key: Inclusive Value
> > |  |  |  |  |  1: 2010-01-27 14:30:00.000000
> > |  |  |  |  Stop Key: End of Data Partition Range
> > |  Active Data Partitions: 4
>
> > Then db2 only scans partition number 4 where the data we want is
> > located.
>
> > Why is it so? Is it not possible to use a computed search predicate as
> > we did (current timestamp - n days)? We gets this predicate from an
> > application - will it work as expected if the application performs the
> > computing and only sends in the computed value as a parameter?
>
> > Regards
> > Odd Bjørn Andersen
> > ErgoGroup AS
> > Oslo, Norway
>
> Do u observer the actual Performance Problem or only db2expln is
> reporting this ? Why i am asking is.... there were
> some open APAR's with older versions of DB2 the db2expln is reporting
> this funny stuff. That could be very well with V9.7 also.
> Can u run db2exfmt in both cases and see the REAL Plan....
>
> Cheers...
> Shashi Mannepalli- Hide quoted text -
>
> - Show quoted text -

BTW...I am refrerring this APAR

IZ00009

Cheers...
Shashi Mannepalli
From: oddbande on 31 Jan 2010 09:34
On Jan 29, 4:32 pm, whatever <audheya2...(a)gmail.com> wrote:
> On Jan 29, 4:28 pm, whatever <audheya2...(a)gmail.com> wrote:
>
>
>
>
>
> > On Jan 29, 9:01 am, oddbande <odd.ander...(a)ergo.no> wrote:
>
> > > I am new to using table partitioning, but now we want to use it on a
> > > couple of tables. But when trying this we have experienced something I
> > > consider strange, when db2 doesn't act as i would expect.
>
> > > The environment is DB2 9.7 (fixpack 1) on Linux for z/OS (64-bits).
>
> > > We have created a table like this:
> > > CREATE TABLE "MLTEST01"."HENDELSE_I_FORSTE1"  (
> > >                   "KOLLI" VARCHAR(35) NOT NULL ,
> > >                   "I_FORSTE_TIDSPUNKT" TIMESTAMP NOT NULL ,
> > >                   "I_FORSTE_ENHET" VARCHAR(6) NOT NULL ,
> > >                   "I_FORSTE_KODESELSKAPHEND" VARCHAR(3) ,
> > >                   "I_FORSTE_POSTREF" VARCHAR(35) ,
> > >                   "I_FORSTE_IDENTMVU" VARCHAR(10) ,
> > >                   "I_FORSTE_KODELANDHEND" CHAR(2) ,
> > >                   "I_FORSTE_POSTNR" VARCHAR(10) ,
> > >                   "I_FORSTE_STATUSEMS" CHAR(1) ,
> > >                   "I_FORSTE_STATUS" CHAR(1) ,
> > >                   "I_FORSTE_KODEARSAKLM" VARCHAR(3) ,
> > >                   "I_FORSTE_KODETILTAKLM" VARCHAR(3) ,
> > >                   "I_FORSTE_SIGNMOTTAKER" VARCHAR(28)
> > >                    )
> > >                  IN "TSJAHEND" INDEX IN "IXJAHEND"
> > >                  PARTITION BY RANGE (I_FORSTE_TIDSPUNKT)
> > >                    ( PARTITION UKE532009 STARTING ('2009-12-28-00.00.00.000000')
> > > INCLUSIVE,
> > >                      PARTITION UKE012010 STARTING ('2010-01-04-00.00.00.000000')
> > > INCLUSIVE,
> > >                      PARTITION UKE022010 STARTING ('2010-01-11-00.00.00.000000')
> > > INCLUSIVE,
> > >                      PARTITION UKE032010 STARTING ('2010-01-18-00.00.00.000000')
> > > INCLUSIVE,
> > >                      PARTITION UKE042010 STARTING ('2010-01-25-00.00.00.000000')
> > >                      ENDING ('2010-02-01-00.00.00.000000') EXCLUSIVE
> > >                    )
> > >                    ;
>
> > > And one index:
> > > CREATE INDEX "MLTEST01"."HENDELSE_I_FORSTE__TIDSPUNKT" ON
> > > "MLTEST01"."HENDELSE_I_FORSTE1"
> > >                 ("I_FORSTE_TIDSPUNKT" ASC,
> > >                  "KOLLI" ASC)
> > >                 CLUSTER
> > >                 PCTFREE 10 MINPCTUSED 10
> > >                 COMPRESS NO ALLOW REVERSE SCANS;
>
> > > We have tried to run db2expln on one statement and db2 doesn't seem to
> > > be able to eliminate partitions from the scan if the sql statement is
> > > like this:
> > > select * from mltest01.hendelse_i_forste1  where i_forste_tidspunkt
>
> > > >= current timestamp - 2 days
>
> > > Then it says in the explain output:
> > > List Prefetch Preparation
> > > |  Access Table Name = MLTEST01.HENDELSE_I_FORSTE1  ID = -6,-32767
> > > |  |  #Columns = 13
> > > |  |  Data-Partitioned Table
> > > |  |  Skip Inserted Rows
> > > |  |  Avoid Locking Committed Data
> > > |  |  Currently Committed for Cursor Stability
> > > |  |  RID List Fetch Scan
> > > |  |  All data partitions will be accessed
>
> > > Seems that there is no partitions excluded from the scan.
>
> > > But if the search predicate is hard coded like this:
> > > select * from mltest01.hendelse_i_forste1  where i_forste_tidspunkt
>
> > > >= '2010-01-27-14.30.00.000000'
>
> > > then the explain output says:
> > > |  Data Partition Elimination Info:
> > > |  |  Range 1:
> > > |  |  |  #Key Columns = 1
> > > |  |  |  |  Start Key: Inclusive Value
> > > |  |  |  |  |  1: 2010-01-27 14:30:00.000000
> > > |  |  |  |  Stop Key: End of Data Partition Range
> > > |  Active Data Partitions: 4
>
> > > Then db2 only scans partition number 4 where the data we want is
> > > located.
>
> > > Why is it so? Is it not possible to use a computed search predicate as
> > > we did (current timestamp - n days)? We gets this predicate from an
> > > application - will it work as expected if the application performs the
> > > computing and only sends in the computed value as a parameter?
>
> > > Regards
> > > Odd Bjørn Andersen
> > > ErgoGroup AS
> > > Oslo, Norway
>
> > Do u observer the actual Performance Problem or only db2expln is
> > reporting this ? Why i am asking is.... there were
> > some open APAR's with older versions of DB2 the db2expln is reporting
> > this funny stuff. That could be very well with V9.7 also.
> > Can u run db2exfmt in both cases and see the REAL Plan....
>
> > Cheers...
> > Shashi Mannepalli- Hide quoted text -
>
> > - Show quoted text -
>
> BTW...I am refrerring this APAR
>
> IZ00009
>
> Cheers...
> Shashi Mannepalli- Hide quoted text -
>
> - Show quoted text -

This is what the explain output showed. But when I run db2exfmt it
also shows that all 5 patitions are acessed.

5) IXSCAN: (Index Scan)
Cumulative Total Cost: 222.219
Cumulative CPU Cost: 1.26534e+07
Cumulative I/O Cost: 28.8473
Cumulative Re-Total Cost: 0.0234757
Cumulative Re-CPU Cost: 71855.9
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 15.1324
Estimated Bufferpool Buffers: 33.8473

Arguments:
---------
CUR_COMM: (Currently Committed)
TRUE
DPESTFLG: (Number of data partitions accessed are Estimated)
TRUE
DPNUMPRT: (Number of data partitions accessed)
5
GLOBLOCK: (Global Lock intent)
INTENT NONE
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
23
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NONE
SCANDIR : (Scan Direction)
FORWARD
SKIP_INS: (Skip Inserted Rows)
TRUE
TABLOCK : (Table Lock intent)
INTENT NONE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
UR_EXTRA: (UR plus extra table lock and may upgrade to CS)
TRUE

Or could it be that I misunderstand the output?

Regards
Odd Bjørn
From: Naresh Chainani on 31 Jan 2010 12:43
On Jan 29, 6:01 am, oddbande <odd.ander...(a)ergo.no> wrote:
> I am new to using table partitioning, but now we want to use it on a
> couple of tables. But when trying this we have experienced something I
> consider strange, when db2 doesn't act as i would expect.
>
> The environment is DB2 9.7 (fixpack 1) on Linux for z/OS (64-bits).
>
> We have created a table like this:
> CREATE TABLE "MLTEST01"."HENDELSE_I_FORSTE1"  (
>                   "KOLLI" VARCHAR(35) NOT NULL ,
>                   "I_FORSTE_TIDSPUNKT" TIMESTAMP NOT NULL ,
>                   "I_FORSTE_ENHET" VARCHAR(6) NOT NULL ,
>                   "I_FORSTE_KODESELSKAPHEND" VARCHAR(3) ,
>                   "I_FORSTE_POSTREF" VARCHAR(35) ,
>                   "I_FORSTE_IDENTMVU" VARCHAR(10) ,
>                   "I_FORSTE_KODELANDHEND" CHAR(2) ,
>                   "I_FORSTE_POSTNR" VARCHAR(10) ,
>                   "I_FORSTE_STATUSEMS" CHAR(1) ,
>                   "I_FORSTE_STATUS" CHAR(1) ,
>                   "I_FORSTE_KODEARSAKLM" VARCHAR(3) ,
>                   "I_FORSTE_KODETILTAKLM" VARCHAR(3) ,
>                   "I_FORSTE_SIGNMOTTAKER" VARCHAR(28)
>                    )
>                  IN "TSJAHEND" INDEX IN "IXJAHEND"
>                  PARTITION BY RANGE (I_FORSTE_TIDSPUNKT)
>                    ( PARTITION UKE532009 STARTING ('2009-12-28-00.00.00.000000')
> INCLUSIVE,
>                      PARTITION UKE012010 STARTING ('2010-01-04-00.00.00.000000')
> INCLUSIVE,
>                      PARTITION UKE022010 STARTING ('2010-01-11-00.00.00.000000')
> INCLUSIVE,
>                      PARTITION UKE032010 STARTING ('2010-01-18-00.00.00.000000')
> INCLUSIVE,
>                      PARTITION UKE042010 STARTING ('2010-01-25-00.00.00.000000')
>                      ENDING ('2010-02-01-00.00.00.000000') EXCLUSIVE
>                    )
>                    ;
>
> And one index:
> CREATE INDEX "MLTEST01"."HENDELSE_I_FORSTE__TIDSPUNKT" ON
> "MLTEST01"."HENDELSE_I_FORSTE1"
>                 ("I_FORSTE_TIDSPUNKT" ASC,
>                  "KOLLI" ASC)
>                 CLUSTER
>                 PCTFREE 10 MINPCTUSED 10
>                 COMPRESS NO ALLOW REVERSE SCANS;
>
> We have tried to run db2expln on one statement and db2 doesn't seem to
> be able to eliminate partitions from the scan if the sql statement is
> like this:
> select * from mltest01.hendelse_i_forste1  where i_forste_tidspunkt
>
> >= current timestamp - 2 days
>
> Then it says in the explain output:
> List Prefetch Preparation
> |  Access Table Name = MLTEST01.HENDELSE_I_FORSTE1  ID = -6,-32767
> |  |  #Columns = 13
> |  |  Data-Partitioned Table
> |  |  Skip Inserted Rows
> |  |  Avoid Locking Committed Data
> |  |  Currently Committed for Cursor Stability
> |  |  RID List Fetch Scan
> |  |  All data partitions will be accessed
>
> Seems that there is no partitions excluded from the scan.
>
> But if the search predicate is hard coded like this:
> select * from mltest01.hendelse_i_forste1  where i_forste_tidspunkt
>
> >= '2010-01-27-14.30.00.000000'
>
> then the explain output says:
> |  Data Partition Elimination Info:
> |  |  Range 1:
> |  |  |  #Key Columns = 1
> |  |  |  |  Start Key: Inclusive Value
> |  |  |  |  |  1: 2010-01-27 14:30:00.000000
> |  |  |  |  Stop Key: End of Data Partition Range
> |  Active Data Partitions: 4
>
> Then db2 only scans partition number 4 where the data we want is
> located.
>
> Why is it so? Is it not possible to use a computed search predicate as
> we did (current timestamp - n days)? We gets this predicate from an
> application - will it work as expected if the application performs the
> computing and only sends in the computed value as a parameter?
>
> Regards
> Odd Bjørn Andersen
> ErgoGroup AS
> Oslo, Norway

db2expln reports on any partition elimination that was done at compile
time. In the hard coded predicate case, you saw exactly which
partitions will be accessed since the partition elimination was done
at compile time.

In the CURRENT TIMESTAMP predicate, the value of this special register
is obtained at execution time and thus DB2 Compiler has no idea what
the expression is going to resolve to. Only during runtime, when the
expression is computed, DB2 does partition elimination at runtime.
Other cases where runtime partition elimination applies is host
variables or parameter markers or look-up of inner table of a nested
loop join.

Naresh
 |  Next  |  Last
Pages: 1 2
Prev: Much RAM or fast RAM?
Next: Stored Procedure performance issue - PLEASE HELP!