Professional Web Applications Themes

DB2 dropped into first gear? - IBM DB2

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

  1. #1

    Default DB2 dropped into first gear?

    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
    Al Guest

  2. #2

    Default 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:
     

    Larry Guest

  3. #3

    Default 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" <net> a écrit dans le message de
    news:net... 


    PM-pm3iinc-nospam Guest

  4. #4

    Default 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:
     [/ref]

    Willard Guest

  5. #5

    Default Re: DB2 dropped into first gear?

    > > "Al Murry" <net> a écrit dans le message de [/ref][/ref]

    Is DB2 6.1 for AIX still supported by IBM?


    Mark Guest

  6. #6

    Default 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" <net> a écrit dans le message de
    news:net... [/ref]
    utilities, [/ref]
    >[/ref]


    PM Guest

  7. #7

    Default Re: DB2 dropped into first gear?

    No.

    Mark A wrote:

     

    Blair Guest

  8. #8

    Default Re: DB2 dropped into first gear?

    On Mon, 20 Oct 2003 21:37:57 +0000, Mark A wrote:
     [/ref]
    >
    > Is DB2 6.1 for AIX still supported by IBM?[/ref]

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

    TS
    Al Guest

  9. #9

    Default 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)sympatico.ca> wrote in message news:<pk2lb.3112$bellglobal.com>... [/ref]
    > utilities, 
    > >[/ref][/ref]
    Evan Guest

  10. #10

    Default Re: DB2 dropped into first gear?

    "Evan Smith" <com> wrote in message 
    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.


    Mark Guest

  11. #11

    Default 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:
     [/ref]

    Al Guest

  12. #12

    Default Re: DB2 dropped into first gear?

    "Al Murry" <net> wrote in message
    news:net... 
    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.


    Mark Guest

Similar Threads

  1. New Gear. Drool!!!!
    By Buko@adobeforums.com in forum Adobe Photoshop Mac CS, CS2 & CS3
    Replies: 2380
    Last Post: May 31st, 03:18 AM
  2. Can I replace a dropped clip with another dropped
    By Toddhil in forum Macromedia Flash Actionscript
    Replies: 0
    Last Post: February 11th, 11:57 PM
  3. FS: Canon EOS Gear
    By Michael in forum Photography
    Replies: 2
    Last Post: October 2nd, 10:44 PM
  4. Drawing a Gear
    By Michael Hamstra in forum Macromedia Freehand
    Replies: 6
    Last Post: August 9th, 09:18 PM
  5. Nikon F2 gear
    By louis reddish in forum Photography
    Replies: 1
    Last Post: July 16th, 08:02 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139