DB2 catalog

From MidrangeWiki
Revision as of 19:38, 8 April 2019 by Rob@dekko.com (talk | contribs) (Development Notes)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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.

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

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.