Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003
    Posts
    35

    Angry Unanswered: behaving irrelevant stored procedures.

    Hi,
    I am running stored procedures in AIX BOX, Some times it was running for 45 minutes and once it was dropped and re-created its running with 2 minutes and same behaviour was happening for almost 10 procedures.
    We are running application, that it will run end-to-end application that creates whole database and tablespaces and other table objects such procedures, functions and loading table and updation table and also index creations.
    And we are running update statistics, reorg and also runstats at every appropriate place.

    By doing snapshot for the tables while executing of these procedures and the tables related to the procedures are going for tablescan instead of index scan and the once procedures was dropped and re-created then after its going for index scan and after you run as many times these procedures, its running with in 2 minutes, so at the bottom line all these procedures needs one time drop and creation after it has been created first time, if the procedure has dropped and created then all these procedures are working fine.

    Our application is running by concurrent users we dont want to drop and re-create these procedures in middle of the transactions and that's not preferable also for us.
    All these procedures was built in SQL NOT IN C.
    I hope you understand my problem clearly.
    If any one knows about this problems or if you have any idea about this problem.
    Please let me know.We are facing with this problem for last one week.

    Thanks in advance.
    muthu

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: behaving irrelevant stored procedures.

    This is my asssumption :

    Your SQL Procedures contain static SQL (ie the access path is decided at bind time, which here is the stored proc creation time)... Possibly, there are no rows in the tables then and hence the optimizer would have decided on tablescan ... Now the table has grown big enough to bring down your response time ... The runstats you do will not be reflected in the access path .. When you dropped and created the stored procedure, your access plan used your latest statistics for the access plan and hence it is fast(er)...

    Well, the solution to this is , whenever you do a reorg/runstats, do a rebind of the static packages ..... To know which packages to rebind, use the syscat.packagedep view ... The columns bschema,bname give you the object names over which the package depends ...

    (If you have any system on which the stored procs have slowed down, use the db2expln utility to explain the access paths of the package and you will see that the access plans have tablescans in them whereas the recently created ones will have an index scan)

    HTH
    Sathyaram





    Originally posted by nmkumaran
    Hi,
    I am running stored procedures in AIX BOX, Some times it was running for 45 minutes and once it was dropped and re-created its running with 2 minutes and same behaviour was happening for almost 10 procedures.
    We are running application, that it will run end-to-end application that creates whole database and tablespaces and other table objects such procedures, functions and loading table and updation table and also index creations.
    And we are running update statistics, reorg and also runstats at every appropriate place.

    By doing snapshot for the tables while executing of these procedures and the tables related to the procedures are going for tablescan instead of index scan and the once procedures was dropped and re-created then after its going for index scan and after you run as many times these procedures, its running with in 2 minutes, so at the bottom line all these procedures needs one time drop and creation after it has been created first time, if the procedure has dropped and created then all these procedures are working fine.

    Our application is running by concurrent users we dont want to drop and re-create these procedures in middle of the transactions and that's not preferable also for us.
    All these procedures was built in SQL NOT IN C.
    I hope you understand my problem clearly.
    If any one knows about this problems or if you have any idea about this problem.
    Please let me know.We are facing with this problem for last one week.

    Thanks in advance.
    muthu
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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