Map to the Catalog Tables

Catalog Synchronization Facility › Map to the Catalog Tables

Map to the Catalog Tables

The following tables show how the meta-entities and their attributes in the DB2 Dialog map to columns of the DB2 catalog tables after executing the Catalog Synchronization Utility.

The CatSync Detail Level refers to the four entity types from which a catalog synchronization can be performed. These include:

Detail level	Refers to
D	DATABASE
TS	TBSPACE
T	TABLE
V	VIEW
DT	DATATYPE
SQ	SEQUENCE

When you execute CatSync at the level of detail indicated by the flag, the entity, relationship, or association is populated at that level of detail.

The following table shows entities mapped from DB2 catalog tables. The comment [see the table following] indicates that the DB2 table or attribute is referred to by another table in this section.

Repository Entity	Attribute	DB2 Catalog Table	Catalog Column	CatSync Detail Level	DB2 Vsn
COLUMNa	Column Name Element Name Table Name Column Label Description Default Value Datatype Start with Increment by Cache	SYSIBM.SYSCOLUMNS SYSIBM.SYSSEQUENCES	[see below] Name Tbname Label Remarks Default Value Typename Start Increment Cache	D, TS, T,V	6
DATABASE	Database Name Bufferpool Index Bufferpool Roshare Type Group Member Encoding Scheme SBCS CCSID DBCS CCSID Mixed CCSID	SYSIBM.SYSDATABASE	Name Bpool Indexbp Roshare Type Group Member Encoding Scheme SBCS CCSID DBCS CCSID Mixed CCSID	D, TSb	6 5 5 5
DATATYPE	Distinct Data Type Name Data Type Maximum Length Decimal Places Encoding Scheme Description	SYSIBM.SYSDATATYPES	Name Sourcetype Length Scale Encoding Scheme Remarks	DT	6
SEQUENCE	Sequence name Sequence type Order Cycle Minimum value Maximum value Increment Start with Cache Restart with Identity Precision	SYSIBM.SYSSEQUENCES	NAME SEQTYPE ORDER CYCLE MINVALUE MAXVALUE INCREMENT START CACHE RESTARTWITH PRECISION	SQ	8
DB PROC	Procedure Name Procedure Schema Dynamic result sets Parameter style Deterministic Fenced Called on null input SQL data access Collection ID WLM environment CPU time External security Commit on return Special registers Program type Stayresident Runtime options Description	SYSIBM.SYSROUTINES	NAME SCHEMA RESULT_SETS PARAMETER_STYLE DETERMINISTIC FENCED NULL_CALL SQL_DATA_ACCESS COLLID WLM_ENVIRONMENT ASUTIME EXTERNAL_SECURITY COMMIT_ON_RETURN SPECIAL_REGS PROGRAM_TYPE STAYRESIDENT RUNOPTS REMARKS		7
ELEMENTa	Element Name DB2 Column Name Data Type Maximum Length Decimal Places Description Field Procc Field Parmscc	SYSIBM.SYSCOLUMNS SYSIBM.SYSFIELDS	Named Name Coltype Length Scale Remarks Fldproc Parmlist	D, TS, T,V D, TS, T,V
FORGNKEY	Foreign Key Name Constraint Name Key Name Delete Rule Table Name	SYSIBM.SYSRELS	[see below] Relname Relname Delete rule Tbname	D, TS, T
KEY	Key Name Creatore Unique Index Clustering Index Bufferpool Number of Subpages Close Rule Dataset Password Index Type Piecesize	SYSIBM.SYSINDEXES	Name Creator Unique rule Clustering Bpool Pgsize Closerule Dsetpass Index Type Piecesize	D, TS, T, V
KEY COL (Inserting Primary keys) (Inserting Foreign Keys)	Key Column Name Key Name Column Name Order By Direction Seq Key Column Name Key Name Column Name Seq	SYSIBM.SYSKEYS SYSIBM.SYSFOREIGNKEYS	[see below] Ixname Colname Ordering Colseq [see below] Relname Colname Colseq	D, TS, T, V D, TS, T
LOCATION	Location Name System Name Table Name	SYSIBM.SYSTABLES	[see below] Location Name	D, TS, T
ORC FUNC	Function Name Function Schema Specificname Function Type Parameter style Deterministic Fenced Called on null input SQL data access Collection ID External action Scratchpad Scratchpad Length Final call Allow parallel Cardinality WLM environment CPU time External security Commit on return Special registers Program type Stayresident Runtime options Description	SYSIBM.SYSROUTINES	NAME SCHEMA SPECIFICNAME FUNCTION_TYPE PARAMETER_STYLE DETERMINISTIC FENCED NULL_CALL SQL_DATA_ACCESS COLLID EXTERNAL_ACTION SCRATCHPAD SCRATCHPAD_LENGTH FINAL_CALL PARALLEL CARDINALITY WLM_ENVIRONMENT ASUTIME EXTERNAL_SECURITY COMMIT_ON_RETURN SPECIAL_REGS PROGRAM_TYPE STAYRESIDENT RUNOPTS REMARKS		7
PARM	Parm Name Datatype Kind Length Numeric Scale	SYSIBM.SYSPARMS	Parmname Typename Rowtype Length Scale		7
PART COL	Partition Column Name Key Col Name Column Value Partition Number	SYSIBM.SYSINDEXPART	[see below] [see below] Limit Key Partition	D, TS, T
PART (Inserting Table Spaces) (Inserting Indexes)	Partition Name Partition Number Partition Name Partition Number	SYSIBM.SYSTABLEPART SYS.SYSINDEXPART	[see below] Partition [see below] Partition	D, TS D, TS, T
PRIM KEYf	Primary Key Name Key Name Table Name	SYSIBM.SYSINDEXES	[see below] Ixname Tbname	D, TS, T
PROGFUNC	Path Name Language	SYSIBM.SYSROUTINES	EXTERNAL_NAME LANGUAGE		7
PROGRAM	Program Name Language	SYSIBM.SYSROUTINES	EXTERNAL_NAME LANGUAGE		7
SPACE (Inserting Table Spaces) (Inserting Indexes)	Space Name Primary Quantity Secondary Quantity Freepage PCTfree Catalog Name Erase Rule Compress GRPBPOOL Cache Space Name Primary Quantity Secondary Quantity Freepage PCTfree Catalog Name Erase Rule GRPBPOOL Cache	SYSIBM.SYSTABLEPART SYSIBM.SYSTABLESPACE SYSIBM.SYSINDEXPART SYSIBM.SYSINDEXES	[see below] Pqty Sqty Freepage Pctfree VCATname Eraserule Compress GBPCACHE [see below] Pqty Sqty Freepage Pctfree VCATname Eraserule GBPCACHE	D, TS D, TS D, TS, T D, TS, T
SQL COL	SQL Column Name Column Name View Column Name Label Description	SYSIBM.SYSVIEWS SYSIBM.SYSCOLUMNS	Text Label Remarks	D, TS, T, V
STOGROUPg	Storage Group Name Storage Group Name Storage Group Name	SYSIBM.SYSDATABASE SYSIBM.SYSDATABASE SYSIBM.SYSTABLEPART	Stgroup Stoname Stoname	D D, TS D, TS, T
SYNONYM	Synonym Name Creatore	SYSIBM.SYSSYNONYMS	Name Creator	D, TS, T, V
SYSTEM	Location Name	SYSIBM.SYSTABLES	Location	D, TS, T
TABLE	Table Name Creatore Edit Procedure Validation Procedure Audit Table Label Description Restrict on Drop Encoding scheme Type	SYSIBM.SYSTABLES	Name Creator Ed proc Valproc Auditing Label Remarks Restrictondrop Encodingscheme Type	D, TS, T, V	6
TB ALIAS	Alias Name Creatorh Description	SYSIBM.SYSTABLES	Tbname Tbcreator Remarks	D, TS, T
TBSPACE	Table Space Name Bufferpool Number of Partitions Locksize Dataset Size Close Rule Segment Size Dataset Password Max Locks Type Encoding Scheme SBCS CCSID DBCS CCSID Mixed CCSID Max Rows Lock part	SYSIBM.SYSTABLESPACE	Name Bpool Partitions Lockrule DssizeClose rule Segsize Dsetpass Lockmax Type Encodingscheme SBCS CCSID DBCS CCSID Mixed CCSID Maxrows Lockpart	D, TS, T	6
TRIGGER	Trigger Name Trigger Schema Trigger Time Triggering Events Granularity Table Name Description	SYSIBM.SYSTRIGGERS	Name Schema Ttrigtime Trigevent Granularity Tbname Remarks	D, TS, T	6
VCAT (Inserting Tbl. Spaces) (Inserting Indexes)	VCAT Name VCAT Name	SYSIBM.SYSTABLEPART SYS.SYSINDEXPART	VCATname VCATname	D, TSg D, TS, T
VIEW	View Name Creator Description Check	SYSIBM.SYSTABLES SYSIBM.SYSVIEWS	Name Creator Remarks Check	D, TS, T, V
WHERE ST	Where Stmt Name	SYSIBM.SYSVIEWS	Text	D, TS, T, V

CatSync creates both a COLUMN relationship and an ELEMENT entity unless an ELEMENT entity is found for reuse. In this situation, CatSync inserts a COLUMN only. CatSync reuses an ELEMENT entity if the DB2 Column Name at-tribute and the element's physical characteristics match that of the column defined in the DB2 catalog. If the DB2 Column Name and the physical characteristics of an ELEMENT match, but the descriptions or remarks do not, CatSync updates description attribute of the related COLUMN occurrence with the new description; the ELEMENT occurrence remains unchanged. CatSync never deletes ELEMENTs during catalog synchronization-it will only disconnect them from a TABLE (by deleting a COLUMN occurrence).
If you execute CatSync at a low level of detail (for example, from the entity types TBSPACE or TABLE), and the Cat-Sync utility is unable to find connecting entities of higher levels for reuse, it inserts an occurrence of that higher level entity. For example, if you are synchronizing from the TBSPACE entity type and the CatSync Utility does not find a reusable DATABASE entity, it inserts a DATABASE occurrence and an IN association connecting the DATABASE occurrence to the TBSPACE occurrence.
For performance considerations, CA Repository for z/OS provides an option that allows the CatSync utility to ignore FIELDPROC information during processing (this is the default setting).
By default, all CA Repository for z/OS converts underscore characters on the NAME column of SYSIBM.SYSCOLUMNS to hyphens for the ELEMENT_NAME attribute on the ELEMENT entity type (the LANGUAGE parameter is set to C). To modify this, change the LANGUAGE parameter to a P. If you want this modification to be permanent, make the change in DBXSYN2 skeleton. Otherwise, make the change in the JCL (this will only affect that particular run).
CREATOR is only compared and loaded if you set the Qualification Level field of Sync DB2 Catalog window to Y or F.
Server is only compared and loaded if you set the Qualification Level field of Sync DB2 Catalog window to F.
On SYSIBM.SYSINDEXES, when UNIQUERULE is set to P, the index is the primary key of the table. This tells CA Repository for z/OS to populate the PRIM KEY relationship as well as the INDEX ON association.
Column STORTYPE on catalog table SYSIBM.SYSTABLEPART indicates whether a STOGROUP is used (I indicates used, E indicates not used). If a STOGROUP is not used, CatSync creates a VCAT occurrence.
CA Repository for z/OS uses TBCREATOR to distinguish tables with the same name when the Qualification Level field is set to Y or F.
CA Repository for z/OS uses the Server name from the CATSYNC Panel to distinguish tables with the same name when the Qualification level is set to F.

Email CA about this topic