Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    4

    Unanswered: migrating data from DB2 to MS-SQL

    hi,

    i am a novice in DB2 and am looking for a step by step procedure to migrate data from DB2 version7 to MS-SQL. i need immediate help..

    thanks

    jackie

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    I think you could use a linked server to do that ... and if i remember correctly, there is also a whitepaper availible on MS Knowledge Base for the same ...
    Get yourself a copy of the The Holy Book

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

  3. #3
    Join Date
    May 2004
    Posts
    4
    thanks...let me check it out...

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    From the Holy Book
    OLE DB Provider for DB2

    The Microsoft OLE DB Provider for DB2, distributed with Microsoft® Host Integration Server 2000, allows Microsoft SQL Server™ 2000 distributed queries to query data in DB2 databases.



    To create a linked server to access a DB2 database
    1. Install the Windows NT Client for Host Integration Server 2000 or the Windows 9x Client for Host Integration Server 2000 on a computer running an instance of SQL Server. Select the options to install the OLE DB Provider for DB2 and the network components needed to communicate with an IBM computer running in an SNA network.
    2. Determine the connection string the OLE DB Provider for DB2 needs to access the DB2 data source you want to query. The best way to determine a connection string is to build a Data Link file using the Host Integration Server New OLE DB Data Source application. For more information, see the Microsoft Host Integration Server 2000 documentation.
    3. Execute sp_addlinkedserver to create a linked server, specifying DB2OLEDB as the provider_name, the name of the DB2 catalog containing the data you want to access as catalog, and the connection string from Step 2 as provider_string. This example shows how to use sp_addlinkedserver to create a linked server definition accessing a DB2 database:

      EXEC sp_addlinkedserver @server = 'DB2SRV', @srvproduct = 'Microsoft OLE DB Provider for DB2', @catalog = 'SEATTLE', @provider = 'DB2OLEDB', @provstr = 'NetLib=SNA;NetAddr=;NetPort=;RemoteLU=SEATTLE;Loc alLU=LOCAL; ModeName=QPCSUPP;InitCat=SEATTLE; Default Schema=WNW3XX;PkgCol=WNW3XX;TPName=;Commit=YES; IsoLvl=NC;AccMode=;CCSID=37;PCCodePage=1252;BinAsC har=NO; Data Source=Seattle_WNW3XX'
    4. Execute sp_addlinkedsrvlogin to create login mappings from SQL Server 2000 logins to DB2 logins. This example maps the SQL Server 2000 login SQLJoe to DB2 login DB2Joe:

      EXEC sp_addlinkedsrvlogin 'DB2SRV', false, 'SQLJoe', 'DB2Joe', 'JoePwd'
    After completing these steps, you can use the linked server name DB2SRV as the server name in four part names and as linked_server in the OPENQUERY function. For example:

    SELECT *FROM DB2SRV.SEATTLE.WNW3XX.DEPARTMENTOr

    SELECT *FROM OPENQUERY(DB2SRV, 'SELECT * FROM SEATTLE.WNW3XX.EMP_ACT')When the distributed queries against DB2 data sources involve NULL comparisons, use IS NULL or IS NOT NULL rather than comparison operators, such as =, <, or >. In addition, INSERT statements should supply values for all columns in a table even if certain columns in the table can be NULL or have default values.
    Get yourself a copy of the The Holy Book

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

  5. #5
    Join Date
    May 2004
    Posts
    4
    hey that was great...i am going to try that...

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OS/390 or Client Server?

    With the mainframe, you'll need a gateway I believe...have yet to get that working..

    If it is the mainframe, you could unload all the data and ftp it down the bcp it in...

    The deal with conversion issues...wonder if linked servers handle DB2 Dates?
    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.

Posting Permissions

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