When updating the LAR date to fix a stats aggregation problem to maximise the data recovery how can the oldest base table partition date be found

Document ID : KB000009901
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

To fix some stats aggregation problems stats tables whose LAR (Last Aggregated Row) date is out of sync with the oldest available base table partition need to have that date updated.

To maximise the data recovery requires finding the oldest partition name for the base tables and then using that partition date as the new LAR date.

Instructions:

The following SQL can be run on the base tables to locate the partition name with the oldest date suffix. The results should be the same for both base table types ts_st_ts_us_int and ts_st_tu_us_int

The last partition in the returned list is the one whose date is needed.

Postgres APM Database:

select table_name from information_schema.tables where table_name like 'ts_st_ts_us_int%' order by table_name desc;  

select table_name from information_schema.tables where table_name like 'ts_st_tu_us_int%' order by table_name desc;  

(The pgadmin tool could also be used to visually inspect the table names from the Schemas/Public/Tables node of the database, but that can be more difficult to read)

 

Oracle APM database:

select partition_name from user_tab_partitions where table_name like 'TS_ST_TS_US_INT%' order by partition_name desc; 

select partition_name from user_tab_partitions where table_name like 'TS_ST_TU_US_INT%' order by partition_name desc;

 

Example (Postgres):

If the oldest partition names returned are ts_st_ts_us_int_20161021 and ts_st_tu_us_int_20161021 then a LAR date of 20161021 ('2016-10-21') should be used for the LAR update.

Additional Information:

See the main knowledgebase article TEC610521 on the full steps to update the LAR date on the stats tables with a specific date (Section: "In this second case, a specific date is set")

A Guide to Solving Common Stats and Defects Aggregation Problems