Posted by As guardians of data we are constantly faced with the challenge of moving data from one platform to another for a multitude of reasons.
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. Thistechnique 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 |
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
nce 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.
DayDreamsDatabases 