Hi Eric,
I guess the reason you haven’t got any replay on your question is that the answer is “it depends”.
Historically, DB2 on z/OS have recommended static SQL but over the last couple of versions and the introduction of the statement cash and tools to look at it that have changed.
This is just to give you a flavour of the answer to your question. There is a lot more to consider.
Why use static SQL:
1)The access path is decided at bind time and if the statistics in the catalogue is “correct” the optimizer choose the best access path (mostly) for it.
2)You do not have any overhead of choosing the access path at execution time. This is important when we have SQL statements that need to be as quick as possible. E.g. ATM-machines transactions.
3)You easily check the chosen access path in the PLAN_TABLE
4)You do not need to run runstats (costly) that often to update the statistics in the catalogue.
Drawbacks of using static SQL:
1)You need to make sure that the chosen access path at bind is what you hade expected and performs well.
2)You probably end up with many SQL statements in your program when you have multiple predicates in the SQL. This to get good access paths for them. (no OR between predicates, no range predicates if not needed)
Why use dynamic SQL:
1)Give you flexibility in creating the SQL in the program. Gives you fewer statements to maintain but perhaps a more complex logic in the program.
2)Can take advantage of new catalogue statistics and changes access path to a better if the data have changed in size or pattern.
Drawbacks of using dynamic SQL:
1)You pay an extra cost (cpu & elapsed) for each SQL statement when the optimizer checks the access path for the SQL. This is not significant if the elapsed time for the SQL is long.
2)Less control of the access path that the SQL that have been using (requires that you use EXPLAIN YES at bind).
To address some of the disadvantages of dynamic SQL DB2 for Z introduced the statement cash. The statement cash hold the latest used SQL statements and the access path chosen for them. So this takes away the need for at mini bind the second time the same dynamic SQL will be executed. But be aware it needs to be exactly identical to the SQL in the cash. If not there will be a new mini bind.
If you need to change the value in the predicate you can use “place holders” for your variables. But I do not how you do that in Java. My coding skill froze at PL/I and Cobol back in the 80s and the us of static SQL.
Well, this post did not answer question but I hope I give you a favour of that each installation, application and so on have there unique environment and you have to evaluate the solution accordingly.
If you find any benchmarks you need to check how well the benchmark environment conforms to yours.
Regards, Christer J
