Addressing Overlapping Query Server Data

If the same table exists on each query node with overlapping data sets, you must resolve the issue. Extract the data files to ensure that data sets are unique, or rename the tables and then reload all the unique tables.

Run iqlsunload against all query servers with local stores that have the data to consolidate.
Modify the reload_schema.sql and reload_data.sql files to use the new table names. Do not modify extract_data.sql; it references the table found in the query server's local store.
Run extract_data.sql from each node.

The following example shows modifications to the iqlsunload output to carry out step 2. Suppose that the reload_schema.sql script contains:

CREATE TABLE "DBA"."sales_order"
	
	"id"    			unsigned int NOT NULL  IQ UNIQUE (648),
	"cust_id"       		unsigned int NOT NULL  IQ UNIQUE (111),
	"order_date"    		"datetime" NOT NULL  IQ UNIQUE (376),
	"fin_code_id"   		char(2) NULL  IQ UNIQUE (1),
	"region"        		char(7) NULL  IQ UNIQUE (5),
	"sales_rep"     		unsigned int NOT NULL  IQ UNIQUE (75), 
PRIMARY KEY ("id"),

Modify reload_schema.sql to:

CREATE TABLE "DBA"."q1_sales_order"

	"id"    			unsigned int NOT NULL  IQ UNIQUE (648),
	"cust_id"       		unsigned int NOT NULL  IQ UNIQUE (111),
	"order_date"    		"datetime" NOT NULL  IQ UNIQUE (376),
	"fin_code_id"   		char(2) NULL  IQ UNIQUE (1),
	"region"        		char(7) NULL  IQ UNIQUE (5),
	"sales_rep"     		unsigned int NOT NULL  IQ UNIQUE (75), 
	PRIMARY KEY ("id"),

extract_data.sql contains:

---- Extract Table Data for table sales_order
-- NOTE: Approximately 57672 bytes of storage space.
-- will be required to extract the data for this table.
--
-- The following will unload the data for table
sales_order, row group 1, column group 1

SET TEMPORARY OPTION temp_extract_name1 =
'DBA_sales_order_1_1_DATA_1.inp';
SET TEMPORARY OPTION temp_extract_name2 =
'DBA_sales_order_1_1_DATA_2.inp';
SET TEMPORARY OPTION temp_extract_name3 =
'DBA_sales_order_1_1_DATA_3.inp';
SET TEMPORARY OPTION temp_extract_name4 =
'DBA_sales_order_1_1_DATA_4.inp';
SET TEMPORARY OPTION temp_extract_name5 =
'DBA_sales_order_1_1_DATA_5.inp';
SET TEMPORARY OPTION temp_extract_name6 =
'DBA_sales_order_1_1_DATA_6.inp';
SET TEMPORARY OPTION temp_extract_name7 =
'DBA_sales_order_1_1_DATA_7.inp';
SET TEMPORARY OPTION temp_extract_name8 =
'DBA_sales_order_1_1_DATA_8.inp';

SELECT id, cust_id, order_date, 
IFNULL(fin_code_id, @null_string, fin_code_id),
IFNULL(region, @null_string, region), sales_rep 
FROM "DBA"."sales_order" 
WHERE rowid( "sales_order" ) >= 1 
AND  rowid( "sales_order" ) <= 648;

SET TEMPORARY OPTION temp_extract_name1 = '';
SET TEMPORARY OPTION temp_extract_name2 = '';
SET TEMPORARY OPTION temp_extract_name3 = '';
SET TEMPORARY OPTION temp_extract_name4 = '';
SET TEMPORARY OPTION temp_extract_name5 = '';

Leave extract_data.sql code unchanged to extract the sales_order table from the query server.

Suppose that reload_data.sql contains:

-- Reload Table Data for table "sales_order"
-------------------------------------------------
ALTER TABLE "DBA"."sales_order" MODIFY cust_id NULL;
ALTER TABLE "DBA"."sales_order" MODIFY order_date NULL;
ALTER TABLE "DBA"."sales_order" MODIFY sales_rep NULL;

SET @max_row_id = 
( SELECT MAX( rowid( "sales_order" ) )+1 
FROM "DBA"."sales_order" );

SET @load_statement = 
'LOAD TABLE "DBA"."sales_order"
(id, cust_id, order_date, fin_code_id NULL(
'''||@null_string||''' ) , region NULL(
'''||@null_string||''' ) , sales_rep) 
FROM
'''||@extract_directory||'DBA_sales_order_1_1_DATA_1.
inp'',
'''||@extract_directory||'DBA_sales_order_1_1_DATA_2.
inp'', '''||@extract_directory||'DBA_sales_order_1_1_DATA_3.
inp'',
'''||@extract_directory||'DBA_sales_order_1_1_DATA_4.
inp'',
'''||@extract_directory||'DBA_sales_order_1_1_DATA_5.
inp'', '''||@extract_directory||'DBA_sales_order_1_1_DATA_6.
inp'', '''||@extract_directory||'DBA_sales_order_1_1_DATA_7.
inp'', '''||@extract_directory||'DBA_sales_order_1_1_DATA_8.
inp'' ROW DELIMITED BY ''\n'' QUOTES ON 
ESCAPES OFF DEFAULTS OFF FORMAT ASCII 
IGNORE CONSTRAINT ALL 0 START ROW ID
'||@max_row_id;

CALL IqExecuteCommand( @load_statement );
ALTER TABLE "DBA"."sales_order" MODIFY cust_id NOT
NULL;
ALTER TABLE "DBA"."sales_order" MODIFY order_date NOT
NULL;
ALTER TABLE "DBA"."sales_order" MODIFY sales_rep NOT
NULL;

Change reload_data.sql to:

-- Reload Table Data for table
"q1_sales_order"
-------------------------------------------------
ALTER TABLE "DBA"."q1_sales_order" MODIFY cust_id NULL;
ALTER TABLE "DBA"."q1_sales_order" MODIFY order_date
NULL;
ALTER TABLE "DBA"."q1_sales_order" MODIFY sales_rep
NULL;

SET @max_row_id = ( SELECT MAX( rowid( "q1_sales_order"
) )+1 FROM "DBA"."q1_sales_order" );

SET @load_statement = 
'LOAD TABLE "DBA"."q1_sales_order"
(id, cust_id, order_date, fin_code_id NULL(
'''||@null_string||''' ) , region NULL(
'''||@null_string||''' ) , sales_rep) FROM
'''||@extract_directory||'DBA_q1_sales_order_1_1_DATA_
1.inp'',
'''||@extract_directory||'DBA_q1_sales_order_1_1_DATA_
2.inp'',
'''||@extract_directory||'DBA_q1_sales_order_1_1_DATA_
3.inp'',
'''||@extract_directory||'DBA_q1_sales_order_1_1_DATA_
4.inp'', '''||@extract_directory||'DBA_q1_sales_order_1_1_DATA_5.inp'', '''||@extract_directory||'DBA_q1_sales_order_1_1_DATA_
6.inp'', '''||@extract_directory||'DBA_q1_sales_order_1_1_DATA_
7.inp'', '''||@extract_directory||'DBA_q1_sales_order_1_1_DATA_
8.inp'' ROW DELIMITED BY ''\n'' QUOTES ON ESCAPES OFF
DEFAULTS OFF FORMAT ASCII IGNORE CONSTRAINT ALL 0 
START ROW ID '||@max_row_id;

CALL IqExecuteCommand( @load_statement );ALTER TABLE "DBA"."q1_sales_order" MODIFY cust_id NOT 
NULL;
ALTER TABLE "DBA"."q1_sales_order" MODIFY order_date 
NOT NULL;
ALTER TABLE "DBA"."q1_sales_order" MODIFY sales_rep NOT 
NULL;

This example shows query server schema and data that require intervention during migration. Your situation may vary, but you have complete control of the content of the final reload_schema.sql and reload_data.sql files.