Japanese Data in SQL Server 2000

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

anandkumarm
Premium Member
Premium Member
Posts: 55
Joined: Tue Feb 24, 2004 8:17 am

Japanese Data in SQL Server 2000

Post by anandkumarm »

Hi,

I am moving data from one table to another in SQL server 2000 database. The collation for the database is SQL_Latin1_General_CP1_CI_AS. When I am moving the data in nVarchar(4000) column the Japanese data is getting garbled. My NLS is disabled and the collation of the source and target are same. I have not tried yet in changing the OS environment variables since I would like to use it as my lot option. Is there anything I can try?

Thanks,
Anand.
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

You haven't explained how you are moving the data (and how DataStage is involved). If you are reading from and then writing to SQL Server with the same settings and no NLS it should work.
anandkumarm
Premium Member
Premium Member
Posts: 55
Joined: Tue Feb 24, 2004 8:17 am

Post by anandkumarm »

ArndW wrote:You haven't explained how you are moving the data (and how DataStage is involved). If you are reading from and then writing to SQL Server with the same settings and no NLS it should work.
Hi ArndW,

I have an ODBC stage followed by a Transformer and ODBC stage. Both the connections are pointing to the same server and Database. I am just reading from one table and then writing it to the other table with out any transformations. Only thing is for my nVarChar column I made it in to VarChar type with double the width. I tried by casting into VarBinary too but it didn't work.

Thanks,
Anand
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

Then it would seem you do need to set your character set differently so that no conversion occurs in the ODBC stages.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

What character set is in effect in your job's environment? Double-click on the second log entry for a complete list of all environment variables in that run. If that is different than your databases, then from what I've seen, conversions will be going on 'in flight'.
-craig

"You can never have too many knives" -- Logan Nine Fingers
anandkumarm
Premium Member
Premium Member
Posts: 55
Joined: Tue Feb 24, 2004 8:17 am

Post by anandkumarm »

chulett wrote:What character set is in effect in your job's environment? Double-click on the second log entry for a complete list of all environment variables in that run. If that is different than your databases, then from what I've seen, conversions will be going on 'in flight'.
ArndW and Chulett,

Thanks for your replies as chulett said I looked into the environmental variables I found the entry for NLS_Lang as follows:

NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

Now how do I change this so that it will be equivalent to SQL_Latin1_General_CP1_CI_AS of SQL server 2000. In one of the Ray's posts he mentioned to create OS environmental variable NLS_LANG from the control panel and enter the appropriate value, can I proceed with that solution? If so what should be the variable value? Once agian thanks for your help

Anand.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Create a User Defined Environment variable in the Administrator for NLS_LANG. Set the value to $ENV so that it defaults to the current 'environment' value. Use the special button on the job's Parameter tab to add this new Environment Variable to your job and then supply the new, override value that should be in effect just for this job run.

You may also need to do something similar to LC_CTYPE or whatever that other pesky variable is. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Your note in the original post mentioned that NLS is disabled. I'm surprised you can do anything at all with Japanese characters.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I was wondering about that but forgot to ask. Disabled = what? Installed but 'turned off' or not installed? :?
-craig

"You can never have too many knives" -- Logan Nine Fingers
anandkumarm
Premium Member
Premium Member
Posts: 55
Joined: Tue Feb 24, 2004 8:17 am

Post by anandkumarm »

NLS is not installed but the Japanese data is changing.

Thanks,
Anand.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

In my experience you need NLS enabled to be able to process Japanese characters correctly - and even then it's not always easy!
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
anandkumarm
Premium Member
Premium Member
Posts: 55
Joined: Tue Feb 24, 2004 8:17 am

Post by anandkumarm »

Hi Ray,

Thanks for your response. If I understand correctly, even if I am not manipulating the japanese data but to make a simple move from one table to another through Datastage requires me to enable NLS?

Thanks,
Anand.
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

No, it doesn't. NLS is only necessary if you need to modify or otherwise manipulate data. In fact, having NLS and not doing it correctly is what usually causes the conversion errors you are seeing.

Try this - read the data in DataStage and write it to a sequential file - is the Japanese data still correct or has it been converted?
anandkumarm
Premium Member
Premium Member
Posts: 55
Joined: Tue Feb 24, 2004 8:17 am

Post by anandkumarm »

ArndW wrote:No, it doesn't. NLS is only necessary if you need to modify or otherwise manipulate data. In fact, having NLS and not doing it correctly is what usually causes the conversion errors you are seeing.

Try this - read the data in DataStage and write it to a sequential file - is the Japanese data still correct or has it been converted?
Hi ArndW,

Yes the Data is being converted to "?" in the sequential file. I directly opened it in wordpad, notepad as well as in word.

Thanks,
Anand.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Can you try it again after overriding the NLS_LANG setting in the job to match your database as I outlined earlier?
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply