INTRA_PARALLEL

[Knut Stolze]

I had a discussion today regarding the INTRA_PARTITION dbm cfg paramater. I
believe that this parameter has only an effect on a single SQL statement to
be executed and it does not control whether multiple db2 agents can run in
parallel on multiple physical CPUs.

The manual does support the first part, i.e. that a query can be spread over
multiple subagents (http://tinyurl.com/mk8ex). But I did not find anything
with which I can prove that the INTRA_PARTITION parameter does not have an
influence on parallel execution of queries from concurrent applications.

Questions:
- Is there some information written down somewhere supporting this?
- Am I wrong with and INTRA_PARTITION does have an influence on concurrent
query execution and the db2agent processes involved in this?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Ian]

Knut Stolze wrote:

I had a discussion today regarding the INTRA_PARTITION dbm cfg paramater. I
believe that this parameter has only an effect on a single SQL statement to
be executed and it does not control whether multiple db2 agents can run in
parallel on multiple physical CPUs.

This is correct. The manual may not say it, but empirically it's simple
to prove - DB2 does asynchronous I/O (prefetching), and the prefetchers,
page cleaners, logger, etc. are all separate EDUs (processes on
UNIX/Linux, threads on Windows). Similarly, execute 2+ queries
simultaneously...

As far as scheduling the agents on the CPU(s), DB2 doesn't make these
decisions - the OS scheduler takes care of this task***. You can watch
CPU utilization during a query and see DB2 using multiple CPUs,
regardless of INTRA_PARALLEL.


*** Note: DB2 does have the ability to restrict itself to a subset of
the CPUs if you are using workload management tools (typically on large
SMP boxes), but this is certainly the exception.

[Knut Stolze]

Ian wrote:

Knut Stolze wrote:

I had a discussion today regarding the INTRA_PARTITION dbm cfg paramater.
I believe that this parameter has only an effect on a single SQL
statement to be executed and it does not control whether multiple db2
agents can run in parallel on multiple physical CPUs.

This is correct. The manual may not say it, but empirically it's simple
to prove - DB2 does asynchronous I/O (prefetching), and the prefetchers,
page cleaners, logger, etc. are all separate EDUs (processes on
UNIX/Linux, threads on Windows). Similarly, execute 2+ queries
simultaneously...

As far as scheduling the agents on the CPU(s), DB2 doesn't make these
decisions - the OS scheduler takes care of this task***. You can watch
CPU utilization during a query and see DB2 using multiple CPUs,
regardless of INTRA_PARALLEL.

*** Note: DB2 does have the ability to restrict itself to a subset of
the CPUs if you are using workload management tools (typically on large
SMP boxes), but this is certainly the exception.

Thanks for the confirmation. Now I just have to get this message across.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany