Bulk Copy WITH Clauses
The WITH clause options on the COPY statement for bulk copy operate like the corresponding clauses in the MODIFY statement.
If these clauses are omitted, the table default values in the system catalogs are used. If any of these clauses are specified, the values become the new defaults for the table in the system catalogs.
The following clauses can be used only with a bulk copy:
Note: If these clauses are used with a COPY statement that cannot be run as a bulk copy, an error message is returned and the copy is not performed.
• WITH ALLOCATION
A bulk copy from can preallocate table space with the allocation clause. This clause specifies how many pages are preallocated to the table. For example, preallocate 1000 pages for bulk copying from the emp.data file into the emp table:
COPY emp() FROM 'emp.data' WITH ALLOCATION = 1000;
• WITH EXTEND
This clause specifies how many pages the table is extended whenever additional disk space is required. When many pages are to be added to a table, increasing the EXTEND value can reduce the number of disk allocation operations, and improve the resulting file layout on disk.
For example, set the emp table’s extend size to 100 pages for bulk copying from the emp.data file:
COPY emp() FROM 'emp.data' WITH EXTEND = 100;
• WITH ROW_ESTIMATE
A bulk copy can specify an estimated number of rows to be copied during the bulk copy. It can be a value from 0 to 2,147,483,647 ((231-1). This clause can be used only on tables with B-tree, hash, or ISAM storage structures.
For example, set the row estimate on the emp table to one million for bulk copy from the emp.data file:
COPY emp() FROM 'emp.data' WITH ROW_ESTIMATE = 1000000;
Providing a row estimate can enhance the performance of the bulk copy by allowing the sorter to allocate a realistic amount of resources (such as in-memory buffers), disk block size, and whether to use multiple locations for the sort. In addition, it is used for loading hash tables in determining the number of hash buckets. If you omit this parameter, the default value is 0, in which case the sorter makes its own estimates for disk and memory requirements.
To obtain a reasonable row estimate value, use known data volumes, the HELP TABLE statement, and information from the system catalogs. For more information, see the chapter “Maintaining Storage Structures.” An over-estimate causes excess resources of memory and disk space to be reserved for the copy. An under-estimate (the more typical case, particularly for the default value of 0 rows) causes more sort I/O to be required.
• WITH FILLFACTOR
A bulk copy from can specify an alternate fillfactor. This clause specifies the percentage (from 1 to 100) of each primary data page that must be filled with rows during the copy. This clause can be used only on tables with B-tree, hash, or ISAM storage structures.
For example, set the fillfactor on the emp table to 10% for bulk copy from the emp.data file:
COPY emp() FROM 'emp.data' WITH FILLFACTOR = 10;
• WITH LEAFFILL
A bulk copy from can specify a leaffill value. This clause specifies the percentage (from 1 to 100) of each B‑tree leaf page that must be filled with rows during the copy. This clause can be used only on tables with a B-tree storage structure.
For example, set the leaffill percentage on the emp table to 10% for bulk copy from the emp.data file:
COPY emp() FROM 'emp.data' WITH LEAFFILL = 10;
• WITH NONLEAFFILL
A bulk copy from can specify a nonleaffill value. This clause specifies the percentage (from 1 to 100) of each B-tree non-leaf index page that must be filled with rows during the copy. This clause can be used only on tables with a B-tree storage structure.
For example, set the nonleaffill percentage on the emp table to 10% for bulk copy from the emp.data file:
COPY emp() FROM 'emp.data' WITH NONLEAFFILL = 10;
• WITH MINPAGES, MAXPAGES
A bulk copy from can specify minpages and maxpages values. The MINPAGES clause specifies the minimum number of primary pages that a hash table must have. The MAXPAGES clause specifies the maximum number of primary pages that a hash table must have. This clause can be used only on tables with a hash storage structure.
If these clauses are not specified, the primary page count for the bulk copy is determined as follows:
– If the COPY statement has a ROW_ESTIMATE clause, that size, along with the row width and fill factor, is used to generate the number of primary pages.
– Otherwise, the table’s default in the system catalogs is used.
The following example sets the number of primary data pages (hash buckets) for bulk copying from the emp.data file into the emp table:
COPY emp() FROM 'emp.data' WITH MINPAGES = 16384, MAXPAGES = 16384
For further details on these WITH clause options, see the chapter “Maintaining Storage Structures.”