Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Location
    Stockholm, Sweden
    Posts
    2

    Unanswered: Drop procedure takes looooong time

    Hello!
    AIX 5.1, DB2 8.1, 64 bit (details below)

    We have serious performance problems when dropping procedures.
    Each drop takes 30 seconds or more.

    There is no difference depending on the amount of code in a procedure.

    We have approximately 800 stored procedures and each time we redeploy the system about 200 procedures need to be dropped and recreated.

    This means that it takes over an hour to redeploy the procedures alone, which is unacceptable.

    We're running shell-scripts to redeploy procedures:

    #!/bin/sh
    db2 connect to ... user ... etc

    for file in $procfiles ; do
    db2 -td@ -vf $file >> $log
    done
    # end example script

    each file contains:
    drop procedure foo
    @
    create procedure foo ...
    @

    However...
    Command line or GUI, same problem!

    Creating a procedure after the drop takes between 1 and 5 seconds depending on the amount of code.

    The problem occurs in our production environment only which is a 64-bit-installation. In the test environment (which is 32 bit), no problem.

    Need i say that the production machine is "a lot faster", double processor, 2 GB memory, faster processors etc

    Any ideas?

    best regards Johan Linderoth


    Version stuff:

    $ uname -a
    AIX hostname 1 5 0030E22C4C00

    $ db2level
    DB21085I Instance "db" uses "64" bits and DB2 code release "SQL08016"
    with level identifier "02070106".
    Informational tokens are "DB2 v8.1.1.56", "s040616", "U497635", and FixPak "6".
    Product is installed at "/usr/opt/db2_08_01".
    Last edited by johanlinderoth; 11-11-04 at 05:07.

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by johanlinderoth
    Hello!
    AIX 5.1, DB2 8.1, 64 bit (details below)

    We have serious performance problems when dropping procedures.
    Each drop takes 30 seconds or more.

    There is no difference depending on the amount of code in a procedure.

    We have approximately 800 stored procedures and each time we redeploy the system about 200 procedures need to be dropped and recreated.

    This means that it takes over an hour to redeploy the procedures alone, which is unacceptable.

    We're running shell-scripts to redeploy procedures:

    #!/bin/sh
    db2 connect to ... user ... etc

    for file in $procfiles ; do
    db2 -td@ -vf $file >> $log
    done
    # end example script

    each file contains:
    drop procedure foo
    @
    create procedure foo ...
    @

    However...
    Command line or GUI, same problem!

    Creating a procedure after the drop takes between 1 and 5 seconds depending on the amount of code.

    The problem occurs in our production environment only which is a 64-bit-installation. In the test environment (which is 32 bit), no problem.

    Need i say that the production machine is "a lot faster", double processor, 2 GB memory, faster processors etc

    Any ideas?

    best regards Johan Linderoth


    Version stuff:

    $ uname -a
    AIX hostname 1 5 0030E22C4C00

    $ db2level
    DB21085I Instance "db" uses "64" bits and DB2 code release "SQL08016"
    with level identifier "02070106".
    Informational tokens are "DB2 v8.1.1.56", "s040616", "U497635", and FixPak "6".
    Product is installed at "/usr/opt/db2_08_01".
    Do you have a lot of packages? I suspect db2 scans the packages to see if any are dependent on the procedure... for each one.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Nov 2004
    Location
    Stockholm, Sweden
    Posts
    2
    I'm not sure... I'll check that as soon as possible!
    Thanks. /johan

  4. #4
    Join Date
    Sep 2003
    Posts
    237
    There is a dependency matrix(table) keeping track of which object is dependent on which other object. When you drop a procedure, system catalog etc. are updated, undo info written to log etc. If system is busy, it could take some time. Can you split 200 drop procedures to 20 scripts and run them in parallel?
    mota

Posting Permissions

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