Reporting tables

Reporting data for IBM Knowledge Catalog is stored in SQL tables in PostgreSQL, Db2 database. You can use the descriptions of the tables to better understand the reporting data and to generate more meaningful reports.

Each table description contains the list of table columns with short descriptions and the CREATE TABLE SQL statement. In some cases, ALTER TABLE and CREATE INDEX statements are provided.

Each of these tables has a history table that is associated with it. The name of a history table starts with the prefix hist_, followed by the table name.

In PostgreSQL SQL tables, use the f_trig_<table_name> function to populate the tables with appropriate data. The function is defined in the following way:

CREATE OR REPLACE
FUNCTION f_trig_<table_name>() RETURNS TRIGGER AS $$
DECLARE
BEGIN
	IF
			(TG_OP = 'DELETE') THEN OLD.tech_end = CURRENT_TIMESTAMP;

INSERT
	INTO
	hist_<table_name>
VALUES (OLD.*);

RETURN OLD;

ELSIF (TG_OP = 'UPDATE') THEN OLD.tech_end = CURRENT_TIMESTAMP;

NEW.tech_start = CURRENT_TIMESTAMP;

INSERT
	INTO
	hist_<table_name>
VALUES (OLD.*);

RETURN NEW;
END
IF;
END;

$$ LANGUAGE 'plpgsql'

To better understand the replationships between the SQL tables that store the reporting data, see Data model for IBM Knowledge Catalog reporting. The list of tables is as follows: