Can Someone Explain (No Pun Intended) this Explain Plan Output ?

[Richard Winston]

We're running DB2 v7.1 on OS/390. The DBA guys gave us a macro to
generate an explain plan against a query that is stored in a PDS member.
The output for a query in the test environment looks like:

PLAN TABLE NAME / ACCESS / IX N N N N C C C C
NUM INDEX NAME MTHD MATCHCOLS ONLY U J O G U J O G
---- -------------------------- ---- --------- ---- - - - - - - - -
1 AUTHID.BIGTABLE 0 I 2 N N N N N N N N N
AUTHID.INDEX1
2 AUTHID.LOOKUP 1 R N N N N N N N N N

3 3 N N N N N N N N Y



In the prod environment, the same query gets the following explain plan:

PLAN TABLE NAME / ACCESS / IX N N N N C C C C
NUM INDEX NAME MTHD MATCHCOLS ONLY U J O G U J O G
---- -------------------------- ---- --------- ---- - - - - - - - -
1 AUTHID.LOOKUP 0 R N N N N N N N N N

2 AUTHID.BIGTABLE 2 I 2 N N Y N N N Y N N
AUTHID.INDEX1

3 3 N N N N N N N N Y



In prod, the big table has about 100 million rows. In Test, about 25
million.

The lookup table has 48 rows.


I'm wondering whether the change in sequence matters. Also, some of the
flags (NJ, CJ) are different. I have no idea what these flags mean.



Thanks.

[Mark A]

> I'm wondering whether the change in sequence matters. Also, some of the

> flags (NJ, CJ) are different. I have no idea what these flags mean.
> Thanks.
>

Please post the SQL statement.

[Richard Winston]

Mark A wrote:

>>I'm wondering whether the change in sequence matters. Also, some of the
>>flags (NJ, CJ) are different. I have no idea what these flags mean.
>>Thanks.
>>

>
> Please post the SQL statement.
>
>

Mark, I was looking for general information on the plans generated.
Something like "The plan number tells you....the method number tells
you..."


Do you need the query to determine that?

[Mark A]

"Richard Winston" <rwinston@NOPSPAM.ORG> wrote in message
news:gHm9b.4368822$cI2.617556@news.easynews.com...

> Mark A wrote:
>

> >>I'm wondering whether the change in sequence matters. Also, some of the
> >>flags (NJ, CJ) are different. I have no idea what these flags mean.
> >>Thanks.
> >>

> >
> > Please post the SQL statement.
> >
> >

>
>
>
> Mark, I was looking for general information on the plans generated.
> Something like "The plan number tells you....the method number tells
> you..."
>
>
> Do you need the query to determine that?
>

No. But it does take some experience to understand what the manuals are
telling you. However, it is worth learning as much about this as possible.

You can look in the DB2 for OS/390 V7 SQL manual starting on page 752. More
comprehensive explanations may be available in some other manuals also (like
DB2 Application Programming and DB2 System Administration).

The column headings used in your report are abbreviations created by the
person who wrote the query and refer to columns SORTN_JOIN and SORTC_JOIN.
These are extra sorts that DB2 performs to satisfy the query.

The main difference between the 2 explains is that the test environment is
doing a nested loop join (method 1) and the prod is doing a merge scan join
(method 2) and also the extras sorts. Usually nested loop join is faster,
but it depends on the statements and the tables (including table size).

If you reorg all the tables/indexes and execute runstats utility, and rebind
the plans, the explains will probably be the same (unless the data
distribution is wildly different between the 2 environments).

[Richard Winston]

Mark A wrote:

> "Richard Winston" <rwinston@NOPSPAM.ORG> wrote in message
> news:gHm9b.4368822$cI2.617556@news.easynews.com...
>

>>Mark A wrote:
>>
>>

>>>>I'm wondering whether the change in sequence matters. Also, some of the
>>>>flags (NJ, CJ) are different. I have no idea what these flags mean.
>>>>Thanks.
>>>>
>>>
>>>Please post the SQL statement.
>>>
>>>

>>
>>
>>
>>Mark, I was looking for general information on the plans generated.
>>Something like "The plan number tells you....the method number tells
>>you..."
>>
>>
>>Do you need the query to determine that?
>>

>
> No. But it does take some experience to understand what the manuals are
> telling you. However, it is worth learning as much about this as possible.
>
> You can look in the DB2 for OS/390 V7 SQL manual starting on page 752. More
> comprehensive explanations may be available in some other manuals also (like
> DB2 Application Programming and DB2 System Administration).
>
> The column headings used in your report are abbreviations created by the
> person who wrote the query and refer to columns SORTN_JOIN and SORTC_JOIN.
> These are extra sorts that DB2 performs to satisfy the query.
>
> The main difference between the 2 explains is that the test environment is
> doing a nested loop join (method 1) and the prod is doing a merge scan join
> (method 2) and also the extras sorts. Usually nested loop join is faster,
> but it depends on the statements and the tables (including table size).
>
> If you reorg all the tables/indexes and execute runstats utility, and rebind
> the plans, the explains will probably be the same (unless the data
> distribution is wildly different between the 2 environments).
>
>

Thanks. I cracked up the guide and I can see there is a lot of good
stuff in there but you're definitely right...I'll need a lot of
experience before I can begin to understand what is going on. I'll
probably have to take a class somewhere to get a headstart because our
DBA's aren't very helpful when it comes to stuff like this.


The test and prod tables should have the same data distribution since
all we did was load one year from the 4 year prod enviroment. I guess
the other things you mentioned (reorg, runstats) might not be the same
across regions.

This at least gives me a headstart if I encounter different performance
times.



Thanks a lot, Mark. You're been very helpful.