JR30875: SPACE GOING MISSING IN THE USER DEFINED SQL OF ODBC STAGE

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 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