SQL/MX 2.x Installation and Management Guide (H06.04+)

Reorganizing SQL/MX Tables and Maintaining Data
HP NonStop SQL/MX Installation and Management Guide—540436-001
10-35
Using DUP to Copy Tables Into Tables
You can use the DataLoader/MX product to help implement these tasks. For
example, use DataLoader/MX to arrange to have the input data delivered to the
correct target partitions. For more information about the DataLoader/MX product,
see the DataLoader/MX Reference Manual.
Example of Using import to Append Data
Suppose that you have a history table containing 80 weeks of data. You could partition
the table so that each week of data resides 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 CPU.
The next example uses the import command each week to add the most recent week
of data to the appropriate 16 partitions. It starts an import process for each target
partition (in each associated CPU). You can enter the next import command from
another OSS shell that is running on another CPU.
The example divides the input data into 16 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 record
layout corresponds exactly to the target table layout.
The -l option ensures that the import operation proceeds even if an input record
contains data that cannot be parsed for loading into the target table.
/user/bin> import cat1.schema1.t1 -I /G/VOL33/TARGET/HIST01 -L 1000
/user/bin> import cat1.schema1.t1 -I /G/VOL33/TARGET/HIST02 -L 1000 T 1000
/user/bin> import cat1.schema1.t1 -I /G/VOL33/TARGET/HIST03 -L 1000 T 1000
.
.
.
/user/bin> import cat1.schema1.t1 -I /G/VOL33/TARGET/HIST16 -L 1000 T 1000
This example issues 16 similar import commands, one for each target partition. The
append operation executes in parallel against the 16 partitions.
Using DUP to Copy Tables Into Tables
DUP is a syntax-based utility you can execute from MXCI to copy a source table—and
optionally its index and constraints—into an existing target table.
For more information about the DUP utility, see the SQL/MX Reference Manual.
Guidelines for Using DUP
•
You must have select privileges for the source table and must own the structure
where the target table will reside. An error is returned if an access violation occurs.
•
Referential integrity constraints and triggers are ignored.