Moving data by using distributed SQL

If you want to move data with different binary pages and page sizes across platforms and you have expertise in using distributed SQL, you can use INSERT and SELECT SQL statements to transfer the data.

Prerequisites: A network connection must exist between database server instances.

To move data using INSERT and SELECT statements with fully qualified table names:

1. Capture the complete database schema from the source database server.
2. Alter the extent sizing and, if necessary, the lock modes on tables from page to row.
3. Create and verify the schema on the target database server.
4. Disable logging on both source and target servers where necessary.
5. Create and run the following scripts:
   1. Create and run separate scripts for:
      • Disabling select triggers on the source server
      • Disabling indexes, triggers and constraints for each table on the target database server.
   2. Create and run one script per table for the fully-qualified INSERT and SELECT statements.
      For example:

      INSERT INTO dbname@target:owner.table SELECT * 
      FROM dbname@source:owner.table 

      You can run the scripts in parallel. In addition, for larger tables, you can create multiple scripts that can partition the table to run in parallel.

   3. Create and run separate scripts for enabling indexes, triggers and constraints for each table
6. Run UPDATE STATISTICS on system catalog tables and stored procedures and functions on the target database server.
7. Adjust starting values for all tables that have serial columns on the target database server.
8. Turn on transaction logging on the source and target database servers.
9. Return the source and target database servers to multi-user mode.
10. Validate the data that was transferred to the target database server.

For information about INSERT and SELECT statements, refer to the IBM® Informix Guide to SQL: Syntax. For information on distributed transactions, refer to the IBM Informix Administrator's Guide and the IBM Informix Administrator's Reference.