Junk Character missing in Attunity replicat

[Gopikrish6]

Hi All,

Source as Db2 column datatype as (CHAR(24) FOR SBCS DATA NOT NULL WITH DEFAULT), While in Attunity replicat showing as CHAR(50) but it replicat to Sql server as blank Values.

Then i changed into WCHAR(50) Even i get blank values.

Kindly share your suggestions to replicat the junk characters in the Sql server target.

Thanks,

Gopi

[Steve_Nguyen]

there are couple of article on this :

https://community.qlik.com/t5/Qlik-Replicate/Qlik-replicate-setting-to-convert-special-character-to-...

https://community.qlik.com/t5/Qlik-Replicate/DB2-How-to-use-transform-expression-with-special-charac...

https://community.qlik.com/t5/Qlik-Replicate/DB2-to-Kafka-NUL-character-problem/td-p/1728163

https://community.qlik.com/t5/Qlik-Replicate/How-to-convert-quot-NEL-quot-from-DB2-iseries-to-SQL-Se...

[Gopikrish6]

Hi All,

Still am not getting junk character in the Sql server.

Db2 values as 

 �������

Sql server showing as Blank Values.

Kindly suggest to get the Junk character in the Sql server target end.

Thanks,

[Gopikrish6]

Hi All,

Please give inputs to resolve the issue.

Still am not getting junk character in the Sql server.

Source: Db2 values as      ?�?��?�?�?��

Sql server showing as  0x100F000F000F000F000F000F000F000F000F000F000F000F

Kindly suggest to get the Junk character in the Sql server target end.

[Steve_Nguyen]

you need to determine what is the special character and then use transformation to change it.

[Gopikrish6]

Hi, 

First i fix special characters into HEX value.

 But need output as (convert HEX into INT)..

In Attunity how to Convert the HEx value into INT. Kindly help

[Gopikrish6]

Hi All,

S0urce side DB2 z/OS i got Special characters ?�?��?�?�?��

Now Converted in the Sql Server Target as  HEX VALUES. 0x100F000F000F000F000F000F000F000F000F000F000F000F

Next i need to convert the HEX values into INTERGER.

Kindly say how to do in Attunity. Tired with Global Transmission but it not working.

I got results from Sql server target (SELECT CONVERT(INT, CONVERT(VARBINARY, '0x053F030F007F003F002F001F001F001F001F001F000F000F', 1)))

Thanks,

Gopi

[Barb_Fill21]

HI @Gopikrish6 

What are the HEX  values on the mainframe? example:  if a col has  " 1 2"   ( one, blank or space , two)  

Then it would look like this:   F1 40 F2 in hex on the mainframe.

or         F 4 F

             1 0 2

Please supply this info here... 

You should be able to go from Db2 to SQL server and carry the characters over if you need them.

Sincerely

Barb

[Gopikrish6]

Hi All,

Those values are COMP -3 Packaged Decimals fixed in the DB2.

����������� � ����������������������

Samples values as above.

I convert those values to HEX using Bytes option in the QLIK.

Now how to convert into INT or Decimal.??

Kindly share your options. 

Thanks,

[Heinvandenheuvel · Accepted solution]

>> Those values are COMP -3 Packaged Decimals fixed in the DB2.

Then why are they not mapped as such in the source DB? Now they are indeed just 'junk' - a binary string.

Replicate (SQLite) has no useful function to manipulate 'bits' or 'nibles' inside the packed decimal. One would need an 'unpack' or similar function. You really should look at solving this in the source DB by properly describing the column or use some magic convert there. Maybe you can do something on the target with a computed by, but it would be a complex function ( somewhat related reading - https://www.mssqltips.com/sqlservertip/3459/importing-mainframe-data-including-packed-numbers-with-s... ) 

Your only option in Replicate is to create a User Defined function. Those are somewhat documented through provided examples and a readme, but this is tricky stuff which is typically best done using Qlik Professional Service or a consultant using Visual C (when using  Replicate on a  Windows server). You have to decide whether it is worth it. I expect 3 .. 10 days ($5,000 .. $50,000) for full testing allowing for multiple input variations  and some documentation.