Fixes are available
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
Closed as program error.
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.
User affected: Users of the Microsoft SQL Server wrapper in InfoSphere Federation Server Problem description and summay: See error description
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.
Reported component name
Reported component ID
Last modified date
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fixed component name
Fixed component ID
Applicable component levels
06 September 2013