External tables in high-availability cluster environments

You use external tables on secondary servers in much the same way they are used on the primary server.

You can perform the following operations on the primary and on secondary servers:

  • Unload data from a database table to an external table:
    INSERT INTO external_table SELECT * FROM base_table WHERE ...
  • Load data from an external table into a database table:
    INSERT INTO base_table SELECT * FROM external_table WHERE ...

Loading data on SDS, RSS, or HDR secondary servers is slower than loading data on the primary server.

The CREATE EXTERNAL TABLE statement and the SELECT ... INTO EXTERNAL ... statement are not supported on secondary servers.

When unloading data from a database table to an external table, data files are created on the secondary server but not on the primary server. External table data files created on secondary servers are not automatically transferred to the primary server, nor are external table data files that are created on the primary server automatically transferred to secondary servers.

When creating an external table on a primary server, only the schema of the external table is replicated to the secondary servers, not the data file.

To synchronize external tables between the primary server and a secondary server, you can either copy the external table file from the primary server to the secondary servers, or use the following steps:
  1. On the primary server:
    1. Create a temporary table with the same schema as the external table.
    2. Populate the temporary table:
      INSERT INTO dummy_table SELECT * FROM external_table
  2. On the secondary server:
    Use the following command to populate the external table:
    INSERT INTO external_table SELECT * FROM dummy_table

Copyright© 2019 HCL Technologies Limited