Udb Db2 Vs 7.2 Vs 8 Upgrade Resulting In Increased Insert Times

[jafastinger]

In our shop we have a 28,000,000 row insert that gets reloaded every
night. I am not looking to change this process. I would like to know
why in UDB Vs 7.2 we were getting an insert rate of 3300 rows per
second. We migrated to version 8.1 of UDB (non-partitioned) and now
the rate decreased to ~1100 rows per second. The only thing we changed
is the database version.

On, a test box I did some testing.
The source in UDB version 7.2 fixpack 9 is reading at 20000 rows a
second.
The source in UDB version 8.1 fixpack 6(non-partitioned) is reading at
13200 rows a second.
The target in UDB version 7.2 is inserting at 4800 rows a second.
The target in UDB version 8.1 is inserting at 1600 rows a second.

This is really close to the percentages of throughput I am getting in
prod so I thought it would be a good place to tune.

I also looked at the database side by doing a
insert into table2
select * from table1
Vs 7.2 is 13000 / sec
Vs 8.1 is 10000 /sec
So to me it looks like UDB and Informatica are collaborating to cause
this issue.

Since the insert select in vs 8.1 is 75% of the vs7.2
In Informatica vs 8.1 is consistently 33% of vs 7.2.

By the way we are using Informatica Power center 6.2.1 (R133 D41).

Any help would be appreciated.

[Norbert Munkel]

Hi,

jafastinger wrote:

why in UDB Vs 7.2 we were getting an insert rate of 3300 rows per
second. We migrated to version 8.1 of UDB (non-partitioned) and now
the rate decreased to ~1100 rows per second. The only thing we changed
is the database version.

I don't know the application you are running but did you run runstats on
your tables after migrating to 8.1 ? I would recommend to do so and run
your tests again after collecting the Stats. Helped in my environment
during migration from 7.2 to 8.1.4.
regards,

Norbert

[Ian]

jafastinger wrote:

Since the insert select in vs 8.1 is 75% of the vs7.2
In Informatica vs 8.1 is consistently 33% of vs 7.2.

By the way we are using Informatica Power center 6.2.1 (R133 D41).

Did you (at some point) rebind the packages on your 7.2 system to
use buffered inserts?

This is a common recommendation from Informatica, and this is not
the default configuration for DB2 (rightly so).

[Larry]

Did you store your dbm/db cfg parms from V7 somewhere? Is it possible
that some of the cfg parms were changed by the migration? I have heard
of situations where this has happened.

Larry Edelstein

jafastinger wrote:

In our shop we have a 28,000,000 row insert that gets reloaded every
night. I am not looking to change this process. I would like to know
why in UDB Vs 7.2 we were getting an insert rate of 3300 rows per
second. We migrated to version 8.1 of UDB (non-partitioned) and now
the rate decreased to ~1100 rows per second. The only thing we changed
is the database version.

On, a test box I did some testing.
The source in UDB version 7.2 fixpack 9 is reading at 20000 rows a
second.
The source in UDB version 8.1 fixpack 6(non-partitioned) is reading at
13200 rows a second.
The target in UDB version 7.2 is inserting at 4800 rows a second.
The target in UDB version 8.1 is inserting at 1600 rows a second.

This is really close to the percentages of throughput I am getting in
prod so I thought it would be a good place to tune.

I also looked at the database side by doing a
insert into table2
select * from table1
Vs 7.2 is 13000 / sec
Vs 8.1 is 10000 /sec
So to me it looks like UDB and Informatica are collaborating to cause
this issue.

Since the insert select in vs 8.1 is 75% of the vs7.2
In Informatica vs 8.1 is consistently 33% of vs 7.2.

By the way we are using Informatica Power center 6.2.1 (R133 D41).

Any help would be appreciated.

[jafastinger]

Norbert Munkel <[e-mail]> wrote in message news:<jM***************@se2-cb104-9.zrh1.ch.colt.net>...

Hi,

jafastinger wrote:

why in UDB Vs 7.2 we were getting an insert rate of 3300 rows per
second. We migrated to version 8.1 of UDB (non-partitioned) and now
the rate decreased to ~1100 rows per second. The only thing we changed
is the database version.

I don't know the application you are running but did you run runstats on
your tables after migrating to 8.1 ? I would recommend to do so and run
your tests again after collecting the Stats. Helped in my environment
during migration from 7.2 to 8.1.4.
regards,

Norbert

we ran a convert of our indexes because of the type-2 indexes in Vs 8
and also had to run runstats after that. I set this up in our test
environment and got the same results but with ~6 Million rows.
Thanks,

[jafastinger]

Larry <la***@nospam.net> wrote in message news:<4C********************@news4.srv.hcvlny.cv.n et>...

Did you store your dbm/db cfg parms from V7 somewhere? Is it possible
that some of the cfg parms were changed by the migration? I have heard
of situations where this has happened.

Larry Edelstein

jafastinger wrote:

In our shop we have a 28,000,000 row insert that gets reloaded every
night. I am not looking to change this process. I would like to know
why in UDB Vs 7.2 we were getting an insert rate of 3300 rows per
second. We migrated to version 8.1 of UDB (non-partitioned) and now
the rate decreased to ~1100 rows per second. The only thing we changed
is the database version.

On, a test box I did some testing.
The source in UDB version 7.2 fixpack 9 is reading at 20000 rows a
second.
The source in UDB version 8.1 fixpack 6(non-partitioned) is reading at
13200 rows a second.
The target in UDB version 7.2 is inserting at 4800 rows a second.
The target in UDB version 8.1 is inserting at 1600 rows a second.

This is really close to the percentages of throughput I am getting in
prod so I thought it would be a good place to tune.

I also looked at the database side by doing a
insert into table2
select * from table1
Vs 7.2 is 13000 / sec
Vs 8.1 is 10000 /sec
So to me it looks like UDB and Informatica are collaborating to cause
this issue.

Since the insert select in vs 8.1 is 75% of the vs7.2
In Informatica vs 8.1 is consistently 33% of vs 7.2.

By the way we are using Informatica Power center 6.2.1 (R133 D41).

Any help would be appreciated.

We stored them off and reset them back up like they were in vs 7
We opend a ticket with IBM and with informatica. we will see what shakes out.
Thanks for replying.

[jafastinger]

Ian <ia*****@mobileaudio.com> wrote in message news:<41**********@newsfeed.slurp.net>...

jafastinger wrote:

Since the insert select in vs 8.1 is 75% of the vs7.2
In Informatica vs 8.1 is consistently 33% of vs 7.2.

By the way we are using Informatica Power center 6.2.1 (R133 D41).

Did you (at some point) rebind the packages on your 7.2 system to
use buffered inserts?

This is a common recommendation from Informatica, and this is not
the default configuration for DB2 (rightly so).

Our Vs7 database did not have the packages bound with Insert buf. So
we did not bind the Vs 8 with insert buf. I will try this in OUR test
environment and let you know. we will see.

[jafastinger]

ja*********@aep.com (jafastinger) wrote in message news:<1d**************************@posting.google. com>...

Larry <la***@nospam.net> wrote in message news:<4C********************@news4.srv.hcvlny.cv.n et>...

Did you store your dbm/db cfg parms from V7 somewhere? Is it possible
that some of the cfg parms were changed by the migration? I have heard
of situations where this has happened.

Larry Edelstein

jafastinger wrote:

In our shop we have a 28,000,000 row insert that gets reloaded every
night. I am not looking to change this process. I would like to know
why in UDB Vs 7.2 we were getting an insert rate of 3300 rows per
second. We migrated to version 8.1 of UDB (non-partitioned) and now
the rate decreased to ~1100 rows per second. The only thing we changed
is the database version.

On, a test box I did some testing.
The source in UDB version 7.2 fixpack 9 is reading at 20000 rows a
second.
The source in UDB version 8.1 fixpack 6(non-partitioned) is reading at
13200 rows a second.
The target in UDB version 7.2 is inserting at 4800 rows a second.
The target in UDB version 8.1 is inserting at 1600 rows a second.

This is really close to the percentages of throughput I am getting in
prod so I thought it would be a good place to tune.

I also looked at the database side by doing a
insert into table2
select * from table1
Vs 7.2 is 13000 / sec
Vs 8.1 is 10000 /sec
So to me it looks like UDB and Informatica are collaborating to cause
this issue.

Since the insert select in vs 8.1 is 75% of the vs7.2
In Informatica vs 8.1 is consistently 33% of vs 7.2.

By the way we are using Informatica Power center 6.2.1 (R133 D41).

Any help would be appreciated.

We stored them off and reset them back up like they were in vs 7
We opend a ticket with IBM and with informatica. we will see what shakes out.
Thanks for replying.

Problem solved. Version 6.2.2 of informatica seemed to solve the
problem there was a patch applied for something to do with casting
Char fields as LONVARCHAR and that seemd to cause the bottle neck.
migrating informatica 6.2.2 to production soon.