IZ41217: SLOW PERFORMANCE BECAUSE PREPARE IS DONE EACH TIME WHEN USING CLI YOU ALTERNATE BETWEEN ARRAY INSERT AND NON-ARRAY INSERT.

DB2 Version 9.5 Fix Pack 4  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

• Using CLI (Call Level Interface), to do an array insert you call
  SQLSetStmtAttr() to specify the value of the
  SQL_ATTR_PARAMSET_SIZE statement attribute and then bind each
  parameter marker to its corresponding array of input values by
  calling SQLBindParameter() for each parameter marker.
  
  Each time a CLI program executes an INSERT statement to do an
  array insert when the most recent execution of the same
  statement was a non-array insert, a DRDA
  (Distributed Relational Database Architecture) PREPARE codepoint
  is flowed to the database server.
  And each time you execute an INSERT statement to do a non-array
  insert when the most recent execution of the same statement was
  an array insert, a DRDA PREPARE codepoint is flowed to the
  database server too.
  
  This behaviour was introduced by the change made for APAR
  IY87244. It can make applications
  perform more slowly by causing them to flow more PREPARE
  codepoints to the database
  server. Because that is likely to cause the database server to
  prepare statements more often it can cause slow
  performance on the database server.
  If that also makes the database server take longer to do each
  individual prepare then that is an additional cause of slow
  performance.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * CLI applications performing an INSERT statement to do        *
  * an array insert.                                             *
  *                                                              *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Slow performance in CLI applications.  A prepare is done     *
  * each time when using CLI and alternating between an array    *
  * insert and a non-array insert.                               *
  *                                                              *
  ****************************************************************
  

Problem conclusion

• Problem was first fixed in Version 9.5, Fixpak 4.
  

Temporary fix

Comments

APAR Information

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

  IZ40665

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

Fix information

• Fixed component name

  DB2 CEE AIX

• Fixed component ID

  5765F3000

Applicable component levels

• R910 PSN

     UP

• R950 PSN

     UP