IBM Support

How to increase the number of statment handles available to your application

Question & Answer


Question

You have ensured your application is freeing all unused statment handles. How can you increase the number of statement handles for your application?

Answer

The CLIPKG CLI/ODBC configuration keyword specifies the number of large packages to be generated. This keyword is used to increase the number of sections for SQL statements in CLI/ODBC applications.

To increase the number of statement handles available to your application a rebind of the CLI packages from the client with a higher CLIPKG value will need to be performed.

For example:

  1. cd <home_directory>/sqllib/bnd
  2. db2 connect to <database_name>
  3. db2 "BIND @db2cli.lst BLOCKING ALL CLIPKG 6 grant public"
  4. db2 terminate
  5. cd <home_directory>/sqllib/cfg
  6. Edit the db2cli.ini file adding the following line under your dbalias section:

    CLIPKG=6

    Note: CLIPKG 6 may not be high enough.
Ensure that you restart the application after rebinding to test.

When changing the CLIPKG value caution should be taken. The higher this value is the more system resources can be used, and performance may take a hit as more open statement handles are in use. It is very possible to hit hardware and operating system limitations when setting this at high values.

[{"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Programming Interface - ODBC","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;9.5;10.1;10.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21394839