Using multiple database threads

To improve performance with commands following RUN QUERY commands that produce large answer sets, or to avoid storage issues when using the SAVE DATA command for large answer sets, you can run end user SQL commands on a separate database thread.

About this task

To use multiple threads, the optional second thread is controlled by the QMF program parameter DSQSMTHD (YES/NO; default: NO). This program parameter allows QMF Administrators to control whether QMF uses an additional database thread or executes on a single thread as it does by default.

The end user SQL that uses the second thread includes the following QMF commands:

  • RUN QUERY (except for RUN QUERY with the TABLE keyword)
  • DISPLAY TABLE
  • Commands that scroll reports (BOTTOM, TOP, FORWARD, BACKWARD, RIGHT and LEFT)
  • Commands that require complete reports (EXPORT DATA, SAVE DATA, PRINT REPORT, DPRE, CONNECT, ERASE TABLE)

When DSQSMTHD is set to YES, the value of the DSQAO_CURSOR_OPEN state global variable will reflect the status of the end user SQL second thread.

DSQSMTHD is applicable to QMF running under TSO (QMF CAF interface) only. DSQSMTHD settings are ignored when running QMF under CICS® and running QMF under the stored procedure interface.

When DSQMTHD is set to YES, the effect of global variable DSQEC_RESET_RPT (prompting if an incomplete report is outstanding) is changed. When you CONNECT to remote databases, or CONNECT to a new userid and password within the same data base, the second thread will also connect. Any open report objects must be completed prior to the CONNECT. DSQEC_RESET_RPT can be used to alert you to this situation.

While the ERASE TABLE command does not utilize the user thread, it continues to require that an incomplete data object be completed; this prevents deadlock and timeout situations.

In the case of an incomplete report (with DSQSMTHD=YES), the list of commands that will COMMIT the report (release locks) and delete the thread is:

  • RESET DATA
  • RUN QUERY (SELECT statements)
  • PRINT TABLE
  • DISPLAY TABLE
  • EXPORT TABLE
  • CONNECT
  • ERASE TABLE

Incomplete data prompting (DSQEC_RESET_RPT) will also occur (except for RESET DATA).

Note: The use of two threads per user can affect general Db2® resource consumption (CTHREAD limits, etc).