1

This is a question of converting strings from DB2 to SQL Server.

On DB2 you can have a column that contains a mix of strings and binary data (e.g. using REDEFINS in COBOL to combine string and decimal values into a DB2 column).

This will have unpredictable results during data replication as the binary zero (0x00) is treated as string-terminator (in the C family of software languages).

Both SQL Server and DB2 are able to store binary zero in the middle of fixed length char columns without any issue.

Has anyone any experiences with this problem? The way I see it, the only way to fix it, is to amend the COBOL program and the database schema, so if you have a column of 14 chars, where the first 10 is a string and the last 4 a decimal, split this up into two columns containing one "part" each.

Improve this question
2
  • Can you elaborate - do you have the ability to modify the COBOL app? When you say replication - are you referring to your own replication or SQL Server Replication? I don't know about the DB2 column type, but splitting them up into their integral parts definitely sounds like the way forward. If you just want to store the DB2 data identically in SQL Server, I'd use a (var)binary(x) data type - it'll store whatever you wish. Commented Oct 4, 2011 at 19:05
  • Hi Mark, Thank you for the comments. I do "own" the COBOL app, but of course the developer/business might not be thrilled with the thought of changing it. About the replication, then I'm sorry for the confusion. It should have said migration, e.g. a one time transfere of data from DB2 to SQL. Commented Oct 5, 2011 at 10:27

1 Answer 1

Reset to default
1

If you want to just transfer the data 1:1, I'd just create a binary(x) field of equal length, of varbinary(x) in case the length differs.

If you need to easily access the stored string and decimal values, you could create a number of computed columns that extract the string/decimal values from the binary(x) field and represents them as normal columns. This would allow you to do an easy 1:1 migration while having simple and strongly typed access to the contents.

The optimal way would be to create strongly typed columns on the SQL Server database and then perform the actual migration either in COBOL or whatever script/system is used to perform the one time migration. You could still store a binary(x) to save the original value, in case a conversion error occurs, or you need to present the original value to the COBOL system.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks again for your answer. I will try to see if it is possible to change the schema for SQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.