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')
INSERT INTO SYS_TEMP_FILE
SELECT a.DEPTNAME, a.ADMRDEPT
FROM DEPARTMENT a
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:
- Creates the temporary file (SYS_TEMP_FILE) into library QRECOVERY.
- Sends the request that populates SYS_TEMP_FILE to the temporary result writer.
- Continues to finish opening the final query (while the temporary result writer is filling the temporary file).
- After the final query is opened, waits until the temporary result writer has finished filling the temporary file before returning control to its caller.