SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
X-21
Considerations—=_SQL_TM_node_vol
syncdepth
is the keyword SYNC1 (if you want the temporary files to use SYNCDEPTH 1), or
a simple file name (if you want the temporary files to use SYNCDEPTH 0, as
usual).
The file name must be a legal Guardian file name, but the file is not used and does
not need to exist.
Considerations—=_SQL_TM_node_vol

You can use the =_SQL_TM_node_vol DEFINE to change the location of
temporary tables that might require more space than available in the volumes
where the temporary tables would normally be located, causing file-system error
122.
NonStop SQL/MP normally places temporary tables in the same volume as the
outermost table in a join. For more information about temporary table placement,
see Temporary Tables on page T-4.

The disk process normally uses SYNCDEPTH 0 to access temporary SQL/MP
tables. This approach saves the overhead of checkpointing operations on the
tables to the backup DP2 process but can cause error 122 if a DP2 takeover
occurs during execution of a statement.
You can use =_SQL_TM_node_vol to specify SYNCDEPTH 1 for temporary
tables if avoiding a potential error 122 justifies the performance penalty for your
application.

=_SQL_TM_node_vol affects temporary tables for both serial and parallel plans.

If a =_SQL_EXE_USE_SWAPVOL DEFINE exists, =_SQL_TM_node_vol
overrides its effect for any temporary files on the specified system and volume.

If SMF is installed on your node and a query references a table by its logical name,
you can use =_SQL_TM_node_vol to:

Locate temporary tables on a particular physical volume in the same virtual
volume as the table

Locate temporary tables on a different virtual volume than the table

For parallel queries, only physical volumes are candidates for temporary tables. If
you specify a virtual volume for =_SQL_TM_node_vol when parallel execution is
on, the optimizer ignores this DEFINE.
Examples—=_SQL_TM_node_vol

This TACL command directs NonStop SQL/MP to create all temporary tables that
would normally go to volume \A.$B on volume \X.$Y instead:
31> ADD DEFINE =_SQL_TM_A_B, CLASS MAP, FILE \X.$Y.Z.NOFILE;