Including multiple SQL statements in one query

Start of change You can include multiple SQL statements on the SQL query panel. The exceptions are CALL and CREATE PROCEDURE statements. These statements must be used alone in a query. End of change

About this task

Start of change If confirmation prompts are enabled, a confirmation prompt is displayed after every COMMIT statement and after the last statement in the query. However, some SQL statements, such as SET, apply to the QMF session or environment and do not cause a confirmation prompt to be displayed. If the query does not contain a COMMIT statement, a single confirmation prompt is displayed. Your response to the prompt applies to all changes that are made by the query. End of change

Start of change If a query contains multiple statements that insert, update, or delete rows in the database object, your response to the confirmation prompt applies to all of the database updates. The information in the confirmation prompt contains the total number of rows that are updated by all of the statements in the query. If the query does not contain statements that insert, update, or delete rows and does not delete objects from the database, a confirmation prompt is not displayed. If the query does not contain statements that insert, update, or delete rows but deletes an object from the database, a DROP confirmation prompt is displayed. Your response to the prompt in this case applies to both the DROP statement and the other statements in the query. End of change

Any variable value that you supply for the query applies to all SQL statements in the query that include a variable with that name. For example, if you supply a value of "Q.STAFF" for the variable &TABLE in the following query, both the UPDATE and INSERT statement change the Q.STAFF table:

UPDATE &TABLE                                       
SET ID=53                                                
WHERE NAME='HANES';                                      
INSERT INTO &TABLE                                  
VALUES (42, 'GRIMLEY', 15, 'SALES', 10, 19264.50, 656.34)

Start of change If the query contains multiple statements and one of the statements fails, processing stops and no subsequent statements are run. If statements before the failing statement changed the database, these changes are rolled back (not applied to the database) unless the query contains a COMMIT statement. If the query contains one or more COMMIT statements, all database changes that occurred before the SQL error and after the last successful COMMIT statement are rolled back. Some statements, such as SET, apply to the QMF™ session or environment and therefore are not rolled back in error situations. The DSQEC_RUN_MQ global variable controls whether queries with multiple SQL statements are allowed. To run a query with multiple statements, ensure that each statement is separated by a semicolon; then set the DSQEC_RUN_MQ global variable to 1 and run the query. When the variable is set to zero, all statements after the first semicolon are ignored. End of change

The maximum allowed length of an SQL query that can be run by a RUN QUERY command is determined by the database to which you are connected when you issue the command:

In DB2® for z/OS®, SQL queries can be up to 2 MB long when the DSQEC_SQLQRYSZ_2M global variable is set to 1. When the variable is set to 0, the maximum size is 32 KB.
In DB2 for iSeries and DB2 for Linux, UNIX, and Windows, SQL queries can be up to 65 KB when the DSQEC_SQLQRYSZ_2M global variable is set to 1. When the variable is set to 0, the maximum size is 32 KB.
DB2 for VSE and VM, SQL queries are limited to 8 KB regardless of how the DSQEC_SQLQRYSZ_2M global variable is set.

Procedure

To include multiple statements in a SQL query:

Set the DSQEC_RUN_MQ global variable to 1:
```
SET GLOBAL (DSQEC_RUN_MQ=1
```
When the variable is set to 0, all statements after the first statement in the query are ignored when you issue the RUN QUERY command.
Place a semicolon at the end of each SQL statement on the panel. No semicolon is required after the last SQL statement. For example, the following query issues an UPDATE statement that updates the product number that is associated with project number 1401 to 20 in the Q.PROJECT sample table. The UPDATE statement is followed by an INSERT statement that adds a row for a new employee named GRIMLEY in the Q.STAFF sample table:
```
UPDATE Q.PROJECT                                       
SET PRODNUM=20                                                
WHERE PROJNO='1401';                                      
INSERT INTO Q.STAFF                                  
VALUES (42, 'GRIMLEY', 15, 'SALES', 10, 19264.50, 656.34)
```