There are two ways to write SQL one is Static SQL which get job done in most case and second one is Dynamic SQL for rest of complex stuff.
- In static SQL statement most part of statement is hard-coded like table names and Column names.This part of statement can not be changed at runtime.
- The only information that can be specified at runtime are values in the WHERE clause of the SQL statement using host variables.
- The access plan for a static SQL is generated as soon as code is compiled.
- Once an access plan is generated, it is stored in the database.
- This access plan is persistent and reusable as long as the same static SQL statement is issued.
Here is an example of Static SQL.
SELECT CUSTNAM, CUSTCTY, CUSTST INTO :Name, :City, :State FROM CUSTMAST WHERE Custno = :Cust
- In Dynamic SQL statement every part of statement can be specified at runtime. Nothing is fixed.
- Dynamic SQL is stored in a STRING and so based on program logic any part of SQL statement like table name,column name can be changed at runtime.
- At time of code compilation system does not know what will be the final SQL statement to run so no access plan is created at compilation time.
- So access plan is decided at run time before executing SQL statement and of course this will cause some overhead on system.
What is “ACCESS PLAN”?
The term “ACCESS PLAN” is valid for both Static and Dynamic SQL statements. When ever there is some SQL Statement to execute DB2 query optimizer examines the SQL statement and determines the most efficient way to execute it. Many decisions need to be considered by the optimizer, such as
- Is the query syntax valid and is the query semantically correct?
- How can the query be rewritten so that it can be more easily optimized?
- What is the best index or combination of indexes to use?
- For queries that join tables, in what order should they be joined to minimize disk I/O or optimize memory usage?
- And I think lot of other stuff like this.
There are 2 options to run Dynamic SQL
- EXECUTE IMMEDIATE
- PREPARE and EXECUTE
In coming articles we will discuss about these options in detail.