Migrate your local stores

If you created local stores for your 12.x multiplex query nodes, you need to move these stores before migrating data to Sybase IQ 15.1. 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 means consolidating node-specific information into either the existing 12.7 Writer or the new Sybase IQ 15.1 Main Store. You can customize the steps in 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. For more information, see Data storage in Chapter 3, “Multplex Overview,” in the New Features Guide.

If information is duplicated across your query nodes, you may only need to migrate 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, then you must manually edit the local store migration scripts.

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

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

   The reload_schema.sql script produced for each query node will contain 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.1 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 of the query node data sets.

If the same table exists on each query node with overlapping data sets, you must resolve the issue. You may edit the extract data files to ensure that the data sets are unique, or you may decide to just 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 wish to consolidate.

2. Modify the reload_schema.sql and reload_data.sql files to have the new table names. Leave the extract_data.sql file alone as if references the table found in the query server's local store.

3. Run the 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 the following:

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"),

Edit the reload_schema.sql and change it as follows:

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"),

The extract_data.sql file contains the following:

---- 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 the previous extract_data.sql code unchanged in order to extract the sales_order table from the query server.

Suppose that reload_data.sql contains the following:

-- 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 the following:

-- 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;

The preceding 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.