DB2 DYNAMIC 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.
SELECT CUSTNAM, CUSTCTY, CUSTST INTO :Name, :City, :State
FROM CUSTMAST WHERE Custno = :Cust
  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.
  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.
  1. EXECUTE IMMEDIATE
  2. PREPARE and EXECUTE

EXECUTE IMMEDIATE:

  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.
  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.

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.
  • 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.
  • 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 "?"

--

--

--

A programmer.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Why we invested in our Platform early and how we did it

Setup Visual Studio Code for C# Development on Linux, Ubuntu

Namecheap, Webflow, Auth0, Apollo, and the Whole 9 Yards

Tangram Flex Awarded Contract for Agility Prime

SERVERLESS ARCHITECTURE: FROM BUZZWORD TO REALITY

Azure AppInsights Release Annotations

The Story of Me Becoming A Front-End Developer in the Past Two Years

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Sumit goyal

Sumit goyal

A programmer.

More from Medium

History of SQL

SELECT statement for SQL

SELECT statement for SQL

How to Improve ServiceNSW COVID Case Alerts

SQL Server Primary Keys