Hi,

Does any one have a link or a document on the difference between a static and a dynamic SQL.

Can you please give me an example for both?

Thanks,
Db

A static SQL statement is one whose access path can be decided by the optimizer at bind time

The optimizer will not know the access path at bind time for dynamic SQL .

Example :

a) Static

SELECT col5 from tab1 where col1=<var>

Here the optimizer knows that it has to access tab1 to select col5 and the filtering is based on col1. So it can decide the best access path and is the access path does not change ie 'static' .


b) Dynamic
SET tabname1='tab1' ;
SET pred1='col1=100' ;
SET stmt1='select col9 from '||tab1||' where ' || pred1 ;
EXECUTE IMMEDIATE stmt1 ;

Here the values of tab1 and pred1 are not known until the runtime and therefore the optimizer chooses the access path 'dynamicaly' at runtime

Look at the appl dev guide in the db2 documentation

HTH

Sathyaram

Thanks a lot,sir.
Iam a dba in making ,and i always had this doubt.

-Db