topic Re: Junk Character missing in Attunity replicat in Qlik Replicate

Junk Character missing in Attunity replicat

Gopikrish6 — Thu, 25 Aug 2022 17:42:57 GMT

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

Re: Junk Character missing in Attunity replicat

Steve_Nguyen — Thu, 25 Aug 2022 18:55:30 GMT

there are couple of article on this :

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

https://community.qlik.com/t5/Qlik-Replicate/DB2-How-to-use-transform-expression-with-special-character-in/td-p/1895495

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-Server/td-p/1770039

Re: Junk Character missing in Attunity replicat

Gopikrish6 — Fri, 26 Aug 2022 08:51:57 GMT

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,

Re: Junk Character missing in Attunity replicat

Gopikrish6 — Mon, 29 Aug 2022 10:00:38 GMT

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.

Re: Junk Character missing in Attunity replicat

Steve_Nguyen — Tue, 30 Aug 2022 21:00:58 GMT

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

Re: Junk Character missing in Attunity replicat

Gopikrish6 — Thu, 01 Sep 2022 15:07:49 GMT

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

Re: Junk Character missing in Attunity replicat

Gopikrish6 — Fri, 02 Sep 2022 05:38:51 GMT

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

Re: Junk Character missing in Attunity replicat

Barb_Fill21 — Fri, 02 Sep 2022 18:45:37 GMT

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

Re: Junk Character missing in Attunity replicat

Gopikrish6 — Tue, 06 Sep 2022 10:46:52 GMT

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,

Re: Junk Character missing in Attunity replicat

Heinvandenheuvel — Tue, 06 Sep 2022 13:37:14 GMT

>> 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-sql-server-integration-services/ )

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.

Re: Junk Character missing in Attunity replicat

Gopikrish6 — Wed, 07 Sep 2022 05:30:31 GMT

Hi Qlik Experts,

Still am not getting proper solutions for the COMP 3 Packaged decimals convert HEX into INT.

Kindly share the Solutions.

Thanks,

Gopi

Re: Junk Character missing in Attunity replicat

shashi_holla — Tue, 27 Sep 2022 20:51:59 GMT

Hi @Gopikrish6

You can add the same logic in table level transformation to the column if its working in SSMS:

CONVERT(INT, CONVERT(VARBINARY, $COLUMN_NAME, 1))

Thank you,