Appendix F. DB2 for i5/OS catalog views
The views contained in a DB2 for i5/OS catalog are described in this section. The database manager maintains a set of tables containing information about the data in each relational database. These tables are collectively known as the catalog. The catalog tables contain information about tables, user-defined functions, distinct types, parameters, procedures, packages, views, indexes, aliases, sequences, constraints, triggers, and languages supported by DB2 for i5/OS. The catalog also contains information about all relational databases that are accessible from this system.
There are three classes of catalog views:
- i5/OS™ catalog tables and views
The i5/OS catalog tables and views are modeled after the ANS and ISO catalog views, but are not identical to the ANS and ISO catalog views. These tables and views are compatible with prior releases of DB2 for i5/OS.
These tables and views exist in schemas QSYS and QSYS2.
The catalog tables and views contain information about all tables, parameters, procedures, functions, distinct types, packages, views, indexes, aliases, sequences, triggers, and constraints in the entire relational database. When an SQL schema is created, an additional set of these views (except SYSPARMS, SYSPROCS, SYSFUNCS, SYSROUTINES, SYSROUTINEDEP, and SYSTYPES) are created into the schema that only contain information about tables, packages, views, indexes, and constraints in that schema.
- ODBC and JDBC catalog views
The ODBC and JDBC catalog views are designed to satisfy ODBC and JDBC metadata API requests. For example, SQLCOLUMNS. These views are compatible with views on DB2 for z/OS and DB2 LUW Version 8. These views will be modified as ODBC or JDBC enhances or modifies their metadata APIs.
These views exist in schema SYSIBM.
- ANS and ISO catalog views
The ANS and ISO catalog views are designed to comply with the ANS and ISO SQL standard (the Information Schema catalog views). These views will be modified as the ANS and ISO standard is enhanced or modified.
There are several columns in these views that are reserved for future standard enhancements.
There are two versions of these views:
- The first version of these views exist in schema INFORMATION_SCHEMA113. Only rows associated with objects to which the user has some privilege are included in the views. This version is compatible with the ANS and ISO SQL standard.
If you use of this set of catalog views to prevent users from seeing any information about objects to which they have no privilege, you should revoke privileges to the other catalog views from users and PUBLIC.
- The second version of these views exist in schema SYSIBM. All rows are included in these views whether or not the user has some privilege to the objects associated with rows in the views. These views are compatible with views on DB2 LUW Version 8 and will generally perform better than the ANS and ISO views in QSYS2.
For example, assume that a user has the SELECT privilege to the QSYS2.TABLES and SYSIBM.TABLES catalog views but does not have any privilege to a table called WORK.EMPLOYEE. The following SQL statement will not return a result row:
SELECT * FROM QSYS2.TABLES WHERE TABLE_SCHEMA = 'WORK' AND TABLE_NAME = 'EMPLOYEE'However, the following SQL statement will return a result row:
SELECT * FROM SYSIBM.TABLES WHERE TABLE_SCHEMA = 'WORK' AND TABLE_NAME = 'EMPLOYEE'Some of these views use special catalog functions as part of the view definition. These functions exist in SYSIBM, but should not be used directly in applications. The functions are created for specific independent auxiliary storage pools (IASP) and will likely change in future releases.
113. INFORMATION_SCHEMA is the ANS and ISO SQL standard schema name that contains catalog views. It is a synonym for QSYS2.
[ Top of Page | Previous Page | Next Page | Contents |
Index ]