Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: Dynamic SQL executes in 2 seconds. Same static SQL executes 15 minutes

    Hi,
    I have a strange performance problem. On my production system DB2 ESE v8.2 fp11 on Linux executing SQL from Command Window SQL runs for 2 seconds. But the same SQL through CICS application using static SQL (use of SQL package) it runs for 15 minutes and occupies 100% of DB2 computer CPU during execution.

    What should I check on my production environment? What are conditions that influence so dramatically to static SQL and that does not impact dynamic SQL?

    To be even more strange the same program runs for 2 seconds on test environment as well as Command Window execution. So it go to be something different on production environment. What should I check first?

    Thanks,
    Grofaty
    Last edited by grofaty; 04-20-08 at 04:48.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    I'd think of statistics that influence the access path.
    Do you have host variables in your static SQL?
    What's the (static) access path (Explain info)?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Peter,
    1. On both systems statistics are up to date.
    2. Yes I have host variables in static SQL.
    3. Can you please provide more info how to create explain to get access path for static SQL?
    Thanks,
    Grofaty

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Run the explain using parameter markers (?) in the SQL predicates instead of literals.

    Select * from emp where empno = ?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by grofaty
    2. Yes I have host variables in static SQL.
    In that case, you will have to "convince" the optimizer to use a different access path, viz. the one used by the dynamic query.

    So, run explain both with parameter markers ("?") and with explicit constants, and compare the access paths, especially the index(es) used in the two cases.

    Most of the time, you can change the optimizer's opinion about an index that's being used and shouldn't by a simple query rewrite. Namely by making the condition on the (first) column in that index look "less efficient", e.g. by adding "+ 0" to its right-hand side (if numeric).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Mar 2007
    Posts
    72

    Dynamic SQL executes in 2 seconds. Same static SQL executes 15 minutes

    Quote Originally Posted by Peter.Vanroose
    In that case, you will have to "convince" the optimizer to use a different access path,... Namely by making the condition on the (first) column in that index look "less efficient", e.g. by adding "+ 0" to its right-hand side (if numeric).
    How would you code that? where integer_column = +0?

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by pagwu
    How would you code that?
    If you originally had
    Code:
    where integer_column = ?
    you change that to
    Code:
     where integer_column = ? + 0
    (The question mark can be either a host variable, or a constant, or even an expression.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    thank you very much for your tips. I solved the problem.

    I have found out that SQL was very poorly written. I have rewritten SQL and now it works for 1 second on test and also on production environment.

    I have also found out that explain on test and production system can be very different. Test computer is 20% faster that production that explains why there can appear such a problem.
    Thanks a lot. No more help is needed for this problem,
    Grofaty

Posting Permissions

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