Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2010
    Posts
    36

    Unanswered: Migrating from MSSQL to DB2 : ntext vs CLOB or DBCLOB

    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

  2. #2
    Join Date
    Jun 2010
    Posts
    36
    Anyone?

    Thanks

    Charles

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

  4. #4
    Join Date
    Jun 2010
    Posts
    36
    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

  5. #5
    Join Date
    Jun 2010
    Posts
    36
    Quote Originally Posted by n_i View Post
    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

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

  7. #7
    Join Date
    Jun 2010
    Posts
    36
    Allright I will try to run the migration directly on the dev server.

    Thanks a lot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •