0 / 0
Asset relationships reporting tables
Last updated: Dec 13, 2024
Asset relationships reporting tables

Check the PostgreSQL, Db2 statements for the tables that are related to asset relationships.

Subject area Table name Description
Asset relationships asset_artifact_relations The associations between asset and governance artifacts.
Asset relationships container_assets_associations The relationships between the catalog assets.
Asset relationships asset_tags The tags that are associated with an asset.
Asset relationships governance_artifact_container_associations The governance artifact, which is associated with a catalog asset.
Asset relationships data_asset_column_artifact_associations The governance artifacts, which are associated with the column of the data asset.
Asset relationships column_to_asset_relations The associations between data asset column and an asset.
Asset relationships column_to_column_relations The associations between data asset columns.
Asset relationships column_to_artifact_relations The associations between data asset column and artifacts.
Asset relationships data_asset_pk_fk_columns The associations between data asset column and any asset.
Asset relationships data_asset_fk_ref_columns The associations between data asset reference column and any asset.

asset_artifact_relations

This table contains information about the associations between asset and governance artifacts.

The table has the following columns:

  • end1_asset_id - The identifier of the end1 asset.
  • end2_artifact_id - The target artifact ID of the asset-artifact relationship.
  • end1_container_id - The container ID of the end1 asset.
  • end2_artifact_type - The target artifact type of the asset-artifact relationship.
  • end1_relationship_type - The relationship type as identified from the end1 asset.
  • end2_relationship_type - The relationship type as identified from the end2 asset.
PostgreSQL

CREATE TABLE statement:

CREATE TABLE asset_artifact_relations(end1_asset_id varchar(128) NOT NULL,
end1_container_id varchar(36) NOT NULL,
end2_artifact_id varchar(128) NOT NULL,
end2_artifact_type varchar(128) NOT NULL,
end1_relationship_type varchar(256) NOT NULL,
end2_relationship_type varchar(256) NOT NULL,
tech_start TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) NOT NULL DEFAULT to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(end1_asset_id,
end1_container_id,
end2_artifact_id,
end2_artifact_type,
end1_relationship_type))

ALTER TABLE statements:

alter table asset_artifact_relations add constraint fk_asset_artifact_relations_container_assets_1 foreign key (end1_container_id,
end1_asset_id) references container_assets(container_id,
asset_id) on
delete
	cascade on
	update
	no action

alter table asset_artifact_relations add constraint fk_asset_artifact_relations_governance_artifacts_2 foreign key (end2_artifact_id) references governance_artifacts(artifact_id) on
	delete
	cascade on
	update
	no action
Db2

CREATE TABLE statement:

CREATE TABLE asset_artifact_relations(end1_asset_id varchar(128) NOT NULL,
end1_container_id varchar(36) NOT NULL,
end2_artifact_id varchar(128) NOT NULL,
end2_artifact_type varchar(128) NOT NULL,
end1_relationship_type varchar(256) NOT NULL,
end2_relationship_type varchar(256) NOT NULL,
tech_start TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW
BEGIN
	,
	tech_end TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW
END,
ts_id TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID,
PRIMARY KEY(end1_asset_id,
end1_container_id,
end2_artifact_id,
end2_artifact_type,
end1_relationship_type),
PERIOD SYSTEM_TIME (tech_start,
tech_end) ) ORGANIZE BY ROW

ALTER TABLE statements:

ALTER TABLE asset_artifact_relations ADD CONSTRAINT
fk_asset_artifact_relations_container_assets_1 FOREIGN KEY (end1_container_id,end1_asset_id)REFERENCES container_assets(container_id,asset_id) ON
	DELETE
	CASCADE  ON
	UPDATE
	NO ACTION

ALTER TABLE asset_artifact_relations ADD CONSTRAINT fk_asset_artifact_relations_governance_artifacts_2 FOREIGN KEY (end2_artifact_id) REFERENCES governance_artifacts(artifact_id) ON
DELETE
	CASCADE ON
	UPDATE
	NO ACTION

container_assets_associations table

This table contains information about the relationships between the catalog assets.

This table has the following columns:

  • end1_asset_id - The identifier of the source asset.
  • end2_asset_id - The identifier of the target asset.
  • end1_container_id - The identifier of the workspace of the source asset.
  • end2_container_id - The identifier of the workspace of the target asset.
  • end1_relationship_type - The relationship type as identified from the source asset.
  • end2_relationship_type - The relationship type as identified from the target asset.
PostgreSQL

CREATE TABLE statement:

CREATE TABLE catalog_assets_associations(end1_asset_id varchar(128) NOT NULL,
end2_asset_id varchar(128) NOT NULL,
end1_container_id varchar(36) NOT NULL,
end2_container_id varchar(36) NOT NULL,
end1_relationship_type varchar(128) NOT NULL,
end2_relationship_type varchar(128) NOT NULL,
tech_start TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) NOT NULL DEFAULT to_timestamp('9999-12-30','YYYY-MM-DD'),
ts_id TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(end1_container_id,
end2_container_id,
end1_asset_id,
end2_asset_id,
end1_relationship_type));

ALTER TABLE statements:

alter table catalog_assets_associations add constraint fk_catalog_assets_associations_container_assets_7 foreign key (end1_container_id,
end1_asset_id) references container_assets(container_id,
asset_id) on
delete
	cascade on
	update
	no action

alter table catalog_assets_associations add constraint fk_catalog_assets_associations_container_assets_8 foreign key (end2_container_id,
end2_asset_id) references container_assets(container_id,
asset_id) on
delete
	cascade on
	update
	no action

alter table catalog_assets_associations add constraint fk_catalog_assets_associations_containers_10 foreign key (end1_container_id) references containers(container_id) on
delete
	cascade on
	update
	no action
Db2

CREATE TABLE statement:

CREATE TABLE catalog_assets_associations(end1_asset_id varchar(128) NOT NULL,
end2_asset_id varchar(128) NOT NULL,
end1_container_id varchar(36) NOT NULL,
end2_container_id varchar(36) NOT NULL,
end1_relationship_type varchar(128) NOT NULL,
end2_relationship_type varchar(128) NOT NULL,
tech_start TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW BEGIN,
tech_end TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW END,
ts_id TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID,
PRIMARY KEY(end1_container_id,
end2_container_id,
end1_asset_id,
end2_asset_id,
end1_relationship_type),
PERIOD SYSTEM_TIME (tech_start,
tech_end) )

ALTER TABLE statements:

alter table catalog_assets_associations add constraint fk_catalog_assets_associations_container_assets_7 foreign key (end1_container_id,
end1_asset_id) references container_assets(container_id,
asset_id) on
delete
	cascade on
	update
	no action

alter table catalog_assets_associations add constraint fk_catalog_assets_associations_container_assets_8 foreign key (end2_container_id,
end2_asset_id) references container_assets(container_id,
asset_id) on
delete
	cascade on
	update
	no action

alter table catalog_assets_associations add constraint fk_catalog_assets_associations_containers_10 foreign key (end1_container_id) references containers(container_id) on
delete
	cascade on
	update
	no action

asset_tags table

This table contains information about the tags that are associated with an asset.

This table has the following columns:

  • tag_name - The name of the associated tag.
  • asset_id - The identifier of the asset.
  • container_id - The identifier of the catalog or project.
PostgreSQL

CREATE TABLE statement:

create table asset_tags(asset_id varchar(128) not null,
container_id varchar(36) not null,
tag_name varchar(256) not null,
tech_start TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) not null default to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
primary key(container_id,
asset_id,
tag_name));

ALTER TABLE statements:

alter table asset_tags add constraint fk_asset_tags_container_assets_3 foreign key (container_id,
asset_id) references container_assets(container_id,
asset_id) on
delete
	cascade on
	update
	no action

alter table asset_tags add constraint fk_asset_tags_tags_4 foreign key (tag_name) references tags(tag_name) on
delete
	cascade on
	update
	no action
Db2

CREATE TABLE statement:

create table asset_tags(asset_id varchar(128) not null,
container_id varchar(36) not null,
tag_name varchar(256) not null,
tech_start TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row begin,
tech_end TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row
end,
ts_id TIMESTAMP(12) not null generated always as transaction start ID,
primary key(container_id,
asset_id,
tag_name),
period SYSTEM_TIME (tech_start,
tech_end) )

ALTER TABLE statements:

alter table asset_tags add constraint fk_asset_tags_container_assets_3 foreign key (container_id,
asset_id) references container_assets(container_id,
asset_id) on
delete
	cascade on
	update
	no action

alter table asset_tags add constraint fk_asset_tags_tags_4 foreign key (tag_name) references tags(tag_name) on
delete
	cascade on
	update
	no action

governance_artifact_container_associations table

This table contains information about governance artifacts, for example business terms or classification, which is associated with a catalog asset.

This table has the following columns:

  • asset_id - The identifier of the asset.
  • container_id - The identifier of the catalog.
  • associated_artifact_id - The identifier of the associated governance artifact.
  • associated_artifact_type - The type of the associated governance artifact, for example glossary_term, classification.
  • system_id - The system identifier or global identifier of the associated governance artifact.
  • assignment_state - The assignment state of the artifact with asset.
  • assignment_date - The assignment date of the artifact with asset.
  • assigned_by - The person who assigned the artifact to an asset.
  • confidence - The confidence on artifact that is assigned to an asset.
  • specification - The algorithm uses to assign artifact.
PostgreSQL

CREATE TABLE statement:

create table governance_artifact_container_associations(asset_id varchar(128) not null,
container_id varchar(36) not null,
associated_artifact_id varchar(128) not null,
associated_artifact_type varchar(128),
system_id varchar(128) not null,
assignment_state varchar(32),
assignment_date timestamp(6),
assigned_by varchar(128),
confidence float,
specification varchar(128),
tech_start TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) not null default to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
primary key(asset_id,
container_id,
associated_artifact_id))

ALTER TABLE statements:

alter table governance_artifact_catalog_associations add constraint fk_governance_artifact_catalog_associations_governance_artifacts_17 foreign key (associated_artifact_id) references governance_artifacts(artifact_id) on
delete
	cascade on
	update
	no action

alter table governance_artifact_catalog_associations add constraint fk_governance_artifact_catalog_associations_container_assets_9 foreign key (container_id,
asset_id) references container_assets(container_id,
asset_id) on
delete
	cascade on
	update
	no action
Db2

CREATE TABLE statement:

create table governance_artifact_container_associations(asset_id varchar(128) not null,
container_id varchar(36) not null,
associated_artifact_id varchar(128) not null,
associated_artifact_type varchar(128),
system_id varchar(128) not null,
assignment_state varchar(32),
assignment_date timestamp(6),
assigned_by varchar(128),
confidence float,
specification varchar(128),
tech_start TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) not null default to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
primary key(asset_id,
container_id,
associated_artifact_id))

ALTER TABLE statements:

alter table governance_artifact_catalog_associations add constraint fk_governance_artifact_catalog_associations_governance_artifacts_17 foreign key (associated_artifact_id) references governance_artifacts(artifact_id) on
delete
	cascade on
	update
	no action

alter table governance_artifact_catalog_associations add constraint fk_governance_artifact_catalog_associations_container_assets_9 foreign key (container_id,
asset_id) references container_assets(container_id,
asset_id) on
delete
	cascade on
	update
	no action

data_asset_column_artifact_associations table

This table contains information about governance artifacts, for example business terms or classifications, which are associated with the column of the data asset.

This table has the following columns:

  • name - The name of the column.
  • asset_id - The identifier of the asset.
  • container_id - The identifier of the catalog.
  • associated_artifact_id - The identifier of the associated governance artifact.
  • associated_artifact_type - The type of the associated governance artifact, for example glossary_term, classification.
  • system_id - The system identifier or global identifier of the associated governance artifact.
  • assignment_state - The assignment state of the artifact with column asset.
  • assignment_date - The assignment date of the artifact with column asset.
  • assigned_by - The person who assigned the artifact to the column asset.
  • confidence - The confidence on artifact that is assigned to an asset.
  • specification - The algorithm uses to assign artifact.
PostgreSQL

CREATE TABLE statement:

create table data_asset_column_artifact_associations(asset_id varchar(128) not null,
container_id varchar(36) not null,
name varchar(256) not null,
associated_artifact_type varchar(128),
associated_artifact_id varchar(128) not null,
system_id varchar(128) not null,
assignment_state varchar(32),
assignment_date timestamp(6),
assigned_by varchar(128),
confidence float,
specification varchar(128),
tech_start TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) not null default to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
primary key(container_id,
asset_id,
name,
associated_artifact_id))

ALTER TABLE statements:

alter table data_asset_column_artifact_associations add constraint fk_data_asset_column_artifact_associations_governance_artifacts_16 foreign key (associated_artifact_id) references governance_artifacts(artifact_id) on
delete
	cascade on
	update
	no action

alter table data_asset_column_artifact_associations add constraint fk_data_asset_column_artifact_associations_catalog_data_asset_columns_14 foreign key (container_id,
asset_id,
name) references catalog_data_asset_columns(container_id,
asset_id,
name) on
delete
	cascade on
	update
	no action
Db2

CREATE TABLE statement:

create table data_asset_column_artifact_associations(asset_id varchar(128) not null,
container_id varchar(36) not null,
name varchar(256) not null,
associated_artifact_type varchar(128),
associated_artifact_id varchar(128) not null,
system_id varchar(128) not null,
assignment_state varchar(32),
assignment_date timestamp(6),
assigned_by varchar(128),
confidence float,
specification varchar(128),
tech_start TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) not null default to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
primary key(container_id,
asset_id,
name,
associated_artifact_id))

ALTER TABLE statements:

alter table data_asset_column_artifact_associations add constraint fk_data_asset_column_artifact_associations_governance_artifacts_16 foreign key (associated_artifact_id) references governance_artifacts(artifact_id) on
delete
	cascade on
	update
	no action

alter table data_asset_column_artifact_associations add constraint fk_data_asset_column_artifact_associations_catalog_data_asset_columns_14 foreign key (container_id,
asset_id,
name) references catalog_data_asset_columns(container_id,
asset_id,
name) on
delete
	cascade on
	update
	no action

column_to_asset_relations table

This table contains information about the data asset columns that are associated to assets.

This table has the following columns:

  • end1_container_id - Specifies the identifier of the container of the end1 asset.
  • end2_container_id - Specifies the identifier of the container of the end2 asset.
  • end1_asset_id - Specifies the identifier of the end1 asset.
  • end2_asset_id - Specifies the identifier of the end2 asset.
  • end1_column_name - Specifies the identifier of the end1 data asset column.
  • end1_relationship_type - Specifies the relationship type as identified from the end1 asset.
  • end2_relationship_type - Specifies the relationship type as identified from the end2 asset.
PostgreSQL

CREATE TABLE statement:

CREATE TABLE column_to_asset_relations (
	end1_asset_id varchar(128) NOT NULL,
	end1_container_id varchar(36) NOT NULL,
	end1_column_name varchar(256) NOT NULL,
	end2_container_id varchar(36) NOT NULL,
	end2_asset_id varchar(128) NOT NULL,
	end1_relationship_type varchar(256) NOT NULL,
	end2_relationship_type varchar(256) NOT NULL,
	tech_start timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
	tech_end timestamp(6) NOT NULL DEFAULT to_timestamp('9999-12-30'::text, 'YYYY-MM-DD'::text),
	ts_id timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT column_to_asset_relations_pkey PRIMARY KEY (end1_asset_id, end1_container_id, end1_column_name, end2_container_id, end2_asset_id, end1_relationship_type)
);

ALTER TABLE statements:

ALTER TABLE
  column_to_asset_relations
ADD
  CONSTRAINT fk_column_to_asset_relations_container_assets_2 FOREIGN KEY (
    end2_container_id, end2_asset_id
  ) REFERENCES container_assets(container_id, asset_id) ON DELETE CASCADE;

ALTER TABLE
  column_to_asset_relations
ADD
  CONSTRAINT fk_column_to_asset_relations_container_data_asset_columns_1 FOREIGN KEY (
    end1_container_id, end1_asset_id,
    end1_column_name
  ) REFERENCES container_data_asset_columns(container_id, asset_id, "name") ON DELETE CASCADE;
Db2

CREATE TABLE statement:

CREATE TABLE "COLUMN_TO_ASSET_RELATIONS"  (
		  "END1_ASSET_ID" VARCHAR(128 OCTETS) NOT NULL ,
		  "END1_CONTAINER_ID" VARCHAR(36 OCTETS) NOT NULL ,
		  "END1_COLUMN_NAME" VARCHAR(256 OCTETS) NOT NULL ,
		  "END2_CONTAINER_ID" VARCHAR(36 OCTETS) NOT NULL ,
		  "END2_ASSET_ID" VARCHAR(128 OCTETS) NOT NULL ,
		  "END1_RELATIONSHIP_TYPE" VARCHAR(256 OCTETS) NOT NULL ,
		  "END2_RELATIONSHIP_TYPE" VARCHAR(256 OCTETS) NOT NULL ,
		  "TECH_START" TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW BEGIN ,
		  "TECH_END" TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW END ,
		  "TS_ID" TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID ,
		 PERIOD SYSTEM_TIME ("TECH_START","TECH_END") )
		 IN "USERSPACE1"
		 ORGANIZE BY ROW;

ALTER TABLE statements:

ALTER TABLE "COLUMN_TO_ASSET_RELATIONS"
	ADD PRIMARY KEY
		("END1_ASSET_ID",
		 "END1_CONTAINER_ID",
		 "END1_COLUMN_NAME",
		 "END2_CONTAINER_ID",
		 "END2_ASSET_ID",
		 "END1_RELATIONSHIP_TYPE")
	ENFORCED;

ALTER TABLE "COLUMN_TO_ASSET_RELATIONS"
	ADD CONSTRAINT "FK_COLUMN_TO_ASSET_RELATIONS_CONTAINER_ASSETS_2" FOREIGN KEY
		("END2_CONTAINER_ID",
		 "END2_ASSET_ID")
	REFERENCES "CONTAINER_ASSETS"
		("CONTAINER_ID",
		 "ASSET_ID")
	ON DELETE CASCADE
	ON UPDATE NO ACTION
	ENFORCED
	ENABLE QUERY OPTIMIZATION;

ALTER TABLE "COLUMN_TO_ASSET_RELATIONS"
	ADD CONSTRAINT "FK_COLUMN_TO_ASSET_RELATIONS_CONTAINER_DATA_ASSET_COLUMNS_1" FOREIGN KEY
		("END1_CONTAINER_ID",
		 "END1_ASSET_ID",
		 "END1_COLUMN_NAME")
	REFERENCES "CONTAINER_DATA_ASSET_COLUMNS"
		("CONTAINER_ID",
		 "ASSET_ID",
		 "NAME")
	ON DELETE CASCADE
	ON UPDATE NO ACTION
	ENFORCED
	ENABLE QUERY OPTIMIZATION;

column_to_column_relations table

This table contains information about the data asset columns that are associated to other columns.

This table has the following columns:

  • end1_container_id - Specifies the identifier of the container of the end1 asset.
  • end2_container_id - Specifies the identifier of the container of the end2 asset.
  • end1_asset_id - Specifies the identifier of the end1 asset.
  • end2_asset_id - Specifies the identifier of the end2 asset.
  • end1_column_name - Specifies the identifier of the end1 data asset column.
  • end2_column_name - Specifies the identifier of the end2 data asset column.
  • end1_relationship_type - Specifies the relationship type as identified from the end1 asset.
  • end2_relationship_type - Specifies the relationship type as identified from the end2 asset.
PostgreSQL

CREATE TABLE statement:

CREATE TABLE column_to_column_relations (
	end1_asset_id varchar(128) NOT NULL,
	end1_container_id varchar(36) NOT NULL,
	end1_column_name varchar(256) NOT NULL,
	end2_container_id varchar(36) NOT NULL,
	end2_asset_id varchar(128) NOT NULL,
	end2_column_name varchar(256) NOT NULL,
	end1_relationship_type varchar(256) NOT NULL,
	end2_relationship_type varchar(256) NOT NULL,
	tech_start timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
	tech_end timestamp(6) NOT NULL DEFAULT to_timestamp('9999-12-30'::text, 'YYYY-MM-DD'::text),
	ts_id timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT column_to_column_relations_pkey PRIMARY KEY (end1_asset_id, end1_container_id, end1_column_name, end2_container_id, end2_asset_id, end2_column_name, end1_relationship_type)
);

ALTER TABLE statements:

ALTER TABLE
  column_to_column_relations
ADD
  CONSTRAINT fk_column_to_column_relations_container_data_asset_columns_5 FOREIGN KEY (
    end1_container_id, end1_asset_id,
    end1_column_name
  ) REFERENCES container_data_asset_columns(container_id, asset_id, "name") ON DELETE CASCADE;

ALTER TABLE
  column_to_column_relations
ADD
  CONSTRAINT fk_column_to_column_relations_container_data_asset_columns_6 FOREIGN KEY (
    end2_container_id, end2_asset_id,
    end2_column_name
  ) REFERENCES container_data_asset_columns(container_id, asset_id, "name") ON DELETE CASCADE;
Db2

CREATE TABLE statement:

CREATE TABLE "COLUMN_TO_COLUMN_RELATIONS"  (
		  "END1_ASSET_ID" VARCHAR(128 OCTETS) NOT NULL ,
		  "END1_CONTAINER_ID" VARCHAR(36 OCTETS) NOT NULL ,
		  "END1_COLUMN_NAME" VARCHAR(256 OCTETS) NOT NULL ,
		  "END2_CONTAINER_ID" VARCHAR(36 OCTETS) NOT NULL ,
		  "END2_ASSET_ID" VARCHAR(128 OCTETS) NOT NULL ,
		  "END2_COLUMN_NAME" VARCHAR(256 OCTETS) NOT NULL ,
		  "END1_RELATIONSHIP_TYPE" VARCHAR(256 OCTETS) NOT NULL ,
		  "END2_RELATIONSHIP_TYPE" VARCHAR(256 OCTETS) NOT NULL ,
		  "TECH_START" TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW BEGIN ,
		  "TECH_END" TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW END ,
		  "TS_ID" TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID ,
		 PERIOD SYSTEM_TIME ("TECH_START","TECH_END") )
		 IN "USERSPACE1"
		 ORGANIZE BY ROW;

ALTER TABLE statements:

ALTER TABLE "COLUMN_TO_COLUMN_RELATIONS"
	ADD PRIMARY KEY
		("END1_ASSET_ID",
		 "END1_CONTAINER_ID",
		 "END1_COLUMN_NAME",
		 "END2_CONTAINER_ID",
		 "END2_ASSET_ID",
		 "END2_COLUMN_NAME",
		 "END1_RELATIONSHIP_TYPE")
	ENFORCED;

ALTER TABLE "COLUMN_TO_COLUMN_RELATIONS"
	ADD CONSTRAINT "FK_COLUMN_TO_COLUMN_RELATIONS_CONTAINER_DATA_ASSET_COLUMNS_5" FOREIGN KEY
		("END1_CONTAINER_ID",
		 "END1_ASSET_ID",
		 "END1_COLUMN_NAME")
	REFERENCES "CONTAINER_DATA_ASSET_COLUMNS"
		("CONTAINER_ID",
		 "ASSET_ID",
		 "NAME")
	ON DELETE CASCADE
	ON UPDATE NO ACTION
	ENFORCED
	ENABLE QUERY OPTIMIZATION;

ALTER TABLE "COLUMN_TO_COLUMN_RELATIONS"
	ADD CONSTRAINT "FK_COLUMN_TO_COLUMN_RELATIONS_CONTAINER_DATA_ASSET_COLUMNS_6" FOREIGN KEY
		("END2_CONTAINER_ID",
		 "END2_ASSET_ID",
		 "END2_COLUMN_NAME")
	REFERENCES "CONTAINER_DATA_ASSET_COLUMNS"
		("CONTAINER_ID",
		 "ASSET_ID",
		 "NAME")
	ON DELETE CASCADE
	ON UPDATE NO ACTION
	ENFORCED
	ENABLE QUERY OPTIMIZATION;

column_to_artifact_relations

This table contains information about the data asset columns that are associated to artifacts.

This table has the following columns:

  • end1_container_id - Specifies the identifier of the container of the end1 asset.
  • end2_artifact_id - Specifies the target artifact id of the asset artifact relationships.
  • end1_asset_id - Specifies the identifier of the end1 asset.
  • end1_column_name - Specifies the identifier of the end1 data asset column.
  • end2_artifact_type - Specifies the target artifact type of the asset artifact relationships.
  • end1_relationship_type - Specifies the relationship type as identified from the end1 asset.
  • end2_relationship_type - Specifies the relationship type as identified from the end2 asset.
PostgreSQL

CREATE TABLE statement:

CREATE TABLE column_to_artifact_relations (
	end1_asset_id varchar(128) NOT NULL,
	end1_container_id varchar(36) NOT NULL,
	end1_column_name varchar(256) NOT NULL,
	end2_artifact_id varchar(128) NOT NULL,
	end2_artifact_type varchar(128) NOT NULL,
	end1_relationship_type varchar(256) NOT NULL,
	end2_relationship_type varchar(256) NOT NULL,
	tech_start timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
	tech_end timestamp(6) NOT NULL DEFAULT to_timestamp('9999-12-30'::text, 'YYYY-MM-DD'::text),
	ts_id timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT column_to_artifact_relations_pkey PRIMARY KEY (end1_asset_id, end1_container_id, end1_column_name, end2_artifact_type, end2_artifact_id, end1_relationship_type)
);

ALTER TABLE statements:

ALTER TABLE
  column_to_artifact_relations
ADD
  CONSTRAINT fk_column_to_artifact_relations_container_data_asset_columns_3 FOREIGN KEY (
    end1_container_id, end1_asset_id,
    end1_column_name
  ) REFERENCES container_data_asset_columns(container_id, asset_id, "name") ON DELETE CASCADE;

ALTER TABLE
  column_to_artifact_relations
ADD
  CONSTRAINT fk_column_to_artifact_relations_governance_artifacts_4 FOREIGN KEY (end2_artifact_id) REFERENCES governance_artifacts(artifact_id) ON DELETE CASCADE;
Db2

CREATE TABLE statement:

CREATE TABLE "COLUMN_TO_ARTIFACT_RELATIONS"  (
		  "END1_ASSET_ID" VARCHAR(128 OCTETS) NOT NULL ,
		  "END1_CONTAINER_ID" VARCHAR(36 OCTETS) NOT NULL ,
		  "END1_COLUMN_NAME" VARCHAR(256 OCTETS) NOT NULL ,
		  "END2_ARTIFACT_ID" VARCHAR(128 OCTETS) NOT NULL ,
		  "END2_ARTIFACT_TYPE" VARCHAR(128 OCTETS) NOT NULL ,
		  "END1_RELATIONSHIP_TYPE" VARCHAR(256 OCTETS) NOT NULL ,
		  "END2_RELATIONSHIP_TYPE" VARCHAR(256 OCTETS) NOT NULL ,
		  "TECH_START" TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW BEGIN ,
		  "TECH_END" TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW END ,
		  "TS_ID" TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID ,
		 PERIOD SYSTEM_TIME ("TECH_START","TECH_END") )
		 IN "USERSPACE1"
		 ORGANIZE BY ROW;

ALTER TABLE statements:

ALTER TABLE "COLUMN_TO_ARTIFACT_RELATIONS"
	ADD PRIMARY KEY
		("END1_ASSET_ID",
		 "END1_CONTAINER_ID",
		 "END1_COLUMN_NAME",
		 "END2_ARTIFACT_TYPE",
		 "END2_ARTIFACT_ID",
		 "END1_RELATIONSHIP_TYPE")
	ENFORCED;

ALTER TABLE "COLUMN_TO_ARTIFACT_RELATIONS"
	ADD CONSTRAINT "FK_COLUMN_TO_ARTIFACT_RELATIONS_CONTAINER_DATA_ASSET_COLUMNS_3" FOREIGN KEY
		("END1_CONTAINER_ID",
		 "END1_ASSET_ID",
		 "END1_COLUMN_NAME")
	REFERENCES "CONTAINER_DATA_ASSET_COLUMNS"
		("CONTAINER_ID",
		 "ASSET_ID",
		 "NAME")
	ON DELETE CASCADE
	ON UPDATE NO ACTION
	ENFORCED
	ENABLE QUERY OPTIMIZATION;

ALTER TABLE "COLUMN_TO_ARTIFACT_RELATIONS"
	ADD CONSTRAINT "FK_COLUMN_TO_ARTIFACT_RELATIONS_GOVERNANCE_ARTIFACTS_4" FOREIGN KEY
		("END2_ARTIFACT_ID")
	REFERENCES "GOVERNANCE_ARTIFACTS"
		("ARTIFACT_ID")
	ON DELETE CASCADE
	ON UPDATE NO ACTION
	ENFORCED
	ENABLE QUERY OPTIMIZATION;

data_asset_pk_fk_columns

This table contains information about the associations between data asset column and any asset.

This table has the following columns:

  • constraint_id - The identifier of the constraint defined for a data asset table.
  • column_index - The identifier of the column index for a data asset.
  • container_id - The identifier of the catalog or project in which asset exists.
  • column_name - Specifies the identifier of the data asset column.
  • asset_id - The identifier of the asset.
  • constraint_type - Specifies the contrainst type which is either PRIMARY_KEY or FOREIGN_KEY".
  • constraint_name - Specifies the identifier of the source/end1 data asset column.
PostgreSQL

CREATE TABLE statement:

create table data_asset_pk_fk_columns(
  constraint_id varchar(128) not null,
  column_index integer not null,
  container_id varchar(36) not null,
  column_name varchar(256) not null,
  asset_id varchar(128) not null,
  constraint_type varchar(32) not null,
  constraint_name varchar(256) not null,
  tech_start TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
  tech_end TIMESTAMP(6) not null default to_timestamp('9999-12-30', 'YYYY-MM-DD'),
  ts_id TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
  primary key(
    constraint_id, container_id, column_index
  )
)

ALTER TABLE statements:

alter table
  data_asset_pk_fk_columns
add
  constraint fk_data_asset_pk_fk_columns_container_data_asset_columns_1 foreign key (
    container_id, asset_id, column_name
  ) references container_data_asset_columns(container_id, asset_id, name) on delete cascade on
update
  no action
alter table
  data_asset_pk_fk_columns
add
  constraint fk_data_asset_pk_fk_columns_container_assets_2 foreign key (container_id, constraint_id) references container_assets(container_id, asset_id) on delete cascade on
update
  no action
Db2

CREATE TABLE statement:

CREATE TABLE "DATA_ASSET_PK_FK_COLUMNS"  (
		  "CONSTRAINT_ID" VARCHAR(128 OCTETS) NOT NULL ,
		  "COLUMN_INDEX" INTEGER NOT NULL ,
		  "CONTAINER_ID" VARCHAR(36 OCTETS) NOT NULL ,
		  "COLUMN_NAME" VARCHAR(256 OCTETS) NOT NULL ,
		  "ASSET_ID" VARCHAR(128 OCTETS) NOT NULL ,
		  "CONSTRAINT_TYPE" VARCHAR(32 OCTETS) NOT NULL ,
		  "CONSTRAINT_NAME" VARCHAR(256 OCTETS) NOT NULL ,
		  "TECH_START" TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW BEGIN ,
		  "TECH_END" TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW END ,
		  "TS_ID" TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID ,
		 PERIOD SYSTEM_TIME ("TECH_START","TECH_END") )
		 IN "USERSPACE1"
		 ORGANIZE BY ROW;

ALTER TABLE statements:

ALTER TABLE "DATA_ASSET_PK_FK_COLUMNS"
	ADD PRIMARY KEY
		("CONSTRAINT_ID",
		 "CONTAINER_ID",
		 "COLUMN_INDEX")
	ENFORCED;

ALTER TABLE "DATA_ASSET_PK_FK_COLUMNS"
	ADD CONSTRAINT "FK_DATA_ASSET_PK_FK_COLUMNS_CONTAINER_ASSETS_2" FOREIGN KEY
		("CONTAINER_ID",
		 "CONSTRAINT_ID")
	REFERENCES "CONTAINER_ASSETS"
		("CONTAINER_ID",
		 "ASSET_ID")
	ON DELETE CASCADE
	ON UPDATE NO ACTION
	ENFORCED
	ENABLE QUERY OPTIMIZATION;

ALTER TABLE "DATA_ASSET_PK_FK_COLUMNS"
	ADD CONSTRAINT "FK_DATA_ASSET_PK_FK_COLUMNS_CONTAINER_DATA_ASSET_COLUMNS_1" FOREIGN KEY
		("CONTAINER_ID",
		 "ASSET_ID",
		 "COLUMN_NAME")
	REFERENCES "CONTAINER_DATA_ASSET_COLUMNS"
		("CONTAINER_ID",
		 "ASSET_ID",
		 "NAME")
	ON DELETE CASCADE
	ON UPDATE NO ACTION
	ENFORCED
	ENABLE QUERY OPTIMIZATION;

data_asset_fk_ref_columns

This table contains information about the associations between data asset reference column and any asset.

This table has the following columns:

  • constraint_id - The identifier of the constraint defined for a data asset table.
  • column_index - The identifier of the column index for a data asset.
  • container_id - The identifier of the catalog or project in which asset exists.
  • column_name - Specifies the identifier of the data asset column.
  • asset_id - The identifier of the asset.
PostgreSQL

CREATE TABLE statement:

create table data_asset_fk_ref_columns(
  constraint_id varchar(128) not null,
  column_index integer not null,
  container_id varchar(256) not null,
  column_name varchar(36) not null,
  asset_id varchar(128) not null,
  tech_start TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
  tech_end TIMESTAMP(6) not null default to_timestamp('9999-12-30', 'YYYY-MM-DD'),
  ts_id TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
  primary key(
    constraint_id, container_id, column_index
  )
)

ALTER TABLE statements:

alter table
  data_asset_fk_ref_columns
add
  constraint fk_data_asset_fk_ref_columns_data_asset_pk_fk_columns_3 foreign key (
    container_id, constraint_id, column_index
  ) references data_asset_pk_fk_columns(
    container_id, constraint_id, column_index
  ) on delete cascade on
update
  no action

alter table
  data_asset_fk_ref_columns
add
  constraint fk_data_asset_fk_ref_columns_container_data_asset_columns_4 foreign key (
    container_id, asset_id, column_name
  ) references container_data_asset_columns(container_id, asset_id, name) on delete cascade on
update
  no action
Db2

CREATE TABLE statement:

CREATE TABLE "DATA_ASSET_FK_REF_COLUMNS"  (
		  "CONSTRAINT_ID" VARCHAR(128 OCTETS) NOT NULL ,
		  "COLUMN_INDEX" INTEGER NOT NULL ,
		  "CONTAINER_ID" VARCHAR(256 OCTETS) NOT NULL ,
		  "COLUMN_NAME" VARCHAR(36 OCTETS) NOT NULL ,
		  "ASSET_ID" VARCHAR(128 OCTETS) NOT NULL ,
		  "TECH_START" TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW BEGIN ,
		  "TECH_END" TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW END ,
		  "TS_ID" TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID ,
		 PERIOD SYSTEM_TIME ("TECH_START","TECH_END") )
		 IN "USERSPACE1"
		 ORGANIZE BY ROW;

ALTER TABLE statements:

ALTER TABLE "DATA_ASSET_FK_REF_COLUMNS"
	ADD PRIMARY KEY
		("CONSTRAINT_ID",
		 "CONTAINER_ID",
		 "COLUMN_INDEX")
	ENFORCED;

ALTER TABLE "DATA_ASSET_FK_REF_COLUMNS"
	ADD CONSTRAINT "FK_DATA_ASSET_FK_REF_COLUMNS_CONTAINER_DATA_ASSET_COLUMNS_4" FOREIGN KEY
		("CONTAINER_ID",
		 "ASSET_ID",
		 "COLUMN_NAME")
	REFERENCES "CONTAINER_DATA_ASSET_COLUMNS"
		("CONTAINER_ID",
		 "ASSET_ID",
		 "NAME")
	ON DELETE CASCADE
	ON UPDATE NO ACTION
	ENFORCED
	ENABLE QUERY OPTIMIZATION;

ALTER TABLE "DATA_ASSET_FK_REF_COLUMNS"
	ADD CONSTRAINT "FK_DATA_ASSET_FK_REF_COLUMNS_DATA_ASSET_PK_FK_COLUMNS_3" FOREIGN KEY
		("CONSTRAINT_ID",
		 "CONTAINER_ID",
		 "COLUMN_INDEX")
	REFERENCES "DATA_ASSET_PK_FK_COLUMNS"
		("CONSTRAINT_ID",
		 "CONTAINER_ID",
		 "COLUMN_INDEX")
	ON DELETE CASCADE
	ON UPDATE NO ACTION
	ENFORCED
	ENABLE QUERY OPTIMIZATION;

Learn more

Parent topic: Reporting tables

Generative AI search and answer
These answers are generated by a large language model in watsonx.ai based on content from the product documentation. Learn more