Storing and persisting metrics

Prerequisite

IBM DataStage supports sending the DataStage metrics data to a separate metrics database. PostgreSQL is the designated database for storing metrics, offering a lightweight solution that is not intended as an operational data store. It enables users to run their own queries for insights into job performance. Depending on client preferences, their PostgreSQL database can be hosted within the same Cloud Pak for Data environment, operated on a virtual machine, or managed within a PostgreSQL service.

Creating a connection

To enable repository metrics you need to open your project, and click the Manage tab. On the left side panel, click DataStage. After that, click the Repository tab. Click on Enable persisting metrics option. Next you need to provide your credentials to make a connection. Specify a connection type, configure properties and security details, and test the connection to verify if it works. Click Save button at the top of the page.

To activate repository metrics at the project level, ensure that you create a flow after saving the connection. Once repository metrics are enabled for a specific job, they will not be available for any other job by default. To enable metrics for additional jobs, you must recompile and re-run each job individually. To verify whether repository metrics are active, review the job logs after execution. The parameter DSMetricsRepoEnabled must be set to True

drop schema if exists ds_metrics cascade;
drop table if exists public.databasechangelog;
drop table if exists public.databasechangeloglock;

select has_database_privilege(current_database(), 'create'), has_schema_privilege('public', 'create');

Metrics retention

The Metrics retention field controls how frequently, and if at all, ds-metrics removes old data from the database. If you enable the Max limit option, you can choose between the Days or Runs settings. For example, if you set 30 days, metrics deletes all information that is related to the job and to all its runs when a job is over 30 days old. If you set 100 runs, metrics keeps the 100 most recent runs, and deletes the older runs. The Metrics retention field applies only to metrics data related to the project in which the settings are activated. If the database is shared with another project, the retention settings do not impact the data from that project.

After you save the connection settings, it triggers ds-metrics to clear its connection cache. When you run a job in the project, ds-metrics connects to the database for the first time. After a connection is made, it populates the ds-metrics schema after it is initialized, or migrates it if the schema in the database is an older version.

Database structure

In the metrics database, most tables are in the ds-metrics schema. public.databasechangelog and public.databasechangeloglock are extra internal tables in the public schema that are used to track the database schema versions.

Table schemas