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.
Partitioning query server data
For a department-specific employee table on each query server, you would follow these basic steps:
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.
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.
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.
Addressing overlapping
query server data
Run iqlsunload against all your query servers with local stores that have the data you wish to consolidate.
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.
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.
Moving local stores with iqlsunload
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.
If using an older version, you must upgrade to the minimum version Sybase IQ 12.7 ESD #5 before local store migration.
The iqlsunload utility is installed with Sybase IQ 15.1 in the %IQDIR15%\lsunload directory.
Run the 12.7 iqlsunload utility against each query server with a local store. For syntax and examples, see “Sybase 12.7 Local Store Unload utility (iqlsunload)”.
Edit the reload_schema.sql script as follows:
Delete unwanted objects.
Uncomment any objects that you want to reload that are commented out in the reload_schema.sql script.
Add commands to define any objects that you defined in sp_mpxcfg_<servername> procedures.
Edit the extract_data.sql script to remove objects that you do not wish to migrate. These objects are generally the same ones that you removed from reload_schema.sql.
Use Interactive SQL to run extract_data.sql from your 12.7 local store.
You now have unloaded the schema and data for local objects in the 12.7 local store.
Run the reload_schema.sql and load_data.sql scripts against the 12.7 write server.
If you prefer, you may wait until the write server has
been migrated to 15.1 and run the reload_schema.sql and load_data.sql scripts
against the new 15.1 coordinator.
