SYMPTOM:
When a user executes a report in Strategy Developer 9.x, the query fails with error code SQL0107N when running against a DB2/390 database as a warehouse.
When the user executes a report with an attribute mapped to a column that is 17 characters in length and the column is repeated in the Select clause, an extra character may be appended to the column. The following is an example of the SQL:
Sql
declare global temporary table session.ZZ(
CHAR(1),
0 CHAR(1),
1 CHAR(1),
2 CHAR(1),
3 CHAR(1),
WJXBFS1 CHAR(41)) on commit preserve rows
insert into session.ZZ
select distinct a11. ,
a11. 0,
…
The error generated may look like the following:
Sample Code/Error
Your request could not be processed. Please try again. If the problem persists, contact the Administrator.
(QueryEngine encountered error: Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. ….SQL0107N The name `` is too long. The maximum length is `18`. SQLSTATE=42622 Error in Process method of Component: QueryEngineServer, Project Job 36, Error Code= -XXXXXXXX.)
NOTE: The actual SQL generated may vary in different scenarios. For this example, the '' can be represented by "my_column_alias0".
CAUSE:
The maximum length for the column alias permitted by the DB2/390 is 17. The column alias for the mapping to the warehouse column on the attribute on the report has 17 characters already. When the digits are appended to it, the length is exceeded and the SQL error is thrown by the warehouse database.
ACTION:
In general, it is recommended that the user keeps the length of characters for an attribute's column alias below 15 characters because database thresholds like this are fairly common. For the scenario above, the user can edit the attribute and modify the column alias to have a shorter length, as shown below:
The user can then update the schema for the project.
