How to Retrieve Old Task History After Updating to Version 1.47

How to Retrieve Old Task History After Updating to Version 1.47


Overview

From version 1.47 of Matillion ETL onwards, only a subset of the whole task history will be immediately available without user action. This subset is a maximum of the most recent 500,000 rows. The guide below makes retrieving older task history quick and simple.



How to retrieve task history

1. Log in to your Postgres database via PostgreSQL.

2. Execute the following block of code.

DO $$
    DECLARE
        package RECORD;
        rows_updated INTEGER := 0;
    BEGIN

        FOR package IN SELECT * FROM task_package_history WHERE org_id IS NULL
            LOOP
                UPDATE task_package_history
                SET org_id = 'none'
                WHERE taskid = package.taskid AND batchid = package.batchid;
                rows_updated := rows_updated + 1;
                IF mod(rows_updated, 500000) = 0 THEN
                    RAISE NOTICE 'Rows updated: %', rows_updated;
                END IF;
            END LOOP;

        RAISE NOTICE 'Total rows updated: %', rows_updated;
    END; $$;

3. This script will print out a message for every 500,000 rows updated, to keep users updated on progress.

Please Note

The script is all-or-nothing. If the process crashes while running, nothing is updated.