PM39141: OPEN SESSION COMMAND CONCAT_NULL_YIELDS_NULL OFF HAS NO EFFECT AGAINST SQL SERVER 2005

APAR status

• Closed as suggestion for future release.

Error description

• SET CONCAT_NULL_YIELDS_NULL (Transact-SQL) Controls whether
  concatenation results are treated as null or empty string
  values. ??I have a client that wants to execute the ?SET
  CONCAT_NULL_YIELDS_NULL OFF? command when connecting to the SQL
  Server 2005 database as they have Database Views that are
  effected by having this setting as ?SET CONCAT_NULL_YIELDS_NULL
  ON? (default in SQL Server).??Because they have "String" Values
  that concatenate with each other and if one String is NULL, the
  result is NULL. i.e. String 1 is NULL, String 2 = "Str2", if
  String 1 + String 2 (NULL + "Str2"), the RESULT is NULL when
  "SET CONCAT_NULL_YIELDS_NULL_ON". ??When the ?SET
  CONCAT_NULL_YIELDS_NULL OFF? - RESULT is returned as "Str2",
  i.e. NULL + "Str2" = "Str2", desired result.??When using an open
  session command from either 8.3 or 8.4.1 (other versions
  untested) there appears to be no effect.  We have reproduced
  this in house.  An excerpt from a SQL trace shows that we are
  running the command to set concat OFF but it is the subsequently
  set back to ON as follows:??SQL:BatchStarting exec
  sp_oledb_ro_usrname IBM Cognos 8 test 2608 69 2011-04-12
  13:13:07.530 ?SQL:BatchCompleted exec sp_oledb_ro_usrname IBM
  Cognos 8 test 0 4 0 0 2608 69 2011-04-12 13:13:07.530 2011-04-12
  13:13:07.530 ?SQL:BatchStarting select
  collationname(0x0904D00034) IBM Cognos 8 test 2608 69 2011-04-12
  13:13:07.530 ?SQL:BatchCompleted select
  collationname(0x0904D00034) IBM Cognos 8 test 0 0 0 0 2608 69
  2011-04-12 13:13:07.530 2011-04-12 13:13:07.530
  ?SQL:BatchStarting set CONCAT_NULL_YIELDS_NULL OFF; IBM Cognos 8
  test 2608 69 2011-04-12 13:13:07.530 ?SQL:BatchCompleted set
  CONCAT_NULL_YIELDS_NULL OFF; IBM Cognos 8 test 0 0 0 0 2608 69
  2011-04-12 13:13:07.530 2011-04-12 13:13:07.530
  ?SQL:BatchStarting set CONCAT_NULL_YIELDS_NULL OFF; IBM Cognos 8
  test 2608 69 2011-04-12 13:13:07.530 ?SQL:BatchCompleted set
  CONCAT_NULL_YIELDS_NULL OFF; IBM Cognos 8 test 0 0 0 0 2608 69
  2011-04-12 13:13:07.530 2011-04-12 13:13:07.530
  ?SQL:BatchStarting SET CONCAT_NULL_YIELDS_NULL ON IBM Cognos 8
  test 2608 69 2011-04-12 13:13:07.543 ?SQL:BatchCompleted SET
  CONCAT_NULL_YIELDS_NULL ON IBM Cognos 8 test 0 0 0 0 2608 69
  2011-04-12 13:13:07.543 2011-04-12 13:13:07.543
  ?SQL:BatchStarting SET NO_BROWSETABLE ON IBM Cognos 8 test 2608
  69 2011-04-12 13:13:07.543
  

Local fix

Problem summary

Problem conclusion

Temporary fix

Comments

• The Cognos software is behaving as designed. The Cognos relation
  al query engine follows the ISO-SQL 20xx standard specification
  with respect to how nulls are managed in operations and expressi
  ons.
  
  This applies to the operations performed by the RDBMS via a SQL
  statement as well as any relational operations that are performe
  d locally. In effect, we require the database session to not cha
  nge such that null handling becomes non-standard. Reports which
  require local processing will still apply null semantics per the
   standard and thus you have an inconsistent environment producin
  g potentially wrong reports should the RDBMS not respect the nul
  l semantics standard. The same applies to those vendors who trea
  t a zero length string ('') as a null which itself is non-ISO SQ
  L standard behaviour.
  
  To force standard null semantics behavior when reporting on a da
  ta source that may not be be respecting this standard in all sce
  narios, modify expressions to use coalesce to translate a null v
  alue to an appropriate no-null value may be used:
  
      C1 || coalesce( C2, ' ')
      coalesce ( C3 * C4 , 0 )
  

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

Applicable component levels