Temporary result writer with DB2^® Multisystem

 

Temporary result writers are system-initiated jobs that are always active.

On the system, temporary result writers are paired jobs called QQQTEMP1 and QQQTEMP2. Temporary result writers handle requests from jobs that are running queries. These requests consist of a query (of the query step) to run and the name of a system temporary file to fill from the results of the query. The temporary result writer processes the request and fills the temporary file. This intermediate temporary file is then used by the requesting job to complete the original query.

The following example shows a query that requires a temporary result writer and the steps needed to process the query.

SQL statement:

SELECT COUNT(*)
             FROM DEPARTMENT a, EMPLOYEE b              WHERE a.ADMRDEPT = b.WORKDEPT                 AND b.JOB = 'Manager'
 

OPNQRYF command:

 OPNQRYF FILE((DEPARTMENT) (EMPLOYEE))
                       FORMAT(FMTFILE)
                       MAPFLD((CNTFLD '%COUNT'))
                       JFLD((1/ADMRDEPT 2/WORKDEPT))
                       QRYSLT('2/JOB = 'Manager')

WORKDEPT is the partitioning key for EMPLOYEE, but ADMRDEPT is not the partitioning key for DEPARTMENT. Because the query must be processed in two steps, the optimizer splits the query into the following steps:

  INSERT INTO SYS_TEMP_FILE   SELECT a.DEPTNAME, a.ADMRDEPT   FROM DEPARTMENT a

and

  SELECT COUNT(*) FROM SYS_TEMP_FILE x, EMPLOYEE b   WHERE x.ADMRDEPT = b.WORKDEPT AND b.JOB = 'Manager'

If a temporary result writer is allowed for the job (controlled by the Change Query Attributes (CHGQRYA) options), the optimizer:

1. Creates the temporary file (SYS_TEMP_FILE) into library QRECOVERY.

2. Sends the request that populates SYS_TEMP_FILE to the temporary result writer.

3. Continues to finish opening the final query (while the temporary result writer is filling the temporary file).

4. After the final query is opened, waits until the temporary result writer has finished filling the temporary file before returning control to its caller.

• Temporary result writer job: Advantages with DB2 Multisystem
  The advantage of using a temporary result writer job in processing a request is that the temporary result writer can process its request at the same time (in parallel) that the main job is processing another step of the query.

• Temporary result writer job: Disadvantages with DB2 Multisystem
  The temporary result writer also has disadvantages that must be considered when you determine its usefulness for queries.

• Control of the temporary result writer with DB2 Multisystem
  By default, queries do not use the temporary result writer. Temporary result writer usage, however, can be enabled by using the Change Query Attributes (CHGQRYA) command.

 

Parent topic:

Query design for performance with DB2 Multisystem

 

Related concepts

Changes to the Change Query Attributes command with DB2 Multisystem