DB2 dropped into first gear?

[Al Murry]

A Java Batch job running on AIX 4.3.3 talking to another
RS6000 running DB2 6.1 was taking 15 min last week. This week
it is taking over 6 hours! The job uses 4 tables that have
over 5,000,000 rows each.
I am told nothing has changed in the batch job and other
than about 10K new records inserted each night into a few of
the tables, nothing has changed in the DB. (I am told)

The questions is; Can a query, insert and update job
go from 15mins to 6hrs in one day? I don't think so...

Have reorged all the tables, ran DB2DART on the DB with
no luck.

Any ideas where to look? All smit diags show ok on OS and
hardware.
network seems fine.

Thanks --

TS

[Larry Edelstein]

Re: DB2 dropped into first gear?

Have you considered the network? Have any network changes been made? How
many segments separate the two machines? Could the traffic patterns be
different this week for some reason?

Al Murry wrote:
[color=blue]
> A Java Batch job running on AIX 4.3.3 talking to another
> RS6000 running DB2 6.1 was taking 15 min last week. This week
> it is taking over 6 hours! The job uses 4 tables that have
> over 5,000,000 rows each.
> I am told nothing has changed in the batch job and other
> than about 10K new records inserted each night into a few of
> the tables, nothing has changed in the DB. (I am told)
>
> The questions is; Can a query, insert and update job
> go from 15mins to 6hrs in one day? I don't think so...
>
> Have reorged all the tables, ran DB2DART on the DB with
> no luck.
>
> Any ideas where to look? All smit diags show ok on OS and
> hardware.
> network seems fine.
>
> Thanks --
>
> TS[/color]

[PM-pm3iinc-nospam]

Re: DB2 dropped into first gear?

Other jobs competing for resources at the same time? (scheduler, utilities,
....)
Tempspace/sort parms... Sort is now going to disk?
Clustering ratios changed? Maybe someone reorged the wrong way...
Summary tables involved? ast, ...
lock escalation
forgot to remove traces?
Are we talking jdbc or sqlj?
etc....

PM

"Al Murry" <amurry@northte ch.net> a écrit dans le message de
news:pan.2003.1 0.20.20.38.23.1 5222@northtech. net...[color=blue]
> A Java Batch job running on AIX 4.3.3 talking to another
> RS6000 running DB2 6.1 was taking 15 min last week. This week
> it is taking over 6 hours! The job uses 4 tables that have
> over 5,000,000 rows each.
> I am told nothing has changed in the batch job and other
> than about 10K new records inserted each night into a few of
> the tables, nothing has changed in the DB. (I am told)
>
> The questions is; Can a query, insert and update job
> go from 15mins to 6hrs in one day? I don't think so...
>
> Have reorged all the tables, ran DB2DART on the DB with
> no luck.
>
> Any ideas where to look? All smit diags show ok on OS and
> hardware.
> network seems fine.
>
> Thanks --
>
> TS[/color]

[Willard Farwark]

Re: DB2 dropped into first gear?

I have shut all other jobs down. Taking a snapshot a different times
Shows nothing adnormal. No lock escalation.
Using jdbc.
No cluster idx's.

TS

On Mon, 20 Oct 2003 21:38:13 +0000, PM-pm3iinc-nospam wrote:
[color=blue]
> Other jobs competing for resources at the same time? (scheduler, utilities,
> ...)
> Tempspace/sort parms... Sort is now going to disk?
> Clustering ratios changed? Maybe someone reorged the wrong way...
> Summary tables involved? ast, ...
> .
> forgot to remove traces?
> Are we talking jdbc or sqlj?
> etc....
>
> PM
>
> "Al Murry" <amurry@northte ch.net> a écrit dans le message de
> news:pan.2003.1 0.20.20.38.23.1 5222@northtech. net...[color=green]
>> A Java Batch job running on AIX 4.3.3 talking to another
>> RS6000 running DB2 6.1 was taking 15 min last week. This week
>> it is taking over 6 hours! The job uses 4 tables that have
>> over 5,000,000 rows each.
>> I am told nothing has changed in the batch job and other
>> than about 10K new records inserted each night into a few of
>> the tables, nothing has changed in the DB. (I am told)
>>
>> The questions is; Can a query, insert and update job
>> go from 15mins to 6hrs in one day? I don't think so...
>>
>> Have reorged all the tables, ran DB2DART on the DB with
>> no luck.
>>
>> Any ideas where to look? All smit diags show ok on OS and
>> hardware.
>> network seems fine.
>>
>> Thanks --
>>
>> TS[/color][/color]

[Mark A]

Re: DB2 dropped into first gear?

> > "Al Murry" <amurry@northte ch.net> a écrit dans le message de[color=blue][color=green]
> > news:pan.2003.1 0.20.20.38.23.1 5222@northtech. net...[color=darkred]
> >> A Java Batch job running on AIX 4.3.3 talking to another
> >> RS6000 running DB2 6.1 was taking 15 min last week. This week
> >> it is taking over 6 hours! The job uses 4 tables that have
> >> over 5,000,000 rows each.
> >> I am told nothing has changed in the batch job and other
> >> than about 10K new records inserted each night into a few of
> >> the tables, nothing has changed in the DB. (I am told)
> >>
> >> The questions is; Can a query, insert and update job
> >> go from 15mins to 6hrs in one day? I don't think so...
> >>
> >> Have reorged all the tables, ran DB2DART on the DB with
> >> no luck.
> >>
> >> Any ideas where to look? All smit diags show ok on OS and
> >> hardware.
> >> network seems fine.
> >>
> >> Thanks --
> >>
> >> TS[/color][/color][/color]

Is DB2 6.1 for AIX still supported by IBM?

[PM \(pm3iinc-nospam\)]

Re: DB2 dropped into first gear?

logging performance changed?
db2 list history and db2diag.log (db2 starting with small bufferpool,
etc.)... could provide some clues?
db2level changed recently?
bug/boucle in the program's code (error management)? (could happen)
database activated?

PM

"Willard Farwark" <amurry@northte ch.net> a écrit dans le message de
news:pan.2003.1 0.21.03.09.04.3 89460@northtech .net...[color=blue]
> I have shut all other jobs down. Taking a snapshot a different times
> Shows nothing adnormal. No lock escalation.
> Using jdbc.
> No cluster idx's.
>
> TS
>
> On Mon, 20 Oct 2003 21:38:13 +0000, PM-pm3iinc-nospam wrote:
>[color=green]
> > Other jobs competing for resources at the same time? (scheduler,[/color][/color]
utilities,[color=blue][color=green]
> > ...)
> > Tempspace/sort parms... Sort is now going to disk?
> > Clustering ratios changed? Maybe someone reorged the wrong way...
> > Summary tables involved? ast, ...
> > .
> > forgot to remove traces?
> > Are we talking jdbc or sqlj?
> > etc....
> >
> > PM
> >
> > "Al Murry" <amurry@northte ch.net> a écrit dans le message de
> > news:pan.2003.1 0.20.20.38.23.1 5222@northtech. net...[color=darkred]
> >> A Java Batch job running on AIX 4.3.3 talking to another
> >> RS6000 running DB2 6.1 was taking 15 min last week. This week
> >> it is taking over 6 hours! The job uses 4 tables that have
> >> over 5,000,000 rows each.
> >> I am told nothing has changed in the batch job and other
> >> than about 10K new records inserted each night into a few of
> >> the tables, nothing has changed in the DB. (I am told)
> >>
> >> The questions is; Can a query, insert and update job
> >> go from 15mins to 6hrs in one day? I don't think so...
> >>
> >> Have reorged all the tables, ran DB2DART on the DB with
> >> no luck.
> >>
> >> Any ideas where to look? All smit diags show ok on OS and
> >> hardware.
> >> network seems fine.
> >>
> >> Thanks --
> >>
> >> TS[/color][/color]
>[/color]

[Blair Adamache]

Re: DB2 dropped into first gear?

No.

Mark A wrote:

[color=blue]
> Is DB2 6.1 for AIX still supported by IBM?
>
>[/color]

[Al Murry]

Re: DB2 dropped into first gear?

On Mon, 20 Oct 2003 21:37:57 +0000, Mark A wrote:
[color=blue][color=green][color=darkred]
>> > "Al Murry" <amurry@northte ch.net> a écrit dans le message de
>> > news:pan.2003.1 0.20.20.38.23.1 5222@northtech. net...
>> >> A Java Batch job running on AIX 4.3.3 talking to another RS6000
>> >> running DB2 6.1 was taking 15 min last week. This week it is taking
>> >> over 6 hours! The job uses 4 tables that have over 5,000,000 rows
>> >> each.
>> >> I am told nothing has changed in the batch job and other than about
>> >> 10K new records inserted each night into a few of the tables, nothing
>> >> has changed in the DB. (I am told)
>> >>
>> >> The questions is; Can a query, insert and update job go from 15mins
>> >> to 6hrs in one day? I don't think so...
>> >>
>> >> Have reorged all the tables, ran DB2DART on the DB with no luck.
>> >>
>> >> Any ideas where to look? All smit diags show ok on OS and hardware.
>> >> network seems fine.
>> >>
>> >> Thanks --
>> >>
>> >> TS[/color][/color]
>
> Is DB2 6.1 for AIX still supported by IBM?[/color]

Nope. WE just got 7.2 but I Dont want to put it into prod
without fixing all problems first..

TS

[Evan Smith]

Re: DB2 dropped into first gear?

Ran into a similar problem last night. A batch process that normally
took seconds now took hours. No structural changes, etc. The only
difference is that now one of the tables had 30K more rows in it.

What we finally figured out (or at least guessed at), was that we must
have crossed some internal threshold on rowcounts that made the
optimizer take a path. A quick runstats on the one table in question
returned the performance to seconds again. (Ironically the cost from
the explain utility was higher after the runstats.) Try updating the
stats for your tables and see if that restores performance.

Evan


"PM \(pm3iinc-nospam\)" <PM(pm3iinc-nospam)@sympati co.ca> wrote in message news:<pk2lb.311 2$XO.504600@new s20.bellglobal. com>...[color=blue]
> logging performance changed?
> db2 list history and db2diag.log (db2 starting with small bufferpool,
> etc.)... could provide some clues?
> db2level changed recently?
> bug/boucle in the program's code (error management)? (could happen)
> database activated?
>
> PM
>
> "Willard Farwark" <amurry@northte ch.net> a écrit dans le message de
> news:pan.2003.1 0.21.03.09.04.3 89460@northtech .net...[color=green]
> > I have shut all other jobs down. Taking a snapshot a different times
> > Shows nothing adnormal. No lock escalation.
> > Using jdbc.
> > No cluster idx's.
> >
> > TS
> >
> > On Mon, 20 Oct 2003 21:38:13 +0000, PM-pm3iinc-nospam wrote:
> >[color=darkred]
> > > Other jobs competing for resources at the same time? (scheduler,[/color][/color]
> utilities,[color=green][color=darkred]
> > > ...)
> > > Tempspace/sort parms... Sort is now going to disk?
> > > Clustering ratios changed? Maybe someone reorged the wrong way...
> > > Summary tables involved? ast, ...
> > > .
> > > forgot to remove traces?
> > > Are we talking jdbc or sqlj?
> > > etc....
> > >
> > > PM
> > >
> > > "Al Murry" <amurry@northte ch.net> a écrit dans le message de
> > > news:pan.2003.1 0.20.20.38.23.1 5222@northtech. net...
> > >> A Java Batch job running on AIX 4.3.3 talking to another
> > >> RS6000 running DB2 6.1 was taking 15 min last week. This week
> > >> it is taking over 6 hours! The job uses 4 tables that have
> > >> over 5,000,000 rows each.
> > >> I am told nothing has changed in the batch job and other
> > >> than about 10K new records inserted each night into a few of
> > >> the tables, nothing has changed in the DB. (I am told)
> > >>
> > >> The questions is; Can a query, insert and update job
> > >> go from 15mins to 6hrs in one day? I don't think so...
> > >>
> > >> Have reorged all the tables, ran DB2DART on the DB with
> > >> no luck.
> > >>
> > >> Any ideas where to look? All smit diags show ok on OS and
> > >> hardware.
> > >> network seems fine.
> > >>
> > >> Thanks --
> > >>
> > >> TS[/color]
> >[/color][/color]

[Mark A]

Re: DB2 dropped into first gear?

"Evan Smith" <esmith2112@hot mail.com> wrote in message[color=blue]
> Ran into a similar problem last night. A batch process that normally
> took seconds now took hours. No structural changes, etc. The only
> difference is that now one of the tables had 30K more rows in it.
>
> What we finally figured out (or at least guessed at), was that we must
> have crossed some internal threshold on rowcounts that made the
> optimizer take a path. A quick runstats on the one table in question
> returned the performance to seconds again. (Ironically the cost from
> the explain utility was higher after the runstats.) Try updating the
> stats for your tables and see if that restores performance.
>
> Evan
>[/color]
If runstats had been run, that is a potentially major change. 30K more rows
may have caused major disorganization problems in the table or the index,
which might have caused DB2 to stop using an index. This often happens when
there is not sufficient freespace defined for new table rows or index rows
to be inserted in the middle of the existing data. Reorgs and then runstats
will help this, but also consider adding percent free to tables and indexes.

[Al Murry]

Re: DB2 dropped into first gear?

Got it fixed. Thanks for all the help. After much discussions, got
them to bring the prod to devl and play with it.
Was using Quest to runstats late at night, I thought Quest's
reorg did indexes also. WRONG!
Used DB2's command line. Reorged one of the tables with the
index DB2 suggested and took care of it.

What got me was everything changed it one or two days. Did not
think that would happend.

ts

On Mon, 20 Oct 2003 19:14:02 +0000, Larry Edelstein wrote:
[color=blue]
> Have you considered the network? Have any network changes been made? How
> many segments separate the two machines? Could the traffic patterns be
> different this week for some reason?
>
> Al Murry wrote:
>[color=green]
>> A Java Batch job running on AIX 4.3.3 talking to another RS6000 running
>> DB2 6.1 was taking 15 min last week. This week it is taking over 6
>> hours! The job uses 4 tables that have over 5,000,000 rows each.
>> I am told nothing has changed in the batch job and other than about 10K
>> new records inserted each night into a few of the tables, nothing has
>> changed in the DB. (I am told)
>>
>> The questions is; Can a query, insert and update job go from 15mins to
>> 6hrs in one day? I don't think so...
>>
>> Have reorged all the tables, ran DB2DART on the DB with no luck.
>>
>> Any ideas where to look? All smit diags show ok on OS and hardware.
>> network seems fine.
>>
>> Thanks --
>>
>> TS[/color][/color]

[Mark A]

Re: DB2 dropped into first gear?

"Al Murry" <amurry@northte ch.net> wrote in message
news:pan.2003.1 0.21.21.17.46.9 96803@northtech .net...[color=blue]
> Got it fixed. Thanks for all the help. After much discussions, got
> them to bring the prod to devl and play with it.
> Was using Quest to runstats late at night, I thought Quest's
> reorg did indexes also. WRONG!
> Used DB2's command line. Reorged one of the tables with the
> index DB2 suggested and took care of it.
>
> What got me was everything changed it one or two days. Did not
> think that would happend.
>
> ts[/color]
If you put some percent free in the index (and also the table if you have
defined a clustering index), that will help minimize index page splits
(which cause performance problems during the insert and need to be reorged
more often). The percent free should be the percentage of data that is
inserted into the table between reorgs.