Saving data using the regular and fast save methods

When you are saving query results data to a database, you can choose to save the data using a "regular save" or a "fast save" method.

When saving your query results data using a "regular save" method, the interface that you are using (QMF™ for Workstation or QMF for WebSphere®) saves the retrieved query results to the database using an individual SQL INSERT statement for each row of data. This type of save can be expensive in terms of performance due to the overhead of passing large amounts of data back to the database.

When saving your query results data using a "fast save" method, all the processing occurs at the database. There is no further passing of data between the QMF interface and the database. The QMF interface adds SQL to the original query, the query is rerun and the data is saved directly into the specified table(s). Saving query results data using the "fast save" process significantly improves performance when a large amount of data is to be saved.

Selecting the fast save method

If you elect to save your query results data using a "fast save" method, all ORDER BY clauses remain in the query that is run at the database. Keeping the ORDER BY clause retains the row order of the query results data.

In some instances on DB2® for z/OS® systems, keeping the ORDER BY clause can cause an SQL error. This error results from the QMF interface prepending an insert statement to the query. The original query is now a subordinate query and thus is not permitted to have an ORDER BY clause.

For example if a user enters the following query:

Select [Columns] from [SourceTable]
Order By [Column]
The query runs without error. The user then selects to save it into a given table (e.g. DestTable) using the fast save mode. The QMF interface prepends a line to the query as follows:
Insert into [DestTable]
Select [Columns] from [SourceTable]
Order By [Column]

This new SQL is invalid in certain DB2 for z/OS systems because the original query is now a subordinate query and thus is not permitted to have an ORDER BY clause.

Setting the regular or fast save method

To set the regular or fast save method:

  1. With query results in the editor window, select Export from the Results menu. The Export Query Results window opens. Select Database from the Export Query Results window. Enter values in the Export destination fields. Click Next. The Set up save options page opens.
  2. Select one of the following:
    • Click Regular (send retrieved data back to data source and save using an insert statement for each row) to select the regular save method.
    • Click Fast (directly save results at the data source by re-running the query at the data source) to select the fast save method that retains any ORDER BY clause(s).
    • Select the Remove ORDER BY clause(s) from original SQL before re-running query check box to select the fast save method that runs the query without the ORDER BY clauses. Use this option when the removal of ORDER BY clause(s) does not influence the returned data.

Selecting the fast save method with the remove ORDER BY clause option

If you elect to save your query results data using the "fast save" method, you can choose to have the QMF interface remove all ORDER BY clauses from the query before the query is sent to the database. In keeping with the example used in Selecting the fast save method, the following query would be sent: 
Insert into [DestTable]
Select [Columns] from [SourceTable]
In most cases, removing the ORDER BY clauses has no effect on the query result data that is saved. In some cases, however, removing the ORDER BY clause from the query can affect the result set that is saved. This will happen if the original query limits the number of rows returned. For example, removing the ORDER BY clause from the following query changes the results: 
select x,y,z from table
order by x
fetch first 10 rows only

Setting the regular or fast save method for procedures

You can set up a regular or fast save method for your procedure query results data using the global variable DSQQW_FST_SV_DATA. The default value for DSQQW_FST_SV_DATA is the value zero (0).

  1. Select View > Preferences. Expand the QMF folder. Select Global Variables. The Global variables window opens.
  2. From the Global variables window select the DSQQW_FST_SV_DATA variable.
  3. Select or specify:
    • 0 - Regular save
    • 1 - Fast save with ORDER BY clause(s) stripped
    • 2 - Fast save with ORDER BY clause(s)

You can also set the global variable in either of the following ways:

  • Use the Set Global procedure command using either QMF interface. For example SET GLOBAL ( DSQQW_FST_SV_DATA = 2 ).
  • You can also set DSQQW_FST_SV_DATA to 2 using SetGlobalVariables() in the API for either QMF interface.