KB10455: Users receive error message 'Function Sequence Error' with IBM DB2 Drivers during an ETL process.

SYMPTOM:
When using an IBM DB2 driver (with versions 8.01.03.132 and 8.01.05.449) during an ETL process, an attempt is made to populate a table with data selected from another blank table. For example, this occurs when attempting to update the statistics tables from a previous version of Strategy’s platform to a newer version. If there is a blank table in the old version of the statistics, and a select statement is executed on this empty table from update scripts, users receive the following error message:
Function Sequence Error
This error message is returned after an ODBC call to SQLNumResultCols.
 
Functions such as SQLNumResultCols and SQLExecDirect are ODBC functions that can be executed one by one to troubleshoot issues in a tool such as Microsoft’s ODBC test tool, referenced below.
 
To reproduce the issue, perform the following steps:

1. With any ODBC test tool, create two one-column tables, but do not populate them.
2. Perform a Full Connect (found under 'Connect' on toolbar) from Microsoft’s ODBCTE32.exe and ensure that ODBC 2.0 level support is enabled.
3. Create two tables, each with one column. Execute each statement by using SQLExecDirect (found under ‘Statement’ on the Toolbar):
   Create testtable1(col1 int)
   Create testtable2(col1 int)
   
4. Execute the statement ‘Insert into testtable1 select col1 from testtable2’.
5. Issue SQLNumResultsCols (found under 'Results' on the toolbar).
6. Under Misc on the test tool toolbar, select ‘Errors All.’

The ‘Function Sequence Error’ error message now appears.
 
CAUSE:
This is caused by a known driver issue, and is not directly related to the Strategy platform.
 
This issue occurs because the execution of the SQLEXECDIRECT returns SQL_NO_DATA_FOUND. The ODBC driver manager does not properly handle this returned message. Therefore, when users submit SQLNumResultsCols, the driver manager and the ODBC driver are not in the proper state to perform this command.
 
WORKAROUND:
IBM Technical Support has provided the following workaround for this issue:
 

1. Open the DB2CLI.ini file on the machine on which the IBM ODBC driver is installed.
2. In the ini file, find the name of the DSN/DB Alias to which the workaround is to apply and add the following line under the DSN/DB Alias:
   PATCH1=1024
   
3. (or if a PATCH1 line already exists, add 1024 to the end separated by a comma).
    
   Adding this line to the DB2CLI.ini file causes the Insert into statement to return SQL_SUCCESS_WITH_INFO stating that there are no rows found in the table when performing the SQL statement or that an empty table is being referenced.
    
   

Now, submitting the SQLNumResultsCols will return SQL_SUCCESS with a value of 0 (or TRUE).
 
Third Party Software Installation
WARNING:
The third-party product(s) discussed in this technical note is manufactured by vendors independent of Strategy. Strategy makes no warranty, express, implied or otherwise, regarding this product, including its performance or reliability.