DB2 OPENQUERY default timeout

• ignatov256

  Say Hey Kid

  Points: 677

  More actions

  March 19, 2018 at 11:29 am

  #104659

  Posting this mainly to document the solution in case someone runs into a similar issue.  Could not find a good answer after googling extensively....

  Had a query that used OPENQUERY and went over a linked server to DB2, which timed out after 30 seconds even though all timeout settings were set to 0 (indefinite).

  Here are some details:

  - SQL Server 2017 Enterprise on a VM
  - Microsoft OLE DB Provider for DB2 version 6.0 
  - Remote query timeout = 0 (instance level)
  - Query timeout = 0 (linked server definition)

  OLE DB provider "DB2OLEDB" for linked server "LINKED_SERVER" returned message "Processing of the SQL statement ended. Reason code 1. SQLSTATE: 57014, SQLCODE: -952".
  Msg 7330, Level 16, State 2, Line 1
  Cannot fetch a row from OLE DB provider "DB2OLEDB" for linked server "LINKED_SERVER".

  Turns out that if both timeout values are set to 0 the DB2OLEDB driver will use a default hard-coded value of 30 seconds.  Seems like a bug to me....