A work table file is a file that stores temporary information that is generated when the following operations are performed:

• Execution of SQL statements^#
• Batch index creation
• Index re-creation
• Index reorganization
• Execution of the rebalancing utility

#

A work table file is used at the time that a specific SQL statement is executed, such as when you join multiple tables in a SELECT statement for retrieval, or execute CREATE INDEX. The following types of SQL processing require work table files:

1. Retrieval specifying the UNION [ALL] or EXCEPT [ALL] clause
2. DROP SCHEMA
3. DROP TABLE
4. DROP INDEX
5. Revocation of access privileges by using REVOKE
6. CREATE INDEX
7. Creation of a list from a base table with an ASSIGN LIST statement
8. Specification of the following in a SELECT statement:
   
   • Retrieval by joining multiple tables
   • Specification of an ORDER BY clause in a column for which no index is defined
   • Specification of an ORDER BY clause in a row-partitioned table
   • Specification of a value expression containing a set function in the selection expression (applies only to a HiRDB parallel server configuration)
   • Specification of a value expression that includes the window function COUNT(*) OVER() in the selection expression
   • Specification of a GROUP BY clause
   • Specification of a DISTINCT clause
   • Specification of a retrieval condition based on multiple columns for which an index is defined
   • Specification of a retrieval condition for a column for which a repetition column index is defined
   • Specification of the facility for batch acquisition from functions provided by plug-ins for the SQL optimization option, and specification of and searching for functions provided by plug-ins that use a plug-index for a retrieval condition.
   • Specification of a retrieval condition based on a column for which an index is defined, and for which either a FOR UPDATE clause is specified or an update using this cursor exists
   • Specification of a FOR READ ONLY clause
   • Specification of a subquery of a quantified predicate
   • Specification of a subquery of the IN predicate
   • Creation of an internally derived table in a retrieval from a view table or a retrieval in which a WITH clause is specified
9. Specification in the query body at the insertion source of the INSERT statement of either of the following:
   
   • An update table for a subquery that has an external reference
   • An update table for the main query of the query expression body at the insertion source
10. Specification in an UPDATE statement of any of the following:
    
    • A subquery that has an external reference in a search condition or update value, and specification of an update table in that subquery
    • A subquery with a quantified predicate in a search condition
    • A subquery with the IN predicate in a search condition
    • A column for which an index is defined as the update target and search condition, and use of that index
11. Specification in a DELETE statement of any of the following:
    
    • An update table for a subquery in a search condition that has an external reference
    • A subquery with a quantified predicate in a search condition
    • A subquery with the IN predicate in a search condition
    • A column for which an index is defined as the search condition, and use of that index
12. ALTER TABLE ADD PRIMARY KEY
13. ALTER TABLE DROP PRIMARY KEY

HiRDB creates a work table file in a HiRDB file system area. The HiRDB administrator must do the following:

• Use the pdfmkfs command to initialize HiRDB file system areas for creation of work table files.
• Use the pdwork operand of the system definition to specify the name of the HiRDB file system area that is to be used.

This section explains how to determine the values to be specified in options of the pdfmkfs command options. The following table describes the relationships between the pdfmkfs command options and work table file-related items.

Table 18-1 Options for which values need to be specified

Option	Description
-n	Size of HiRDB file system area in which work table files are to be created
-l	Maximum number of HiRDB files (work table files) that can be created in the HiRDB file system area
-e	Maximum number of secondary allocations for the HiRDB file system area
-a	Whether the HiRDB file system area extends automatically