CLI/ODBC application performance tuning

CLI/ODBC is an SQL application programming interface that can be called by your database applications. Its functions invoke Db2® stored procedures which, in turn, access the system catalog tables. If CLI/ODBC applications are encountering performance problems, consider tuning their behavior with CLI/ODBC keywords.

Some applications use ODBC APIs to gather metadata information that is used in further processing. The ten metadata API calls that can be made are:
  • SQLTables
  • SQLColumns
  • SQLSpecialcolumns
  • SQLStatistics
  • SQLPrimarykeys
  • SQLForeignkeys
  • SQLTablePrivileges
  • SQLColumnPrivileges
  • SQLProcedures
  • SQLProcedureColumns

Certain CLI/ODBC applications that use the metadata APIs listed previously might query all of the objects within the database. For example, an SQLTables call requests metadata for all the tables in the database. On a large system, such requests can result in a lot of network traffic, take a considerable amount of time and consume a considerable amount of server resources.

Several CLI/ODBC initialization keywords can be used to limit the amount of data that will be returned by the initial API calls during the "information gathering" stage after the database is first connected to. These keywords can be set by:
  1. Manually editing the db2cli.ini file.
  2. Updating the database CLI configuration using the Db2 Command Line Interface.
The keywords are:
  • DBName
  • TableType
  • SchemaList
  • SysSchemae
  • GrantorList
  • GranteeList