Often, one of the first tasks an application performs is to display to the user a list of tables from which one or more tables are selected by the user to work with. Although the application can issue its own queries against the database system catalog to get this type of catalog information, it is best that the application calls the DB2 CLI catalog functions instead. These catalog functions provide a generic interface to issue queries and return consistent result sets across the DB2 family of servers. This allows the application to avoid server specific and release specific catalog queries.
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 DB2 CLI catalog functions are:
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 may 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.
Some of the catalog functions result in execution of fairly complex queries, and for this reason should only be called when needed. It is recommended that the application save the information returned rather than making repeated calls to get the same information.
All of the catalog functions have CatalogName and SchemaName (and their associated lengths) on their input argument list. Other input arguments may also include TableName, ProcedureName, or ColumnName (and their associated lengths). These input arguments are used to either identify or constrain the amount of information to be returned. CatalogName, however, must always be a null pointer (with its length set to 0) as DB2 CLI does not support three-part naming.
In the Function Arguments sections for these catalog functions in Chapter 5, DB2 CLI Functions, each of the above input arguments are described either as a pattern-value or just as an ordinary argument. For example, SQLColumnPrivileges() treats SchemaName and TableName as ordinary arguments and ColumnName as a pattern-value.
Inputs treated as ordinary arguments are taken literally and the case of letters is significant. The argument does not qualify a query but rather identifies the information desired. An error results if the application passes a null pointer for this argument.
Inputs treated as pattern-values are used to constrain the size of the result set by including only matching rows as though the underlying query were qualified by a WHERE clause. If the application passes a null pointer for a pattern-value input, the argument is not used to restrict the result set (that is, there is no WHERE clause). If a catalog function has more than one pattern-value input argument, they are treated as though the WHERE clauses in the underlying query were joined by AND; a row appears in this result set only if it meets all the conditions of the WHERE clauses.
Each pattern-value argument can contain:
These argument values are used on conceptual LIKE predicate(s) in the WHERE clause. To treat the metadata characters (_, %) as themselves, an escape character must immediately precede the _ or %. The escape character itself can be specified as part of the pattern by including it twice in succession. An application can determine the escape character by calling SQLGetInfo() with SQL_SEARCH_PATTERN_ESCAPE.
In the browser.c sample application:
Output from the browser.c sample is shown below, relevant segments of the sample are listed for each of the catalog functions.
Enter Search Pattern for Table Schema Name:
STUDENT
Enter Search Pattern for Table Name:
%
### TABLE SCHEMA TABLE_NAME TABLE_TYPE
------------------------- ------------------------- ----------
1 STUDENT CUSTOMER TABLE
2 STUDENT DEPARTMENT TABLE
3 STUDENT EMP_ACT TABLE
4 STUDENT EMP_PHOTO TABLE
5 STUDENT EMP_RESUME TABLE
6 STUDENT EMPLOYEE TABLE
7 STUDENT NAMEID TABLE
8 STUDENT ORD_CUST TABLE
9 STUDENT ORD_LINE TABLE
10 STUDENT ORG TABLE
11 STUDENT PROD_PARTS TABLE
12 STUDENT PRODUCT TABLE
13 STUDENT PROJECT TABLE
14 STUDENT STAFF TABLE
Enter a table Number and an action:(n [Q | C | P | I | F | T |O | L])
|Q=Quit C=cols P=Primary Key I=Index F=Foreign Key |
|T=Tab Priv O=Col Priv S=Stats L=List Tables |
1c
Schema: STUDENT Table Name: CUSTOMER
CUST_NUM, NOT NULLABLE, INTeger (10)
FIRST_NAME, NOT NULLABLE, CHARacter (30)
LAST_NAME, NOT NULLABLE, CHARacter (30)
STREET, NULLABLE, CHARacter (128)
CITY, NULLABLE, CHARacter (30)
PROV_STATE, NULLABLE, CHARacter (30)
PZ_CODE, NULLABLE, CHARacter (9)
COUNTRY, NULLABLE, CHARacter (30)
PHONE_NUM, NULLABLE, CHARacter (20)
>> Hit Enter to Continue<<
1p
Primary Keys for STUDENT.CUSTOMER
1 Column: CUST_NUM Primary Key Name: = NULL
>> Hit Enter to Continue<<
1f
Primary Key and Foreign Keys for STUDENT.CUSTOMER
CUST_NUM STUDENT.ORD_CUST.CUST_NUM
Update Rule SET NULL , Delete Rule: NO ACTION
>> Hit Enter to Continue<<