SYMPTOM:
A user attempts to execute a report in Strategy Desktop against an Informix Dynamic Server database and the following error message appears:
'Call SQLExecDirect got SQL_ERROR Transaction not available.'
The Open Database Connectivity (ODBC) driver used is the Strategy ODBC Driver for Informix Wire Protocol that is shipped with the product. The error occurs in both 2-tier and 3-tier modes.
A step in the report execution involves insertion of data from the Strategy analytical Engine into temp tables. An excerpt from the report Structured Query Language (SQL) that involves this kind of operation, appears as follows:
create temp table ZZT2Y070KCNAB008(
Col_id CHAR(16),
WJXBFS1 FLOAT) with no log
select pa9.Col_id Col_id,
pa9.WJXBFS1 WJXBFS1
from ZZAB08 pa9
where pa9.Col_id in ('123', '456')
into temp ZZOP09 with no log
In the section '', values are inserted into the temp table ZZT2Y070KCNAB008 by running insert SQL statements. For this example, the insert statements are omitted from being displayed in the report SQL.
From analyzing the DSSDatabaseClasses.log and ODBC trace logs, it can be observed that the error message occurs on the very first insert statement in the '' section. The create temp table statement preceding the '' section, executes successfully.
CAUSE:
Before performing analytical Engine inserts, transactions are enabled by the Strategy Engine. This is done by turning off auto-commit at the ODBC level such that transactions are started implicitly on the following SQL statements until they are ended with a SQLTransact ODBC call.
However, in this case, transactions have not been enabled on the Informix database side and hence the above-mentioned error message is returned from the database.
ACTION:
To resolve the issue, enable transactions for the warehouse database on the Informix server side.
WORKAROUND:
If transactions cannot be enabled on the database side, re-create the report in a different way such that it does not require Strategy Analytical Engine inserts.
NOTE: Prior to enabling transactions, the ODBC driver or the underlying data source is checked for transaction capability. This check is done by polling the database using the SQLGetInfoW > SQL_TXN_CAPABLE ODBC call. Refer to the following Strategy Knowledge Base document for more information on this:
KB9151 - 'Database does not support transactions' error message appears when executing a report in Strategy Desktop against an Informix Dynamic Server database
The Strategy ODBC Driver for Informix Wire Protocol behaves differently than the Strategy ODBC Driver for Informix 8 on this call. The former returns the status of the ODBC driver with regard to transaction capability and hence it returns SQL_TC_ALL which means that the driver is transaction capable. On the other hand, the Strategy ODBC Driver for Informix 8 returns the status of the underlying data source. Hence, the latter returns SQL_TC_NONE on the same call because transactions are not enabled on the Informix database side.
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.
KB419624
