Non-SELECT Dynamic SQL

Non- SELECT Dynamic SQL

Non- SELECT dynamic SQL is the second of the four classes of dynamic SQL. You use it to explicitly prepare and execute SQL statements in an application program.

This class of dynamic SQL uses PREPARE and EXECUTE to issue SQL statements. As its name implies, non- SELECT dynamic SQL cannot issue the SELECT statement. Therefore, this class of dynamic SQL cannot query tables.

Listing 12.2 shows a simple use of non- SELECT dynamic SQL that DELETE s rows from a table.

Listing 12.2. A COBOL Program Using Non- SELECT Dynamic SQL

 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         PREPARE STMT1 FROM :STRING-VARIABLE;     END-EXEC.     EXEC SQL         EXECUTE STMT1;     END-EXEC.         .         .         . 

As I noted before, you can replace the DELETE statement in this listing 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 

Non- SELECT dynamic SQL can use a powerful feature of dynamic SQL called a parameter marker , which is a placeholder for host variables in a dynamic SQL statement. In Listing 12.3, a question mark is used as a parameter marker, replacing the 'A00' in the predicate. When the statement is executed, a value is moved to the host variable ( :TVAL ) and is coded as a parameter to the CURSOR with the USING clause. When this example is executed, the host variable value replaces the parameter marker.

Listing 12.3. Non- SELECT Dynamic SQL Using Parameter Markers

 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 +40 TO STRING-VAR-LEN.     MOVE "DELETE FROM DSN8810.PROJ WHERE DEPTNO = ?"         TO STRING-VARIABLE.     EXEC SQL         PREPARE STMT1 FROM :STRING-VARIABLE;     END-EXEC.     MOVE 'A00' TO TVAL.     EXEC SQL         EXECUTE STMT1 USING :TVAL;     END-EXEC. 

Non- SELECT dynamic SQL can provide huge performance benefits over EXECUTE IMMEDIATE . Consider a program that executes SQL statements based on an input file. A loop in the program reads a key value from the input file and issues a DELETE , INSERT , or UPDATE for the specified key. The EXECUTE IMMEDIATE class would incur the overhead of a PREPARE for each execution of each SQL statement inside the loop.

Using non- SELECT dynamic SQL, however, you can separate PREPARE and EXECUTE , isolating PREPARE outside the loop. The key value that provides the condition for the execution of the SQL statements can be substituted using a host variable and a parameter marker. If thousands of SQL statements must be executed, you can avoid having thousands of PREPARE s by using this technique. This method greatly reduces overhead and runtime and increases the efficient use of system resources.

Non- SELECT Dynamic SQL Guidelines

When developing non- SELECT dynamic SQL programs, heed 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 As Many Parameter Markers As Necessary

A prepared statement can contain more than one parameter marker. Use as many as necessary to ease development.

Execute Prepared Statements Multiple Times in a Unit of Work

After a statement is prepared, you can execute it many times in one unit of work without issuing another PREPARE . When you're using non- SELECT dynamic SQL, keep this guideline in mind and avoid the PREPARE verb as much as possible because of its significant overhead.

Know the Difference Between EXECUTE IMMEDIATE and Non- SELECT Dynamic SQL

You must understand the difference between EXECUTE IMMEDIATE and non- SELECT dynamic SQL before development. EXECUTE IMMEDIATE prepares the SQL statement each time it is executed, whereas non- SELECT dynamic SQL is prepared only when the program explicitly requests it. Using non- SELECT dynamic SQL can result in dramatic decreases in program execution time. For this reason, favor non- SELECT dynamic SQL over EXECUTE IMMEDIATE when issuing an SQL statement multiple times in a program loop.