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]

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:

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

[PM-pm3iinc-nospam]

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" <am****@northtech.net> a écrit dans le message de
news:pa***************************@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

[Willard Farwark]

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:

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" <am****@northtech.net> a écrit dans le message de
news:pa***************************@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

[Mark A]

> > "Al Murry" <am****@northtech.net> a écrit dans le message de

news:pa***************************@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

Is DB2 6.1 for AIX still supported by IBM?

[PM \(pm3iinc-nospam\)]

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" <am****@northtech.net> a écrit dans le message de
news:pa****************************@northtech.net. ..

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:

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" <am****@northtech.net> a écrit dans le message de
news:pa***************************@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

[Blair Adamache]

No.

Mark A wrote:

Is DB2 6.1 for AIX still supported by IBM?

[Al Murry]

On Mon, 20 Oct 2003 21:37:57 +0000, Mark A wrote:

> "Al Murry" <am****@northtech.net> a écrit dans le message de
> news:pa***************************@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

Is DB2 6.1 for AIX still supported by IBM?

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

TS

[Evan Smith]

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)@sympatico.ca> wrote in message news:<pk******************@news20.bellglobal.com>. ..

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" <am****@northtech.net> a écrit dans le message de
news:pa****************************@northtech.net. ..

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:

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" <am****@northtech.net> a écrit dans le message de
news:pa***************************@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

[Mark A]

"Evan Smith" <es********@hotmail.com> wrote in message

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

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]

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:

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:

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

[Mark A]

"Al Murry" <am****@northtech.net> wrote in message
news:pa****************************@northtech.net. ..

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

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.