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

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.

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

Hi,

jafastinger wrote:[color=blue]
> 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.[/color]

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

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

jafastinger wrote:[color=blue]
>
> 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).[/color]

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).

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

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:
[color=blue]
> 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.[/color]

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

Norbert Munkel <nm@acoreus.de> wrote in message news:<jM3md.84$SK6.1642@se2-cb104-9.zrh1.ch.colt.net>...[color=blue]
> Hi,
>
> jafastinger wrote:[color=green]
> > 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.[/color]
>
> 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[/color]

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,

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

Larry <larry@nospam.net> wrote in message news:<4Cemd.6667$GV4.8226963@news4.srv.hcvlny.cv.n et>...[color=blue]
> 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:
>[color=green]
> > 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.[/color][/color]

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.

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

Ian <ianbjor@mobileaudio.com> wrote in message news:<4199523c$1_3@newsfeed.slurp.net>...[color=blue]
> jafastinger wrote:[color=green]
> >
> > 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).[/color]
>
> 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).[/color]

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.

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

jafastinger@aep.com (jafastinger) wrote in message news:<1d824efb.0411160518.1ea5983f@posting.google. com>...[color=blue]
> Larry <larry@nospam.net> wrote in message news:<4Cemd.6667$GV4.8226963@news4.srv.hcvlny.cv.n et>...[color=green]
> > 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:
> >[color=darkred]
> > > 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.[/color][/color]
>
> 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.[/color]


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.