Adhoc queries or the Interactive SQL facility (ISQL) can significantly affect how your database system performs.
You should do all ISQL work in AUTOCOMMIT ON mode, the default. In AUTOCOMMIT ON mode, ISQL internally issues a COMMIT WORK, thus freeing DB2 Server for VSE & VM resources between query requests for possible use by others.
The COMMIT WORK is done immediately after a statement completes successfully. The only exception is for INSERT, DELETE, or UPDATE statements that change more than one row. For these statements, ISQL will give you a chance to rollback.
Use AUTOCOMMIT OFF mode, if you must have explicit control over committing work. For example, if two SQL statements update data in two tables simultaneously (as with debit and credit operations) and these updates must be synchronized to prevent inconsistent data, use AUTOCOMMIT OFF mode. If you are using this mode, package SQL statements into an ISQL routine so that terminal read delays are minimized or eliminated.
You can cancel any SQL statement if it is still in progress by issuing the ISQL CANCEL statement. (This statement causes a ROLLBACK WORK RELEASE to be executed.) That is, you can enter CANCEL if you are prompted to clear the screen, or prompted to enter CANCEL. You can also enter CANCEL to any ISQL message requesting a reply.
To minimize contention on shared resources, do all adhoc query work with the isolation level set to cursor stability (CS) unless the work being performed requires the repeatable read (RR) isolation level to ensure consistent data.
Alternatively, if it is not important that the data you are reading has necessarily been committed, consider setting the isolation level to uncommitted read (UR).
Adhoc users should be aware that when they are viewing a query result they may be delaying other users especially if querying the catalog tables.
If a long series of adhoc queries is expected against certain data in a large database, it may be best to copy that data into one or more temporary tables and query the copy. The queries will run faster and indexes can be created without being concerned with the effect of additional index maintenance on production work that is updating the data.
If a series of adhoc queries is expected against data in several related tables, consider creating a temporary table that contains the joined results of those tables. Queries run against this temporary table will run faster and be easier to formulate. An added benefit is that the temporary table can be created in a PRIVATE DBSPACE where locking overhead during query execution is negligible. The INSERT using subselect form of the SQL INSERT statement can be used to create the copy.
Instead of giving end users access to an entire table, provide them with a view on just the portion of the table that they need. In addition to the security benefits, this is an effective strategy for reducing the amount of processor and input/output usage that can be generated by indiscriminate querying of the data.