Results 1 to 3 of 3

Thread: Prepare SQL

  1. #1
    Join Date
    Feb 2005

    Unanswered: Prepare SQL

    We are using DB2 ver 7.2 for Windows. The developers are developing lot of stored procedures using the stored procedure builder.
    I noticed that most of the stored procedures have a PREPARE SQL Stmt to generate the SELECT statements. Meaning, the SQL statements are dynamic and that the plan is created at run time.
    Doesn't a PREPARE have more overheads than a Static SQL ? Then, why is it such a common practice to use PREPARE stmt ? Even many of the books and manuals have sample SPs using PREPARE stmt ?
    Would it enhance the performance if the SPs are modifed to remove the PREPARE ? ANd how much gain is achived by removing the prepare ?


  2. #2
    Join Date
    May 2003
    I hope you are aware that DB2 version 7.2 is no longer supported by IBM.

    The only reason that I know about for using a PREPARE statement in a SP would be if the SQL statement itself is dyanmic (not known before hand). Otherwise you should just hardcode the SQL statement (but still use variables for the INTO clause and WHERE clause.

    One reason that books an manuals have PREPARE examples is that it is harder to do than regular SQL, so they want to be sure and explain how to do it. But PREPARE should not be used in an SQL SP if not needed.
    Last edited by Marcus_A; 02-23-05 at 12:54.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2001
    This is what I think:

    Always try to use Static SQL ... In case you can't use dynamic SQL

    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