Migrate your local stores

If you created local stores for your 12.x multiplex query nodes, move these stores before migrating data to Sybase IQ 15.2. The iqlsunload tool connects to a Sybase IQ 12.7 database with an IQ local store and creates scripts that unload its schema and data.

Local Store migration requires you to consolidate node-specific information into either the existing 12.7 writer or the new Sybase IQ 15.2 main store. You can customize the process to meet your data requirements.

If existing query nodes have node- or department-specific information, you can use tablespaces and partitioning to achieve the same results.

If information is duplicated across your query nodes, you may need to migrate only a single query server's local store. The duplicated information on the other query servers becomes redundant and can be ignored for multiplex migration.

If the same table exists on multiple query nodes, and each node has its own subset of the data, you must manually edit the local store migration scripts.

For a department-specific employee table on each query server, follow these basic steps:

1. Unload the schema and data from the query nodes.

   The reload_schema.sql script produced for each query node contains the same schema definition for employee.

2. Execute the reload_schema.sql from one of the query nodes against either the existing 12.7 writer or the new Sybase IQ 15.2 main store.

3. Execute the reload_data.sql script from each of the query nodes against the same server used in the previous step.

   This procedure creates the employee table once but loads each query node data set.

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

1. Run iqlsunload against all your query servers with local stores that have the data you want to consolidate.

2. 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.

3. Run extract_data.sql from each of the nodes.

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 = '';
SET TEMPORARY 

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.

Follow these steps to unload the schema and data for local objects in the 12.7 IQ Local Store and move them to another node for migration.