Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2005
    Posts
    24

    Unanswered: static and dynamic sql.......help

    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

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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

    Quote Originally Posted by db1db2
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Nov 2005
    Posts
    24
    Thanks a lot,sir.
    Iam a dba in making ,and i always had this doubt.

    -Db

Posting Permissions

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