DB2 catalog dependency and type tables

Why dependencies and types share a chapter

Object tables tell you a view or distinct type exists. They do not tell you the blast radius of DROP TABLE HR.EMPLOYEE. SYSVIEWDEP (and SYSPACKDEP from the package page) is that blast radius for views and programs. SYSDATATYPES is the blast radius for DROP TYPE: columns, parameters, and globals that use the type. Aliases and synonyms are extra names that must be resolved before you decide two shops are talking about the same table. SYSSTRINGS and SYSXMLSTRINGS are conversion dictionaries—not application tables, but still catalog rows you will query during CCSID and XML incidents.

SYSIBM.SYSVIEWDEP

Each row says: view DCREATOR.DNAME depends on object BCREATOR.BNAME of type BTYPE. Nested views produce a chain: V1 depends on V2, V2 depends on T. Always walk until BTYPE is a table (or you understand the remaining types).

1
2
3
4
5
6
7
8
9
10
11
12
13
-- What does view HR.V_EMP depend on?
SELECT DCREATOR, DNAME, BCREATOR, BNAME, BTYPE
  FROM SYSIBM.SYSVIEWDEP
 WHERE DCREATOR = 'HR'
   AND DNAME = 'V_EMP'
  WITH UR;
 
-- Which views depend on table HR.EMPLOYEE?
SELECT DCREATOR, DNAME, BTYPE
  FROM SYSIBM.SYSVIEWDEP
 WHERE BCREATOR = 'HR'
   AND BNAME = 'EMPLOYEE'
  WITH UR;

IBM tools and vendor DROP-impact reports join SYSVIEWDEP with SYSPACKDEP, SYSRELS, and SYSTRIGGERS. Recreate that join yourself before you trust a GUI. Watch for functions (BTYPE F): dropping a UDF can invalidate views even when no table was dropped.

Finding triggers

Triggers are not rows in SYSVIEWDEP. They live in SYSIBM.SYSTRIGGERS. Finding them is part of every dependency checklist because a BEFORE UPDATE trigger can be the real reason an ALTER fails or a batch slows down.

1
2
3
4
5
SELECT SCHEMA, NAME, TRIGTIME, TRIGEVENT, GRANULARITY
  FROM SYSIBM.SYSTRIGGERS
 WHERE TBOWNER = 'HR'
   AND TBNAME = 'EMPLOYEE'
  WITH UR;

• TRIGTIME — B before, A after, I instead of
• TRIGEVENT — I insert, U update, D delete
• GRANULARITY — R row, S statement

INSTEAD OF triggers on views couple SYSTRIGGERS to SYSVIEWS: the view has no base-table UPDATE of its own; the trigger is the write path. Include them when you ask “is this view updatable?”

SYSIBM.SYSDATATYPES

One row per user-defined type. Built-in INTEGER does not appear here. CREATE TYPE AS INTEGER and CREATE TYPE … AS … ARRAY do.

Other columns IBM documents: SCHEMA, OWNER, NAME, CREATEDBY, SOURCESCHEMA, SOURCETYPE, DATATYPEID, SOURCETYPEID, LENGTH, SCALE, ARRAYLENGTH (ordinary array cardinality; 0 otherwise), and associative-array index type fields. LENGTH is maximum length or DECIMAL precision depending on the source type.

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT SCHEMA, NAME, METATYPE, SOURCESCHEMA, SOURCETYPE,
       LENGTH, SCALE, ENCODING_SCHEME, ARRAYLENGTH
  FROM SYSIBM.SYSDATATYPES
 WHERE SCHEMA = 'HR'
  WITH UR;
 
-- Columns that use a distinct type
SELECT C.TBCREATOR, C.TBNAME, C.NAME, C.COLTYPE, C.TYPESCHEMA, C.TYPENAME
  FROM SYSIBM.SYSCOLUMNS C
 WHERE C.COLTYPE = 'DISTINCT'
   AND C.TYPESCHEMA = 'HR'
   AND C.TYPENAME = 'EMPNO_T'
  WITH UR;

Distinct types are not assignment-compatible with their source type without CAST. That is why SYSCOLUMNS COLTYPE DISTINCT plus TYPESCHEMA/TYPENAME matter: a report that only prints LENGTH will look like INTEGER and then your COBOL host variable will not match.

SYSIBM.SYSSYNONYMS and aliases

SYSSYNONYMS (deprecated CREATE SYNONYM)

SYSSYNONYMS has one row per synonym: NAME and CREATOR of the synonym, TBNAME and TBCREATOR of the table. CREATE SYNONYM is deprecated on Db2 for z/OS. You still query this table on subsystems that never converted old names. New designs should use aliases.

1
2
3
4
5
SELECT CREATOR, NAME, TBCREATOR, TBNAME
  FROM SYSIBM.SYSSYNONYMS
 WHERE TBCREATOR = 'HR'
   AND TBNAME = 'EMPLOYEE'
  WITH UR;

Aliases (CREATE ALIAS) — SYSTABLES TYPE A

IBM’s Administration Guide tells you to retrieve alias information from SYSIBM.SYSTABLES, not from a separate everyday “SYSALIASES” inventory table in the same way LUW or IBM i expose alias views. On z/OS:

• TYPE is A
• NAME and CREATOR are the alias
• TBCREATOR and TBNAME are the target table or view
• LOCATION is the remote location for a three-part alias, or blank for local
• Alias object rows typically sit in catalog database DSNDB06 table space SYSTSTAB

1
2
3
4
5
6
SELECT LOCATION, CREATOR, NAME, TBCREATOR, TBNAME
  FROM SYSIBM.SYSTABLES
 WHERE TYPE = 'A'
   AND TBCREATOR = 'DSN8D10'
   AND TBNAME = 'EMP'
  WITH UR;

IBM also documents TABLE_NAME, TABLE_SCHEMA, and TABLE_LOCATION functions that resolve an alias chain. Use them when aliases point at aliases. Sequence aliases are cataloged with sequence objects (SYSSEQUENCES), not as TYPE A table rows—do not search only SYSTABLES for every CREATE ALIAS you ever issued.

Some checklists and other IBM platforms name a catalog object SYSALIASES. On this site’s z/OS path, treat “SYSALIASES” as “alias rows in SYSTABLES (TYPE A)” unless your shop has a local view with that name.

SYSIBM.SYSSTRINGS

Each row describes conversion of character strings from INCCSID to OUTCCSID. Db2 uses the conversion tables those rows identify (along with z/OS Unicode Services). IBM-supplied rows have IBMREQD = Y and must not be updated or deleted. You may add a row for the same CCSID pair with IBMREQD = N; if both exist, Db2 uses your row.

Other documented columns include TRANSTYPE (for example SS versus PS conversion classes), ERRORBYTE, SUBBYTE, TRANSPROC, and TRANSTAB. IBM’s example rows convert CCSID 500 ↔ 37 and 948 → 37 with substitute code points.

1
2
3
4
5
SELECT INCCSID, OUTCCSID, TRANSTYPE, ERRORBYTE, SUBBYTE, IBMREQD
  FROM SYSIBM.SYSSTRINGS
 WHERE INCCSID IN (37, 500)
   AND OUTCCSID IN (37, 500)
  WITH UR;

When a query “looks almost right” after joining EBCDIC and Unicode tables, SYSSTRINGS (and the CCSID columns on SYSCOLUMNS) are the catalog place to confirm that a conversion path exists—not the first place to rewrite business SQL.

SYSIBM.SYSXMLSTRINGS

XML support stores string identifiers for URIs and related XML names in SYSXMLSTRINGS. Each row maps an internal string ID to the string value Db2 uses for XML typed storage. You do not GRANT application access to maintain this table. You query it when IBM service or an XML utility message cites a string ID, or when you compare XML dictionaries after copy/recover.

Related XML catalog tables (SYSXMLRELS, XML type-modifier tables) describe XML column relationships and schemas. They are neighbors, not substitutes, for SYSXMLSTRINGS.

A DROP impact mini-script

Before DROP TABLE HR.EMPLOYEE, run at least:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Views
SELECT DCREATOR, DNAME
  FROM SYSIBM.SYSVIEWDEP
 WHERE BCREATOR = 'HR' AND BNAME = 'EMPLOYEE'
  WITH UR;
 
-- Aliases
SELECT CREATOR, NAME
  FROM SYSIBM.SYSTABLES
 WHERE TYPE = 'A' AND TBCREATOR = 'HR' AND TBNAME = 'EMPLOYEE'
  WITH UR;
 
-- Synonyms (legacy)
SELECT CREATOR, NAME
  FROM SYSIBM.SYSSYNONYMS
 WHERE TBCREATOR = 'HR' AND TBNAME = 'EMPLOYEE'
  WITH UR;
 
-- Triggers
SELECT SCHEMA, NAME
  FROM SYSIBM.SYSTRIGGERS
 WHERE TBOWNER = 'HR' AND TBNAME = 'EMPLOYEE'
  WITH UR;
 
-- Packages (from SYSPACKDEP)
SELECT DCOLLID, DNAME
  FROM SYSIBM.SYSPACKDEP
 WHERE BCREATOR = 'HR' AND BNAME = 'EMPLOYEE' AND BTYPE = 'T'
  WITH UR;

Add SYSRELS (children and parent) from the object-catalog page. That set is the difference between a clean DROP and a week of invalid packages.

Explain It Like I'm Five

SYSVIEWDEP is a friendship bracelet chart: this window-view is friends with that real table. If you throw away the table, the window looks at nothing. Aliases and synonyms are nicknames taped on the table; SYSTABLES TYPE A and SYSSYNONYMS are the nickname lists. SYSDATATYPES is a homemade label maker (“this is not just a number, it is an employee number”). SYSSTRINGS is the translation dictionary between two alphabets (CCSIDs). SYSXMLSTRINGS is the dictionary for XML name stickers. Triggers are alarms bolted to the table—look them up in SYSTRIGGERS before you move the table out of the room.

Exercises

1. Pick a view and list every SYSVIEWDEP row. Recursively follow any BTYPE = 'V' until you reach tables.
2. Query SYSDATATYPES in a schema that has distinct types. Join SYSCOLUMNS to find columns of those types.
3. Find all TYPE = 'A' aliases that point at one sample table.
4. Explain IBMREQD Y versus N on SYSSTRINGS and what happens if both rows exist for the same CCSID pair.
5. List triggers on a table and classify each by TRIGTIME and TRIGEVENT.