2

I am currently trying to convert a SQL Server database to DB2. I am using the Data Conversion Workbench (DCW) for IBM Data Studio. The issue I have is that DCW can not seem to correctly extract data from SQL Server to flat files. All the columns in my database (in SQL Server) that have the data type of uniqueidentifier are not extracted correctly by DCW. Any data that is of the uniqueidentifier type is written as random symbols and characters. What could be the reason?

1

I feel your pain. I've worked with both SQL Server and DB2. I will admit that IMHO, the data extraction and importing tools are not as simple or straightforward to use in SQL Server as they are in DB2.

If you wish to stick to the database tools, here is what I recommend: Create queries to extract your data. Use CONCAT to encase each field with a character (one that won't conflict with your data). Hopefully you don't have millions of rows. Then run the query and copy and paste results into something like Notepad++ and save it as a CSV file. You can now use db2import to pull that data in much cleaner and easier. Clunky, yes. But its the best I've found. I've yet to find an easy way to use bcp or SSIS to do what db2import, db2load, and db2export do....

Anyway, if that doesn't suit your fancy, or if the data sets are too large for that, you may wish to consider a different tool. There are ETL tools (perhaps you have one in shop?) to move the data across. Microsoft's SQL Server Integration Services (SSIS) or IBM's InfoSphere DataStage are just two examples of this. (I've not really worked with SSIS, and what little I tried, it wasn't simple or intuitive to me compared with db2export. But perhaps that is just me and I need further exposure/training.)

Another tool (and one I can happen to leverage as I am not only a DBA, but also a Java developer) is to leverage an existing API such as pure JDBC in tandem with Apache POI or Apache Commons-CSV to get the data extracted in simple format. POI lets you extract to Excel format (both .xls and .xlsx) and Commons-CSV lets you obviously extract to CSV format. And once again, when you have the data in CSV format, it is easy thing to import/load into DB2.

This will help with some of the data issues. You still may need to spot check the data to make sure you don't have any codepage conversions going on. If so, you may need to deal with that in your extraction process.

I realize others will mention bcp or SSIS. I can only speak to what I have experienced. But hopefully I've given you some other options to consider.

0

An answer constructed from comments:

What are you expecting in the flat files? uniqueidentifier is a 16-byte GUID, so in its native format it's just a series of raw bytes. (Which would be mapped to CHAR(16) FOR BIT DATA in DB2). – Ian Bjorhovde Jan 20 '16 at 20:32

As mentioned, a GUID is just a 16-byte value (you could think of it as a 128-bit number if you wanted). What SQL Server is showing you is a simply a common representation of that number, while what DB2 is showing you is those 16-bytes as ASCII characters. If you use the HEX() function when querying the column you'll probably see something closer to the SQL Server representation (but without the dashes which aren't part of the column's data; just the representation SQL Server's using) – Dave Jones Jan 21 '16 at 12:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.