SYMPTOM:
This scenario refers specifically to an environment where the data warehouse is held in a supported type and version of IBM DB2. Further details regarding supported database systems (RDBMS) may be found in the appropriate product ReadMe file.
The user defines an attribute column alias, using the Strategy Developer Attribute Editor, as data type LongVarChar:
When Structured Query Language (SQL) is generated that references this column alias in the generation of temporary tables, the LongVarChar column is interpreted as a column of type CLOB, as in the following example:
declare global temporary table session.ZZEA00( CustCol_3 CLOB(378),
CALL_CTR_ID INTEGER,
WJXBFS1 DOUBLE)
partitioning key (CustCol_3, CALL_CTR_ID) on commit preserve rows not logged
where CustCol_3 is defined column alias for this attribute column.
This may in turn give rise to errors if the user attempts to use the column in question as a partition key.
CAUSE:
This is expected behavior. Strategy maps the DB2 database datatype CLOB internally to datatype LongVarChar.
ACTION:
The user should define the column alias using the VarChar data type:
Having made the modification, the schema must be updated and it is advisable to disconnect from and reconnect to the project source in order to clear local object caches.
The SQL generated will now be as follows, preserving the correct data type:
declare global temporary table session.ZZMQ00( CustCol_5 VARCHAR(378),
CALL_CTR_ID INTEGER)
partitioning key (CustCol_5, CALL_CTR_ID) on commit preserve rows not logged
Where CustCol_5 is the defined column alias for this attribute column.
