Technical Blog Post
Abstract
Cross Platform Data Migrations
Body
As guardians of data we are constantly faced with the challenge of moving data from one platform to another for a multitude of reasons.
We faced such challenge at one of my favorite client location where most databases were being migrated to reside on private cloud environment.
A standard procedure in order migrate a few hundred database instance into a Linux cloud farm was built.
Challenges:
- Large volumes of data, typically close to a TB in each database instance.
- Minimal downtime and maintenance window
- Staging and moving data efficiently across the network
- Endianness challenges in a cross platform migration
- Disparate sources – Solaris, AIX, HP-UX to Linux
- Diverse database versions – DB2 8, 9 and 10
- Monitoring the migrations over extended durations
There are a number of best practices and such but the scope of this post will solely focus on a using a load from cursor with the database option. This
technique can be used for migrations to any supported version from any release of DB2 across platforms.
General process flow:
| Step | DB Source | DB Target |
| 1 |
| Create database |
| 2 | Identify hostname, DB2 port, User ID and password |
|
| 3 |
| Create catalog entries to SOURCE Database using information from step 2 |
| 4 | Use db2look to generate database DDL |
|
| 5 |
| Use the db2look to create tablespaces, tables, indexes and any other DB objects |
| 6 |
| Generate the list of tables that require data load |
| 7 |
| Generate SQL for loading each table |
| 8 |
| Execute load script |
| 9 |
| Monitor, Rejects |
Generating Load scripts for all tables:
| connect to SOURCEDB user USERNAME using PASSWORD; select ' db2 -tx "CONNECT TO TARGETDB;'|| ' DECLARE C_'||rtrim(tabname)||' CURSOR DATABASE sourcedb USER username USING password FOR '|| ' SELECT * FROM '||rtrim(tabschema)||'.'||rtrim(tabname)||';' || ' LOAD FROM C_'||rtrim(tabname)||' OF CURSOR MESSAGES /tmp/msg_'||rtrim(tabname)|| ' REPLACE INTO '||rtrim(tabschema)||'.'||rtrim(tabname)|| ' STATISTICS yes WITH DISTRIBUTION AND DETAILED INDEXES ALL NONRECOVERABLE DATA BUFFER 4096;' || ' TERMINATE;"' from syscat.tables where ((type in ('T','S')) and (tabschema not like 'SYS%') and (tabschema not in ('MYSCHEMA','DB2PM'))) order by npages desc;
SOURCEDB – replace with the name of the database you are migrating from TARGETDB – replace with the name of the database you are moving to USERNAME – replace with the ID with which you will connect to SOURCEDB PASSWORD – replace with the password with which you will connect to the SOURCEDB MYSCHEMA – Any schema name that you wish to eliminate (e.g DB2PM that follows is an example)
The SQL above can be copied into a file, modified to suit your need and executed as db2 –tf <filename> |
The sql above would generate the declare cursor with the rows to be selected, load statement and build distributions as the load happens.
You can eliminate specific tables or schema’s in the where clause of the main sql. It orders the tables based on the size.The output of this
sql will appear as below for every table in the SOURCE database.
| db2 -tx "CONNECT TO TARGETDB; DECLARE C_DYNSQL CURSOR DATABASE SOURCEDB USER username USING password FOR SELECT * FROM DB2PM_57.DYNSQL; LOAD FROM C_DYNSQL OF CURSOR MESSAGES /tmp/msg_DYNSQL REPLACE INTO DB2PM_57.DYNSQL STATISTICS yes WITH DISTRIBUTION AND DETAILED INDEXES ALL NONRECOVERABLE DATA BUFFER 4096; TERMINATE;" db2 -tx "CONNECT TO TARGETDB; DECLARE C_DYNSQL CURSOR DATABASE SOURCEDB USER username USING password FOR SELECT * FROM DB2PM_55.DYNSQL; LOAD FROM C_DYNSQL OF CURSOR MESSAGES /tmp/msg_DYNSQL REPLACE INTO DB2PM_55.DYNSQL STATISTICS yes WITH DISTRIBUTION AND DETAILED INDEXES ALL NONRECOVERABLE DATA BUFFER 4096; TERMINATE;" … And so on |
Tables with XML Columns:
In the example above, tables with XML columns are also returned. Fetching XML data from a remote data source using DATABASE CURSOR
is not supported. The implementation of DATABASE cursor uses a feature in load called SOURCEUSEREXIT, where by the load job calls an
external program. In case of XML type data, there is a restriction on SOURCEUSEREXIT or REMOTE FETCH, hence we run into this error.
| SQL1407N The "REMOTE FETCH" option is incompatible with "XML columns". |
Eliminating tables that have XML datatypes :
The SQL below generates the SQL’s statement required to perform the load for all tables except for tables having XML columns data.
| connect to SOURCEDB user USERNAME using PASSWORD; select ' db2 -tx "CONNECT TO TARGETDB;'|| ' DECLARE C_'||rtrim(tabname)||' CURSOR DATABASE sourcedb USER username USING password FOR '|| ' SELECT * FROM '||rtrim(tabschema)||'.'||rtrim(tabname)||';' || ' LOAD FROM C_'||rtrim(tabname)||' OF CURSOR MESSAGES /tmp/msg_'||rtrim(tabname)|| ' REPLACE INTO '||rtrim(tabschema)||'.'||rtrim(tabname)|| ' STATISTICS yes WITH DISTRIBUTION AND DETAILED INDEXES ALL NONRECOVERABLE DATA BUFFER 4096;' || ' TERMINATE;"' from syscat.tables where ((type in ('T','S')) and (tabschema not like 'SYS%') and (rtrim(tabschema)||rtrim(tabname)) NOT IN (select distinct rtrim(tabschema)||rtrim(tabname) from syscat.columns where TYPENAME in ('XML') and (tabschema not like 'SYS%')) AND (type in ('T','S')) and (tabschema not like 'SYS%')) order by npages desc ; SOURCEDB – replace with the name of the database you are migrating from TARGETDB – replace with the name of the database you are moving to USERNAME – replace with the ID with which you will connect to SOURCEDB PASSWORD – replace with the password with which you will connect to the SOURCEDB MYSCHEMA – Any schema name that you wish to eliminate (e.g DB2PM that follows is an example)
The SQL above can be copied into a file, modified to suit your need and executed as db2 –tf <filename> |
Migrating data from tables that have XML datatypes:
The section below describes how you would go about setting up federation for data transfer from tables having XML datatype. A detailed step-by-step
explanation of setting up federation can be found in the blog by Terry Frangos titled "How to setup federation between two DB2 LUW databases "
For the purposes of this article, we will assume the remote database is cataloged and you are familiar with federation setup and only display the commands
to set it up.
| -- One time DB setup on the target database server connect to TARGETDB; update dbm cfg using federated yes; -- Restart DB2 using db2stop followed by db2start -- creating the federation definitions create wrapper drda; create server SOURCEDB_s type DB2/UDB version '9.5' wrapper DRDA authid "YOUR_USERNAME" password "YOUR_PASSWORD" options (dbname 'SOURCEDB’); create user mapping for YOUR_DB2INSTANCE server SOURCEDB_s options ( remote_authid 'YOUR_USERNAME' , remote_password 'YOUR_PASSWORD');
SOURCEDB – replace with the name of the database you are migrating from TARGETDB – replace with the name of the database you are moving to YOUR_USERNAME – replace with the ID with which you will connect to SOURCEDB YOUR_PASSWORD – replace with the password with which you will connect to the SOURCEDB YOUR_DB2INSTANCE – replace with your instance name e.g. db2inst1 The server name has a suffix of _s to help with identification The SQL above can be copied into a file, modified to suit your need and executed as db2 –tvf <filename> |
Creating nicknames for all the XML tables:
Next step would be to create nickname for all tables having the XML data type.
| connect to TARGETDB; select 'db2 -tx "create nickname '||rtrim(tabschema)||'.N_'||rtrim(tabname)||' for SOURCEDB_s.'||rtrim(tabschema)||'.'||rtrim(tabname)||';";' from syscat.tables where ((type in ('T','S')) and (tabschema not like 'SYS%') and (rtrim(tabschema)||rtrim(tabname)) IN (select distinct rtrim(tabschema)||rtrim(tabname) from syscat.columns where TYPENAME in ('XML') and (tabschema not like 'SYS%')) AND (type in ('T','S')) and (tabschema not like 'SYS%')) order by npages desc ; SOURCEDB – replace with the name of the database you are migrating from TARGETDB – replace with the name of the database you are moving to Nicknames have a suffix of _N to help with identification Output of this query against the sample database is shown below: db2 -tx "create nickname DB2INST4.N_PURCHASEORDER for SOURCEDB_s.DB2INST4.PURCHASEORDER;"; db2 -tx "create nickname DB2INST4.N_SUPPLIERS for SOURCEDB_s.DB2INST4.SUPPLIERS;” ; And so on.. This output can be sent to a file and then executed to create all the nicknames against tables that have XML data type. The SQL above can be copied into a file, modified to suit your need and executed as db2 –tf <filename> |
Load from Nicknames:
The remaining XML tables can now be loaded by from the nicknames. The query below generates the SQL declare,cursor, load statements for all nicknames
that was created in the database.
| connect to TARGETDB ; select ' db2 -tx "CONNECT TO TARGETDB;'|| ' DECLARE C_'||rtrim(tabname)||' CURSOR FOR '|| ' SELECT * FROM '||rtrim(tabschema)||'.'||rtrim(tabname)||';' || ' LOAD FROM C_'||rtrim(tabname)||' OF CURSOR MESSAGES /tmp/msg_'||rtrim(tabname)|| ' REPLACE INTO '||rtrim(tabschema)||'.'||substr(rtrim(tabname),3)|| ' STATISTICS yes WITH DISTRIBUTION AND DETAILED INDEXES ALL NONRECOVERABLE DATA BUFFER 4096;' || ' TERMINATE;"' from syscat.tables where ((type in ('N')) and (tabschema not like 'SYS%') ); TARGETDB – replace with the name of the database you are moving to Tables.type of ‘N’ retrieve nicknames The SQL above can be copied into a file, modified to suit your need and executed as db2 –tf <filename> The above query would return db2 -tx "CONNECT TO TARGETDB; DECLARE C_N_PRODUCTSUPPLIER CURSOR FOR SELECT * FROM DB2INST4.N_PRODUCTSUPPLIER; LOAD FROM C_N_PRODUCTSUPPLIER OF CURSOR MESSAGES /tmp/msg_N_PRODUCTSUPPLIER REPLACE INTO DB2INST4.PRODUCTSUPPLIER STATISTICS yes WITH DISTRIBUTION AND DETAILED INDEXES ALL NONRECOVERABLE DATA BUFFER 4096; TERMINATE;" And so on.. These sql’s can be saved to a file and executed as a batch to complete the loads. |
Monitoring the loads:
Db2 list utilities and db2pd –util can be used to monitor the progress of the loads. A sample screen shot of the outputs is shown below.
db2pd -util
Since statistics are updated a part of the load statement, you can also check the stats_time from syscat.tables for completion times to identify which tables were loaded.
| db2 "select substr(tabname,1,25), stats_time from syscat.tables where tabschema = 'MYSCHEMANAME' order by stats_time asc" |
Finally monitor the rejects file for any failures and correct them as you deem necessary.
UID
ibm13286965