Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2012
    Posts
    2

    Unanswered: Dropping and recreating the DB2 stored procedure improves the performance

    Hello,

    I am facing a peculiar problem. Here I explain it

    I create a stored procedure on a table which doesnot contain any data.
    As and when the system runs, the table gets data piled up in it.
    I kept on observing the stored procedure execution time. As the data grows the execution time also grows. At some point stored procedure takes long time to return (like 20 mins).

    To resolve the above issue, I just dropped the procedure and re created it. Now it executes within secs.

    Any idea what could be the issue here?

    Thanks
    Chandru

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'm presuming this is an SQL stored procedure. The execution plan(s) are compiled when you create the procedure or rebind its package. Since you created the procedure when the table was empty, the corresponding statistics were used in compilation. Now that the statistics have changed, the plans have become suboptimal. Dropping and re-creating the procedure caused the plans to be compiled using current statistics. You could have issued a REBIND command against the procedure's package to achieve the same effect.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by n_i View Post
    You could have issued a REBIND
    Could? You mean should. You could buy a new car when your out of gas...
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by dr_te_z View Post
    You mean should.
    I'm not in a position to dictate anything to anyone (except may be my dog, but even then...) I'm just trying to show various opportunities. Some may choose to actually buy a new car...
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Aug 2012
    Posts
    2
    Hello,

    Thanks for the reply.
    Can you please tell me, if this REBIND command needs to be executed as and when the data grows in the table ? Or is it something which we can execute during the procedure creation itself?

    Also can you give me an example for REBIND command?

    I found one is this correct => CALL SYSPROC.REBIND_ROUTINE_PACKAGE('P', 'MYSCHEMA.MYPROC', 'CONSERVATIVE')

    Thanks
    Chandru

  6. #6
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    I'm not in a position to dictate anything to anyone (except may be my dog, but even then...)
    The dog - possibly. Don't dictate to the cat. . .

    Dogs have owners (family) - cats have staff

  7. #7
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by naik_b View Post
    Can you please tell me, if this REBIND command needs to be executed as and when the data grows in the table ? Or is it something which we can execute during the procedure creation itself?
    Sorry, I do not mean to dictate anything to anybody. My recommendation is a "db2rbind" after each runstats run (and a drop package cache for the dynamic SQL).
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by dr_te_z View Post
    My recommendation is a "db2rbind" after each runstats run (and a drop package cache for the dynamic SQL).
    I prefer to generate individual rebinds for each non-system package (determined by package schema). Running db2rbind does them all at once, and can cause lock contention. When using db2rbind, if the rebind of any of the packages encounters a deadlock or a lock timeout, the rebind of all the packages will be rolled back.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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