EXECUTE IMMEDIATE

 <  Day Day Up  >  

EXECUTE IMMEDIATE

EXECUTE IMMEDIATE implicitly prepares and executes complete SQL statements coded in host variables .

Only a subset of SQL statements is available when you use the EXECUTE IMMEDIATE class of dynamic SQL. The most important SQL statement that is missing is the SELECT statement. Therefore, EXECUTE IMMEDIATE dynamic SQL cannot retrieve data from tables.

If you don't need to issue queries, you can write the SQL portion of your program in two steps. First, move the complete text for the statement to be executed into a host variable. Second, issue the EXECUTE IMMEDIATE statement specifying the host variable as an argument. The statement is prepared and executed automatically.

Listing 12.1 shows a simple use of EXECUTE IMMEDIATE that DELETE s rows from a table. The SQL statement is moved to a string variable and then executed.

Listing 12.1. A COBOL Program Using EXECUTE IMMEDIATE
 WORKING-STORAGE SECTION.         .         .         .     EXEC SQL         INCLUDE SQLCA     END-EXEC.         .         .         .     01  STRING-VARIABLE.         49  STRING-VAR-LEN     PIC S9(4)   USAGE COMP.         49  STRING-VAR-TXT     PIC X(100).         .         .         . PROCEDURE DIVISION.         .         .         .     MOVE +45 TO STRING-VAR-LEN.     MOVE "DELETE FROM DSN8810.PROJ WHERE DEPTNO = 'A00'"         TO STRING-VARIABLE.     EXEC SQL         EXECUTE IMMEDIATE :STRING-VARIABLE      END-EXEC.         .         .         . 

You can replace the DELETE statement in Listing 12.1 with any of the following supported statements:

 

 ALTER COMMENT ON COMMIT CREATE DELETE DROP EXPLAIN GRANT INSERT LABEL ON LOCK TABLE REVOKE ROLLBACK SET UPDATE 

Despite the simplicity of the EXECUTE IMMEDIATE statement, it usually is not the best choice for application programs that issue dynamic SQL for two reasons. One, as I mentioned, EXECUTE IMMEDIATE does not support the SELECT statement. Two, performance can suffer when you use EXECUTE IMMEDIATE in a program that executes the same SQL statement many times.

After an EXECUTE IMMEDIATE is performed, the executable form of the SQL statement is destroyed . Thus, each time an EXECUTE IMMEDIATE statement is issued, it must be prepared again. This preparation is automatic and can involve a significant amount of overhead. A better choice is to code non- SELECT dynamic SQL using PREPARE and EXECUTE statements.

EXECUTE IMMEDIATE Guidelines

When developing dynamic SQL programs that use EXECUTE IMMEDIATE , observe the following guidelines.

Verify Dynamic SQL Syntax

Verify that the SQL statement to be executed with dynamic SQL uses the proper SQL syntax. This way, you can reduce the overhead incurred when improperly formatted SQL statements are rejected at execution time.

Use EXECUTE IMMEDIATE for Quick, One-Time Tasks

The EXECUTE IMMEDIATE class of dynamic SQL is useful for coding quick-and-dirty one-time processing or DBA utility-type programs. Consider using EXECUTE IMMEDIATE in the following types of programs:

  • A DBA utility program that issues changeable GRANT and REVOKE statements

  • A program that periodically generates DDL based on input parameters

  • A parameter-driven modification program that corrects common data errors

Declare EXECUTE IMMEDIATE Host Variables Properly

The definition of the host variable used with EXECUTE IMMEDIATE must be in the correct format. Assembler, COBOL, and C programs must declare a varying-length string variable. FORTRAN programs must declare a fixed-list string variable. PL/I programs can declare either type of variable.

 <  Day Day Up  >  


DB2 Developers Guide
DB2 Developers Guide (5th Edition)
ISBN: 0672326132
EAN: 2147483647
Year: 2004
Pages: 388

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net