Calculating Number of Days with Current Timestamp / Stage 2 predicate

carol goldberg

Calculating Number of Days with Current Timestamp / Stage 2 predicate

I am quite surprised to find that using a Date Function on  a timestamp column is causing a tablespace scan.

DATE(TIME_STAMP) >= CURRENT DATE - 14 DAYS 

I have tried Cast,  I have moved the function to the right side of the . We have had issues with Our BMC utilites putting an function on our indexes , so I am staying away from that.

Does anyone have any suggestions on how to make this a stage 1 / at least an indexable predicate?

Thanks in advance.

 

Carol

 

bernd oppolzer

Calculating Number of Days with Current Timestamp / Stage 2 predicate
(in response to carol goldberg)
IMO, it should work, if you code like this:

TIME_STAMP >= TIMESTAMP (CURRENT DATE - 14 DAYS)

which means the same, IMHO.

(No matter if you omit the TIME from the TIME_STAMP column,
the comparison with CURRENT DATE - 14 DAYS and >= will yield
the same result).

And using the above coding, both sides of the WHERE condition
are TIMESTAMPs. The left side is a simple column reference.

This should IMO be stage 1 since three or four DB2 releases
(just guessing). Before it was not, maybe, because an expression
is on the right side. When inside a program, you could overcome
this by calculating CURRENT DATE - 14 DAYS using programming
language methods and specify a simple host variable.

HTH,
kind regards

Bernd


Am 11.10.2018 um 23:24 schrieb carol goldberg:
>
> I am quite surprised to find that using a Date Function on  a
> timestamp column is causing a tablespace scan.
>
> DATE(TIME_STAMP) >= CURRENT DATE - 14 DAYS
>
> I have tried Cast,  I have moved the function to the right side of the
> . We have had issues with Our BMC utilites putting an function on our
> indexes , so I am staying away from that.
>
> Does anyone have any suggestions on how to make this a stage 1 / at
> least an indexable predicate?
>
> Thanks in advance.
>
> Carol
>
>
> -----End Original Message-----

Greg Palgrave

RE: Calculating Number of Days with Current Timestamp / Stage 2 predicate
(in response to carol goldberg)

Hi Carol,

Assuming the TIME_STAMP column has a suitable index, I would suggest:

TIME_STAMP >= TIMESTAMP_ISO(CURRENT DATE) - 14 DAYS

This is equivalent to your original SQL as you want the whole day, and the timestamp_iso function gives you a start-of-day timestamp e.g. 2018-10-12-00.00.00.000000, with both sides using the same data type.

 

Cheers

Greg

 


In Reply to carol goldberg:

I am quite surprised to find that using a Date Function on  a timestamp column is causing a tablespace scan.

DATE(TIME_STAMP) >= CURRENT DATE - 14 DAYS 

I have tried Cast,  I have moved the function to the right side of the . We have had issues with Our BMC utilites putting an function on our indexes , so I am staying away from that.

Does anyone have any suggestions on how to make this a stage 1 / at least an indexable predicate?

Sam Baugh

Calculating Number of Days with Current Timestamp / Stage 2 predicate
(in response to carol goldberg)
Time_stamp 》trunc_timestamp (current timestamp - 14 days)

On Thu, Oct 11, 2018, 4:24 PM carol goldberg <[login to unmask email]> wrote:

> I am quite surprised to find that using a Date Function on a timestamp
> column is causing a tablespace scan.
>
> DATE(TIME_STAMP) >= CURRENT DATE - 14 DAYS
>
> I have tried Cast, I have moved the function to the right side of the .
> We have had issues with Our BMC utilites putting an function on our indexes
> , so I am staying away from that.
>
> Does anyone have any suggestions on how to make this a stage 1 / at least
> an indexable predicate?
>
> Thanks in advance.
>
>
>
> Carol
>
>
>
> -----End Original Message-----
>

Michael Hannan

RE: Calculating Number of Days with Current Timestamp / Stage 2 predicate
(in response to carol goldberg)

Carol,

I was surprised too. What version of Db2 are you on?

I quote from Db2 z/OS 11 Redbook on performance topics:

"The following predicates on YEAR(col), DATE(col), and SUBSTR(col,1,len) are simplified into
sargable predicates (or stage 1) on the column directly in DB2 11:
YEAR(col) op value
The input column (col) is of DATE, TIMESTAMP, or TIMESTAMP WITH TIME ZONE data
type. The value on the right side of the predicate is an INTEGER.
DATE(col) op value
The input column (col) is of TIMESTAMP data type. TIMESTAMP WITH TIME ZONE data
type is supported in this case. The value on the right side of the predicate is a DATE,
CHAR, or VARCHAR data type.
SUBSTR(col, 1, len) op value
The input column (col) is of CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type.
The data type and CCSID of the value on the right side of the predicate are the same as
that of the column.

The values on the right side of the above predicates can only be constants, variables, or
invariant expressions. Invariant expressions are expressions whose results do not change
during the processing of an SQL statement, as shown in Example 7-3."

So, at first glance, it looks like if you were Db2 zOS 11 or above you would have an index match with no change, REBIND needed. Good to always state Db2 flavour for these questions.

Now we come to a possible catch. The above states the right side should be a Date value or invariant expression, where you have a Date expression. Your expression looks to be Invariant. I think I am going to test it when get a chance, to make sure it works as suggested in the Redbook.

Meanwhile 3 other SQL change solutions all look good to me. 


In Reply to carol goldberg:

I am quite surprised to find that using a Date Function on  a timestamp column is causing a tablespace scan.

DATE(TIME_STAMP) >= CURRENT DATE - 14 DAYS 

I have tried Cast,  I have moved the function to the right side of the . We have had issues with Our BMC utilites putting an function on our indexes , so I am staying away from that.

Does anyone have any suggestions on how to make this a stage 1 / at least an indexable predicate?

Thanks in advance.

 

Carol

 

Michael Hannan,

DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Oct 12, 2018 - 05:52 AM (Europe/Berlin)

Tony Andrews

Calculating Number of Days with Current Timestamp / Stage 2 predicate
(in response to carol goldberg)
Generally speaking, as long as you keep functions or mathematics off a column
directly in the ‘Where’ logic, you should be OK. Putting the function or mathematics
on the other side of the operator is fine, just not directly on the column.

So for some of more common functions on column in predicates in the past that were stage 2, instead of trying to enforce developers to rewrite the predicates, the V11 optimizer is now rewriting it for us.

This is the case for the DATE function on a timestamp column. So for your predicate, if
it is showing as stage 2, you must be on V10. Here is an example of using the DATE
function on a timestamp column in V11, and the rewrite from the optimizer:

Original Predicate: WHERE DATE(HIRE_TSP) > CURRENT DATE – 14 days

[cid:[login to unmask email]

Optimizer rewrites it (transforms it) by putting the expression on the opposite side), ensuring that it is rewritten as a stage 1 predicate.
Optimizer Rewrite (Transformed): Expression oved to the other side as others have posted.

[cid:[login to unmask email]


In Db2 V11 the optimizer will also rewrite predicates with the following functions on columns in the ‘Where’ logic ==> DATE function and SUBSTR function (if starting in position 1). Data Studio does a great job of showing the original queries, and any transformation from the optimizer.

If you are on V11, then the table scan is for other reasons than the predicate being stage 2,
because it gets transformed to stage 1.

BTW: This also gets rewritten by the optimizer in Db2 LUW.

Tony



From: carol goldberg [mailto:[login to unmask email]
Sent: Thursday, October 11, 2018 5:24 PM
To: [login to unmask email]
Subject: [DB2-L] - Calculating Number of Days with Current Timestamp / Stage 2 predicate


I am quite surprised to find that using a Date Function on a timestamp column is causing a tablespace scan.

DATE(TIME_STAMP) >= CURRENT DATE - 14 DAYS

I have tried Cast, I have moved the function to the right side of the . We have had issues with Our BMC utilites putting an function on our indexes , so I am staying away from that.

Does anyone have any suggestions on how to make this a stage 1 / at least an indexable predicate?

Thanks in advance.



Carol



-----End Original Message-----
Attachments

  • image001.png (8.4k)
  • image002.png (7.5k)

carol goldberg

Calculating Number of Days with Current Timestamp / Stage 2 predicate
(in response to Tony Andrews)
We are actually on v12 fl 100 my apologies for not specifing in the
beginning. A big smile Tony for hearing a reply from you. Greetings. We
are actually on v12 fl100, I will post the query rewrite in a bit. I will
say this the cardinality is very low, and I even tried it with using a
similar date column as a virtual index and also got a table space scan. But
I am surprised that date(timestamp) - days is not stage 1.

On Fri, Oct 12, 2018, 08:26 Tony Andrews <[login to unmask email]> wrote:

> Generally speaking, as long as you keep functions or mathematics off a
> column
>
> directly in the ‘Where’ logic, you should be OK. Putting the function or
> mathematics
>
> on the other side of the operator is fine, just not directly on the
> column.
>
>
>
> So for some of more common functions on column in predicates in the past
> that were stage 2, instead of trying to enforce developers to rewrite the
> predicates, the V11 optimizer is now rewriting it for us.
>
>
>
> This is the case for the DATE function on a timestamp column. So for your
> predicate, if
>
> it is showing as stage 2, you must be on V10. Here is an example of
> using the DATE
>
> function on a timestamp column in V11, and the rewrite from the
> optimizer:
>
>
>
> Original Predicate: WHERE DATE(HIRE_TSP) > CURRENT DATE – 14 days
>
>
>
>
>
>
>
> Optimizer rewrites it (transforms it) by putting the expression on the
> opposite side), ensuring that it is rewritten as a stage 1 predicate.
>
> Optimizer Rewrite (Transformed): Expression oved to the other side as
> others have posted.
>
>
>
>
>
>
>
> In Db2 V11 the optimizer will also rewrite predicates with the following
> functions on columns in the ‘Where’ logic è DATE function and SUBSTR
> function (if starting in position 1). Data Studio does a great job of
> showing the original queries, and any transformation from the optimizer.
>
>
>
> If you are on V11, then the table scan is for other reasons than the
> predicate being stage 2,
>
> because it gets transformed to stage 1.
>
>
>
> BTW: This also gets rewritten by the optimizer in Db2 LUW.
>
>
>
> Tony
>
>
>
>
>
>
>
> *From:* carol goldberg [mailto:[login to unmask email]
> *Sent:* Thursday, October 11, 2018 5:24 PM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - Calculating Number of Days with Current Timestamp /
> Stage 2 predicate
>
>
>
> I am quite surprised to find that using a Date Function on a timestamp
> column is causing a tablespace scan.
>
> DATE(TIME_STAMP) >= CURRENT DATE - 14 DAYS
>
> I have tried Cast, I have moved the function to the right side of the .
> We have had issues with Our BMC utilites putting an function on our indexes
> , so I am staying away from that.
>
> Does anyone have any suggestions on how to make this a stage 1 / at least
> an indexable predicate?
>
> Thanks in advance.
>
>
>
> Carol
>
>
>
>
> -----End Original Message-----
>
> -----End Original Message-----
>
Attachments

  • image002.png (7.5k)
  • image001.png (8.4k)

carol goldberg

Calculating Number of Days with Current Timestamp / Stage 2 predicate
(in response to Tony Andrews)
Just like year(date)

On Fri, Oct 12, 2018, 08:26 Tony Andrews <[login to unmask email]> wrote:

> Generally speaking, as long as you keep functions or mathematics off a
> column
>
> directly in the ‘Where’ logic, you should be OK. Putting the function or
> mathematics
>
> on the other side of the operator is fine, just not directly on the
> column.
>
>
>
> So for some of more common functions on column in predicates in the past
> that were stage 2, instead of trying to enforce developers to rewrite the
> predicates, the V11 optimizer is now rewriting it for us.
>
>
>
> This is the case for the DATE function on a timestamp column. So for your
> predicate, if
>
> it is showing as stage 2, you must be on V10. Here is an example of
> using the DATE
>
> function on a timestamp column in V11, and the rewrite from the
> optimizer:
>
>
>
> Original Predicate: WHERE DATE(HIRE_TSP) > CURRENT DATE – 14 days
>
>
>
>
>
>
>
> Optimizer rewrites it (transforms it) by putting the expression on the
> opposite side), ensuring that it is rewritten as a stage 1 predicate.
>
> Optimizer Rewrite (Transformed): Expression oved to the other side as
> others have posted.
>
>
>
>
>
>
>
> In Db2 V11 the optimizer will also rewrite predicates with the following
> functions on columns in the ‘Where’ logic è DATE function and SUBSTR
> function (if starting in position 1). Data Studio does a great job of
> showing the original queries, and any transformation from the optimizer.
>
>
>
> If you are on V11, then the table scan is for other reasons than the
> predicate being stage 2,
>
> because it gets transformed to stage 1.
>
>
>
> BTW: This also gets rewritten by the optimizer in Db2 LUW.
>
>
>
> Tony
>
>
>
>
>
>
>
> *From:* carol goldberg [mailto:[login to unmask email]
> *Sent:* Thursday, October 11, 2018 5:24 PM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - Calculating Number of Days with Current Timestamp /
> Stage 2 predicate
>
>
>
> I am quite surprised to find that using a Date Function on a timestamp
> column is causing a tablespace scan.
>
> DATE(TIME_STAMP) >= CURRENT DATE - 14 DAYS
>
> I have tried Cast, I have moved the function to the right side of the .
> We have had issues with Our BMC utilites putting an function on our indexes
> , so I am staying away from that.
>
> Does anyone have any suggestions on how to make this a stage 1 / at least
> an indexable predicate?
>
> Thanks in advance.
>
>
>
> Carol
>
>
>
>
> -----End Original Message-----
>
> -----End Original Message-----
>
Attachments

  • image001.png (8.4k)

Tony Andrews

Calculating Number of Days with Current Timestamp / Stage 2 predicate
(in response to carol goldberg)
The date(timestamp) is a stage 1 predicate. Take your query to Data Studio to prove it. Your table scan is probably because the
optimizer thinks you are going to return enough rows that will warrant a table scan over an index. Data Studio will provide
a guesstimate of the number of rows to be returned.

Also good to hear from you! Hope all is well in Texas!

Tony

From: Carol Goldberg [mailto:[login to unmask email]
Sent: Friday, October 12, 2018 10:00 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Calculating Number of Days with Current Timestamp / Stage 2 predicate

We are actually on v12 fl 100 my apologies for not specifing in the beginning. A big smile Tony for hearing a reply from you. Greetings. We are actually on v12 fl100, I will post the query rewrite in a bit. I will say this the cardinality is very low, and I even tried it with using a similar date column as a virtual index and also got a table space scan. But I am surprised that date(timestamp) - days is not stage 1.

On Fri, Oct 12, 2018, 08:26 Tony Andrews <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Generally speaking, as long as you keep functions or mathematics off a column
directly in the ‘Where’ logic, you should be OK. Putting the function or mathematics
on the other side of the operator is fine, just not directly on the column.

So for some of more common functions on column in predicates in the past that were stage 2, instead of trying to enforce developers to rewrite the predicates, the V11 optimizer is now rewriting it for us.

This is the case for the DATE function on a timestamp column. So for your predicate, if
it is showing as stage 2, you must be on V10. Here is an example of using the DATE
function on a timestamp column in V11, and the rewrite from the optimizer:

Original Predicate: WHERE DATE(HIRE_TSP) > CURRENT DATE – 14 days

Error! Filename not specified.

Optimizer rewrites it (transforms it) by putting the expression on the opposite side), ensuring that it is rewritten as a stage 1 predicate.
Optimizer Rewrite (Transformed): Expression oved to the other side as others have posted.

Error! Filename not specified.


In Db2 V11 the optimizer will also rewrite predicates with the following functions on columns in the ‘Where’ logic ==> DATE function and SUBSTR function (if starting in position 1). Data Studio does a great job of showing the original queries, and any transformation from the optimizer.

If you are on V11, then the table scan is for other reasons than the predicate being stage 2,
because it gets transformed to stage 1.

BTW: This also gets rewritten by the optimizer in Db2 LUW.

Tony



From: carol goldberg [mailto:[login to unmask email]<mailto:[login to unmask email]>]
Sent: Thursday, October 11, 2018 5:24 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Calculating Number of Days with Current Timestamp / Stage 2 predicate


I am quite surprised to find that using a Date Function on a timestamp column is causing a tablespace scan.

DATE(TIME_STAMP) >= CURRENT DATE - 14 DAYS

I have tried Cast, I have moved the function to the right side of the . We have had issues with Our BMC utilites putting an function on our indexes , so I am staying away from that.

Does anyone have any suggestions on how to make this a stage 1 / at least an indexable predicate?

Thanks in advance.



Carol



-----End Original Message-----

-----End Original Message-----

Phil Grainger

Calculating Number of Days with Current Timestamp / Stage 2 predicate
(in response to Tony Andrews)
So throw an OPTIMIZE FOR <some small number> on the end of the statement?

But remember, if Db2 thinks a tablespace scan is better, don’t always assume this is a mistake – sometimes a tablespace scan IS best!
________________________________

Phil Grainger

Principal Enablement Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[http://media.cms.bmc.com/images/corp_signature_bmclogo_2014.jpg] http://www.bmc.com

[https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]





From: Tony Andrews [mailto:[login to unmask email]
Sent: 12 October 2018 15:15
To: [login to unmask email]
Subject: [DB2-L] - RE: Calculating Number of Days with Current Timestamp / Stage 2 predicate

The date(timestamp) is a stage 1 predicate. Take your query to Data Studio to prove it. Your table scan is probably because the
optimizer thinks you are going to return enough rows that will warrant a table scan over an index. Data Studio will provide
a guesstimate of the number of rows to be returned.

Also good to hear from you! Hope all is well in Texas!

Tony

From: Carol Goldberg [mailto:[login to unmask email]
Sent: Friday, October 12, 2018 10:00 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Calculating Number of Days with Current Timestamp / Stage 2 predicate

We are actually on v12 fl 100 my apologies for not specifing in the beginning. A big smile Tony for hearing a reply from you. Greetings. We are actually on v12 fl100, I will post the query rewrite in a bit. I will say this the cardinality is very low, and I even tried it with using a similar date column as a virtual index and also got a table space scan. But I am surprised that date(timestamp) - days is not stage 1.

On Fri, Oct 12, 2018, 08:26 Tony Andrews <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Generally speaking, as long as you keep functions or mathematics off a column
directly in the ‘Where’ logic, you should be OK. Putting the function or mathematics
on the other side of the operator is fine, just not directly on the column.

So for some of more common functions on column in predicates in the past that were stage 2, instead of trying to enforce developers to rewrite the predicates, the V11 optimizer is now rewriting it for us.

This is the case for the DATE function on a timestamp column. So for your predicate, if
it is showing as stage 2, you must be on V10. Here is an example of using the DATE
function on a timestamp column in V11, and the rewrite from the optimizer:

Original Predicate: WHERE DATE(HIRE_TSP) > CURRENT DATE – 14 days

Error! Filename not specified.

Optimizer rewrites it (transforms it) by putting the expression on the opposite side), ensuring that it is rewritten as a stage 1 predicate.
Optimizer Rewrite (Transformed): Expression oved to the other side as others have posted.

Error! Filename not specified.


In Db2 V11 the optimizer will also rewrite predicates with the following functions on columns in the ‘Where’ logic ==> DATE function and SUBSTR function (if starting in position 1). Data Studio does a great job of showing the original queries, and any transformation from the optimizer.

If you are on V11, then the table scan is for other reasons than the predicate being stage 2,
because it gets transformed to stage 1.

BTW: This also gets rewritten by the optimizer in Db2 LUW.

Tony



From: carol goldberg [mailto:[login to unmask email]<mailto:[login to unmask email]>]
Sent: Thursday, October 11, 2018 5:24 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Calculating Number of Days with Current Timestamp / Stage 2 predicate


I am quite surprised to find that using a Date Function on a timestamp column is causing a tablespace scan.

DATE(TIME_STAMP) >= CURRENT DATE - 14 DAYS

I have tried Cast, I have moved the function to the right side of the . We have had issues with Our BMC utilites putting an function on our indexes , so I am staying away from that.

Does anyone have any suggestions on how to make this a stage 1 / at least an indexable predicate?

Thanks in advance.



Carol



-----End Original Message-----

-----End Original Message-----

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (8k)
  • image002.png (9.3k)

Michael Hannan

RE: Calculating Number of Days with Current Timestamp / Stage 2 predicate
(in response to carol goldberg)

In Reply to carol goldberg:

We are actually on v12 fl 100 my apologies for not specifing in the
beginning. A big smile Tony for hearing a reply from you. Greetings. We
are actually on v12 fl100, I will post the query rewrite in a bit. I will
say this the cardinality is very low, and I even tried it with using a
similar date column as a virtual index and also got a table space scan. But
I am surprised that date(timestamp) - days is not stage 1.

Carol,

What do you mean by cardinality very low? Low for me might be something else. I don't believe it should affect the filter factor calculation too much (but may), as you don't have Hostvars in the predicate, although would have been critical in former times for Static SQL. 

Some critical factors for the access path decision are NPAGESF for the table, DATAREPEATFACTORF and CLUSTERRATIOF for the Index, perhaps HIGH2KEY and LOW2KEY. I don't know the exact Filter factor formula for this predicate. Wasn't long ago that filter factors on range predicates involving CURRENT DATE or CURRENT TIMESTAMP were changed to be a better estimate for  Static SQLs, but perhaps your SQL is Dynamic anyway.

If DATAREPEATFACTORF is very high compared to NPAGESF, this may encourage Db2 to select the TS Scan.

I am expecting the FF estimate to be a reasonable one (much better than FFs with hostvars), so I don't see Db2 choosing a tablespace scan unless the index is poorly clustered, or you are actually selecting quite a large portion of the data rows, or at least the Db2Stats suggest so.

Just to throw a spanner into the works, I tested on SYSAUTORUNS_HIST table STARTTS column (on Db2 zOS V11), with no Catalog Stats populated at all (default Stats).

WHERE DATE(STARTTS) > Current date - 14 days

DB2 showed STAGE1 (DSN_FILTER_TABLE), with FF= 0.3333 (DSN_PREDICAT_TABLE), not a great estimate perhaps (but garbage data in with no Stats), and chose TS Scan. A much lower FF might have used the index. Exactly the same with predicate rewritten to put function on the right side (so that wasn't the answer). Even OPTIMIZE FOR 2 ROWS did not switch  access path away from AT='R' TS Scan. Brute force OPTIMIZE FOR 1 ROWS worked, and suddenly Index Match was chosen. Also the STAGE explain column switched to show 'MATCHING'.

So I guess what the Stats are, matters, and OPTIMIZE FOR 1 ROWS (suggested by Phil) might be the critical thing to be sure of getting an Index match. Making the Table Volatile might also work. 

Interesting problem all up. Sometimes the possible best answer (Phil's OPTIMIZE FOR n ROWS)  is not the most obvious one. Test Explaining various possibilities helps to eliminate false leads. Using the extended Explain tables can be important. The two I mentioned are the most important ones and can be joined together and even joined back to PLAN_TABLE rows.

I always liked to see OPTIMIZE clause (or FETCH FIRST clause) coded on most Cursors, especially in online programs. OPTIMIZE FOR won't do much harm if row estimates are not quite misleading, and can improve access paths in right circumstance.

Data Studio explain options as mentioned by Tony is another option (when have it available). I have my own queries as well that I can use anywhere, and were enhanced over time.

Would be interesting to here what is the performance difference between the TS Scan and the use of the index. The optimizer does not always know best. I am hoping that Db2 AI coming in the future will do better because it can know how many rows are typically fetched, without an OPTIMIZE clause. If your problem was in Static SQL, and too much trouble to alter that program to put in an OPTIMIZE clause and restest, who could probably fix the access path to use the index using SQL Statement Stabilisation specified access path, or using SQL Statement Selectivity Overrides to specify a realistic strong Filter Factor, assuming your index is quite well clustered. If the index is poorly clustered and large data, a DB2 Parallel access path TS Scan with offload to zIIP might be an option.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Oct 14, 2018 - 06:38 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Oct 14, 2018 - 07:19 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Oct 14, 2018 - 07:23 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Oct 14, 2018 - 07:28 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Oct 14, 2018 - 07:30 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Oct 14, 2018 - 07:38 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Oct 14, 2018 - 07:42 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Oct 14, 2018 - 07:47 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Oct 14, 2018 - 07:52 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Oct 14, 2018 - 07:53 AM (Europe/Berlin)