Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone

50 Modernizing IBM Eserver iSeries Application Data Acess - A Roadmap Cornerstone

4.3 SQL system catalogs: Definitions

SQL cannot only be used to manipulate data but also to define databases, that is, creating,

modifying, or dropping database tables or entire databases. There are five distinct object

types involved with SQL databases:

򐂰 Schemas

򐂰 Aliases

򐂰 Tables

򐂰 Indexes

򐂰 Views

We have seen that a schema is a repository that contains SQL objects and that a schema

corresponds to a library in the iSeries. In fact, each schema is of type *LIB, that is, a library.

Creating a schema by itself would not make any sense. It is the starting point, a container

destined to be filled with tables, indexes, etc., all the objects that make up your database and

are at the heart of most applications.

But where is all the information about the database itself, the so-called metadata? Well, that

is where the term

catalog comes into the picture. Catalogs are automatically created when a

schema is created, and they contain all the relevant information about the databases. Each

modification of a table in a SQL schema (that is, creating, renaming, dropping, moving, etc.) a

table updates the catalog files for that schema.

To summarize, the following can be said: The structure of the database is maintained by the

DBMS in special tables that are called catalogs. The catalogs can be queried by users or

tools to display information about tables, columns, referential integrity constraints, security

rights, and any other information that composes a database.

iSeries catalog tables and views

The iSeries catalog includes the following views and tables in the QSYS2 schema.

Note: Metadata is information about information. They serve to describe data, and their

use is not limited to the field of SQL or information technology. Most, if not all, SQL-based

RDBMS allow the extraction of the metadata of their content. For example, metadata is

very important to reverse engineer databases. Database design tools typically use

metadata to display database models.

DB2 UDB for iSeries

name

Corresponding ANSI/ISO name Description

Information about...

SYSCATALOGS CATALOGS ...relational databases

SYSCHKCST CHECK_CONSTRAINTS ... check constraints

SYSCOLUMNS COLUMNS ... column attributes

SYSCST TABLE_CONSTRAINTS ... all constraints

SYSCSTCOL CONSTRAINT_COLUMN_USAGE ... the columns referenced in a

constraint

SYSCSTDEP CONSTRAINT_TABLE_USAGE ... constraint dependencies on

tables

SYSFUNCS ROUTINES ... user defined functions

Chapter 4. Modernizing database definitions 51

ODBC and JDBC catalog

The catalog includes the following views and tables in the SYSIBM library.

SYSINDEXES ... indexes

SYSJARCONTENTS ... jars for Java routines

SYSJAROBJECTS ... jars for Java routines

SYSKEYCST KEY_COLUMN_USAGE ... unique, primary, and foreign

keys

SYSKEYS ... index keys

SYSPACKAGE ... packages

SYSPARMS PARAMETERS ... routine parameters

SYSPROCS ROUTINES ... procedures

SYSREFCST REFERENTIAL_CONSTRAINTS ... referential constraints

SYSROUTINES ROUTINES ... functions and procedures

SYSROUTINEDEP ROUTINE_TABLE_USAGE ... function and procedure

dependencies

SYSSEQUENCES ... sequences

SYSTABLEDEP ... materialized query table

dependencies

SYSTABLES TABLES ... tables and views

SYSTRIGCOL TRIGGER_COLUMN_USAGE ... columns used in a trigger

SYSTRIGDEP TRIGGER_TABLE_USAGE ... objects used in a trigger

SYSTRIGGERS TRIGGERS ... triggers

SYSTRIGUPD TRIGGERED_UPDATE_COLUMNS ... columns in the WHEN clause of

a trigger

SYSTYPES USER_DEFINED_TYPES ... built-in data types and distinct

types

SYSVIEWDEP VIEW_TABLE_USAGE ... view dependencies on tables

SYSVIEWS VIEWS ... definition of a view

View name Description

SQLCOLPRIVILEGES Information about privileges granted on columns

SQLCOLUMNS Information about column attributes

SQLFOREIGNKEYS Information about foreign keys

SQLPRIMARYKEYS Information about primary keys

SQLPROCEDURECOLS Information about procedure parameters

DB2 UDB for iSeries

name

Corresponding ANSI/ISO name Description

Information about...