A fix is available
APAR status
Closed as program error.
Error description
When the partitioned reads are enabled in the ODBC Connector job and the user is using user defined SQL with nested select statement and aliases are used for the table name and the query has some where clauses, then the jobs are failing with errors such as the following. ODBC function "SQLExecute" reported: SQLSTATE = HY000: Native Error Code = 156: Msg = [IBM(DataDirect OEM)][ODBC SQL Server Native Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near the keyword 'as'. (CC_OdbcDBStatement::executeSelect, file CC_OdbcDBStatement.cpp, line 1,422) For example, with the user defined query below: SELECT C1 FROM (SELECT C1 FROM TC_CRM25322_2 WHERE C2 = 1) AS T1_C1 Partitioning for ODBC may require inserting a partitioning clause such as "(C1 % 4 = 1)", but the way it was inserted could result in invalid syntax such as: SELECT C1 FROM (SELECT C1 FROM TC_CRM25322_2 WHERE (C1 % 4 = 1) AND ( C2 = 1) AS T1_C1)
Local fix
Problem summary
When the partitioned reads are enabled in the ODBC Connector job and the user is using user defined SQL with nested select statement and aliases are used for the table name and the query has some where clauses, then the jobs are failing with errors such as the following. ODBC function "SQLExecute" reported: SQLSTATE = HY000: Native Error Code = 156: Msg = [IBM(DataDirect OEM)] [ODBC SQL Server Native Wire Protocol driver] [Microsoft SQL Server]Incorrect syntax near the keyword 'as'. (CC_OdbcDBStatement::executeSelect, file CC_OdbcDBStatement.cpp, line 1,422)
Problem conclusion
The problem is seen because while inserting the partitioning where clause, we search if there is already a where clause present in the SQL Query and then enclose that complete statement after the where keyword within parenthesis. There could be cases such as these where the SQL statement might be having aliases or nested statements after the where keyword, in which case, the SQL syntax can get changed if we insert whole of the statement after the Where keyword within parenthesis. Corrected the code to enclose only the condition that we are inserting in the parenthesis and leave the rest of the SQL statement as-is.
Temporary fix
Comments
APAR Information
APAR number
JR40226
Reported component name
WIS DATASTAGE
Reported component ID
5724Q36DS
Reported release
850
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2011-07-28
Closed date
2011-08-05
Last modified date
2014-07-25
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
WIS DATASTAGE
Fixed component ID
5724Q36DS
Applicable component levels
R850 PSY
UP
[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSVSEF","label":"InfoSphere DataStage"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.5"}]
Document Information
Modified date:
06 October 2021