Upgraded to 4.1 - new problem with scandinavian characters

system · May 14, 2013, 9:27am

Hello everyone!

We have recently updated our Business Objects installation with relatively good results. Our old setting was Data Services XI 3.1 (component version 21.1) running on Red Hat Enterprise Linux Server release 5.4 (Tikanga)

Our new setting is Data Services 4.1. running on Red Hat Enterprise Linux Server release 6.3 (Santiago)

Our only problem right now is with a batch job between Oracle and MS SQL. This worked fine with the previous setting and is running well enough with the new setting, with one problem: we are missing scandinavian characters (ä is now a etc.)

Source is Oracle 11.2 with locale:


SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER LIKE '%ERSET';
NLS_NCHAR_CHARACTERSET	AL16UTF16
NLS_CHARACTERSET	AL32UTF8

Target is SQL Server 10.0 with database collation Finnish_Swedish_CI_AS_KS_WS and connected with ODBC driver DAsqls25.so

What I’ve tried so far:

Changing locales in Designer on both source and target databases. First with default, then both as utf8, then both as cp1252, then the source as utf8 and target as cp1252 etc. All reasonable combinations that I can think of.
Tweaking the ODBC connection(IANAAppCodePage)

Right now the odbc connection is looking like this


odbc.ini:

[ODBC_CONNECTION]
Driver=/opt/bods/dataservices/DataDirect/odbc/lib/DAsqls25.so
Description=DataDirect 6.1 SQL Server Wire Protocol
AlternateServers=
AnsiNPW=No
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=<database>
EnableQuotedIdentifiers=1
Address=<dns_name>,<port>
LoadBalancing=0
QuotedId=No
ReportCodePageConversionErrors=1
DriverExpirationBehavior=1
BulkLoadOptions=2
ValidateServerCertificate=1
PacketSize=-1
SnapshotSerializable=0
LoginTimeout=15
QueryTimeout=0
BulkCharacterThreshold=-1
AuthenticationMethod=1
TrustStorePassword=
AlwaysReportTriggerResults=0
GSSClient=native
InitializationString=
WorkStationID=
MaxPoolSize=100
FetchTSWTZasTimestamp=0
EncryptionMethod=0
Pooling=0
ApplicationName=
MinPoolSize=0
FetchTWFSasTime=1
XML Describe Type=-10
ConnectionReset=0
EnableBulkLoad=0
FailoverGranularity=0
LoadBalanceTimeout=0
BulkLoadBatchSize=1024
FailoverPreconnect=0
BulkBinaryThreshold=32
HostNameInCertificate=
Language=us_english
FailoverMode=0
ApplicationUsingThreads=1
TrustStore=
IANAAppCodePage=2252

ds_odbc.ini:

[ODBC_CONNECTION]
Driver=/opt/bods/dataservices/DataDirect/odbc/lib/DAsqls25.so
RebrandedLib=YES
OdbcConformanceLevel=3
LazyLoading=
DriverUnicodeType=2
ODBC64SqlLenSize=64
ODBC64SqlHandleSize=64

Any idea as to what’s wrong?

melin (BOB member since 2013-05-14)

system · May 14, 2013, 2:35pm

Hi,
Don’t know about the 4.0 (don’t have installed on my laptop yet) but with XI 3.2 you have a tool called “SAP BusinessObjects Data Services Locale Selector”.
I’m not sure if it overwritte the local defined in the designer/datastore… but this is an idea!

Can’t do more right now sorry…

you can see this post and the reply from Werner:

Maybe you’ll find usefull information!

cedrickb (BOB member since 2005-08-19)

system · May 16, 2013, 6:22am

Thanks for the reply, tried reading the thread but found no advice to grasp.

Further question:

When I try running a test job and choose to print all trace messages I see that in the transform queries there are no special characters - ä is already a etc. at this point.

Does this mean that they are read from the source db incorrectly?

melin (BOB member since 2013-05-14)

system · May 16, 2013, 6:26am

Write as a test to a utf-8 flat file. Then you know if that is the case.

Johannes Vink (BOB member since 2012-03-20)

system · May 17, 2013, 6:57am

Thanks Johannes!

That either helped or created new problems - depends how you look at it

So writing to flat file shows that the characters are not OK at this point. So I did a test from another source table that has worked fine between Oracle and Oracle and the same happens.

It seems that when I run a job between Oracle and Oracle, some transformation is not done, (to the sources codepage?), but as soon as the target is different (SQL Server via ODBC, flat file) also the source is transformed.

Does this ring any bells for anyone?

melin (BOB member since 2013-05-14)

system · May 17, 2013, 9:39am

On Windows the Oracle client it self has also a codepage setting. Hack in to the registry etc… but I have no clue at all how do this on a non-Windows platform.

Johannes Vink (BOB member since 2012-03-20)

system · May 17, 2013, 11:16am

Login to the DS user account in Linux, stop the jobservice. Then set the environment variable via export NLS_LANG=MAERICAN_AMERICA.AL32UTF8 and start the jobserver again.

Make sure you update the .profile for this user account as well

export NLS_LANG=MAERICAN_AMERICA.AL32UTF8

The codepage of the Oracle datastore should be UTF-8 now as well.

Werner Daehn (BOB member since 2004-12-17)

system · May 20, 2013, 7:17am

Thanks for the input Werner, we already had that option set. Any other ideas?

I tried to narrow down the problem and read a UTF8 flat file with special characters to target SQL Server and the characters were all in place.

At this point the target codepage and server codepage were set as default so it seems the problem is not in the ODBC connection or in that end rather than reading from Oracle.

So this is the situation:

Oracle → Oracle OK
Oracle → SQL Server NOT OK
Oracle → flat file NOT OK
flat file → SQL Server OK

melin (BOB member since 2013-05-14)

system · May 20, 2013, 11:43am

It has to be that. Can you proof the setting is active to me somehow?

How about a job with a script where you do a print(get_env(‘NLS_LANG’)) or whatever the syntax is?

Werner Daehn (BOB member since 2004-12-17)

system · May 21, 2013, 6:37am

You might be on to something, Werner! All other environment variables print out nicely with get_env, but NLS_LANG is null.

I must’ve done something wrong when updating the value? What I did was log on to server, change value and then start and stop. I’ll now try to first stop the server and then change file content somehow and then start.

EDIT: Same thing happened. I only restarted our AdaptiveJobServer - do I need to restart something else?

EDIT number 2: I did the starting and stopping from central management console

melin (BOB member since 2013-05-14)

system · May 21, 2013, 7:13am

It’s the jobservice/jobserver (svrcfg on Unix) that needs that environment variable being set before starting!

Werner Daehn (BOB member since 2004-12-17)

system · May 21, 2013, 7:41am

That did it!

Thank you very much Werner and others who helped! It was a simple solution in the end

This is a great community!

melin (BOB member since 2013-05-14)

system · May 22, 2013, 6:34am

Simple solutions are the worst because they usually are not obvious. Oh dear, how often did I come up with something very cool just to get smacked over the head by somebody else saying “and why did you not click this button? Same result, much simpler?”

Werner Daehn (BOB member since 2004-12-17)

system · May 29, 2013, 9:56am

The database collation only sets the rules about how characters compare and sort against each other; it has nothing to do with how characters are stored in the database.

My recommendation would be to set the the database code page to be utf-8 and make sure all columns that can contain language text are nvarchar in the database tables. You will need to re-import the table meta-data - nvarchar is displayed in DS as varchar, but the imported table metadata is different. For template tables there is a setting “use nvarchar for varchar in supported databases” in the target table editor.

You also need to think about what tools you are using to view the data; e.g. if you’re using SSMS to run queries to check results you may need to set data grids to be displayed using a Unicode font.

dastocks (BOB member since 2006-12-11)