请求指导导出z/OS DB2所有Schema的主键、外键及关联表信息
2026-6-26
需求与求助
我是一名懂DB2的COBOL程序员，并非DB2管理员。原负责DB2数据恢复的4人团队已全部退休且未补岗，现我被授予所有数据库管理员权限，需要导出所有数据库Schema的主键、外键、关联表及尽可能多的关系信息，恳请指点方向。
以下是系统表列表：
1_| DB2_SYSPARM 2_| JES_SYSOUT 3_| SYSAUDITPOLICIES 4_| SYSAUDITPOLICIES_H 5_| SYSAUTOALERTS 6_| SYSAUTOALERTS_OUT 7_| SYSAUTORUNS_HIST 8_| SYSAUTORUNS_HISTOU 9_| SYSAUTOTIMEWINDOWS 10_| SYSAUXRELS 11_| SYSCHECKDEP 12_| SYSCHECKS 13_| SYSCHECKS2 14_| SYSCOLAUTH 15_| SYSCOLAUTH_H 16_| SYSCOLDIST 17_| SYSCOLDIST 18_| SYSCOLDISTSTATS 19_| SYSCOLDISTSTATS 20_| SYSCOLDIST_HIST 21_| SYSCOLSTATS 22_| SYSCOLSTATS 23_| SYSCOLUMNS 24_| SYSCOLUMNS 25_| SYSCOLUMNS_HIST 26_| SYSCONSTDEP 27_| SYSCONTEXT 28_| SYSCONTEXTAUTHIDS 29_| SYSCONTEXTAUTHID_H 30_| SYSCONTEXT_H 31_| SYSCONTROLS 32_| SYSCONTROLS_DESC 33_| SYSCONTROLS_DESC_H 34_| SYSCONTROLS_H 35_| SYSCONTROLS_RTXT 36_| SYSCONTROLS_RTXT_H 37_| SYSCOPY 38_| SYSCTXTTRUSTATTRS 39_| SYSCTXTTRUSTATTR_H 40_| SYSDATABASE 41_| SYSDATATYPES 42_| SYSDBAUTH 43_| SYSDBAUTH_H 44_| SYSDBD_DATA 45_| SYSDBRM 46_| SYSDEPENDENCIES 47_| SYSDUMMY1 48_| SYSDUMMYA 49_| SYSDUMMYE 50_| SYSDUMMYU 51_| SYSDYNQRY 52_| SYSDYNQRYDEP 53_| SYSDYNQRY_EXPL 54_| SYSDYNQRY_OPL 55_| SYSDYNQRY_SHTEL 56_| SYSDYNQRY_SPAL 57_| SYSDYNQRY_TXTL 58_| SYSENVIRONMENT 59_| SYSFIELDS 60_| SYSFOREIGNKEYS 61_| SYSINDEXCLEANUP 62_| SYSINDEXCONTROL 63_| SYSINDEXES 64_| SYSINDEXES 65_| SYSINDEXES_HIST 66_| SYSINDEXES_RTSECT 67_| SYSINDEXES_TREE 68_| SYSINDEXPART 69_| SYSINDEXPART_HIST 70_| SYSINDEXSPACESTATS 71_| SYSINDEXSTATS 72_| SYSINDEXSTATS 73_| SYSINDEXSTATS_HIST 74_| SYSIXSPACESTATS_H 75_| SYSJARCLASS_SOURCE 76_| SYSJARCONTENTS 77_| SYSJARDATA 78_| SYSJAROBJECTS 79_| SYSJAVAOPTS 80_| SYSJAVAPATHS 81_| SYSJSON_INDEX 82_| SYSKEYCOLUSE 83_| SYSKEYS 84_| SYSKEYTARGETS 85_| SYSKEYTARGETSTATS 86_| SYSKEYTARGETS_HIST 87_| SYSKEYTGTDIST 88_| SYSKEYTGTDISTSTATS 89_| SYSKEYTGTDIST_HIST 90_| SYSLEVELUPDATES 91_| SYSLGRNX 92_| SYSLOBSTATS 93_| SYSLOBSTATS_HIST 94_| SYSLOG 95_| SYSOBDS 96_| SYSOBD_AUX 97_| SYSOBJROLEDEP 98_| SYSPACKAGE 99_| SYSPACKAUTH 100_| SYSPACKAUTH_H 101_| SYSPACKCOPY 102_| SYSPACKDEP 103_| SYSPACKLIST 104_| SYSPACKSTMT 105_| SYSPACKSTMT_STMB 106_| SYSPACKSTMT_STMT 107_| SYSPARMS 108_| SYSPARM_SETTINGS 109_| SYSPENDINGDDL 110_| SYSPENDINGDDLTEXT 111_| SYSPENDINGOBJECTS 112_| SYSPKSYSTEM 113_| SYSPLAN 114_| SYSPLANAUTH 115_| SYSPLANAUTH_H 116_| SYSPLANDEP 117_| SYSPLSYSTEM 118_| SYSPRINT 119_| SYSPROFILE_TEXT 120_| SYSPSMOUT 121_| SYSQUERY 122_| SYSQUERYOPTS 123_| SYSQUERYPLAN 124_| SYSQUERYPREDICATE 125_| SYSQUERYSEL 126_| SYSQUERY_AUX 127_| SYSRELS 128_| SYSRESAUTH 129_| SYSRESAUTH_H 130_| SYSROLES 131_| SYSROUTINEAUTH 132_| SYSROUTINEAUTH_H 133_| SYSROUTINES 134_| SYSROUTINESTEXT 135_| SYSROUTINES_OPTS 136_| SYSROUTINES_SRC 137_| SYSROUTINES_TREE 138_| SYSSCHEMAAUTH 139_| SYSSCHEMAAUTH_H 140_| SYSSEQUENCEAUTH 141_| SYSSEQUENCEAUTH_H 142_| SYSSEQUENCES 143_| SYSSEQUENCESDEP 144_| SYSSESSION 145_| SYSSESSION_DATA 146_| SYSSESSION_EX 147_| SYSSESSION_GV 148_| SYSSESSION_STATUS 149_| SYSSPTSEC_DATA 150_| SYSSPTSEC_EXPL 151_| SYSSTATFEEDBACK 152_| SYSSTMT 153_| SYSSTOGROUP 154_| SYSSTRINGS 155_| SYSSYNONYMS 156_| SYSTABAUTH 157_| SYSTABAUTH_H 158_| SYSTABCONST 159_| SYSTABLEPART 160_| SYSTABLEPART 161_| SYSTABLEPART_HIST 162_| SYSTABLES 163_| SYSTABLES 164_| SYSTABLESPACE 165_| SYSTABLESPACE 166_| SYSTABLESPACESTATS 167_| SYSTABLES_HIST 168_| SYSTABLES_PROFILES 169_| SYSTABSPACESTATS_H 170_| SYSTABSTATS 171_| SYSTABSTATS 172_| SYSTABSTATS_HIST 173_| SYSTEM_HOSTNAME 174_| SYSTEXTCOLUMNS 175_| SYSTEXTCONFIGURATION 176_| SYSTEXTCONNECTINFO 177_| SYSTEXTDEFAULTS 178_| SYSTEXTINDEXES 179_| SYSTEXTLOCKS 180_| SYSTEXTSERVERHISTORY 181_| SYSTEXTSERVERS 182_| SYSTEXTSTATUS 183_| SYSTLOB1 184_| SYSTRIGGERS 185_| SYSTRIGGERS_STMT 186_| SYSUSERAUTH 187_| SYSUSERAUTH_H 188_| SYSUTIL 189_| SYSUTILX 190_| SYSVARIABLEAUTH 191_| SYSVARIABLES 192_| SYSVARIABLES_DESC 193_| SYSVARIABLES_TEXT 194_| SYSVIEWDEP 195_| SYSVIEWDEP_H 196_| SYSVIEWS 197_| SYSVIEWS_STMT 198_| SYSVIEWS_TREE 199_| SYSVOLUMES 200_| SYSXMLRELS 201_| SYSXMLSTRINGS 202_| SYSXMLTYPMOD 203_| SYSXMLTYPMSCHEMA
操作方向
1. 核心系统表关联逻辑
- 主键信息提取：从
SYSTABCONST表筛选TYPE='P'的主键约束记录，关联SYSKEYCOLUSE获取主键列顺序与名称，再通过SYSTABLES和SYSCOLUMNS匹配表的Schema、名称及列详细信息。 - 外键与关联表提取：从
SYSTABCONST表筛选TYPE='F'的外键约束记录，关联SYSFOREIGNKEYS获取关联的父表约束信息，再通过SYSKEYCOLUSE关联外键列与父表主键列，最终匹配SYSTABLES得到子表、父表的Schema和名称。 - 关系补充：
SYSCONSTDEP可查看约束的依赖关系，SYSRELS能获取表间参照关系的额外细节。
2. 实用查询语句示例
查询所有Schema的主键详情：
SELECT T.CREATOR AS SCHEMA_NAME, T.NAME AS TABLE_NAME, C.NAME AS COLUMN_NAME, K.COLSEQ AS COLUMN_ORDER FROM SYSTABCONST TC JOIN SYSTABLES T ON TC.TBCREATOR = T.CREATOR AND TC.TBNAME = T.NAME JOIN SYSKEYCOLUSE K ON TC.CONSTNAME = K.CONSTNAME AND TC.TBCREATOR = K.TBCREATOR JOIN SYSCOLUMNS C ON K.TBCREATOR = C.TBCREATOR AND K.TBNAME = C.TBNAME AND K.COLNAME = C.NAME WHERE TC.TYPE = 'P' ORDER BY SCHEMA_NAME, TABLE_NAME, COLUMN_ORDER;
查询所有Schema的外键及关联表详情：
SELECT CHILD.TBCREATOR AS CHILD_SCHEMA, CHILD.TBNAME AS CHILD_TABLE, FK_COL.NAME AS FK_COLUMN, FK_COLS.COLSEQ AS FK_COLUMN_ORDER, PARENT.TBCREATOR AS PARENT_SCHEMA, PARENT.TBNAME AS PARENT_TABLE, PK_COL.NAME AS PK_COLUMN FROM SYSTABCONST FK_CONST JOIN SYSFOREIGNKEYS FK ON FK_CONST.CONSTNAME = FK.CONSTNAME AND FK_CONST.TBCREATOR = FK.TBCREATOR JOIN SYSTABCONST PK_CONST ON FK.REFTBCREATOR = PK_CONST.TBCREATOR AND FK.REFTBNAME = PK_CONST.TBNAME AND FK.REFCONSTNAME = PK_CONST.CONSTNAME JOIN SYSKEYCOLUSE FK_COLS ON FK_CONST.CONSTNAME = FK_COLS.CONSTNAME AND FK_CONST.TBCREATOR = FK_COLS.TBCREATOR JOIN SYSKEYCOLUSE PK_COLS ON PK_CONST.CONSTNAME = PK_COLS.CONSTNAME AND PK_CONST.TBCREATOR = PK_COLS.TBCREATOR AND FK_COLS.COLSEQ = PK_COLS.COLSEQ JOIN SYSTABLES CHILD ON FK_CONST.TBCREATOR = CHILD.CREATOR AND FK_CONST.TBNAME = CHILD.NAME JOIN SYSTABLES PARENT ON PK_CONST.TBCREATOR = PARENT.CREATOR AND PK_CONST.TBNAME = PARENT.NAME JOIN SYSCOLUMNS FK_COL ON FK_COLS.TBCREATOR = FK_COL.TBCREATOR AND FK_COLS.TBNAME = FK_COL.TBNAME AND FK_COLS.COLNAME = FK_COL.NAME JOIN SYSCOLUMNS PK_COL ON PK_COLS.TBCREATOR = PK_COL.TBCREATOR AND PK_COLS.TBNAME = PK_COL.TBNAME AND PK_COLS.COLNAME = PK_COL.NAME WHERE FK_CONST.TYPE = 'F' ORDER BY CHILD_SCHEMA, CHILD_TABLE, FK_COLUMN_ORDER;
3. 结果导出与可视化
- 使用DB2的
EXPORT命令将查询结果导出为CSV格式，方便后续处理：EXPORT TO PK_INFO.CSV OF DEL MODIFIED BY COLDEL, SELECT ...; - 若需要直观的关系图，可将导出的数据导入数据库建模工具（如ERStudio、PowerDesigner）生成ER图。
4. 注意事项
- 查询时可通过
T.CREATOR NOT IN ('SYSIBM', 'SYSCAT')过滤系统自带Schema的数据，避免冗余结果。 - 列表中部分系统表存在重复条目（如两次出现的
SYSCOLUMNS），查询时优先使用无后缀的主表即可。
内容的提问来源于stack exchange，提问作者user3166462
相关产品推荐
相关产品推荐
