DB2 Simple Query and its equivalent OPENQUERY in SQL Server

• Adiseshan-170248

  SSC Eights!

  Points: 887

  More actions

  December 2, 2005 at 4:24 am

  #102967

  Hi ,

  This may be simple!!

  In DB2, select * from ifit.revenue where date(changedtimestamp) = '2005-10-05'

  gives the output 2 rows.

  In SQL Server by using OPENQUERY,

  SELECT *

  FROM OPENQUERY(IFIT,

   'select * from S65824BE.IFIT.REVENUE where date(changedtimestamp)

                          = ''2005-10-05'''

  gives me the output only 1 row. ie., After seeing the record, only first row is being fetched.

  For a different date, the same query gives me in DB2 as 47 records and in SQL Server 3 records..

  Could anyone update me what could be the issue on this!! Is this because of timestamp ??

  Thanks,

  Adi

   

• Adiseshan-170248

  SSC Eights!

  Points: 887

  More actions

  December 21, 2005 at 10:14 pm

  #611025

  Hello All,

  This issue has been resolved now.

   

  Finally, in the advanced mode of the 'Performance' tab of IBM's ODBC setup wizard .. I saw 'Use blocking with a fetch of 1 row' was on and the 'Record blocking size' was set to 32KB. As per the associated help, it says that this limit only determines how much data to retrieve when asked for a single record (so that the ODBC driver can cache it for cursors, etc. which request a single row at a time). Unclicking this option fixed my problems ... so for anyone else running into this problem and finding this in the search engines, uncheck the 'Use blocking' option when using OPENQUERY .. I think OPENQUERY uses an internal cursor that confuses the DB2 ODBC driver and only retrieves the first chunk...

   

  Hope this will be a good bookmark for everyone.

   

  By Default, ODBC Driver for DB2 has this limits.

   

  Thanks for everyone who had a look on my query.

   

  Regards,

  Adi