Migrating from MSSQL to DB2 : ntext vs CLOB or DBCLOB

CharleyDC5 · #1 (**permalink**) 07-08-10, 13:20

Hi folks,

I am currently migrating a database from MSSQL2000 to IBM DB2 UDB 9.5 WSE (running on Windows Server 2008).

I used the IBM MTK (Migration Toolkit) for the migration process. My source database has some ntext fields and I am having problems importing/loading the data to my DB2 database.

If I keep the default Global Type Mapping configuration (NTEXT > DBCLOB), I get the following error message for every ignored/rejected rows :

Code:

SQL3229W  The field value in row "1" and column "40" is invalid. The row was rejected. Reason code: "1".

SQL3185W  The previous error occurred while processing data from row "1" of the input file.

Only the rows with empty or null values in the ntext fields are getting imported. All rows having characters/content in the ntext fields are ignored.

If I use my personal Global Type Mapping configuration (NTEXT > CLOB (2000000000)), DB2 does not extract the table at all.

Any thoughts?

Thanks for help

Charles

CharleyDC5 · #2 (**permalink**) 07-09-10, 08:03

Anyone?

Thanks

Charles

n_i · #3 (**permalink**) 07-09-10, 10:10

Quote:

SQL3229W The field value in row "<row-number>" and column
"<column-number>" is invalid. The row was rejected. Reason code:
"<reason-code>".

Explanation:

The row of data from the input file has invalid data.

Reason codes:

1 The file named in the row and column cannot be found.

2 The file named in the row and column cannot be accessed.

3 Data is too long for the column.

The row was not inserted. Processing continues with the next row.

User response:

If necessary, correct the input file and resubmit the command.

LOBs are loaded into a DB2 table from files. Make sure that the LOB files are available in the path where they are expected. If you do the load remotely, the LOB files must be on the DB2 server.

CharleyDC5 · #4 (**permalink**) 07-09-10, 10:28

Is it a configuration I can set in the MTK itself, or I have to play with the code before deploying the project to target?

Regards

CharleyDC5 · #5 (**permalink**) 07-09-10, 10:33

Quote:

Originally Posted by n_i

LOBs are loaded into a DB2 table from files. Make sure that the LOB files are available in the path where they are expected. If you do the load remotely, the LOB files must be on the DB2 server.

Yes I am running/deploying the project using the MTK from my laptop to the DB2 dev server.

Thanks

n_i · #6 (**permalink**) 07-09-10, 13:53

Sorry, I never used MTK so I can't say if it can be configured or not. Theoretically speaking, there would be two steps involved: export from MSSQL, then import (or load) into DB2. Files are created at the first step and placed somewhere in the file system; the second steps picks those files and loads their content. If in MTK you can perform these steps separately, you would need to pause after the first step and transfer LOB files to the server, then run the second step (path and file names must not be changed).

Better yet, if the target DB2 server is also Windows, you run MTK from the DB2 server.

CharleyDC5 · #7 (**permalink**) 07-09-10, 14:28

Allright I will try to run the migration directly on the dev server.

Thanks a lot!

Internet Services

Web Development

Digital Marketing

Consumer Tech