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

DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows

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 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