IT21510: THE ESQL COMMIT CALL NO LONGER PREVENTS A 42S02 ERROR FROM A STORED PROCEDURE THAT USES TEMPORARY TABLE

APAR status

• Closed as program error.

Error description

• In ESQL when executing a database stored procedure that uses a
  temporary Sybase database table and Sybase is in 'chained' mode
  a 42S02 error is reported.
  
  To avoid this error it is necessary to issue an ESQL commit
  immediately before executing the stored procedure. Changes to
  IIB have stopped this commit call from fixing the 42S02 error.
  
  The Sybase error reported is "The 'CREATE TABLE' command is not
  allowed within a multi-statement transaction in the 'tempdb'
  database."
  

Local fix

• The problem can be worked around by changing the ESQL "commit;",
  immediately before the call to execute the stored procedure, to
  "PASSTHRU ('COMMIT');"
  

Problem summary

• ****************************************************************
  USERS AFFECTED:
  All users of IBM Integration Bus using a Sybase database in
  'chained' mode and who use ESQL to call a database stored
  procedure that creates a table in a temporary database.
  
  
  Platforms affected:
  AIX, HP-UX PA-RISC platform, HP-UX Itanium platform, Linux on
  Power platform, Linux on x86 platform, Linux on x86-64 platform,
  Linux on zSeries platform, Solaris x86-64 platform, Solaris
  SPARC platform, Windows on x86 platform, Windows on x86-64
  platform
  
  ****************************************************************
  PROBLEM DESCRIPTION:
  When Calling a Sybase stored procedure that uses a temporary
  database and Sybase is operating in 'chained' mode, you must
  issue an ESQL 'commit' command immediately before calling the
  stored procedure. If you do not, Sybase reports an error.
  Following changes made in IIB 9.0.0.4, the functioning 'commit'
  call changed and the Sybase error was reported even if commit
  had been called.
  

Problem conclusion

• The ESQL code for calling commit for a Sybase database has been
  changed back.
  
  ---------------------------------------------------------------
  The fix is targeted for delivery in the following PTFs:
  
  Version    Maintenance Level
  v10.0      10.0.0.10
  v9.0       9.0.0.9
  
  The latest available maintenance can be obtained from:
  http://www-01.ibm.com/support/docview.wss?rs=849&uid=swg27006041
  
  If the maintenance level is not yet available,information on
  its planned availability can be found on:
  http://www-1.ibm.com/support/docview.wss?rs=849&uid=swg27006308
  ---------------------------------------------------------------
  

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:

Fix information

• Fixed component name

  INTEGRATION BUS

• Fixed component ID

  5724J0530

Applicable component levels

• R900 PSY

     UP