DB2 DYNAMIC SQL

There are two ways to write SQL: Static SQL, which gets the job done in most cases, and the second one is Dynamic SQL for the rest of the complex stuff.

Static SQL

1. In a static SQL statement, most of the statement is hard-coded like table names and Column names. This part of the statement can not be changed at runtime.
2. The only information that can be specified at runtime values in the WHERE clause of the SQL statement using host variables.
3. The access plan for a static SQL is generated as soon as code is compiled.
4. Once an access plan is generated, it is stored in the database.
5. 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

Dynamic SQL

1. In a Dynamic SQL statement, every part of the statement can be specified at runtime. Nothing is fixed.
2. 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.
3. At the time of code, the compiler does not know the final SQL statement to run, so no access plan is created at compilation time.
4. So access plan is decided at run time before executing the SQL statement, and of course, this will cause some overhead on the system.

What is “ACCESS PLAN”?

The term “ACCESS PLAN” is valid for both Static and Dynamic SQL statements. Whenever 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

1. Is the query syntax valid, and is the query semantically correct?
2. How can the query be rewritten so that it can be more easily optimized?
3. What is the best index or combination of indexes to use?
4. For queries that join tables, in what order should they be joined to minimize disk I/O or optimize memory usage?
5. And I think a lot of other stuff like this.

There are 2 options to run Dynamic SQL

1. EXECUTE IMMEDIATE
2. PREPARE and EXECUTE

EXECUTE IMMEDIATE:

EXECUTE IMMEDIATE is a simple option to execute a Dynamic SQL.

Here is an example

TableName = 'CUSTOMER';

SQLString = 'DELETE FROM '+ %TRIM(TableName) ;

EXECUTE IMMEDIATE : SQLString

This is a straightforward example.

1. Program gets a TableName as input parameter, and program logic deletes all records from that table.
2. Based on the TableName variable, a SQL DELETE statement is assembled and saved in the SQLString variable.
3. In the final step, the SQLString variable is passed to EXECUTE IMMEDIATE as the host variable, and all is done.

So what does actually EXECUTE IMMEDIATE do?

1. Create an access plan based on SQL Statement provided. A new “access plan” is created each time any statement is executed.
2. If everything is fine, run the SQL Statement.
3. Delete the access plan created for this SQL Statement.

So EXECUTE IMMEDIATE perform both PREPARE (the access plan) and EXECUTE (the SQL statement) in a single step. You can run almost any DML (except SELECT statement) or DDL SQL statement using EXECUTE IMMEDIATE.

You can not use EXECUTE IMMEDIATE for the SELECT statement, so you can not use EXECUTE IMMEDIATE to declare a CURSOR.

Here is the list of SQL statements allowed with EXECUTE IMMEDIATE:

EXECUTE IMMEDIATE should be used when the SQL statement only needs to be executed once or infrequently. The cost of creating a new access plan every time to execute the SQL statement is a little bit on the higher side.

PREPARE and EXECUTE

• If you need to run the same dynamic SQL statement more than once per job using the PREPARE — EXECUTE statements
• using EXECUTE IMMEDIATE incurs the unnecessary cost of re-preparing the SQL statement each time
• PREPARE is like a mini-compile of its own.
• If required info to PREPARE a statement is not available at COMPILE TIME, then the statement will be PREPARED at run time.

PREPARE and EXECUTE: The Two-step process

STEP 1: The PREPARE: With PREPARE statement, the DB2 query system examines the SQL statement and determines the most efficient method to retrieve the requested data. Many decisions need to be considered by the optimizer, such as

• Is the query syntax 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?
• The method by which DB2 chooses to retrieve the data is called an access plan.

STEP 2: The EXECUTE: now, based on analysis of STEP1 DB2 engine runs the SQL and gets the result.

These are the SQL statements allowed for PREPARE statement

Yes, “select-statement” is allowed

From a coding perspective, PREPARE-EXECUTE has the following advantage over EXECUTE IMMEDIATE.

• You can use PREPARE-EXECUTE for SQL SELECT statement and to declare a cursor.
• PREPARE-EXECUTE gives options to use Parameter Markers which comes very handy when working on long SQL statements.

Examples

• Without Parameter Markers

SqlString= 'DELETE FROM EMPLOYEE WHERE EMPNAME = ''' + %Trim(EmployeeName)+'''';
EXEC SQL PREPARE SqlSTMT FROM :SqlString;
EXEC SQL EXECUTE SqlSTMT ;

• With Parameter markers

SqlString= 'DELETE FROM EMPLOYEE WHERE EMPNAME = ? and EMPID = ? ; 
// "?" represents parameter marker 

EXEC SQL PREPARE SqlSTMT FROM :SqlString;

EXEC SQL EXECUTE SqlSTMT using :EmployeeName , :EmployeeId 
// Each host variable will replace respective "?"

• SELECT statement/DECLARE CURSOR Without Parameter Markers

SqlString= 'SELECT * FROM EMPLOYEE WHERE EMPNAME = ''' + %Trim(EmployeeName)+'''';
EXEC SQL PREPARE SqlSTMT FROM :SqlString;
EXEC SQL DECLARE @C1 cursor for SqlSTMT ;
// Now process @C1 cursor as normal cursor

• SELECT statement/DECLARE CURSOR With Parameter Markers

SqlString= 'SELECT * FROM EMPLOYEE WHERE EMPNAME = ? and EMPID = ? ; 
// "?" represents parameter marker 

EXEC SQL PREPARE SqlSTMT FROM :SqlString;

EXEC SQL DECLARE @C1 cursor for SqlSTMT;

EXEC SQL OPEN @C1 using :EmployeeName , :EmployeeId ;
// Each host variable will replace respective "?"