IBM Support

JR40407: 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 fistly fixed in Version 9.5, FixPak 9. This
    fix should be applied on the federation Server.
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR40407

  • Reported component name

    MSSQL WRAPPER

  • Reported component ID

    5724N9705

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-08-18

  • Closed date

    2012-03-27

  • Last modified date

    2012-03-27

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

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

    JR40410

Fix information

  • Fixed component name

    MSSQL WRAPPER

  • Fixed component ID

    5724N9705

Applicable component levels

  • R950 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":"950","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
27 March 2012