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 ?

thanks.
Anil

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.

This is what I think:

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

Cheers
Sathyaram