Catalog functions for querying system catalog information in CLI applications

Catalog functions, which are also called schema functions, provide a generic interface so that you can issue queries and receive consistent result sets across the Db2® family of servers. By using catalog functions, you can avoid receiving server-specific and release-specific catalog queries.

One of the first tasks an application often performs is to display a list of tables from which one or more are selected by the user. Although the application can issue its own queries against the database system catalog to get catalog information for such a Db2 command, it is best that the application calls the CLI catalog functions instead.

The catalog functions operate by returning to the application a result set through a statement handle. Calling these functions is conceptually equivalent to using SQLExecDirect() to execute a select against the system catalog tables. After calling these functions, the application can fetch individual rows of the result set as it would process column data from an ordinary SQLFetch(). The CLI catalog functions are:
  • SQLColumnPrivileges()
  • SQLColumns()
  • SQLExtendedProcedures()
  • SQLExtendedProcedureColumns()
  • SQLForeignKeys()
  • SQLGetTypeInfo()
  • SQLPrimaryKeys()
  • SQLProcedureColumns()
  • SQLProcedures()
  • SQLSpecialColumns()
  • SQLStatistics()
  • SQLTablePrivileges()
  • SQLTables()

The result sets returned by these functions are defined in the descriptions for each catalog function. The columns are defined in a specified order. In future releases, other columns might be added to the end of each defined result set, therefore applications should be written in a way that would not be affected by such changes.

Note: By default, the Informix® database server returns schema information (such as table names, and column names) in the system catalog in lowercase. This is different from Db2 data servers which return schema information in upper case.

Some of the catalog functions result in execution of fairly complex queries. It is recommended that the application save the information returned rather than making repeated calls to get the same information.