ERROR [HY000] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0666 - Estimated query processing time xxx exceeds limit yyy

ERROR [HY000] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0666 - Estimated query processing time xxx exceeds limit yyy.

 Where xxx and yyy represent the estimated amount of time it will take to process the query and the OS/400 query time limit, respectively.

The error occurs when the estimated SQL Query run time exceeds the system's query processing limit.

Solution:

You can turn off the query timeout limit in the iSeries Access ODBC Driver.

 

IBM now provides you with a mechanism for turning off query timeout value support for applications that use a particular ODBC Data Source Name (DSN).

 

You can turn off query timeout limit processing in a DSN by performing the following steps:

• Open the ODBC Data Source Administrator on the client machine that is experiencing the SQL0666 error
  
• In the Administrator window, highlight the DSN that you want to change and click on the Configure button
  
• Click on the Performance tab in the Windows Setup dialogue
  
• Click on the Advanced button under the Performance options. This will bring up the Advanced performance options window
  
• Turn off the checkmark in the Allow Query Timeout checkbox. Click on OK to exit this screen
  
• In the Windows Setup screen, click on the Apply button followed by the OK button. This allows you to exit the screen and save your changes
  

  
   

•  
   

Once this option is turned off, applications using this particular ODBC DSN will automatically disable support for query timeout value checking.

Note:

But remember that while this option is handy for allowing longer running queries to automatically finish, it also removes a safeguard against run-away queries that will throttle PC and AS/400 performance. So use it only when it's needed and leave it on the rest of the time.

Another Tip:

The ODBC Progress database can store binary or array of data into "CHAR" field type.

The SQLBulkCopy error "The byte array of data cannot be converted to nvarchar/varchar" might throw while migrating the data into SQL Server table's nvarchar/varchar (equivalent SQL data types)field.

Reason: While retrieving the data will be reaching into the destination table as an array of bytes which is supposed to be a text or string of characters.

IBM provides a mechanism to convert the binary data into text. This is achieved by performing the following steps:
 

• Open the ODBC Data Source Administrator on the client machine that is experiencing this error
  
• In the Administrator window, highlight the DSN that you want to change and click on the Configure button
  
• Click on the Translation tab in the Windows Setup dialogue that appears
  
• Turn on the checkmark in the Convert binary data (CCSID 65535) to text checkbox.
  
• Click on the Apply button. This allows you to exit the screen and save your changes