locked
Data Validation after Migration RRS feed

  • Question

  • Not sure if this is the right forum ....Please guide to the right on if itd not ..

    We are migrating data from DB2 on mainframe to SQL Server 2008 R2 .After the migtation is over , we need to do data validation to ensure that the data is valid and correct ..

    Has anyone done that before or are there any such tools available outside ..

    Regards


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Wednesday, January 26, 2011 5:12 PM

Answers

  • hi,

    I would create a linked server to DB2, create a staging database on the SQL Server, create views to the DB2 back-end tables and views. Then run the tablediff utility.

    The only caveat: obviously it will transmit all the back-end data, thus generate a high load on the DB2 server, the network and your SQL Server.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Monday, January 31, 2011 12:00 PM

All replies

  • Hi Abhay,

    You can use DBCC CHECKDB to check logical and physical integrity of all the objects in the specified database. For more information about DBCC CHECKDB, see http://msdn.microsoft.com/en-us/library/ms176064.aspx.


    Best Regards,
    Chunsong Feng [MSFT]

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Proposed as answer by John Sansom Friday, January 28, 2011 9:38 AM
    • Unproposed as answer by Abhay_78 Friday, January 28, 2011 11:35 AM
    Friday, January 28, 2011 9:30 AM
  • This is not what I was asking for Chungfong .I think I shoud have posted this question to some other forum ....If someone can move this thread to that forum that would be great ...

    Regards


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Friday, January 28, 2011 11:37 AM
  • hi,

    We are migrating data from DB2 on mainframe to SQL Server 2008 R2 .After the migtation is over , we need to do data validation to ensure that the data is valid and correct ..

    This really depends on what kind of migration was done. If you simply moved the storage from DB2 to SQL Server then you only need to compare the tables and the view row by row, column by column. A simple approach would be creating a linked server to your DB2 database and use the tablediff utility to compare the tables and views:

    http://msdn.microsoft.com/en-us/library/ms162843.aspx


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Friday, January 28, 2011 11:46 AM
  • ABhay,

    What are you trying to achieve? For database integrity checks, yes, Feng's suggestion DBCC.

    But for general data validation, I don't think there is any tool existing to use. Maybe there is some database compare tools on the market that can compare row by row.

     

    Regards


    Sevengiants.com
    Friday, January 28, 2011 12:57 PM
  • Hi,

    I would suggest you to use manual testing of records counts between your source and target database.If you want compare at column level, you can use file compare tools (lot of them available in the market for free for couple of weeks).

     

    Thanks, Sengwa

     

     

     


    Best regards, Sengwa
    Friday, January 28, 2011 5:09 PM
  • Thanks Stefan .But will tablediff work for DB2 Linked server ???? I have used it many times though but not sure if it will work for the linked server ...
    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Friday, January 28, 2011 5:41 PM
  • hi,

    I would create a linked server to DB2, create a staging database on the SQL Server, create views to the DB2 back-end tables and views. Then run the tablediff utility.

    The only caveat: obviously it will transmit all the back-end data, thus generate a high load on the DB2 server, the network and your SQL Server.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Monday, January 31, 2011 12:00 PM
  • Stefan ,

    My question is "WILL TABLEDIFF SUPPORT LINKED SERVER ? IF YES WILL IT SUPORT LINKED SERVER TO DB2 ?".Sorry but I do not have the access to DB2 enviornment yet ...else  I woul dhave tested it ..

    Regards


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Monday, January 31, 2011 3:02 PM
  • Hi,

    Both SQL and DB2 let you export table contents to CSV, then compare them as character files.

    However, there are some caveats and you'll have to enforce rules to deal with :

    1. NULLs, spaces and empty strings. How will they be represented on CSV on both sides?

    Is " " same as "     ". Is NULL same as ""?

    2. non-ASCII text characters (anything below 0x20 or above 0x7f plus Unicode strings)?

    Will control characters like TAB or CR/LF be converted properly? What about non-English characters?

    3. Binary fields : VARBINARY and IMAGE.

    4. Floats.

    1/7 may be represented as 0.142857 on one server but as 0,142857 or 1.428571E-1 on the other

    5. Date and time data. Sometimes it resides on "pure" datetime fields, sometimes on alphanumeric or

    even numeric fields. "December 31st, 1999" = "19991231" (string) in some places, 19991231 (int) in others,

    36525 (number of days since 1900), 946598400 (number of seconds since Jan 1, 1970) or "31/12/1999"

    (locale nightmare), and let's not forget about time representations (1:12 PM, 13:12:00, 0.55 day, GMT or

    EST or PDT?)


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Tuesday, September 10, 2013 5:09 PM
  • All answers in this thread are about technical validation of data. I presume that migration is being done because new IT system has been developed and users will also migrate.

    After data migration, besides technical validation, you should also do business validation. You should take some reference report from DB2 based system and run those same reports on SQL 2K8 R2 to see if you get the same results. The reports to compare should be defined by key business users.


    Regards, Dean Savović

    Tuesday, September 10, 2013 5:32 PM