SQL DYNAMIC SQL

DB2 DYNAMIC SQL : PREPARE and EXECUTE


  • If you need to run the same dynamic SQL statement more than once per job use 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 on COMPILE TIME than 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 get the result.

These are the SQL statements allowed for PREPARE statement

Yes “select-statement” is allowed

ALLOCATE CURSOR
ALTER
ASSOCIATE LOCATORS
COMMENT
COMMIT
CREATE
DECLARE GLOBAL
TEMPORARY TABLE
DELETE
DROP
EXPLAIN
FREE LOCATOR
GRANT
HOLD LOCATOR
INSERT
LABEL
LOCK TABLE
MERGE
REFRESH TABLE
RELEASE SAVEPOINT
RENAME
REVOKE
ROLLBACK
SAVEPOINT
select-statement
SET CURRENT DEGREE
SET CURRENT DEBUG MODE
SET CURRENT DECFLOAT
ROUNDING MODE
SET CURRENT LOCALE LC_CTYPE
SET CURRENT MAINTAINED TABLE
TYPES FOR OPTIMIZATION
SET CURRENT OPTIMIZATION HINT
SET CURRENT PRECISION
Start of changeSET CURRENT QUERY ACCELERATIONEnd of change
SET CURRENT REFRESH AGE
SET CURRENT ROUTINE VERSION
SET CURRENT RULES
SET CURRENT SQLID
SET ENCRYPTION PASSWORD
SET PATH
SET SCHEMA
Start of changeSET SESSION TIME ZONEEnd of change
SIGNAL
TRUNCATE
UPDATE
From coding prospective PREPARE-EXECUTE has 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

  1. With out Parameter Markers

    
    SqlString= 'DELETE FROM EMPLOYEE WHERE EMPNAME = ''' + %Trim(EmployeeName)+'''';
    EXEC SQL PREPARE SqlSTMT FROM :SqlString;
    EXEC SQL EXECUTE SqlSTMT ;
    
  2. 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 "?"
    
    
  3. SELECT statement/DECLARE CURSOR With out 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
    
  4. SELECT statement/DECLARE CURSOR With Parameter Markers

    
    SqlString= 'DELETE 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 "?"
    
    

Read more about cursors HERE


IBM i developer.

View Comments
There are currently no comments.