DB2 10.5 for Linux, UNIX, and Windows

Built-in views versus table functions

Built-in views provide a simplified application programming interface to DB2® functions through SQL.

The built-in views fall into three categories:
  • Views that are based on catalog views
  • Views that are based on table functions with no input parameters
  • Views that are based on table functions with one or more input parameters

A table function can return similar information as the built-in view, however you can use a table function to retrieve the information for a specific database on a specific database partition, a subset of all database partitions, or all database partitions.

The following examples illustrate the difference between using an built-in view, based on a table function with one or more input parameters, and using the corresponding table function:
  • The PDLOGMSGS_LAST24HOURS view, which retrieves notification log messages, provides quick access to data from the previous 24 hours. By contrast, you can use the PD_GET_LOG_MSGS table function to retrieve data from a specified period of time.
  • The snapshot monitor views, which are identified by names beginning with SNAP) provide access to data from each database partition. However, the snapshot monitor table functions, which are identified by names beginning with SNAP_GET_) provide the option to choose between data from a single database partition or a data subset from across all database partitions.
  • The ADMINTABINFO view retrieves information for all tables in a database, which can significantly affect the performance of applications that use large databases. Instead, you can reduce the performance impact by using the ADMIN_GET_TAB_INFO table function and specifying the schema name, table name, or both, as input.

For built-in views based on table functions with one or more input parameters, both the built-in view and the table function can be used, each achieving a different goal:

The built-in views are always based on the most current version of the table functions. However, the column positions in the returned information may change from release to release to enable new information to be returned. Therefore, you should select specific columns from the built-in views or table functions, or describe your result set if your application uses a SELECT * statement.

Table functions with a version suffix (_Vxx) have been deprecated or discontinued. The deprecated functions might be discontinued in a future release. Therefore, you should change applications and scripts that use these table functions to invoke the corresponding table functions that have no version suffix.