Server Rename - Windows 2 Linux - db2move errors
In the process of a server rename from Windows to Linux for a CLM V6.0.2 environment.
Trying to move the databases from DB2 V10.5.8 on Windows to the same version on Linux using this procedure:
- Used db2move to export data on the Windows side successfully, no errors reported.
- Created the new databases on the Linux side, then used repotools-xxx to create the tables for each database.
- Used db2move to import the data on the Linux side with mostly good results. Getting the following errors when I import:
JTS, QM and RM:
IMPORT: table "RESOURCE"."RESOURCE"
ERROR -3088. Check message file tab211.msg
SQLCODE: -3088 - SQLSTATE:
SQL3088N The source column specified to be loaded into database column "JZ_DISCRIMINATOR" is not compatible with the database column, but the database column is not nullable
The tab211.msg file contains these messages:
SQL3168W The PC/IXF column specified to be loaded into the database column "CHANGE_ID" is not compatible with the database column. Nulls will be inserted.
SQL3088N The source column specified to be loaded into database column "JZ_DISCRIMINATOR" is not compatible with the database column, but the database column is not nullable.
SQL3110N The utility has completed processing. "0" rows were read from the input file.
CCM:
* IMPORT: table "SCM "."BASELINE"
WARNING 3107. Check message file tab26.msg
SQL Warning! SQLCODE is 3107
SQL3107W At least 1 load message was encountered during LOAD processing
Rows Read: 20839
Inserted: 0
Rejected: 20839
Committed: 20839
The tab26 file contains these errors:
SQL3148W A row from the input file was not inserted into the table. SQLCODE "-180" was returned.
SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007.
IMPORT: table "MODEL "."CATEGORY"
WARNING 3107. Check message file tab76.msg
SQL Warning! SQLCODE is 3107
*** SQL3107W At least 1 load message was encountered during LOAD processing
Rows Read: 83
Inserted: 83
Rejected: 0
Committed: 83
tab76.msg contains these errors:
SQL3168W The PC/IXF column specified to be loaded into the database column "DEFAULT_CATEGORY" is not compatible with the database column. Nulls will be inserted.
SQL3168W The PC/IXF column specified to be loaded into the database column "DEFAULT_TEAM_AREA_ITEM_ID" is not compatible with the database column. Nulls will be inserted.
Any thoughts as to why I'm getting these errors? What's the best way to fix them?
Thanks,
Fran
OK - so I've figured out what's going on. After using Data Studio and looking at the DDL, the tables with issues have several columns in a different order for the source and destination databases.
For instance, in the RESOURCE.RESOURCE table (in JTS,CCM,QM and RM), the source and destination databases have 16 columns. The first 13 columns are the same. The final 3 in the source database are:
-
"JZ_DISCRIMINATOR" INTEGER NOT NULL
-
"APPLICATION_STORAGE_AREA_ATH_K" VARCHAR(250 OCTETS)
- "CHANGE_ID" VARCHAR(250 OCTETS)
The final 3 columns in the destination database are:
- "CHANGE_ID" VARCHAR(250 OCTETS)
- "JZ_DISCRIMINATOR" INTEGER NOT NULL
- "APPLICATION_STORAGE_AREA_ATH_K" VARCHAR(250 OCTETS)
DB2MOVE is trying to put data from APPLICATION_STORAGE_AREA_ATH_K into the Integer JZ_DISCRIMINATOR which is causing the error.
The tables in the source database on Windows were created as part of the original CLM V4.0.1 installation a few years ago. The database has been upgraded many times to get to V6.0.2.
The tables in the destination database on Linux were created using repotools on Linux in preparation for the server rename.
All of the data import errors in the other databases are similar situations.
I can think of several ways to fix this - but what's the best way? For future upgrades, will it matter what order the columns are in?
2 answers
This will end up as a DB2 that not many here are familiar with. Why don't you use the "repotools -export" and "repotools -import" commands for the task? I think the pair provides the most compatible way to move data across platforms.
Comments
Currently prototyping the server rename using online backups. repotools is very slow and requires the jazz environment to be down to grab the backups.
This is not a lot of errors for an operation like this and I was hoping to get these resolved. Will use repotools for the real move if necessary.
Thanks for the feedback.
I'm a little late to provide an answer but I have actually gone through a similar scenario in the past while bringing an external vendor SQL Server database into one of my customers Oracle environment. It was a very interesting project.