SQL/MP Installation and Management Guide

Reorganizing Tables and Maintaining Data
HP NonStop SQL/MP Installation and Management Guide—523353-004
8-17
Appending Data to Tables or Partitions
3. Supply each APPEND command with the specific range of input data for the target
partition. Three possible strategies are:
•
Arrange the input data so that it is divided into separate files, each containing
input for a specific target partition. Use these files as input to the APPEND
commands.
•
Do a SORTED append and specify FIRST KEY. When the SORTED and
PARTONLY options are specified, APPEND stops processing input as soon as
it encounters a row beyond the end of the target partition.
•
Use processes to read input data. Start each data source process as a named
process before entering the APPEND command. Use the process name as the
input file for the APPEND command. The process must wait for requests on its
$RECEIVE file and then supply data by replying to those requests. When using
this approach, be sure to balance processing for optimal performance.
You can use the DataLoader/MP product to help implement the preceding tasks.
For example, you can use DataLoader/MP to arrange to have the input data
delivered to the correct target partitions. For more information about the
DataLoader/MP product, see the DataLoader/MP Reference Manual.
Example of Appending Data
This example assumes you have a history table containing 80 weeks of data. You
could partition the table so that each week of data resided on one partition. This
scheme, however, might create disproportionate requests for data from certain
partitions, particularly those containing the most recent weeks of data. Therefore, the
table is partitioned by a hash value so that each week of data is striped (partitioned)
across 16 partitions. To maximize parallel execution, each partition is associated with a
different processor.
The example uses the APPEND utility each week to add the most recent week of data
to the appropriate 16 partitions. It starts an SQLCI process for each target partition (in
each associated processor). The NOWAIT option allows you to run separate,
concurrent SQLCI processes—you can enter the next SQLCI command without having
to wait for the last process to finish. Prompts, errors, and other messages are directed
to separate output files so that you can distinguish events occurring in each process.
The example divides the input data into 16 Enscribe files; each file’s data is appended
to the corresponding target partition. The input files reside on volumes $VOL1 through
$VOL4. The target partitions reside on volumes $VOL33 through $VOL48. The
Enscribe record layout corresponds exactly to the target table layout; therefore, no
move options are needed to convert input fields into target columns.