Migrate DB2 (z/OS) v11 to SQL Server 2016 (or 2019), possible? RRS feed

  • Question

  • Need to migrate a DB2 database running v11.1 on z/OS to MS SQL Server.

    Is there a version of SSMA that supports DB2 v.11.1 on z/OS and migrete into SQL Server 2016 or later?

    Wednesday, January 9, 2019 12:57 PM

All replies

  • Is SSMA, SSMS? How much data are we talking? Do you just want the tables, tables and data. Do you also want indexes, triggers, foreign keys, views, etc. Does the DB2 have blob, clob or any unusual data types?

    Googling "how to migrate a db2 database to sql server" gave some microsoft docs.

    We import DB2 table data into SQL tables as part of a nightly import. When there are new DB2 tables added we have to add the same tables. We have IBM iAccess installed and use the OLE DB IBMDA400 provider to query DB2 from SQL. We use IBMDA400 in an SSIS package and in Linked Servers. SSIS package is much faster for large amounts of data. 

    If you want to query the DB2 catalog I use some variation of the following. This can tell you the names of the tables you need to import and do a DISTINCT on the column type to see what type of you data that you need to import. If you find any additional DB2 system type queries please pass them on. I have spent quite a bit of time looking for them and have really just come up with these as being useful.

      Get AS400 Table and Column definitions
      -- AS400 is Case Senstive - Column Names in UPPER CASE
      -- some older IBMDA400 (linked servers) iaccess clients do not like these commands
    		or maybe it is the iSeries version
    Has a list of qsys2 commands
    DB2 SQL syntax
    -- AS400 Table Defintion - Has Table Description
    select * from openquery(Store03_db2_linkedserver,
    'SELECT * FROM sysibm.sqltables WHERE TABLE_NAME = ''ICMCGCF'' AND TABLE_SCHEM = ''Store04'' ')
    -- AS400 Table Defintion
    select *  from openquery(Store03_db2_linkedserver,
        'SELECT * FROM qsys2.tables WHERE TABLE_NAME = ''ICMCGCF'' AND TABLE_SCHEMA = ''Store04'' ')
    -- AS400 Return all columns for a tables in the Store Library - has COLUMN_TEXT
        from openquery(Store03_db2_linkedserver,    'SELECT * FROM sysibm.sqlcolumns WHERE TABLE_NAME = ''RMCUSR'' AND TABLE_SCHEM = ''Store01'' 
             order by COLUMN_NAME ') 
    -- AS400 Return a single column
    select * from openquery(Store08_db2_linkedserver,
    		'SELECT * FROM sysibm.sqlcolumns WHERE TABLE_NAME = ''ICMCGCF'' AND TABLE_SCHEM = ''Store04'' and COLUMN_NAME like 
    		''%RMIBPG%'' order by ordinal_position ')
    -- as400 find libraries
    select * from openquery(Store72_db2_linkedserver, 'select * from qsys2.SYSSCHEMAS where SCHEMA_NAME like ''Store__'' order by SCHEMA_NAME')

    New tables we create the SQL. Of course if you combine a query from above into a loop you could easily create all the DB2 table in SQL.

     IF EXISTS (SELECT * FROM sys.tables WHERE NAME = 'TableName' )
        DROP TABLE [dbo].[TableName]
    SELECT a.* INTO TableName  
        FROM (SELECT * FROM OPENQUERY(Store01_db2_linkedserver,
        'SELECT * FROM Store01Library.TableName  Where 1=2') ) a 

    I have code to import data using the linked server, which takes longer than SSIS, but is easier to code and run, especially it is a one time use. Let me know if you want me to post it. 

    Wednesday, January 9, 2019 2:49 PM
  • Hi TomwiiSwe,

    The latest version of SSMA for DB2 is designed to support migration from DB2 on z/OS versions 9.0 and 10.0 to SQL Server 2012 through SQL Server 2019 and Azure SQL Database. So it doesn’t support DB2 on z/OS v11.

    I would suggest you try this with SSIS. You will need OLE DB Provider for DB2. The Microsoft OLE DB Provider for DB2 version 6.0 supports connectivity to IBM DB2 for z/OS V11. For more information, please refer to SQL Server Integration Services and OLE DB Provider for DB2. There is also a good pdf guide Guide to Migrating from DB2 to SQL Server and Azure SQL DB.

    Best Regards
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Edited by Puzzle_Chen Thursday, January 10, 2019 2:10 AM
    Thursday, January 10, 2019 2:10 AM