0

we are right now developing a system which runs on websphere z/OS 8.5 with a db2 z/OS 10. At the moment we face the following performance problem:

We need to copy records from one table to another. This copying should finish in about 15 minutes for more than 30mio entries.

What are we having right now: - both tables are partitioned (same partitioning column x) - copy batch in java by using websphere batch. This batch job is operating in parallel, each parallel sub job is executing exactly one insert/select on one partition (copying about 3-5 mio entries). From a data point perspective the parallel jobs should be totally disjunctive.

The SQL we're executing looks something like this 

Insert into targetTable (...) Select (...) from sourceTable Where partitionKey='xxx'.

What are we observing: Insert/select does not use the full potential of insert performance of db2: - insert/select: about 8000 inserts/s - plain inserts via jdbc batching: more than 50 000 inserts/s

We would like to ask the following questions: - why is the insert performance of a single insert/select so slow? Do we need to make any special configurations? - is working in parallel on multiple partitions the correct approach? Any alternatives? - should we submit smaller packages of insert/selects? - what is db2 doing in the background on an insert/select

Thank you and best regards

dsolimano
  • 8,870
  • 3
  • 48
  • 63
getty23
  • 93
  • 1
  • 8
  • Care to show the actual statement, or should we guess? Did you look at the statement explain plan? And why are you doing this with Java in the first place? – mustaccio Nov 27 '14 at 20:29
  • I'm with @mustaccio - if you're doing this "per row" in Java, expect to take a (potentially huge) performance hit. For large numbers of records, it's almost always best to use one single query that captures/moves everybody at once. – Clockwork-Muse Nov 28 '14 at 03:39
  • It is one large statement which inserts all records of one partition (about 3mio) at once. Let me add a comparable sql statement: Insert into targetTable (...) Select (...) from sourceTable Where partitionKey='xxx'. – getty23 Nov 28 '14 at 07:34

1 Answers1

1

It's not a "single insert" that's taking so long. It's the 3,000,000 inserts.

You analyze this problem the same way you analyze any other problem with an RDBMS...

  1. Is the where clause on your select statement sargable?
  2. Are there indexes on the target table? How many? Can you reduce that number?
  3. What does the explain output tell you?
  4. Is there a sort operation implicit in your select?

...none of which are things we can help you with, unfortunately.

As for the difference between a single insert/select and JDBC batching, there are enough variables in the latter (use of a DB2 gateway, other "helper" software) that may come into play, that I cannot comment. I will say that moving the single insert/select into an SQL/PL native stored procedure is worth exploring.

cschneid
  • 10,237
  • 1
  • 28
  • 39