"ANSI_Varchar_Behavior=TRUE" in DSConfig.txt (DI 11.5.3.0)

[Former Member]

I am working for a customer with a large DI 11.5.3.0 installation. I was puzzled by an issue with "ANSI_Varchar_Behavior" in the DSConfig.txt brought up by them. They told me that this parameter was set to "FALSE" in the DSConfig.txt in all their environments (many development and test DI servers) except production. I think "FALSE" is the default value. The job has been working correctly in all these servers.

For some reason, this value is set to "TRUE" in the DSConfig.txt on a pre-production box. The job still retrieves the correct number of rows from the Oracle source tables. However, when they copy the DSConfig.txt file to the production server, the job breaks and some rows are missing from the same SQL select statement and the same Oracle tables which work just fine on the pre-production box. They know how to fix it - either adding a trim function in the "where" clause or changing the value from "TRUE" to "FALSE" will do. But they demand an explanation as why the "TRUE" value works on the pre-production box but not the production box. I just couldn't think of any reasonable explanation.

They think both boxes are almost indentical. Is it possible different versions of Oracle clients on the DI servers could cause this issue? The source column is a CHAR type so the ANSI and Oracle definitely treat the trailing spaces differently. But how to explain it works when the value is "TRUE" on the pre-production server? Should it fail the job just like the production server?

Thanks,

Larry

[Former Member]

Thanks for your help, Scott! Your reply helps the customer made their own decision to set the flag to "FALSE".

- Larry

[scott_broadway]

Hi Larry,

Here is a summary of the expected behavior of the ANSI Varchar flag, first introduced in the 11.5 release. The FALSE value forces the engine to operate the same as 6.5 and 11.0 releases of DI (most similar to Oracleu2019s rules for empty and null handling); TRUE forces the ANSI SQL92-compliant behavior.

Empty string behavior:

ANSI_Varchar_Behavior = FALSE u2013 empty strings (e.g. '') are treated the same as nulls.

ANSI_Varchar_Behavior = TRUE u2013 empty strings (e.g. '') are empty strings, and never evaluate to NULL when processed by the DI engine.

NULL behavior:

ANSI_Varchar_Behavior = FALSE u2013 a comparison NULL = NULL evaluates to TRUE. A comparison between NULL and any other value evaluates to FALSE.

ANSI_Varchar_Behavior = TRUE u2013 any comparison involving a NULL value always evaluates to FALSE. The IS NULL syntax must be used to compare for NULL.

Trailing blanks from data readers:

ANSI_Varchar_Behavior = FALSE u2013 right trailing blanks on any char/varchar field are always trimmed, except when the source is an XML file.

ANSI_Varchar_Behavior = TRUE u2013 trailing blanks are never trimmed, and always left intact.

Trailing blanks for in-engine operations:

ANSI_Varchar_Behavior = FALSE u2013 during any comparison, function, or sort involving a char/varchar, trailing blanks are included.

ANSI_Varchar_Behavior = TRUE u2013 during any comparison, function, or sort involving a char/varchar, trailing blanks are ignored.

Thanks,

~Scott

[Former Member]

Scott,

Thanks very much for your quick reply! You are the best!

Your explanation makes a lot of sense. However, the issue now becomes that the customer isn't sure what value they should set on the production server. The situation is that the value "FALSE" works fine for all their development and testing servers. When they did the final testing on the pre-production server, the job also works OK with the value "TRUE" there. However, when they copy the DSConfig.txt file from the pre-production to the production box, the job failed to retrieve all rows from the same Oracle source tables (pre-production and production servers point to the same database). Do you have any idea why the job behaviors differently with the same value "TRUE" and same source database? Any environmental issue I should check - like the Oracle client library versions etc?

They need to make a "go" or "no go" decision in next 4 hours. The DI code is already frozen so they can't add "trim()" function in the where clause. The only option now is to change the value to "FALSE" on production. They are asking me to get a confirmation/validation from BOBJ that they are doing the right thing and there is no side effect of doing so. I can open a 911 support case but don't think it makes sense because I don't know why the value is set to "TRUE" on pre-production server in the first place. This is a server wide parameter and the default value should be "FALSE", is it right? If we say "no problem, just change it" without carefully reviewing all the jobs running on production, we will get blamed if anything breaks later on. Any advice?

Thanks,

Larry