SYMPTOM:
Users attempt to run a report involving ranking against DB2 UDB for iSeries, in Strategy Developer 9.x and they obtain the following error message:
Call SQLExecDirect got SQL_ERROR SQL7008 - ZZFW0W8701 in DSSTEMP not valid for operation.
In the above error message, ZZFW0W8701 is the name of a temporary table and DSSTEMP is the name of the library in DB2 UDB for AS/400, where this temporary table is created.
It is noticed that the error occurs during a SQL statement that involves an insert of values into a temporary table, which is a step that happens during the execution of a ranking report. Such an example SQL statement is as follows:
Insert into DSSTEMP.ZZFW0W8701 values (1, 1, 5)
CAUSE:
The exact cause of the issue is not known. It is likely that the issue is related to the commit mode that is being used at the ODBC level.
ACTION:
The following action will resolve the issue:
In the Data Source Name (DSN) definition of the Client Access ODBC driver to the DB2 UDB for iSeries warehouse, there is a setting called 'Commit mode'. Change this setting to 'Commit immediate (*NONE)'. Stop and re-start Strategy Intelligence Server (if using 3-tier mode) after making this change. Disconnect and re-connect to the project source (if using 2-tier mode) after making this change.
