Microsoft Sql Server to DB2 ZOs

gylbharat · Posted: Thu Sep 27, 2012 3:16 pm

Hi,

I am working on a project, where we have to migrate from Microsoft Sql Server 2008 to DB2 9 Zos.

Can you please suggest how we can convert the Sql server DDL's to DB2 ZOs DDL's?

I tried with IBM migration tool kit, It converts from Sql server to DB2 9.7 Luw and also there are some issues when this tools does the conversion. Its does not process XML and BLOB data types correctly.

Nic Clouston · Posted: Thu Sep 27, 2012 4:58 pm

If an IBM supplied tool does not work properly then contact IBM. They may have a fix, may not have faced this problem before or say 'tough - it says in the manual that this happens' or whatever. But if what you are facing is not documented then they need to know.

sushanth bobby · Posted: Wed Oct 03, 2012 3:50 pm

Hi Gylbharat,

As per this link, the migration toolkit does not support it and the tool itself is deprecated.

This link might help you to picture the opposite

SQL Server sql limits are bit higher when compared with DB2 like maximum number of columns DB2 can have, stuff like that. Should review the DDL's in SQL server and compare with the limits in DB2.

What about stored procedures ? Are you trying to migrate that also or rewrite in cobol ?

For XML data, try to check whether they are bigger than 32K if not, as a first step, you can move them to VARCHAR and later to XML datatype.

Thanks,
Sushanth

gylbharat · Posted: Thu Oct 04, 2012 10:41 am

Thanks sushanth !!

I already found that document by googling it

There are only tables and views in Sql server database. No other objects.

My only concern is the tables contain LOB and XML data types.

I have not worked with these datatypes before...

I need to work on the performance tuning of these also. These tables will be accessed by dynamic sql through JDBC Driver...

Can any one let me know some best practices (system parameters/table space parameters) in defining these tables on DB2 v9.1?

sushanth bobby · Posted: Thu Oct 04, 2012 12:25 pm

gylbharat,

SSIS would be the easiest way to transfer data from SQL Server to DB2 z/OS and SSIS package can be easily built using SQL Server Management Studio itself, since you have datatypes defined as XML and LOB's in DB2, you may have challenges there, for that you have to get help from a person who is quite good with SSIS, basically you have to make some edits in the column mappings.

Are the columns in SQL server which have XML data are they XML data type or VARCHAR, because to successfully insert XML they need to be well formed in DB2.

SSIS Export Transform

Mapping Data Types from SQL Server to IBM DB2

This link has query to check the DATALENGTH, which might help you find length of XML/LOB columns

Thanks,
Sushanth