IZ10230: SQL STORED PROCEDURE PERFORMS EXTREMELY SLOW WHEN IT IS INVOKED FROM A REMOTE CLIENT

DB2 Version 9.5 Fix Pack 3b for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 1  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 2a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 2  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• SQL stored procedure that uses Declared Global Temporary Tables
  (DGTT) performs very slow when it is invoked from a remote
  client via TCP/IP. When a network trace is taken via operating
  system monitoring utilities, it may be noticed that the stored
  procedure returns one row per packet. Thus, when the stored
  procedure returns a very large resultset, the amount of time to
  return the entire resultset to the remote client is increased
  proportionally. This causes a huge performance degradation.
  .
  This problem was introduced as an unintended side effect for a
  fix included in DB2 V8.1.
  

Local fix

• Workaround is to drop the procedure, and recreate it with the
  BLOCKING ALL option, AND add the 'FOR READ ONLY' clause on the
  specific cursor within the stored procedure code.
  

Problem summary

• Users Affected:  SQL stored procedure users who use Declared
  Global Temporary tables in their procedures.
  
  Problem Description: SQL stored procedures using Declared Global
  Temporary Tables (DGTT) perform extremely slowly when invoked
  remotely.
  
  Problem Summary:  SQL stored procedures using Declared Global
  Temporary Tables (DGTT) perform extremely slowly when invoked
  remotely.  If a network trace is taken it may be noted the
  stored procedure returns 1 row per packet, so it can take a long
  time to return a large resultset.
  

Problem conclusion

• This issue was first fixed in Version 9.5 FixPack 1
  
  The fix should be applied to the server.
  

Temporary fix

• Workaround is to drop the procedure, and recreate it with the
  BLOCKING ALL option, AND add the 'FOR READ ONLY' clause on the
  specific cursor within the stored procedure code.
  

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

  IY98618

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  DB2 UDB ESE SOL

• Fixed component ID

  5765F4102

Applicable component levels

• R950 PSY

     UP