Including multiple SQL statements in one query
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.
About this task
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.
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.
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)
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.
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.
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)