CLI/ODBC application performance tuning

 

CLI/ODBC is an SQL application programming interface that can be called by your database applications. CLI functions invoke DB2® stored procedures which, in turn, access the system catalog tables.

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 above 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. Changing ODBC/CLI settings for the database using the Client Configuration Assistant (on those platforms which support it).

  3. Updating the database CLI configuration using the DBA Command Line Interface.
The keywords are: 

   - DBName    - TableType    - SchemaList    - SysSchema    - GrantorList    - GranteeList

Related tasks
Calling stored procedures from CLI applications

Related reference
SysSchema CLI/ODBC Configuration Keyword