February 23, 2017 at 3:10 am
Hmm... I did it slightly crazy this way. Yes, it was all in SSIS and some C# script tasks, but I just don't like the "Import/Export Wizard" in SSMS.
I used the IBM DB2 OLEDB driver instead of Microsoft's. I had to have the customer get it for me though, as it required an IBM customer login to access it from IBM.
But that was 4 years ago when I did this. Maybe they've changed...
The customer let me use a privileged account on their DB2 server (which I believe was on a AS/400)... I forget which account it was... They were migrating off DB2, and I certainly know 0 about AS/400, so... And they wanted as much off of it as they could get.
in SSIS, I used the OLEDB "GetSchema" command in C# script task to get the equivalent of "INFORMATION_SCHEMA" views - tables, columns, indexes... and stored that data into tables on their new SQL Server. From there, I did whatever column type mapping from DB2 to SQL Server datatypes and made my own SQL Server create table scripts to create my SQL Server tables, and could hand-tweak my SQL Server DDL scripts as needed.
With help of IBM's dev website, I figured out where in the DB2 system tables to query table sizes and row counts. That was helpful to know, too, and I of course pulled that into SQL Server tables too. The privileged account facilitated that...
With some of the large "blobby" fields in DB2, there were some collation issues on the DB2 side I had to be mindful of for transferring data to SQL Server. I found out workarounds on the IBM technical website.
DB2's date/time fields *should* in theory work just fine into SQL Server's datetime2 data type. I had pretty good luck with for the most part. Other times it caused me to have to put a column transform into that particular data flow for those columns.
Hopefully there's no "BCD" (Binary Coded Decimal) fields to transfer! You'll need to come up with a workaround for those, I believe, since SQL Server doesn't support BCD data type.
Once I got my tables defined and built in SQL Server, I did do something like a BIML script to automate creating a bazillion SSIS package s from the downloaded DB2 schema, one package for each table. Again, I needed to tweak a few of the packages to get them to run or complete running.
Then I created another SSIS package that simply iterated through those packages serially.
I had to do the data transfers off-hours , respecting the customer's backup windows etc. This way it was easy enough to transfer 99% of the tables and do them incrementally, and then figure out workarounds for the problem ones.
There were a few columns/data types I had to throw in some data conversions into the data flows for various reasons.
IBM's developer documentation website was helpful for all of this.
Dumping a several gigabyte database to text files did not seem like a good option for either of us. If you do it that way, do it with tab-delimited files, not CSV.
The IBM DB2 OLEDB driver was faster than Microsoft's, if memory serves me. It worked better with more of the DB2-specific data types and edge cases too.
If using the GetSchema methods in OLEDB or ODBC .Net libraries, beware a couple of the methods have bugs that have not ever been fixed. I don't remember which one it was for (indexes, IIRC), but one of the bugs forced you to just download the whole schema entity, you couldn't run it for specific items, despite the documentation.
If you're really crazy, the ODBC GetSchema() methods work against Timberline's "ODBC" driver. Normally, you cannot get catalog info out of that driver interactively... I could not tap into the base BTrieve driver layer, either... slower than the sample Text ODBC driver from Microsoft...
YMWV.
good luck. It was one of the more entertaining and gratifying nuts I've cracked.
February 24, 2017 at 10:27 am
Divine Flame - Wednesday, February 1, 2017 6:15 AMHi Sam,I know it is an old thread but could you confirm if SSIS can be used to migrate the DB2 views and other such PL\SQL objects to SQL Server database? Would SSIS be able to convert the DB2 syntax to SQL Server syntax?
Thanks for the help.
We've done it using both SSIS and database links between SQL and DB2, you'll just need to grab the Microsoft OLE DB Provider for DB2. With this either add your DB2 server as a Linked database or you can setup an OLE DB source to DB2 through SSIS. For smaller tables the linked servers work well, but for anything large (many gigs of data) I'd suggest using SSIS to migrate the data. Just be sure your data types all match or you'll run into a nightmare.
February 27, 2017 at 8:16 pm
Divine Flame - Wednesday, February 1, 2017 6:15 AMHi Sam,I know it is an old thread but could you confirm if SSIS can be used to migrate the DB2 views and other such PL\SQL objects to SQL Server database? Would SSIS be able to convert the DB2 syntax to SQL Server syntax?
Thanks for the help.
There is a migration assistant for DB2 but I don't know if it would work if you really do have PL/SQL code in there.I have never worked with the PL/SQL compiler they added to DB2 so don't know what it does to the code but I would guess that makes a difference. Hopefully it's a typo and you meant SQL PL. That would make things easier.
Sue
April 16, 2018 at 5:29 am
I need help in getting the whole database migration from DB2/VSE under z/VSE Operating System on IBM System z10 System to Microsoft SQL Server 2008 or Above.
June 3, 2020 at 9:29 am
This was removed by the editor as SPAM
June 23, 2020 at 5:32 am
This was removed by the editor as SPAM
August 26, 2024 at 2:31 pm
This was removed by the editor as SPAM
August 26, 2024 at 2:31 pm
This was removed by the editor as SPAM
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply