SQL/MX 2.x Database and Application Migration Guide (G06.23+, H06.04+, J06.03+)

Converting an SQL/MP Database to SQL/MX Tables
HP NonStop SQL/MX Database and Application Migration Guide—540435-005
11-23
Using INSERT-SELECT to Copy Data
Using INSERT-SELECT to Copy Data
Because NonStop SQL/MX supports DML statements that access both SQL/MP and
SQL/MX tables, you can use a simple INSERT-SELECT statement to copy data from
an SQL/MP table into an SQL/MX table.
Example of Using INSERT-SELECT to Copy Data
This example copies the contents of the SQL/MP table MPCAT.SALES.CUSTOMER
into the SQL/MX table MXCAT.SALES.CUSTOMER:
INSERT INTO mxcat.sales.customer
(custnum, custname, street, city, state, postcode)
SELECT custnum, custname, street, city, state, postcode
FROM mpcat.sales.customer FOR READ UNCOMMITTED;
The FOR READ UNCOMMITTED access option allows the transaction to access
locked data, which might be suitable for testing. For a higher level of data consistency,
use the FOR REPEATABLE READ access option.
One drawback of using INSERT-SELECT is that the data is inserted in a single
transaction. If the table is large, this operation might fail because of TMF transaction
length or size limitations. You can work around these limitations by doing multiple
inserts, each accessing a separate key range in the SQL/MP table. For more
information, see Loading Data Into Partitioned Tables
on page 11-23.
Loading Data Into Partitioned Tables
For range-partitioned tables, the ranges of the partitions are known in advance.
Therefore, you can separate the data on those range boundaries and quickly load
separate, sorted, parallel streams of data into each partition.
However, for hash-partitioned tables, the partitioning algorithm is internal to NonStop
SQL/MX. Before loading data into a hash-partitioned table, use the HASHPARTFUNC
function to determine the partition into which each row will go. After you know how
each row will be partitioned, you can separate the data and load parallel streams of
data into each partition. For more information about HASHPARTFUNC, see the
SQL/MX Installation and Management Guide and the SQL/MX Reference Manual.
Using DataLoader/MX to Copy Data
DataLoader/MX is a software tool that loads data into SQL/MP and SQL/MX databases
and that supports single or multiple data sources and parallel loading of large
databases. DataLoader/MX works with the
import utility, which imports data from an
input file in ASCII or UCS2 format into an SQL/MX table.