Preprocessing of Dynamic Non-Queries

There are two methods for dynamic preprocessing of a non-query:

Using EXECUTE IMMEDIATE.
Using PREPARE and EXECUTE.

The first method can be used with any non-query; the second is only for those non-query commands that use sections at execution time.

Using EXECUTE IMMEDIATE

If you know in advance that a dynamic command will not be a query, you can dynamically preprocess and execute the command in one step, using the EXECUTE IMMEDIATE command. Figure 9-3 illustrates a procedure hosting a dynamic UPDATE STATISTICS command that can be handled in this fashion.

Subroutine UpdateStatistics 1 prompts the user for a table name 2 . The table name entered is assigned to the host variable CmdLine 3 to complete the UPDATE STATISTICS command. After the command is prepared and executed 4 , the transaction is terminated with a COMMIT WORK command 5 or a ROLLBACK WORK command 6 , depending on the value in SQLCA.SQLCODE. Terminating the transaction before accepting another table name and re-executing the UPDATE STATISTICS command releases any locks obtained and improves concurrency.

Figure 9-3 Procedure Hosting Dynamic Non-Query Commands

 





           SUBROUTINE UpdateStatistics                                     1





           CHARACTER*50      TableName



           EXEC SQL BEGIN DECLARE SECTION

           CHARACTER*1024    CmdLine

           EXEC SQL END DECLARE SECTION







           DO WHILE (TableName .NE. '/')



           WRITE (*,*) 'Enter table name or a / to stop > '               2

           READ(6,100) TableName

   100     FORMAT(A50)

           IF (TableName .NE. '/') THEN



           CmdLine ='UPDATE STATISTICS FOR TABLE '// TableName //  ;'     3



               EXEC SQL EXECUTE IMMEDIATE :CmdLine                        4



               IF (SQLCode .EQ. 0) THEN

                 EXEC SQL COMMIT WORK                                     5

               ELSE

                 EXEC SQL ROLLBACK WORK                                   6

               ENDIF



           ENDIF                  (* END OF IF TABLENAME *)



           END DO



           RETURN

           END                    (* END OF UPDATESTATISTICS PROCEDURE *)

Using PREPARE and EXECUTE

Use the PREPARE command to create and store a temporary section for the dynamic command:

   PREPARE CommandName FROM CommandSource

Because the PREPARE command operates only on sections, it can be used to dynamically preprocess only SQL commands executed by using sections. The DBE session management and transaction management commands can only be dynamically preprocessed by using EXECUTE IMMEDIATE.

With PREPARE, ALLBASE/SQL creates a temporary section for the command that you can execute one or more times in the same transaction by using the EXECUTE command:

   EXEC SQL PREPARE MyNonQuery FROM :DynamicCommand;



   I = MaxIterations

   DO WHILE (I .NE. 0)

   EXEC SQL EXECUTE MyNonQuery;

   I = I - 1

   END DO

As soon as you process a COMMIT WORK or ROLLBACK WORK command, the temporary section is deleted.

The program examined later in this chapter under "Sample Program Using PREPARE and EXECUTE" illustrates how to handle PREPARE and EXECUTE.

Preprocessing of Dynamic Non-Queries

Technical documentation

» Table of Contents

» Index

Using EXECUTE IMMEDIATE

Using PREPARE and EXECUTE