Should system temporary tablespaces generally be on automaticstorage?

Hello,
>
I have recently run a rather large data import where the imported data i
pumped through some updatable views equipped with INSTEAD OF triggers. For
various reasons, the exact same data where imported into the exact same
table/view/trigger structures (but with slightly different tablespace
configuration, see later). The involved hardware was different (one
running on x86_64, one running on PPC), but with comparable CPU, memory
and I/O assignments.
>
The update running on one of the servers was performing extremely bad:
Even though the import was consuming 5 CPU cores, the import ran for more
than a week: On the other server, the import on the other server ran for
less than a day. I couldn't make any sense out of it, until I saw a
difference:
On the fast server, there was a "Buffer pool temporary data logical
reads" / "Buffer pool data logical reads" ratio of 10. On the slow server,
the same ratio was 30.
>
This led me to compare the temporary tablespaces on the two servers. I has
added 8KB system temporary tablespaces on both databases, but it turned
out that I had done it differently. The fast server was using automatic
storage, while the slow server was using non-automatic, database managed
storage. The slow database's temporary tablespace was rather large (25GB),
to I doubt that the size of the tablespace was contributing to the
problem. Actually, when looked at the actual size of the temporary
tablespace on the fast server, the involved files didn't seem to take up
more than a gigabyte of data.
>
I interrupted the import on the slow system, and re-created the 8KB system
temporary tablespace, this time using automatic storage. Now, a subsequent
import ran as fast as on the fast system.
>
How can this be? I always thought that automatic storage would be a bit
_slower_ than DMS tablespaces.
>
Is there any merit to concluding that system temporary storage should
generally reside on automatic storage?
>
>

Hello,
>
I have recently run a rather large data import where the imported data i
pumped through some updatable views equipped with INSTEAD OF triggers. For
various reasons, the exact same data where imported into the exact same
table/view/trigger structures (but with slightly different tablespace
configuration, see later). The involved hardware was different (one
running on x86_64, one running on PPC), but with comparable CPU, memory
and I/O assignments.
>
The update running on one of the servers was performing extremely bad:
Even though the import was consuming 5 CPU cores, the import ran for more
than a week: On the other server, the import on the other server ran for
less than a day. I couldn't make any sense out of it, until I saw a
difference:
On the fast server, there was a "Buffer pool temporary data logical
reads" / "Buffer pool data logical reads" ratio of 10. On the slow server,
the same ratio was 30.

This led me to compare the temporary tablespaces on the two servers. I has
added 8KB system temporary tablespaces on both databases, but it turned
out that I had done it differently. The fast server was using automatic
storage, while the slow server was using non-automatic, database managed
storage. The slow database's temporary tablespace was rather large (25GB),
to I doubt that the size of the tablespace was contributing to the
problem. Actually, when looked at the actual size of the temporary
tablespace on the fast server, the involved files didn't seem to take up
more than a gigabyte of data.

I interrupted the import on the slow system, and re-created the 8KB system
temporary tablespace, this time using automatic storage. Now, a subsequent
import ran as fast as on the fast system.
>
How can this be? I always thought that automatic storage would be a bit
_slower_ than DMS tablespaces.
>
Is there any merit to concluding that system temporary storage should
generally reside on automatic storage?

From my talks with DB2 guys I can say that everything should be
automatic storage on SMS, not DMS.

>
That is definitely interesting, but what about physical data
reads/writes?

I would also dig into *why* you have 3 times more temporary data reads.

If you have the same access path on both servers

Have you compared the average I/O service times for the tablespaces on
both systems? What did you find?

How many containers did you have when the temp tablespace was DMS?

This is certainly sweet music to my DBA ears. But it sort of breaks
"conventional wisdom" which seems to state that DMS-tablespaces give
optimal performance.
>
Does someone (IBM?) have information with performance data from actual,
thorough testing across different workloads/platforms/configurations, so
that the choice doesn't have to rely on ad-hoc wisper and different
versions of conventional wisdom?
>
--
Troels

Ian wrote:

>
So would I. But: 3 times more temporary data reads on the slow server
didn't explain why the slow server was _much_, _much_ slower than the
fast one.

>
I didn't compare access plans. I hope to get a chance to do that at some
point.

>
No. How would you do that?

>
Only one container. But that container was on SAN with many involved
spindles. And as far as I remember, iostat reported of very light I/O.
(But CPU usage was heavy).