SYMPTOM:
When attempting to execute a report in Strategy Developer, the report fails with the following error:
SQLEngine got an Exception from DFC: Engine Logic: The report generates more than 1000 temp table, exceeding the limit. Error in Process method of Component: SQLEngineServer, Project XXX, Job XXX, Error Code= -2147212800
This error can also be seen when viewing the SQL of the report.
CAUSE:
This is a defect which is caused from having metadata on DB2 database and large custom groups on a report. These custom groups create temporary tables which exceed the limits given by DB2 for a single report request.
ACTION:
Contact Technical Support for an update on this issue.
WORKAROUND:
As this issue is seen when there are multiple large custom groups, the report can be split into smaller reports with each having only one or two custom groups.
Alternatively, consolidations can be used instead of custom groups, and this behavior will not be seen.
A final workaround is possible if customer groups are required. This is to use a different intermediate table type. Either permanent tables or derived tables will work but bring their own performance impacts on the database.
