Conversion of strings with binary values

This is a question of converting strings from DB2 to SQL Ser开发者_如何学运维ver.

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.

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.