IBM Support

JR40410: FEDERATION SERVER WAITS A LONG TIME BEFORE IT STARTS RETURNING A LARGE RESULT SET FROM MS SQL SERVER DATA SOURCE.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • On Unix/Linux platform, when you query a MS SQL Server table by
    Federation Server MSSQL Wraper, and the result set is
    very large, Federation will wait for a while before it can start
    returning data, the waiting time is propotional to
    result set size.
    
    Federation Server uses branded DataDirect SQL Server ODBC Driver
    to connect to SQL Server Database system, if you make a ODBC
    trace to the query statment, you will observe that the
     waiting time is consumed by ODBC function "SQLExecute",
     such as following trace example:
    
    2011-01-01 10:06:55.935538 :         EXIT  SQLPrepare  with
    return code 0 (SQL_SUCCESS)
      HSTMT               0x0000008009b280
      UCHAR *             0x0003ffff9cb750 [      -3] "SELECT * FROM
    LARGE_TABLE"
      SDWORD                    -3
    
    2011-01-01 10:06:55.935599 :         ENTER SQLExecute
      HSTMT               0x0000008009b280
    
    2011-01-01 10:11:04.905061 :         EXIT  SQLExecute  with
    return code 0 (SQL_SUCCESS)
      HSTMT               0x0000008009b280
    
    There will be a heavy network traffic during the waiting time.
    If you mointor with any tool, such as DataDirect ODBC Snoop
    tool, you will observe that ODBC Client is retrieving large
     amount of data from data source.
    
    The long time waiting is because of the SQL Server cursor
    Federation Server uses in a pure Query statement is : STATIC
    cursor, that means when execute the query statement, a snapshot
     of result set will be prepared separately, this process is
     very time consuming if the result set is very large.
    
    The heavy network traffic during waiting is because of  that,
    DataDirect SQL Server ODBC Driver use Client side cursor rather
    than server side cursor. that means, the result set snapshot
    of STATIC cursor will be transferred to client side before it
    can be fetched.
    

Local fix

Problem summary

  • User affected:
      Users of the Microsoft SQL Server wrapper in InfoSphere
    Federation Server
    Problem description and summay:
      See error description
    

Problem conclusion

  • Problem was firstly fixed in Version 9.7, FixPak 6.  A new
    server options "DEFAULT_CURSOR_TYPE" which allows user setting
    default SQL Server query cursor type is added with the fix:
    1. Valid values of this option are:
             F/f  : FORWARD_ONLY cursor.
             K/k : KEYSET_DRIVEN cursor.
             D/d : DYNAMIC cursor.
             S/s : Static cursor.
    The default value is "S" which means use Static cursor by
    default.
    2. To resolve the issue from Federation Server side, set
    DEFAULT_CURSOR_TYPE to 'F', here is an example:
      ALTER SERVER <SERVER NAME> OPTIONS(ADD DEFAULT_CURSOR_TYPE
    'F')
    NOTE: After altering the server, the server option will take
    effect at once.
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR40410

  • Reported component name

    MSSQL WRAPPER

  • Reported component ID

    5724N9705

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-08-18

  • Closed date

    2012-06-10

  • Last modified date

    2013-09-06

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

    JR40407

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

Fix information

  • Fixed component name

    MSSQL WRAPPER

  • Fixed component ID

    5724N9705

Applicable component levels

  • R970 PSY

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSCAVPL","label":"Data Sources and Wrappers - Microsoft SQL Server"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"970","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
06 September 2013