Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2002
    Posts
    84

    Lightbulb Unanswered: Migrate from DB2 to MSSQL

    Dear All,

    I am exploring the feasibility of migrating from DB2 (v7) to MSSQL server 2005. My current applications are using UDB, but the DBA wants to change to MSSQL. I believe the impact is huge. For example, how to create schema in MSSQL, how to migrate the data...

    Anyone had the experience before? Could kindly suggest some reference for such a migration? Thanks!


    Regards,

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    From the database perspective, this is almost trivial.

    Use ErWin, Visio, or another modeling tool to harvest your schema from the DB2 database, then project that (empty) schema into an MS-SQL database), then use DTS or a similar tool to migrate your data. If you aren't as finicky about the schema details as I am, you can simply use DTS all by itself, which will get you pretty close.

    A much, much larger issue is the difference in string and date handling. DB2 syntax for both string and date operations is significantly different than MS-SQL syntax for the same operations. Unless you've been abnormally careful to avoid any manipulation of strings and dates within your SQL, there will be a potentially enormous amount of effort needed for this conversion.

    -PatP

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    String Manipulation?

    Dates, OK, you are going to have to trim the DB2 dates as SQL Server only supports 3 ms...and only cpu clock speeds...I believe every .333 ms

    But strings/chars?

    Huh?

    And what, besides sheer whim or only knowledge of sql sever does the dba want to move off DB2
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Apr 2002
    Posts
    84
    Sorry for my ignorance: my table in DB2 is something like "db2n1.tuser", while db2n1 is the schema and tuser is the table name. Is there such a corresponding schema name in MSSQL?


    Quote Originally Posted by Pat Phelan
    From the database perspective, this is almost trivial.

    Use ErWin, Visio, or another modeling tool to harvest your schema from the DB2 database, then project that (empty) schema into an MS-SQL database), then use DTS or a similar tool to migrate your data. If you aren't as finicky about the schema details as I am, you can simply use DTS all by itself, which will get you pretty close.

    A much, much larger issue is the difference in string and date handling. DB2 syntax for both string and date operations is significantly different than MS-SQL syntax for the same operations. Unless you've been abnormally careful to avoid any manipulation of strings and dates within your SQL, there will be a potentially enormous amount of effort needed for this conversion.

    -PatP

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by qxz
    Sorry for my ignorance: my table in DB2 is something like "db2n1.tuser", while db2n1 is the schema and tuser is the table name. Is there such a corresponding schema name in MSSQL?
    Yes, there is. Your DBA can tell you for sure, but my first guess is "dbo".

    You are treading very near one of the confusing points in the switch from DB2 to MS-SQL... In MS-SQL 2000 and all earlier versions, there was a logical concept called a "user" that actually spanned across many of the concepts such as schema, permissions, etc in the purely relational world. Each user could logically own objects within the database, giving them something quite close to their own schema. There was a kind of "uber user" named dbo (an acronym for DataBase Owner) that was the default user under the name resolution rules.

    In MS-SQL 2005, true schemas were introduced into the product. The schemas don't behave quite the way that you are accustomed to in DB2, but they're pretty close. For the moment, I wouldn't worry too much about them unless you have to make adjustments to cope with them.

    -PatP

Posting Permissions

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