APAR status
Closed as program error.
Error description
Space going missing in the user defined SQL of ODBC Stage. This problem causes SQL errors when trying to run the affected job. The SQL statement in the job log shows the missing space in the SQL being used. However this is caused by the extra trailing space in the tablename property. Because of the way that the replacement of the job parameters in the SQL is working, when there is a trailing space in the table name property, the replacement replaces too many characters and loses the space before the WHERE. Without the trailing space in the tablename property the job parameters is replaced correctly and there is still a space before the WHERE. This trailing space is showing up a bug in the parameter replacement code.
Local fix
The workaround is to remove the unnecessary trailing space from the tablename field. This fix is included in 8.0.1 fix pack 3
Problem summary
**************************************************************** USERS AFFECTED: Server Jobs with User generated SQL from ODBC stages, with job parameters for table names may be affected. **************************************************************** PROBLEM DESCRIPTION: This problem can affect Server Jobs that use User generated SQL from ODBC stages, where job parameters are used for tablenames within the user SQL. In most cases this will work correctly. The one case where it will not work correctly is if the value in the 'Table used in query' field on the stage editor Output/SQL Query tab has a trailing space. if this field contains a trailing space, then the parameter substitution will be incorrect. This shows up when the job is run. A SQL error will be returned in the log, which should show the SQL being used. This SQL is invalid, as a space will have been lost after the replaced tablename parameter. For example the end of the tablename may run straight into the WHERE keyword, so the WHERE keyword is lost, resulting in the SQL error. **************************************************************** RECOMMENDATION: An immediate way to avoid the issue is to remove any trailing spaces from the end of the 'Table used in query' field in the stage's Output/SQL Query tab. The problem only occurs when this field has trailing spaces. Alternatively apply patch JR30875. ****************************************************************
Problem conclusion
The resolution was to correct the parameter replacement code, so that no spaces are lost. The SQL should then run correctly. A patch is available, but a customer can avoid the issue by removing any trailing spaces from the end of the 'Table used in query' field in the stage's Output/SQL Query tab.
Temporary fix
Comments
APAR Information
APAR number
JR30875
Reported component name
WIS DATASTAGE
Reported component ID
5724Q36DS
Reported release
801
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2008-10-22
Closed date
2008-11-05
Last modified date
2010-12-09
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Modules/Macros
SERVER
Fix information
Fixed component name
WIS DATASTAGE
Fixed component ID
5724Q36DS
Applicable component levels
R801 PSY
UP
R810 PSY
UP
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSVSEF","label":"IBM InfoSphere DataStage"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.0.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
09 December 2010