System Catalog Views (IBM DB2 for i)

Use

This group of options displays some of the system catalog views with information about database objects like tables, indexes, or views in IBM DB2 for i. The information can be used, for instance, to map SQL names (used in the SAP system) to IBM i system object names, which allow fewer characters and a maximum length of only 10 characters.

Note

The originally available system catalog view screens have been enhanced and new screens have been added. For more information, see SAP Note 2188147 Information published on SAP site

Table 1: System Catalog Views
Monitor	Description
SYSTABLES – Tables and Views	Contains one row for every table, view or alias in the database.
SYSCOLUMNS – Column Attributes	Contains one row for every column of each table and view.
SYSCST – Constraints	Contains one row for each constraint in the database.
SYSKEYCST – Constraint Keys	Contains one row for each column in every unique or primary key constraint and the referencing columns of a referential constraint.
SYSINDEXES – Indexes	Contains one row for every index created using the SQL CREATE INDEX statement.
SYSKEYS – Index Keys	Contains one row for every column of an index.
SYSVIEWS – View Definitions	Contains one row for each view in the database.
SYSVIEWDEP – View Dependencies on Tables	Displays the dependencies of views on tables or other database objects.
SYSTRIGGERS – Triggers	Contains one row for each trigger in the database.
SYSROUTINES – Routines	Contains one row for each procedure created by the SQL CREATE PROCEDURE statement and each function created by the SQL CREATE FUNCTION statement.
SYSPARMS – Routine Parameters	Contains one row for each parameter of a procedure created by the SQL CREATE PROCEDURE statement or function created by the SQL CREATE FUNCTION statement. The result of a scalar function and the result columns of a table function are also returned.
SYSVARIABLES – Global Variables	Contains one row for each global variable. This monitor is available as of IBM i 7.1.

For detailed information about the system catalog views and their fields, see the IBM documentation DB2 for i SQL Reference.

To display the system catalog views, call transaction DBACOCKPIT and select the system you want to monitor. In the navigation frame, open the folder Start of the navigation path Diagnostics Next navigation step System Catalog Views End of the navigation path and choose the required system catalog view.

You can set the sorting of the displayed rows in the group box Query Options. By default, the output table will be ordered by the first column in ascending order. There is a drop-down list with all table columns to select as sort criterion, and a check-box to sort descending instead of ascending. There is also a drop-down list to restrict or expand the number of rows to be fetched from the database.

The group box Query Filter allows you to restrict the rows to be read from the database with query conditions. Even if you do not define any condition, the result set is limited to objects located in one of the monitored schemas. Monitored schemas are the SAP system’s main database schema (in an ABAP system, the main schema uses the format R3<SID>DATA and in a Java system, the main schema uses the format SAP<SID>DB) and the SAP Database Performance Collector schema (SAPDB4M for non-switchable ASPs or SAPDB4M<nnn> for independent ASPs). The value R3SAP400 is added to this list in the screens of the system catalog views SYSROUTINES, SYSPARMS, and SYSVARIABLES. You can select either one of these schemas or All Monitored Schemas in the top row of the group box.

Further, you can specify up to eight free query conditions. In the first field, select the column to be filtered from a drop-down list of all table columns. In the second field, select a comparison operator (for details see table below). In the third field, enter the filter value. You can add or delete filter rows with the plus/minus buttons at the end of each row.

Table 2: Comparison Operators
Operator	Meaning	Value Format
Equal	=	Single value
Does Not Equal	<>	Single value
Matches	Matches pattern	Character string with wildcards. ? or _ for one character, * or % for any number of characters.
Does Not Match	Does not match pattern	Character string with wildcards. ? or _ for one character, * or % for any number of characters.
In	Equals any value from the list	List of values separated by comma. Parentheses around list are optional.
Not In	All values except the ones in the list	List of values separated by comma. Parentheses around list are optional.
Less	<	Single value. For non-numeric fields, the codepage sorting will be used for determining the order.
Less or Equal	<=	Single value. For non-numeric fields, the codepage sorting will be used for determining the order.
Greater	>	Single value. For non-numeric fields, the codepage sorting will be used for determining the order.
Greater or Equal	>=	Single value. For non-numeric fields, the codepage sorting will be used for determining the order.
Between	All values in interval including boundaries	Two values (low and high interval boundary) separated by and
Not Between	All values outside of interval boundaries	Two values (low and high interval boundary) separated by and

Note

The filter condition is case-sensitive because the field values in the system catalogs are case sensitive too.

When all query options and filter conditions have been set, choose the pushbutton Apply Selection on the upper left of the screen to refresh the display with the new list of system catalog elements.

Only rows that meet all the conditions on the different table columns are selected. If there are different conditions on the same field, all records are selected that meet at least one of them.

Recommendation

You will find a status message below the toolbar of the output table, right above the displayed rows. It shows the name of the current system catalog view, the number of selected rows, and it indicates whether the result set is complete or if it was truncated (due to limiting the number of fetched rows in the Query Options group box).

Note

The output table control itself also offers means for changing the sorting, to define filters, search within the result set, define subtotals and more. Please note that this will only affect the already displayed records. No new rows are read from the database. So if you want to use this functionality, you should set the Number of Rows in the Query Options box to All for the initial selection.

Depending on the chosen option, the output table contains additional pushbuttons. Select a row of the output table and choose a pushbutton to navigate to one of the other options, showing further information about the selected object.

Table 3:
Option	Pushbutton	Description
SYSTABLES	Columns	Displays the columns of the selected table.
	Indexes	Displays secondary indexes on the selected table.
	Constraints	Displays constraints on the selected table.
	Views	Displays views defined over the selected table.
	Triggers	Displays database triggers defined on the selected table.
SYSCOLUMNS	Table	Displays the table information for the selected table.
SYSCST	Table	Displays the table information for the table the selected constraint is defined on.
SYSCST	Keys	Displays the constraint keys of the selected constraint.
SYSKEYCST	Table	Displays the table information for the table the selected constraint is defined on.
SYSKEYCST	Constraint	Displays the constraint information for the selected constraint.
SYSINDEXES	Table	Displays the table information for the table the selected index is defined on.
SYSINDEXES	Keys	Displays the index keys of the selected index.
SYSKEYS	Table	Displays the table information for the table the selected index is defined on.
	Index	Displays the index information for the selected index.
	Column	Displays the table column attributes for the selected index key.
SYSVIEWS	Viewtext	Displays the complete definition text of the selected view.
	View	Displays the table information for the selected view.
	Dependencies	Displays the objects the selected view is based on.
SYSVIEWDEP	Cascade	Displays all objects the selected view is based on including all sub-dependencies
	Definition	Displays the view definition information of the selected view.
	View	Displays the table information for the selected view.
	Base Table	Displays the table information for the selected base table the view is based on.
SYSTRIGGERS	Statement	Displays the SQL action statement of the selected trigger.
SYSTRIGGERS	Table	Displays the table information for the subject table or view of the selected trigger.
SYSROUTINES	SQL	Displays the SQL body of the selected routine.
SYSROUTINES	Parameters	Displays the parameters of the selected routine.
SYSPARMS	Routine	Displays the routine info for the selected routine.