Record “PTS write time” in query history database

Resolving The Problem

IBM Netezza PDA 7.2.1.6-P2 (hereafter mentioned as NPS) introduces feature to record “PTS write time” in query history database.

·    Enabling the feature:

The feature is disabled by default. To enable, set the following parameter in postgresql.conf

REPLICATION_LOG_BYVALUE_UNLOAD = 1

This will enable logging on “PTS write time” into query history version 3.

·    Disabling the feature:

To disable the feature, set above parameter to 0.

After enabling the parameter, “PTS write time” can be retrieved using following queries.

·    Queries to observe write times:

Following query retrieves the information like querytext and runtime from view $v_hist_queries  in history database.
SELECT * from  (select QUERY::varchar(100), SUBMITTIME, FINISHTIME, RUNTIME from "$v_hist_queries" where sessionid=<session-id> and submittime>=current_Date and NUMPLANS is not null order by SUBMITTIME)a ORDER BY SUBMITTIME;

Following query retrieves the plan information from hist_plan_prolog and hist_plan_epilog.
SELECT sum(runtime) from (select epi.sessionid,epi.planid,submittime,endtime,(endtime-starttime)as runtime from "$hist_plan_epilog_3" epi, "$hist_plan_prolog_3" pro where epi.planid in (<planids of create external tables>) and epi.sessionid in (<session-id>) and epi.sessionid=pro.sessionid and epi.NPSID=pro.npsid and epi.PLANID=pro.planid order by PLANID)<alias_name>;