SQL Bible [Electronic resources] نسخه متنی

IBM DB2 UDB 8.1 System Catalogs

IBM DB2 UDB maintains two sets of the
INFORMATION_SCHEMA views — one in
SYSCAT schema and a subset in the
SYSSTAT schema (used for SQL Optimizer to
improve query performance). All views are created whenever the
CREATE
DATABASE command is run; the views
comprising the catalog cannot be explicitly dropped, altered, or updated,
except for some columns in the
SYSSTAT views.

The INFORMATION_SCHEMA objects in
DB2

The
Table
13-5 shows some of the views we consider most useful.

The
SELECT privilege to views is granted to
PUBLIC by default. IBM explicitly states
that columns in the views might be changed from release to release and
recommends querying these tables using
SELECT
*
FROM
SYSCAT.<view> syntax. Nevertheless,
IBM specifies that some columns are "guaranteed" to work with the corresponding
views. (See
Table
13-6.)

The following query retrieves
information about the table
CUSTOMER created in the ACME database. To
display meaningful results, we've limited the number of columns selected to
three, because the table contains over 30 columns.

db2 => SELECT tabschema,
colcount, create_time FROM syscat.tables WHERE tabname = 'CUSTOMER' TABSCHEMA
COLCOUNT CREATE_TIME ------------------- -------- --------------------------
ACME 7 2002-09-23-17.55.50.95300 1 record(s) selected.

The information returned shows that the
table
CUSTOMER belongs to the schema
ACME, was created on 9/23/2002, and
contains seven columns. It is possible to join tables of the system catalog to
produce combined results. The following query joins two tables,
SYSCAT.TABLES and
SYSCAT.COLUMNS, to give a single set of
values extracted from both tables.

db2 =>
SELECT tbl.tabname, cl.colname FROM syscat.tables tbl, syscat.columns cl WHERE
syscat.tables.tabname = syscat.columns.tabname AND syscat.tables.tabname =
'CUSTOMER' TABNAME COLNAME ------------- ------------------------------
CUSTOMER CUST_ID_N CUSTOMER CUST_PAYTERMSID_FN CUSTOMER CUST_SALESMANID_FN
CUSTOMER CUST_STATUS_S CUSTOMER CUST_NAME_S CUSTOMER CUST_ALIAS_S CUSTOMER
CUST_CREDHOLD_S 7 record(s) selected.

Some
INFORMATION_SCHEMA views contained in the
SYSSTAT schema are updateable as shown in
Table
13-7.

Obtaining information about
INFORMATION_SCHEMA objects

The
DESCRIBE
TABLE
<table_name> command can be used to
obtain information about the internal structure of the
INFORMATION_SCHEMA objects in DB2. For
example:

db2 =>
describe table syscat.views Column Type Type name Length Scale Nulls name
schema --------------- ------------- --------- ------- ------ ----- VIEWSCHEMA
SYSIBM VARCHAR 128 0 No VIEWNAME SYSIBM VARCHAR 128 0 No DEFINER SYSIBM VARCHAR
128 0 No SEQNO SYSIBM INTEGER 4 0 No VIEWCHECK SYSIBM CHARACTER 1 0 No READONLY
SYSIBM CHARACTER 1 0 No VALID SYSIBM CHARACTER 1 0 No QUALIFIER SYSIBM VARCHAR
128 0 No FUNC_PATH SYSIBM VARCHAR 254 0 No TEXT SYSIBM CLOB 65536 0 No 10
record(s) selected.