IBM Support

JR36487: CREATE NICKNAME FOR SQL SERVER 2008 TABLES WITH NULLABLE DATETIME COLUMNS RETURN SQL0180 ERROR.

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as fixed if next.

Error description

  • On Windows platform, when you create nickname for remote MS SQL
    Server 2008 tables with NULLABLE DATATIME columns,
    the execution returns SQL0180 error, the error message is "The
    syntax of the string representation of a datetime value  is
    incorrect.  SQLSTATE=22007"
    
    You can reproduce this error by following steps:
    On remote MS SQL Server 2008 server, create a table with
    NULLABLE DATETIME column like below,
    1) CREATE TABLE [mssql_table]([col_int][INT],
    [col_datetime][DATETIME] NULL);
    2) Insert some data and create statistics for this table.
    
    On local Federation Server.
    1) CREATE NICKNAME nick for server.schema.mssql_table;
    SQL0180N  The syntax of the string representation of a datetime
    value  is incorrect.  SQLSTATE=22007
    
    The error is caused by an undocumented change newly introduced
    in MS SQL Server 2008 which causes error return in Federation
    Server nickname creation.
    
    When creating nickname, Federation will retrieve statistics from
    remote datasource table. Specially for MS SQL Server, Histogram
    of table is retrieved. The first line will used as LOW2KEY
    of nickname statistics.
    
    In MS SQL Server 2005 and previous version, the first line of
    histogram is always non-null real data. Federation logic works
    very well.
    
    But in MS SQL Server 2008,  a NULL value could be the first
    line of histogram. In this case the above error is  reported
     since the conversion from a NULL to datetime is improper.
    
    Microsoft doesn't officially document this change. Therefore
    Federation Server NULL value was not handled.
    

Local fix

  • workaround:
    set DB2_II_LOW2KEY_HIGH2KEY in db2dj.ini:
    
    If set to NO, then when creating a nickname, the wrappers do not
    collect HIGH2KEY or LOW2KEY statistics. (See the description of
    these columns in DB2 catalog table SYSIBM.SYSCOLUMNS.
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.i
    bm.db2.luw.sql.ref.doc/doc/r0001038.html)
    If this variable is not set or set to anything other than NO,
    then the wrappers collect these statistics.
    
    The steps are:
    - add the following line in db2dj.ini
    DB2_II_LOW2KEY_HIGH2KEY=NO
    - recycle DB2 :
      db2stop force
      db2start
    - retry the create nickname statement
    
    One side effect of the workaround is that, the option is for all
    wrappers. That means all nicknames created when this variable
    takes affect won't have HIGH2KEY or LOW2KEY statistics. This MAY
    have some side effect for the queries on the nickname.  Customer
    may set DB2_II_LOW2KEY_HIGH2KEY=NO only for failed creating
    nickname and remove it for others.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * Federation Server for Windows                                *
    * SQL Server 2008                                              *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * On Windows platform, when you create nickname for remote MS  *
    * SQL                                                          *
    * Server 2008 tables with NULLABLE DATATIME columns,           *
    *                                                              *
    * the execution returns SQL0180 error, the error message is    *
    * "The                                                         *
    * syntax of the string representation of a datetime value  is  *
    *                                                              *
    * incorrect.  SQLSTATE=22007"                                  *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrate to IBM Federation Server Version 9.7 Fixpack 3       *
    ****************************************************************
    

Problem conclusion

Temporary fix

Comments

APAR Information

  • APAR number

    JR36487

  • Reported component name

    MSSQL WRAPPER

  • Reported component ID

    5724N9705

  • Reported release

    970

  • Status

    CLOSED FIN

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-05-04

  • Closed date

    2010-09-25

  • Last modified date

    2010-09-25

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

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

    JR36730

Fix information

Applicable component levels

  • R970 PSY

       UP



Document information

More support for: InfoSphere Federation Server
Data Sources and Wrappers - Microsoft SQL Server

Software version: 970

Reference #: JR36487

Modified date: 25 September 2010