SQL DYNAMIC SQL

DB2 DYNAMIC SQL : EXECUTE IMMEDIATE


EXECUTE IMMEDIATE is simple option to execute a Dynamic SQL.

Here is an example
TableName = 'CUSTOMER';
SQLString = 'DELETE FROM '+ %TRIM(TableName) ;
EXECUTE IMMEDIATE : SQLString


This is a very simple example.

  1. Program gets a TableName as input parameter and program logic delete all records from that table.
  2. Based on TableName variable a SQL DELETE statement is assembled and saved in SQLString variable.
  3. In final step SQLString variable is passed to EXECUTE IMMEDIATE as host variable and all done.
So what does actually EXECUTE IMMEDIATE do ?

The day has 200-105 question past delightfully. 200-105 question Delight itself, however, CISSP pdf is a weak term 300-208 questions CISSP pdf to express 300-208 questions the feelings of a naturalist who, for the first time, has been wandering by 200-105 question himself in a Brazilian 200-105 question CISSP pdf forest. Among the multitude of striking objects, the general luxuriance of the vegetation bears CISSP pdf away the victory. The CISSP pdf elegance of the grasses, the novelty of 300-208 questions the parasitical plants, the beauty of the flowers, the glossy 300-208 questions 300-208 questions green of 300-208 questions the foliage, CISSP pdf all tend to this 200-105 question end. 300-208 questions A most paradoxical mixture 300-208 questions of 200-105 question sound CISSP pdf and silence 200-105 question pervades the shady parts of the wood. 300-208 questions

The geology CISSP pdf of the surrounding country possesses little interest. Throughout the coast of Brazil, CISSP pdf and certainly for a considerable CISSP pdf space inland from the Rio Plata CISSP pdf CISSP pdf CISSP pdf to Cape St. 300-208 questions Roque, lat. 5S., 300-208 questions a distance of more than 2000 geographical 300-208 questions miles, wherever solid rock occurs, it CISSP pdf belongs to a granitic 300-208 questions formation. 300-208 questions 200-105 question The circumstance CISSP pdf 200-105 question of this enormous area being thus constituted of materials, which almost 200-105 question CISSP pdf every geologist believes have been crystallized by the action of heat under pressure, gives rise 200-105 question to many curious reflections. 200-105 question Was this effect produced 200-105 question beneath the depths of a profound ocean? Or did a covering of strata formerly extend over it, which has since been removed? Can we believe 200-105 question that 300-208 questions any power, 200-105 question action for 300-208 questions a time short of 200-105 question infinity, could have denuded the granite over so many thousand square leagues?

  1. Create a access plan based on SQL Statement provided.A new “access plan” is created each time any statement is executed.
  2. If every thing 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 SELECT statement so you can not use EXECUTE IMMEDIATE to declare a CURSOR.

Here is list of SQL statement allowed with EXECUTE IMMEDIATE:

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
SET CURRENT DEGREE
SET CURRENT DECFLOAT ROUNDING MODE
SET CURRENT DEBUG 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
SIGNAL
TRUNCATE
UPDATE

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


IBM i developer.

View Comments
There are currently no comments.