Recently 700,000 rows were INSERTED intra-day (while the db was busy
with servicing queries) into an MDC table organized on 4 dimensions.
Some 30 different cells were hit. The INSERTS were not sorted by MDC
key. The performance was terrible. The Informatica job seemed to hang
for hours and then, when a lull in the query workload appeared, all
the rows were quickly inserted.

I believe buffer contention is the primary cause of the poor
performance and that if the INSERTS were done ordered by MDC key, this
performance could be significantly improved.

Is my assumption correct? Will mass INSERTS perform better if sorted
on the MDC key?

BTW, a test was performed by INSERTing the same rows into a non-MDC
and the performance was fine.

Hmm, this sounds like either contention on certain index pages and/or locking
problems. Which version of DB2 or you using? db2level output, please. I
remember some APARs related to that problem, so you might want to consider
upgrading to the latest fixpak. I know that this is easier said than done, but
this is also a reason why test machines should be considered part of the
production environment... Unfortunately most CFOs see this differently.

Yes, this might be true in some cases, but also depends on the root cause of
your problem. It's definitely worth a shot.

Yes, this is a reasonable assumption, but depends on several factors. The
easiest would be to try it out. I'd say, your performance will be better.
I could try to elaborate which factors would influence insert performance, but
this is rather a theoretical discussion w/o more detailed information on the
environment.

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

On Jul 19, 4:18*am, Helmut Tessarek <tessa... (AT) evermeet (DOT) cx> wrote:
as requested:

DB21085I Instance "v95inst1" uses "64" bits and DB2 code release
"SQL09053"
with level identifier "06040107".
Informational tokens are "DB2 v9.5.0.3", "s081210", "U823474", and Fix
Pack
"3a".
Product is installed at "/opt/IBM/db2/V9.5".

We will endevor to sort the input and monitor execution.

Thanks

Be careful for lock escalations. If users are doing long queries that
lock many rows, you can have lock escalations that lock entire table,
and the inserts will have to wait to the end of the query. If you have
locktimeout to -1, the lock never ends. Ensure in the db2diag.log and
the nfy file (or windows event viewer if you are using windows) that
don't have lock escalations, and if you have it, avoid them using the
locklist and maxlocks parameters.

Best regards,
Ricardo Marcos Alonso

You really should try the latest fixpak. There were some perf related fixes
since FP3a for MDCs...

Although those fixes will only help, if you don't have any locking problems
(as Ricardo pointed out).

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

On Jul 19, 6:50*pm, rmarcosa <rmarc... (AT) gmail (DOT) com> wrote:
I monitored this event via db2top. This problem was not locking
related.

On Jul 20, 12:39*am, Helmut Tessarek <tessa... (AT) evermeet (DOT) cx> wrote:
I'll check out the MDC fixes. Thanks