Migrating Database from DB2/VSE to DB2 LUW
You want to migrate your existing DB2/VSE to DB2 LUW to take advantage of functions and capabilities that are in DB2 LUW, but are not available in DB2/VSE. The challenge is in moving a large number of tables and data in an efficient way without using any tool.
Here is cookbook for setting up a DB2 federated database to quickly and efficiently migrate your database (Tables & Data) from DB2/VSE to DB2 LUW.
There are many reason people considering a migration from their legacy database server DB2/VSE to more advanced DB2 LUW Database running on any new operating systems including Red Hat Enterprise Linux. For some customers this becomes a critical necessity in order to provide the information management services to their companies and customers.
Some of the reasons for wanting to do this type of migration are:
Functional Limitations of DB2/VSE
There are numerous steps required to perform such type of migration. As this migration is with different platforms not only in hardware but also in operating systems and database versions due different hardware architectures and required the following skills for successfully complete this kind of data and application migration.
Skills required for database migration form DB2/VSE running on IBM z/VSE operating system on IBM System z (Architecture Support from z10-z14) to DB2 LUW 11.x running on for Red hat Enterprise Linux 7.x
Linux:
z/VSE:
I have started this migration process from DB2/VSE running z/VSE operating by using SQLDBDEF utility. The SQLDBDEF utility extracts the definition of database objects from a DB2/VSE database, and generates a DBSU job that can be used to create the same objects on another DB2 database.
Before executing SQLDBDEF utility. Firstly, punch ARISLKRF.OBJ member from PRD2.DB2750 library with the following LIBRP command.
? LIBRP PRD2.DB2750 ARISLKRF.OBJ ARISLKRF
and add the include statement for ARIS215D and re-catalog the member as shown in below sample job.
?CATALOG ARISLKRF.OBJ???????????REPLACE=YES?????????
?????PHASE ARIDBDF,S,NOAUTO????????????????????
?INCLUDE ARIDBDF????????????????????????????
?INCLUDE ARIDBD2????????????????????????????
?INCLUDE ARIDBD3?????????????????????????????????????????????????????????????????????????????????????????????????
?INCLUDE ARIDBD4????????????????????????????
?INCLUDE ARIDBD5????????????????????????????
?INCLUDE ARIDBD6????????????????????????????
?INCLUDE ARIMMGM????????????????????????????
?INCLUDE ARIMSMF????????????????????????????
?INCLUDE ARISYSDD???????????????????????????
?INCLUDE ARIPRDID
?INCLUDE ARIS215D????????????????????????????
?÷END??????????????????????????????????
?/+???????????????????????????????????
?/*?????????????????
Secondly, punch member ARISRLDF.Z from PRD2.DB2750, a DB2/VSE system library with the following LIBRP command.
LIBRP PRD2.DB2750 ARISRLDF.Z ARISRLDF
Edit with DB2/VSE Instance Name (PAMMDB2), DBA Credentials (SQLDBA/PASSWORD) and execute the ARISRLDF member to load the packages in memory.
* $$ JOB JNM=ARISRLDF,CLASS=A,DISP=D??????????????????????????????????
// JOB ARISRLDF???????????????????????????????????????????????????????
// LIBDEF *,SEARCH=(PRD2.DB2750)??????????????????????????????????????
// EXEC PGM=ARIDBS,SIZE=AUTO??????????????????????????????????????????
CONNECT SQLDBA IDENTIFIED BY xxxxxxxxxxxx TO PAMMDB2;???????????????????????
SET AUTOCOMMIT OFF;???????????????????????????????????????????????????
SET ERRORMODE CONTINUE;???????????????????????????????????????????????
RELOAD PROGRAM (SQLDBA.ARIDBDF) REPLACE KEEP INFILE(SYSIPT BLKSZ(80)??
PDEV(DASD));??????????????????????????????????????????????????????????
READ MEMBER ARIDBDFM.A;???????????????????????????????????????????????
/*????????????????????????????????????????????????????????????????????
RELOAD PROGRAM (SQLDBA.ARIDBD2) REPLACE KEEP INFILE(SYSIPT BLKSZ(80)??
PDEV(DASD));??????????????????????????????????????????????????????????
READ MEMBER ARIDBD2M.A;???????????????????????????????????????????????
/*????????????????????????????????????????????????????????????????????
RELOAD PROGRAM (SQLDBA.ARIDBD3) REPLACE KEEP INFILE(SYSIPT BLKSZ(80)??
PDEV(DASD));??????????????????????????????????????????????????????????
READ MEMBER ARIDBD3M.A;???????????????????????????????????????????????
/*????????????????????????????????????????????????????????????????????
RELOAD PROGRAM (SQLDBA.ARIDBD4) REPLACE KEEP INFILE(SYSIPT BLKSZ(80)??
PDEV(DASD));??????????????????????????????????????????????????????????
READ MEMBER ARIDBD4M.A;?
/*??????????????????????????????????????????????????????????????????????
RELOAD PROGRAM (SQLDBA.ARIDBD5) REPLACE KEEP INFILE(SYSIPT BLKSZ(80)????
PDEV(DASD));????????????????????????????????????????????????????????????
READ MEMBER ARIDBD5M.A;?????????????????????????????????????????????????
/*??????????????????????????????????????????????????????????????????????
RELOAD PROGRAM (SQLDBA.ARIDBD6) REPLACE KEEP INFILE(SYSIPT BLKSZ(80)????
PDEV(DASD));????????????????????????????????????????????????????????????
READ MEMBER ARIDBD6M.A;?????????????????????????????????????????????????
/*??????????????????????????????????????????????????????????????????????
/&??????????????????????????????????????????????????????????????????????
* $$ EOJ?????????????????????????????????????????????????
?
Lastly, punch member ARIS215D.Z from PRD2.DB2750, a DB2/VSE system library with the following LIBRP command.
LIBRP PRD2.DB2750 ARIS215D.Z ARIS215D
Following is the sample ARIS215D (SQLDBDEF) member, which can be edit and execute with the DB2/VSE Service Name and DBA credentials (Default DBA: SQLDBA/PASSWORD) parameters to generate the DBSU Utility in punch list
?
????* $$ JOB JNM=ARIS215D,CLASS=A,DISP=D????????????????????????????????????
????// JOB ARIS215D?????????????????????????????????????????????????????????
???// LIBDEF *,SEARCH=(PRD2.DB2750)????????????????????????????????????????
????// EXEC REXX=SQLDBDEF,PARM='DB(PAMMDB2) ID(SQLDBA) PW(XXXXXXXX)??????????
???TARGET(OTHER db2 udb) DBSP(*) STOREDPROCS(NO)'???????????????????????????????????????
????/*??????????????????????????????????????????????????????????????????????
???/&??????????????????????????????????????????????????????????????????????
???* $$ EOJ????????????????????????????????????????????????????????????????
?????????????????????????????
Sample DBSU Utility output, copy from Punch List to ICCF library and edit the member as required. (with reference to target database like DB2 LUW)
?COMMENT '*******************************************************************?????????????COMMENT '* This job contains:?????????????????????????????????????????????????????????????*'???????????????????
COMMENT '* - GRANT statements to recreate user authorities????????????*'???????????????????
COMMENT '* - SQL statements to recreate all objects in all dbspaces?*'
COMMENT '* It is to run on a DB2 database on OTHER???????????????????????*'???????????????????
COMMENT *****'*****************************************************************???????????COMMENT '***********************************************************************??????COMMENT '* Change the following CONNECT statement:?????????????????*'???????????????????
COMMENT '*?1) Replace <PW> with the correct password???????????????????*'???????????????????
COMMENT '*?2) Replace <TARGETDB> with the name of the target database??*'
COMMENT '***********************************************************************?????CONNECT SQLDBA IDENTIFIED BY <PW> TO <TARGETDB>;?????????????????????????????
SET ERRORMODE CONTINUE;????????????????????????????????????????????????????????
COMMENT '**************************************************'???????????????????
COMMENT '* Creating user authorities?????????????????????*'???????????????????
COMMENT '**************************************************'???????????????????
GRANT DBADM ON DATABASE TO SQLDBA;
GRANT DBADM ON DATABASE TO LRUSQL;
GRANT DBADM ON DATABASE TO CICSUSER;
GRANT CONNECT ON DATABASE TO PAMMIS;??????????????????????????????????????????
COMMENT '**************************************************'???????????????????
COMMENT '* Creating tablespaces??????????????????????????*'???????????????????
COMMENT '**************************************************'???????????????????
CREATE TABLESPACE LRUSP1 MANAGED BY <system or database> USING <container-clause>;?????????????????????????????????????????????????????
COMMENT '**************************************************'???????????????????
COMMENT '* Creating tables and table privileges??????????*'???????????????????
COMMENT '**************************************************'???????????????????
CREATE TABLE LRUSQL.DBATH01 (USRID CHAR (6),PASWD CHAR (6),AUTHR CHAR (2),EMPNO INTEGER,EMNAM CHAR (30),EMPUSR CHAR (4)) IN LRUSP1;??????????
CREATE TABLE LRUSQL.DBRABTB (RACDE CHAR (1),RANM VARCHAR (20),RAADD1 VARCHAR (20),RAADD2 VARCHAR (20),RAADD3 VARCHAR (20)) IN LRUSP1;
CREATE TABLE LRUSQL.DB06 (STN_CD CHAR (3),STN_NAME VARCHAR (20),STN_TYPE CHAR (1)) IN LRUSP1;????????????????????????????????????????
CREATE TABLE LRUSQL.DB07 (REG CHAR (3),SHORT_NAME CHAR (4),LONG_NAME VARCHAR (16),AC_TYPE SMALLINT,AC_STAT CHAR (1)) IN LRUSP1;??????????????????????????????????????????????????
CREATE TABLE LRUSQL.DB08 (ATA_TRD_CD SMALLINT, CHAPTER VARCHAR (20),SUB_CHAP VARCHAR (20),LRU_SEQNO INTEGER,TOOL_SEQNO INTEGER) IN LRUSP1;????????????????????????????????????????
GRANT ALL ON LRUSQL.DBATH01 TO USER CICSUSER;?????????????????????????????????
GRANT ALL ON LRUSQL.DBATH01 TO USER PAMMIS;???????????????????????????????????
GRANT ALL ON LRUSQL.DBATH01 TO USER GPA;??????????????????????????????????????
COMMENT '**************************************************'???????????????????
COMMENT '* ?????????Creating indexes?????????????????????????????*'???????????????????
COMMENT '**************************************************'???????????????????
CREATE INDEX LRUSQL.DBAUTH01IX ON LRUSQL.DBATH01 (EMPNO) PCTFREE?10;?
CREATE UNIQUE INDEX LRUSQL.I1RABTB ON LRUSQL.DBRABTB (RACDE) PCTFREE?10;
CREATE UNIQUE INDEX LRUSQL.I106 ON LRUSQL.DB06 (STN_CD) PCTFREE?10;??????????????????????????????????????????????????????????????????
CREATE UNIQUE INDEX LRUSQL.I107 ON LRUSQL.DB07 (REG) PCTFREE?10;??????????????????????????????????????????????????????????????????
领英推荐
CREATE INDEX LRUSQL.I207 ON LRUSQL.DB07 (AC_TYPE) PCTFREE 10;???????????????????????????????????????????????????????????????????????????
CREATE UNIQUE INDEX LRUSQL.I108 ON LRUSQL.DB08 (ATA_TRD_CD) PCTFREE?10;??????????????????????????????????????????????????????????????????
CREATE UNIQUE INDEX LRUSQL.I109 ON LRUSQL.DB09 (AC_TYPE,STN_FROM,STN_TO USER) PCTFREE?10;??????????????????????????????????????????????
This will complete my task from DB2/VSE running z/VSE Operating on IBM System z10.
In DB2 LUW for Red Hat Enterprise Linux, create a new database "PAMMDB" to be the target database for the migration of data from DB2/VSE.
CREATE DATABASE PAMMDB
AUTOMATIC STORAGE YES
ON '/home/db2inst1/data/pammdata'
DBPATH ON '/home/db2inst1/databases/pammdb/'
USING CODESET ISO8859-1 TERRITORY EN_US
COLLATE USING IDENTITY
PAGESIZE 8192
DFT_EXTENT_SZ 32
CATALOG TABLESPACE MANAGED BY AUTOMATIC STORAGE
EXTENTSIZE 4
NO FILE SYSTEM CACHING
AUTORESIZE YES
INITIALSIZE 32 M
MAXSIZE NONE
TEMPORARY TABLESPACE MANAGED BY AUTOMATIC STORAGE
EXTENTSIZE 32
FILE SYSTEM CACHING
USER TABLESPACE MANAGED BY AUTOMATIC STORAGE
EXTENTSIZE 32
NO FILE SYSTEM CACHING
AUTORESIZE YES
INITIALSIZE 32 M
MAXSIZE NONE;;
After creating a Database, before creating tablespace, define Storage groups and Buffer pools for each tablespace by using following command
Storage Group Definition:
CREATE STOGROUP SGLRUSP1 ON '/home/db2inst1/data/pammdata/lruspace1';
Buffer Pool Definition:
CREATE BUFFERPOOL BPLRUSP1 PAGESIZE 8192;
Now, customize the tablespace definitions in DBSU utility for DB2 LUW format.
In DBSU Utility tablespace definition:
CREATE TABLESPACE LRUSP1 MANAGED BY <system or database> USING <container-clause>;?????????????????????????????????????????????????????
?To DB2 LUW format
CREATE LARGE TABLESPACE LRUSPACE1
PAGESIZE 8K
MANAGED BY AUTOMATIC STORAGE
USING STOGROUP SGLRUSP1
AUTORESIZE YES
INITIALSIZE 32 M
MAXSIZE NONE
EXTENTSIZE 4
PREFETCHSIZE 4
BUFFERPOOL BPLRUSP1
OVERHEAD INHERIT
TRANSFERRATE INHERIT
NO FILE SYSTEM CACHING
DATA TAG INHERIT
DROPPED TABLE RECOVERY ON;
Configure the DB2 LUW for Linux, database to be enabled for federated database support by using the following command
db2 connect to PAMMDB user db2inst1
db2 update dbm cfg using federated yes
?To verify the FEDERATED parameter by entering the following commands,
? db2 get database manager configuration
db2 get dbm cfg
Look for FEDERATED variable:
Federated Database System Support (FEDERATED) = YES
Configure the DB2 for Linux database to accept incoming TCP/IP connections from remote requesters:
db2 get database manager configuration
db2 update dbm cfg using svcename 50000
db2set db2comm=TCPIP
db2stop
db2start
To verify, look for the TCP/IP Service name variable:
db2 get dbm cfg
TCP/IP Service name (SVCENAME) = 50000
Now configuring DB2 LUW for Red Hat Enterprise Linux to access to remote databases DB2/VSE running on z/VSE Operating System.
Define a TCP/IP node to your system where the DB2 VSE database resides:
db2 catalog tcpip node vselnxnd remote 192.168.1.1 server 546 remote_instance db2inst1 ostype VSE
Catalog the VSE database to your Linux:
db2 catalog db pammdb2 as vsedb2 at node vselnxnd authentication dcs
?Catalog the VSE database as a host or Database Connection Service (dcs):
? db2 catalog dcs db vsedb2 as pammdb2
?Enter the following commands to activate the new directory changes:
db2 terminate
db2start
Test your definitions:
Connect to the z/VSE database VSEDB2:
db2 connect to vsedb2 user sqldba using sqldbapw
If the definition is correct, you see the following connection messages:
Database Connection Information
Database server = SQL/DS VSE 7.5.0
SQL authorization ID = SQLDBA
Local database alias = VSEDB2
Execute a bind to issue select requests. Use the ddcsvse.lst bind file from your installation library in Linux (sqllib/bnd) for the bind:
bind sqllib/bnd/@ddcsvse.lst blocking all sqlerror continue messages vse.msg grant public
To test whether you can select data from VSEDB2, type a select statement as follows:
select * from SQLDBA.EMPLOYEE
db2 connect reset
On the DB2 for Linux server, use PAMMDB as the federated server. Create a DRDA wrapper for federated access.
? db2 connect to PAMMDB
db2 create wrapper DRDA
?On the DB2 for Linux server, create a server for federated access to the remote DB2 Server for VSE server.
? db2 “create server vsedb2 type db2/vse version '7.5' wrapper drda authorization “sqldba”
password “sqldbapw” options (dbname 'vsedb2', password 'Y')”
On the DB2 for Linux server, create a user mapping for federated access to the remote DB2 Server for VSE server.
db2 “create user mapping for "db2inst1" server "vsedb2" options (remote_authid 'sqldba',
add remote_password 'sqldbapw')”
Test the federated access from DB2 for Linux to the remote DB2 Server for VSE database server.
db2 “connect to pammdb”
db2 “set passthru vsedb2”
db2 “select * from SYSTEM.SYSOPTIONS”
db2 “set passthru reset”
db2 “connect reset”
Create a new table in PAMMDB, whose definition matches the table definition on the remote DB2/VSE. Use DBSU Utility output to export table definitions from z/VSE and import them in DB2 LUW on Red Hat Enterprise Linux.
In the final step, you have to do data loading quickly and efficiently. The basic idea of this method is to define federated database cursors on the DB2 LUW server for real tables that exists on DB2/VSE server. Then new tables are created
The DB2 LUW load utility is capable of efficiently moving large quantities of data into newly created tables, or into tables that already contain data.
The DB2 LUW federated server will get the data from the remote DB2/VSE server using a DRDA protocol query over TCP/IP connection. The load command specify the cursor name, which results in obtaining the DB2/VSE data and the target of the load is the new table that was created in DB2 LUW, The net result is that the table data is copied from DB2/VSE source table into the DB2 LUW target table. The copy is performed basically in one step.
For each table identified on the DB2/VSE, which you want to migrate to the DB2 LUW for Red Hat Enterprise Linux server.
From DB2 LUW, declare cursor for database and select statement for specific table and load from Cursor command, specifying the cursor; set the target to the new table that we created in step
DECLARE C1 CURSOR DATABASE VSEDB2 USER SQLDBA USING XXXXXXXXX
FOR SELECT?* FROM LRUSQL.DB01;
LOAD FROM C1 OF CURSOR INSERT INTO LRUSQL.DB01;
Recovering from Failed LOAD Operation in DB2 for Linux
?DECLARE C1 CURSOR DATABASE VSEDB2 USER SQLDBA USING XXXXXXXXX
FOR SELECT * FROM LRUSQL.DB01;
LOAD FROM C1 OF CURSOR RESTART INTO LRUSQL.DB01;
Interesting....