DB2 catalog
Like other DB2 databases, System i has a catalog (V5R4).
The catalog is built over physical files in QSYS (like QADBIFLD) but you should probably use the tables and views in the catalog rather than the underlying physical files.
The following documentation is captured from the V5R4 Infocenter.
Contents
IBM i catalog tables and views
The Db2 for i catalog includes the following views and tables in the QSYS2 schema:
| DB2 for i5/OS name | Corresponding ANSI/ISO name | Description |
| SYSCATALOGS | CATALOGS | Information about relational databases |
| SYSCHKCST | CHECK_CONSTRAINTS | Information about check constraints |
| SYSCOLUMNS | COLUMNS | Information about column attributes |
| SYSCOLUMNSTAT | Information about column statistics | |
| SYSCST | TABLE_CONSTRAINTS | Information about all constraints |
| SYSCSTCOL | CONSTRAINT_COLUMN_USAGE | Information about the columns referenced in a constraint |
| SYSCSTDEP | CONSTRAINT_TABLE_USAGE | Information about constraint dependencies on tables |
| SYSFUNCS | ROUTINES | Information about user-defined functions |
| SYSINDEXES | Information about indexes | |
| SYSJARCONTENTS | Information about jars for Java™ routines. | |
| SYSJAROBJECTS | Information about jars for Java routines. | |
| SYSKEYCST | KEY_COLUMN_USAGE | Information about unique, primary, and foreign keys |
| SYSKEYS | Information about index keys | |
| SYSPACKAGE | Information about packages | |
| SYSPARMS | PARAMETERS | Information about routine parameters |
| SYSPARTITIONINDEXSTAT | Information about partition index statistics | |
| SYSPARTITIONSTAT | Information about partition statistics | |
| SYSPROCS | ROUTINES | Information about procedures |
| SYSREFCST | REFERENTIAL_CONSTRAINTS | Information about referential constraints |
| SYSROUTINES | ROUTINES | Information about functions and procedures |
| SYSROUTINEDEP | ROUTINE_TABLE_USAGE | Information about function and procedure dependencies |
| SYSSEQUENCES | Information about sequences | |
| SYSTABLEDEP | Information about materialized query table dependencies | |
| SYSTABLEINDEXSTAT | Information about table index statistics | |
| SYSTABLES | TABLES | Information about tables and views |
| SYSTABLESTAT | Information about table statistics | |
| SYSTRIGCOL | TRIGGER_COLUMN_USAGE | Information about columns used in a trigger |
| SYSTRIGDEP | TRIGGER_TABLE_USAGE | Information about objects used in a trigger |
| SYSTRIGGERS | TRIGGERS | Information about triggers |
| SYSTRIGUPD | TRIGGERED_UPDATE_COLUMNS | Information about columns in the WHEN clause of a trigger |
| SYSTYPES | USER_DEFINED_TYPES | Information about built-in data types and distinct types |
| SYSVIEWDEP | VIEW_TABLE_USAGE | Information about view dependencies on tables |
| SYSVIEWS | VIEWS | Information about definition of a view |
ODBC and JDBC catalog views
The catalog includes the following views and tables in the SYSIBM library:
| 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 |
| SQLPROCEDURES | Information about procedures |
| SQLSCHEMAS | Information about schemas |
| SQLSPECIALCOLUMNS | Information about columns of a table that can be used to uniquely identify a row |
| SQLSTATISTICS | Statistical information about tables |
| SQLTABLEPRIVILEGES | Information about privileges granted on tables |
| SQLTABLES | Information about tables |
| SQLTYPEINFO | Information about the types of tables |
| SQLUDTS | Information about built-in data types and distinct types |
ANS and ISO catalog views
There are two versions of some of the ANS and ISO catalog views. The version documented is the normal set of ANS and ISO views. A second set of views have names that are limited to no more than 18 characters and other than the view names are not documented in this book.
The ANS and ISO catalog includes the following tables in the QSYS2 library:
| View Name | Shorter View Name | Description |
| SQL_FEATURES | Information about features supported by the database manager | |
| SQL_LANGUAGES | SQL_LANGUAGES_S | Information about the supported languages |
| SQL_SIZING | Information about the limits supported by the database manager |
The ANS and ISO catalog includes the following views and tables in the SYSIBM and QSYS2 libraries:
| View Name | Shorter View Name | Description |
| AUTHORIZATIONS | AUTHORIZATIONS | Information about authorization IDs |
| CHARACTER_SETS | CHARACTER_SETS_S | Information about supported CCSIDs |
| CHECK_CONSTRAINTS | Information about check constraints | |
| COLUMNS | COLUMNS_S | Information about columns |
| INFORMATION_SCHEMA_CATALOG_NAME | CATALOG_NAME | Information about the relational database |
| PARAMETERS | PARAMETERS_S | Information about procedure parameters |
| REFERENTIAL_CONSTRAINTS | REF_CONSTRAINTS | Information about referential constraints |
| ROUTINES | ROUTINES_S | Information about routines |
| SCHEMATA | SCHEMATA_S | Statistical information about schemas |
| TABLE_CONSTRAINTS | Information about constraints | |
| TABLES | TABLES_S | Information about tables |
| USER_DEFINED_TYPES | UDT_S | Information about distinct types |
| VIEWS | Information about views |
Examples
- List of columns
SELECT TABLE_NAME, SYSTEM_TABLE_NAME, COLUMN_NAME, SYSTEM_COLUMN_NAME FROM QSYS2/SYSCOLUMNS WHERE TABLE_NAME='SYSTABLESTAT'
Support
PTF SI27314 (R540 SI27314 7226) adds the following support:
IBM-supplied database views are being added. These views are used to return statistical information of existing database objects. The column names and data in the views are a superset of similar statistical views in DB2 for Linux, Unix, and Windows.
- Views:
- QSYS2.SYSCOLUMNSTAT
- QSYS2.SYSPARTITIONINDEXSTAT
- QSYS2.SYSPARTITIONSTAT
- QSYS2.SYSTABLEINDEXSTAT
- QSYS2.SYSTABLESTAT
- Functions:
- QSYS2.COLUMN_STATISTICS
- QSYS2.INDEX_PARTITION_STATISTICS
- QSYS2.PARTITION_STATISTICS
In addition to the new statistical objects, this PTF also: - redefines SYSIBM.SQLColPrivileges to be consistent with DB for LUW, by returning table privileges. - Add QSYS2.SQLGetAuth Function. - Fix a problem where QSYS2 views were incorrectly referring to Functions within SYSIBM.
Development Notes
CCSID
Some fields in the DB2 catalog are stored with CCSID 1200 instead of CCSID 37. This data cannot be directly handled by application programs and has to be converted to the job CCSID. See http://imho.midrange.com/2010/03/16/sql-columns-with-ccsid-1200/ for information on how to perform the conversion.
ODBC
openquery
It will often enhance performance to use openquery to have the server run a bulk of the processing.
For example,
-- Pull all rows from the table(s) back to the client (such as MS SQL server) and do the where
locally
select * from LINKEDSVR.MYIBMI.MYLIB.MYTBL where locnbr = '00335';
-- Sends the statement to linked server for processing
select * from openquery(LINKEDSVR, 'select * from MYTBL where locnbr =
00335');
--OPENQUERY() isn't just for SELECTS
delete from openquery(LINKEDSVR, 'select * from MYTBL where locnbr =
00335');
Categories
This article is a stub. You can help by editing it.
