Limit the use of catalog functions

You can improve performance and reduce lock contention by limiting the use of catalog functions.

Limit the number of times that you call catalog functions in your application, limit the number of rows that the functions return, and close all open cursors on catalog result sets.

Call each catalog function once and store the information that the function returns in your application to reduce the number of catalog functions that you call.

Specify the following parameters to limit the number of rows that a catalog function returns:

Schema name or pattern for all catalog functions
Table name or pattern for all catalog functions other than SQLTables()
Column name or pattern for catalog functions that return detailed column information

Close any open cursors (call the SQLCloseCursor() function) for statement handles that are used for catalog queries to release any locks against the catalog tables. Outstanding locks on the catalog tables can prevent CREATE, DROP, or ALTER statements from executing.

Important: Plan ahead. Although you might develop and test an application on a data source with hundreds of tables, the final application might need to run on a production database with thousands of tables.