SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Migrating an SQL/MP Database to NonStop SQL/MX
Release 2.1 or Release 2.0
HP NonStop SQL/MX Installation and Management Guide—523723-004
14-15
Tools for Migrating Data
For Enscribe files with other data types, the default format of data as read from the
Enscribe file is not compatible with import. In this case, DataLoader/MX must be
customized by modifying the user exit routines, and the required data conversion must
be specified by the user. The transformations that are required depend on the Enscribe
output format and the import input format. The customized DataLoader/MX can then
be used in the two-step procedure to load the data into SQL/MX tables.
Tools for Migrating Data
To copy data from SQL/MP or Enscribe databases into an SQL/MX table, use:
•
INSERT...SELECT statement
•
DataLoader/MX software
Using INSERT...SELECT to Copy Data
Because NonStop SQL/MX supports DML statements against both SQL/MP and
SQL/MX tables, you can use a simple INSERT statement to copy data from an
SQL/MP table into an SQL/MX table.
This example copies the contents of an SQL/MP table named
MPCAT.SALES.CUSTOMER into an SQL/MX table named
MXCAT.SALES.CUSTOMER. The FOR READ UNCOMMITTED access option allows
the transaction to access locked data. FOR READ UNCOMMITTED might be suitable
for testing purposes. For a higher level of data consistency, you might substitute the
FOR REPEATABLE READ access option:
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;
One drawback of using INSERT...SELECT is that the INSERT is done in a single
transaction. If the table is large, this operation can 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.
Using DataLoader/MX to Copy Data
DataLoader/MX, a software tool to load SQL/MP and SQL/MX databases, supports
single or multiple data sources and parallel loading of large databases. DataLoader/MX
works in conjunction with the import utility. (The import utility is described in the
SQL/MX Reference Manual.) For more information about DataLoader/MX, see the
DataLoader/MX Reference Manual.