Oppolzer - Informatik / Blog

There are sites that use Q/A tools on DB2 selects that enforce
that all access paths are done via index, no TS scan allowed. It is not possible
to bring a TS scan program to production level without an exception ticket
from the DB admin people.

That seems pretty strange to me, because especially with batch programs,
TS scan often is the best choice for the overall controlling cursor select.

But I had a situtation one day (at a weekend), when I was forced to bring
a program to production at such a site, and it was friday evening, and I
had such a problem (the program should run at the weekend, to repair
some things in production, and I couldn't get one of the DB people to
get a ticket for the TS scan exception). So I found a solution to avoid the
TS scan and do an index scan instead (you guess: OPTIMIZE FOR 1 ROW etc.).

The result was: the program had to be cancelled after 5 hours ... it was evident
that it would not terminate during the weekend.

I then did the repair at sunday morning using simple SQL statements,
with the help of one of the DB people, who was called in from the weekend.

This only needed 5 minutes (TS scan).

Maybe the OP faces a similar situation (strange Q/A tool) and for that he
tries to avoid the TS scan.

Kind regards

Bernd




Am 03.01.2015 um 02:01 schrieb J.G.:
>
> Tim,
>
> You may have a different understanding of what "resolved" means than I do.
>
> 1) Does the PAYMENT_TRAN table have only 3 values for CLIENT_NO, and does the program want all 3?
>
> 2) Is this access path against the production database or against your small test table?
>
> 3) (and most important),  by resolved, do you mean:
>   a) You got an access path that uses the index?
>   b) The query ran faster against the production database?
>
> The goal is not to get the access path you want; it is to get the query to run faster.
> Part of that process is to get an access path you think is better, but you have to
> actually run it and see if it is really better (or in other words, who is smarter -
> you or the Optimizer).
>
> If the answer to question 3 was b) that it did run faster in production, then yes -
> it is resolved.  But if the answer is a), then all you have proved is that there is
> a way to get the access path you wanted.  That is good, but not good enough.
>
> If the answer is a), and the answer to question 1 is yes, it is getting all 3 CLIENT_NOs,
> then I would question the statistics on the table.  If the stats show that there are
> 3 values for CLIENT_NO and that the index is clustered, I do not see why DB2 would
> pick to use the index instead of a tablespace scan.  The index might be of value
> if the table were not clustered by CLIENT_NO,  but in an earlier post you said
> the index was clustered.  If on the other hand, there are really many more vlaues
> of CLIENT_NO, but the program is not interested in the others, then you are comparing
> apples to oranges (or to pears in Europe) because the new query is not the same
> as the one with the problem.
>
> Joe
>