Metadata imports and enrichments reporting tables (IBM Knowledge Catalog)

Check the Postgres, Db2 , and Oracle statements for the tables that are related to metadata imports and enrichments.

Subject area	Table name	Description
Metadata imports and enrichments	metadata_imports	Metadata import information.
Metadata imports and enrichments	metadata_import_executions	The scheduled time of the metadata import job.
Metadata imports and enrichments	metadata_enrichments	Metadata enrichments information.
Metadata imports and enrichments	job_asset	This table contains information about job assets.
Metadata imports and enrichments	job_run_asset	This table contains information about executions of jobs.

metadata_imports table

This table contains information about the metadata import.

This table has the following columns:

metadata_import_id - Identifier for the metadata import.
container_id - The identifier of the project.
metadata_import_name - The name of the metadata import.
target_container_id - The identifier of the target project.
connection_id - The connection identifier on which an import is created.

Postgres

CREATE TABLE statement:

create table metadata_imports(metadata_import_id varchar(128) not null,
container_id varchar(128) not null,
metadata_import_name varchar(256) not null,
target_container_id varchar(128),
connection_id varchar(128),
metadata_enrichment_id 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,
metadata_import_id))

ALTER TABLE statement:

alter table metadata_imports add constraint fk_metadata_imports_container_assets_2 foreign key (container_id,
metadata_import_id) references container_assets(container_id,
asset_id) on
delete
	cascade on
	update
	no action

Db2

CREATE TABLE statement:

create table metadata_imports(metadata_import_id varchar(128) not null,
container_id varchar(128) not null,
metadata_import_name varchar(256) not null,
target_container_id varchar(128),
connection_id varchar(128),
metadata_enrichment_id varchar(128),
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,
metadata_import_id),
period SYSTEM_TIME (tech_start,
tech_end) ) ORGANIZE by row

ALTER TABLE statement:

alter table metadata_imports add constraint fk_metadata_imports_container_assets_2 foreign key (container_id,
metadata_import_id) references container_assets(container_id,
asset_id) on
delete
	cascade on
	update
	no action

Oracle

CREATE TABLE statement:

CREATE TABLE metadata_imports(metadata_import_id varchar(128) NOT NULL,
	container_id varchar(128) NOT NULL,
	metadata_import_name varchar(256) NOT NULL,
	target_container_id varchar(128),
	connection_id varchar(128),
	metadata_enrichment_id varchar(128),
	tech_start TIMESTAMP(6) DEFAULT SYSTIMESTAMP NOT NULL,
	tech_end TIMESTAMP(6) DEFAULT to_timestamp('9999-12-30', 'YYYY-MM-DD') NOT NULL,
	ts_id TIMESTAMP(6) DEFAULT SYSTIMESTAMP NOT NULL,
	PRIMARY KEY(container_id,
	metadata_import_id))

ALTER TABLE statement:

ALTER TABLE metadata_imports ADD CONSTRAINT fk_metadata_imports_container_assets_2 FOREIGN KEY (container_id,
	metadata_import_id) REFERENCES container_assets(container_id,
	asset_id) ON
	DELETE
	CASCADE

metadata_import_executions table

This table contains information about the Metadata Import job.

This table has the following columns:

metadata_import_id - Identifier for the metadata import.
container_id - The identifier of the project.
job_run_id - The identifier of the job run.
invoked_by - The ID of the person who started it.
is_scheduled_run - The scheduled time of the job for metadata import job.
start_time - The start time of the job for metadata import job.
end_time - The end time of the job for metadata import job.
state - The state of the metadata import job.
count_discovered - The number of assets that is discovered.
count_discovered_with_children - The number of assets with children that is discovered.
count_submitted - The number of assets that is discovered.
count_succeeded - The number of assets that is imported successfully.
count_succedded_with_children - The number of assets with children that is imported successfully.
count_new_assets - The number of new assets.
count_updated_assets - The number of updated assets.
count_deleted_assets - The number of deleted assets.

Postgres

CREATE TABLE statement:

create table metadata_import_executions(metadata_import_id varchar(128) not null,
container_id varchar(36) not null,
job_run_id varchar(128) not null,
invoked_by varchar(128) not null,
is_scheduled_run boolean not null,
start_time timestamp(6) not null,
end_time timestamp(6),
state varchar(32) not null,
count_discovered bigint default 0 not null,
count_discovered_with_children bigint default 0 not null,
count_submitted bigint default 0 not null,
count_succeeded bigint default 0 not null,
count_succedded_with_children bigint default 0 not null,
count_new_assets bigint default 0 not null,
count_updated_assets bigint default 0 not null,
count_deleted_assets bigint default 0 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,
metadata_import_id,
job_run_id))

ALTER TABLE statements:

alter table metadata_import_executions add constraint fk_metadata_import_executions_metadata_imports_3 foreign key (container_id,
metadata_import_id) references metadata_imports(container_id,
metadata_import_id) on
delete
	cascade on
	update
	no action

alter table metadata_import_executions add constraint fk_metadata_import_executions_container_assets_4 foreign key (container_id,
job_run_id) references container_assets(container_id,
asset_id) on
delete
	cascade on
	update
	no action

Db2

CREATE TABLE statement:

create table metadata_import_executions(metadata_import_id varchar(128) not null,
container_id varchar(36) not null,
job_run_id varchar(128) not null,
invoked_by varchar(128) not null,
is_scheduled_run boolean not null,
start_time timestamp(12) not null,
end_time timestamp(12),
state varchar(32) not null,
count_discovered bigint default 0 not null,
count_discovered_with_children bigint default 0 not null,
count_submitted bigint default 0 not null,
count_succeeded bigint default 0 not null,
count_succedded_with_children bigint default 0 not null,
count_new_assets bigint default 0 not null,
count_updated_assets bigint default 0 not null,
count_deleted_assets bigint default 0 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,
metadata_import_id,
job_run_id),
period SYSTEM_TIME (tech_start,
tech_end) ) ORGANIZE by row

ALTER TABLE statements:

alter table metadata_import_executions add constraint fk_metadata_import_executions_metadata_imports_3 foreign key (container_id,
metadata_import_id) references metadata_imports(container_id,
metadata_import_id) on
delete
	cascade on
	update
	no action

alter table metadata_import_executions add constraint fk_metadata_import_executions_container_assets_4 foreign key (container_id,
job_run_id) references container_assets(container_id,
asset_id) on
delete
	cascade on
	update
	no action

Oracle

CREATE TABLE statement:

CREATE TABLE metadata_import_executions(metadata_import_id varchar(128) NOT NULL,
	container_id varchar(36) NOT NULL,
	job_run_id varchar(128) NOT NULL,
	invoked_by varchar(128) NOT NULL,
	is_scheduled_run decimal(1) NOT NULL,
	start_time timestamp(6) NOT NULL,
	end_time timestamp(6),
	state varchar(32) NOT NULL,
	count_discovered NUMBER(19) DEFAULT 0 NOT NULL,
	count_discovered_with_children NUMBER(19) DEFAULT 0 NOT NULL,
	count_submitted NUMBER(19) DEFAULT 0 NOT NULL,
	count_succeeded NUMBER(19) DEFAULT 0 NOT NULL,
	count_succedded_with_children NUMBER(19) DEFAULT 0 NOT NULL,
	count_new_assets NUMBER(19) DEFAULT 0 NOT NULL,
	count_updated_assets NUMBER(19) DEFAULT 0 NOT NULL,
	count_deleted_assets NUMBER(19) DEFAULT 0 NOT NULL,
	tech_start TIMESTAMP(6) DEFAULT SYSTIMESTAMP NOT NULL,
	tech_end TIMESTAMP(6) DEFAULT to_timestamp('9999-12-30', 'YYYY-MM-DD') NOT NULL,
	ts_id TIMESTAMP(6) DEFAULT SYSTIMESTAMP NOT NULL,
	PRIMARY KEY(container_id,
	metadata_import_id,
	job_run_id))

ALTER TABLE statements:

ALTER TABLE metadata_import_executions ADD CONSTRAINT fk_metadata_import_executions_metadata_imports_3 FOREIGN KEY (container_id,
	metadata_import_id) REFERENCES metadata_imports(container_id,
	metadata_import_id) ON
	DELETE
	CASCADE

ALTER TABLE metadata_import_executions ADD CONSTRAINT fk_metadata_import_executions_container_assets_4 FOREIGN KEY (container_id,
	job_run_id) REFERENCES container_assets(container_id,
	asset_id) ON
	DELETE
	CASCADE

metadata_enrichments table

This table contains information about the metadata enrichments.

This table has the following columns:

metadata_enrichment_id - Identifier for the metadata enrichment.
container_id - The identifier of the project.
metadata_enrichment_name - The name of the metadata enrichment.

Postgres

CREATE TABLE statement:

create table metadata_enrichments(metadata_enrichment_id varchar(128) not null,
container_id varchar(36) not null,
metadata_enrichment_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,
metadata_enrichment_id))

ALTER TABLE statement:

alter table metadata_enrichments add constraint fk_metadata_enrichments_container_assets_1 foreign key (container_id,
metadata_enrichment_id) references container_assets(container_id,
asset_id) on
delete
	cascade on
	update
	no action

Db2

CREATE TABLE statement:

create table metadata_enrichments(metadata_enrichment_id varchar(128) not null,
container_id varchar(36) not null,
metadata_enrichment_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,
metadata_enrichment_id),
period SYSTEM_TIME (tech_start,
tech_end) ) ORGANIZE by row

ALTER TABLE statement:

alter table metadata_enrichments add constraint fk_metadata_enrichments_container_assets_1 foreign key (container_id,
metadata_enrichment_id) references container_assets(container_id,
asset_id) on
delete
	cascade on
	update
	no action

Oracle

CREATE TABLE statement:

CREATE TABLE metadata_enrichments(metadata_enrichment_id varchar(128) NOT NULL,
	container_id varchar(36) NOT NULL,
	metadata_enrichment_name varchar(256) NOT NULL,
	tech_start TIMESTAMP(6) DEFAULT SYSTIMESTAMP NOT NULL,
	tech_end TIMESTAMP(6) DEFAULT to_timestamp('9999-12-30', 'YYYY-MM-DD') NOT NULL,
	ts_id TIMESTAMP(6) DEFAULT SYSTIMESTAMP NOT NULL,
	PRIMARY KEY(container_id,
	metadata_enrichment_id))

ALTER TABLE statement:

ALTER TABLE metadata_enrichments ADD CONSTRAINT fk_metadata_enrichments_container_assets_1 FOREIGN KEY (container_id,
	metadata_enrichment_id) REFERENCES container_assets(container_id,
	asset_id) ON
	DELETE
	CASCADE

job_asset table

This table contains information about job assets.

This table has the following columns:

container_id - The identifier of the container (catalog or project) in which the asset exists.
asset_id - The identifier of the data asset.
version - Indicates the version of the job asset.
job_name - The name of the job asset.
asset_ref_type - Specifies the type of asset for which the job is created.
asset_ref - Identifier for the actual asset.
schedule - Defines when the job runs (e.g. 0 0 * * *).
schedule_info - Additional metadata about the schedule, such as start on, end on.
last_run_status_timestamp - Timestamp of the job's most recent execution.
future_scheduled_runs - A list or array of the upcoming scheduled execution times for the job.
enable_notifications - Boolean flag to determine if notifications need to be enabled for changes.
project_name - The name of the project.
schedule_id - Unique identifier of the schedule itself, allowing multiple jobs to reference the same scheduling configuration.
schedule_creator_id - Identifier of the user that created the schedule.

Postgres

CREATE TABLE statement:

CREATE TABLE "globalschema".job_asset(
  container_id varchar(36) NOT NULL,
  asset_id varchar(128) NOT NULL,
  version integer NOT NULL,
  job_name varchar(256) NOT NULL,
  asset_ref_type varchar(256),
  asset_ref varchar(36),
  schedule varchar(36),
  schedule_info jsonb,
  last_run_status_timestamp timestamp(6),
  future_scheduled_runs jsonb,
  enable_notifications decimal(1) CHECK (
    enable_notifications in (0, 1)
  ) NOT NULL,
  project_name varchar(256) NOT NULL,
  schedule_id varchar(36),
  schedule_creator_id 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)
)

Db2

CREATE TABLE statement:

CREATE TABLE "globalschema".job_asset(
  container_id varchar(36) NOT NULL,
  asset_id varchar(128) NOT NULL,
  version integer NOT NULL,
  job_name varchar(256) NOT NULL,
  asset_ref_type varchar(256),
  asset_ref varchar(36),
  schedule varchar(36),
  schedule_info clob,
  last_run_status_timestamp timestamp(12),
  future_scheduled_runs clob,
  enable_notifications decimal(1) CHECK (
    enable_notifications in (0, 1)
  ) NOT NULL,
  project_name varchar(256) NOT NULL,
  schedule_id varchar(36),
  schedule_creator_id varchar(128),
  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),
  PERIOD SYSTEM_TIME (tech_start, tech_end)
)

Oracle

CREATE TABLE statement:

CREATE TABLE "globalschema".job_asset(
  container_id varchar(36) NOT NULL,
  asset_id varchar(128) NOT NULL,
  version integer NOT NULL,
  job_name varchar(256) NOT NULL,
  asset_ref_type varchar(256),
  asset_ref varchar(36),
  schedule varchar(36),
  schedule_info clob,
  last_run_status_timestamp timestamp(6),
  future_scheduled_runs clob,
  enable_notifications decimal(1) NOT NULL CHECK (
    enable_notifications in (0, 1)
  ),
  project_name varchar(256) NOT NULL,
  schedule_id varchar(36),
  schedule_creator_id varchar(128),
  tech_start TIMESTAMP(6) DEFAULT SYSTIMESTAMP NOT NULL,
  tech_end TIMESTAMP(6) DEFAULT to_timestamp('9999-12-30', 'YYYY-MM-DD') NOT NULL,
  ts_id TIMESTAMP(6) DEFAULT SYSTIMESTAMP NOT NULL,
  PRIMARY KEY(container_id, asset_id)
)

MS SQL Server

CREATE TABLE statement:

CREATE TABLE "globalschema".job_asset(
  container_id varchar(36) NOT NULL,
  asset_id varchar(128) NOT NULL,
  version integer NOT NULL,
  job_name varchar(256) NOT NULL,
  asset_ref_type varchar(256),
  asset_ref varchar(36),
  schedule varchar(36),
  schedule_info varchar(MAX),
  last_run_status_timestamp DATETIME2,
  future_scheduled_runs varchar(MAX),
  enable_notifications decimal(1) CHECK (
    enable_notifications in (0, 1)
  ) NOT NULL,
  project_name varchar(256) NOT NULL,
  schedule_id varchar(36),
  schedule_creator_id varchar(128),
  tech_start DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
  tech_end DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
  ts_id DATETIME2 DEFAULT CURRENT_TIMESTAMP NOT NULL,
  CONSTRAINT PK_job_asset_globalschema PRIMARY KEY(container_id, asset_id),
  PERIOD FOR SYSTEM_TIME (tech_start, tech_end)
) WITH (
  SYSTEM_VERSIONING = ON (
    HISTORY_TABLE = "globalschema".hist_job_asset
  )
)

job_run_asset table

This table contains information about executions of jobs.

This table has the following columns:

container_id - The identifier of the container (catalog or project) in which the asset exists.
asset_id - The identifier of the data asset.
version - Indicates the version of the job asset.
job_name - The name of the job asset.
job_ref - A reference ID linking to job.
job_type - Specifies the type of asset for which the job run is created.
state - The status of the job run asset (E.g Running, Completed, FAILED).
isScheduledRun - Indicates whether the run was triggered by a schedule or manually.
project_name - The name of the project.
last_state_change_timestamp - Timestamp of the most recent state update.
duration - Time taken for the job run to complete.

Postgres

CREATE TABLE statement:

CREATE TABLE "globalschema".job_run_asset(
  container_id varchar(36) NOT NULL,
  asset_id varchar(128) NOT NULL,
  job_ref varchar(36) NOT NULL,
  version integer NOT NULL,
  job_name varchar(256) NOT NULL,
  job_type varchar(256) NOT NULL,
  state varchar(32),
  isScheduledRun decimal(1) CHECK (
    isScheduledRun in (0, 1)
  ),
  project_name varchar(256) NOT NULL,
  last_state_change_timestamp timestamp(6) NOT NULL,
  duration integer 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, job_ref)
)

Db2

CREATE TABLE statement:

CREATE TABLE "globalschema".job_run_asset(
  container_id varchar(36) NOT NULL,
  asset_id varchar(128) NOT NULL,
  job_ref varchar(36) NOT NULL,
  version integer NOT NULL,
  job_name varchar(256) NOT NULL,
  job_type varchar(256) NOT NULL,
  state varchar(32),
  isScheduledRun decimal(1) CHECK (
    isScheduledRun in (0, 1)
  ),
  project_name varchar(256) NOT NULL,
  last_state_change_timestamp timestamp(12) NOT NULL,
  duration integer 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, job_ref),
  PERIOD SYSTEM_TIME (tech_start, tech_end)
)

Oracle

CREATE TABLE statement:

CREATE TABLE "globalschema".job_run_asset(
  container_id varchar(36) NOT NULL,
  asset_id varchar(128) NOT NULL,
  job_ref varchar(36) NOT NULL,
  version integer NOT NULL,
  job_name varchar(256) NOT NULL,
  job_type varchar(256) NOT NULL,
  state varchar(32),
  isScheduledRun decimal(1) CHECK (
    isScheduledRun in (0, 1)
  ),
  project_name varchar(256) NOT NULL,
  last_state_change_timestamp timestamp(6) NOT NULL,
  duration integer NOT NULL,
  tech_start TIMESTAMP(6) DEFAULT SYSTIMESTAMP NOT NULL,
  tech_end TIMESTAMP(6) DEFAULT to_timestamp('9999-12-30', 'YYYY-MM-DD') NOT NULL,
  ts_id TIMESTAMP(6) DEFAULT SYSTIMESTAMP NOT NULL,
  PRIMARY KEY(container_id, asset_id, job_ref)
)

MS SQL Server

CREATE TABLE statement:

CREATE TABLE "globalschema".job_run_asset (
    container_id varchar(36) NOT NULL,
    asset_id varchar(128) NOT NULL,
    job_ref varchar(36) NOT NULL,
    version integer NOT NULL,
    job_name varchar(256) NOT NULL,
    job_type varchar(256) NOT NULL,
    state varchar(32),
    isScheduledRun decimal(1) CHECK (isScheduledRun in (0, 1)),
    project_name varchar(256) NOT NULL,
    last_state_change_timestamp DATETIME2 NOT NULL,
    duration integer NOT NULL,
    tech_start DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    tech_end DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    ts_id DATETIME2 DEFAULT CURRENT_TIMESTAMP NOT NULL,

    CONSTRAINT PK_job_run_asset_globalschema
    PRIMARY KEY (container_id, asset_id, job_ref),

    PERIOD FOR SYSTEM_TIME (tech_start, tech_end)

    WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = "globalschema".hist_job_run_asset))
);

Learn more

Parent topic: Reporting tables