Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Rueda
Contributor
Contributor

How to convert "NEL" from DB2 iseries to SQL Server

Hello all,

We've a field in our source table in DB2 that contains the value "NEL" (EBCDIC new line):

Apparently there’s no newline, but when that is pasted to an editor which can show special characters, the following is shown:

Rueda_2-1608652912456.png

But, in the SQL Server  we’ve lost that special character, gets replaced by a question mark (“?”):

8000 -------:?Nr. -------

According to the EBCDIC specification (e.g. this one), “NEL” is the convention in EBCDIC for a new line, which makes sense according to what’s shown in our main app.

Is there a way to configure the settings in Attunity so that this special character is not lost and we can identify it, so that we’re able to handle it and differentiate it from a “real” “?”?

 

Test already done:

After check a similar post in this forum for Null value we did:

Rueda_3-1608653067714.png

Also test with u+A --> An there is the result

Rueda_1-1608652816209.png

We got the same error using:

Line and Paragraph Separator. The Unicode Standard provides two unambiguous characters, U+2028 line separator and U+2029 paragraph separator

 

Do you have any idea how to convert this "NEL" value to the correct character in SQL?

Labels (1)
1 Reply
john_wang
Support
Support

Hello @Rueda ,

 

Seems it's hard to understand what's the real values in the source table from the above description, in my env the "New Line" character was replicated to SQL Server correctly. I'd like to understand further:

1. What's the hex values in your source table ?

2. What's the data type of the 'problematic' column? in general the "new line" was not stored in char/varchar columns, the usage is a bit weird (it's more reasonable if it's a CLOB column).

3. What's the collation of the SQL Server target DB

4. Please create a SalesForce ticket with below information (our region support team will help):

    4.1 - the SAVF of the table

    4.2 - the task Diagnostics Package with source_unload/source_capture/target_load/target_apply set to verbose

              It's better you use 2 (a few of) rows to demonstrate the issue so far it's easier to read/analyze the task log file. Please marke which columns values are problematic.

 

In My experience, the source table CHAR column contains hex value

0D25

(see https://www.ibm.com/support/pages/inserting-end-line-characters-and-format-data-data-transfer-downlo...) , it's replicated to hex value

0D0A

in SQL Server target table, it's correct in both Full Load and CDC without any transformation or Character Substitution (Replicate 6.6 SP07).

 

Hope this helps.

Happy New Year,

 

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!