@VLu,
Just in case you are seeing a similar error in your logs, here is something I wrote up that sounds simlar to what you're experiencing; I've encorporated several CA Support tickets worth of feedback into it (from tickets 20823288-1: CEMDB ERRORS, 20913091-1: CEMDB ERRORS (AGAIN), and 21026019-1: CEMDB RELATION DOES NOT EXIST). This only applies if you see a similar error, however, and [color=#ff0000]I would confirm with CA Support before following the resolution steps[color].
ERRORS:
relation "ts_st_ts_us_int_YYYYMMDD" does not exist
relation "ts_st_ts_us_dly_YYYYMMDD" does not exist
relation "ts_st_tu_us_int_YYYYMMDD" does not exist
Summary
In the [font=Courier New]IntroscopeEnterpriseManager.log[font] of the CEM Collector (or equivalent), you seen an error like the below (exact table name may be different):
6/20/12 12:02:49.610 AM PDT [ERROR] [Thread-815] [Manager.com.timestock.tess.services.processors.StatsAggregator] [main] [com.timestock.tess.util.TessLogger] Select failed for ts_st_ts_us_int_20120523
org.postgresql.util.PSQLException: ERROR: relation "ts_st_ts_us_int_20120523" does not exist
6/20/12 12:02:49.612 AM PDT [ERROR] [Thread-815] [Manager.com.timestock.tess.services.processors.StatsAggregator] [main] [com.timestock.tess.util.MainProgram] Unexpected exception in daily aggregation
com.timestock.common.exceptions.TsUnexpectedDatabaseErrorException: Unexpected database error: could not execute query
at com.timestock.tess.util.DbUtils.getObjectWithRetries(DbUtils.java:4447)
at com.timestock.tess.util.DbUtils.getObjectWithRetries(DbUtils.java:4382)
at com.timestock.tess.util.StatisticsAggregation.getLastStats(StatisticsAggregation.java:828)
at com.timestock.tess.util.StatisticsAggregation.getDatabaseRows(StatisticsAggregation.java:655)
at com.timestock.tess.util.StatisticsAggregation.getDatabaseRows(StatisticsAggregation.java:613)
at com.timestock.tess.util.StatisticsAggregation.getStatsForPeriod(StatisticsAggregation.java:501)
at com.timestock.tess.util.StatisticsAggregation.getStatsForInterval(StatisticsAggregation.java:476)
at com.timestock.tess.services.processors.StatsProcessingBase.processMissingStats(StatsProcessingBase.java:389)
at com.timestock.tess.services.processors.StatsAggregator.doDailyAggregations(StatsAggregator.java:689)
at com.timestock.tess.services.processors.StatsAggregator.doDailyAggregations(StatsAggregator.java:601)
at com.timestock.tess.util.MainProgram.doStatsAggregation(MainProgram.java:98)
at com.timestock.tess.util.MainProgram.run(MainProgram.java:50)
at com.timestock.tess.util.MainProgram.main(MainProgram.java:41)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2214)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
at org.hibernate.loader.Loader.list(Loader.java:2090)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:150)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:804)
at com.timestock.tess.util.DbUtils.getObjectWithRetries(DbUtils.java:4414)
... 12 more
Caused by: org.postgresql.util.PSQLException: ERROR: relation "ts_st_ts_us_int_20120523" does not exist
Resolution
1. Make note of which table(s) is impacted; transet (ts_st_ts...), transgrp (ts_st_tsgrp...), transunit (ts_st_tu...). The rest of the steps will assume it occurs at the transet level, but it could be any or multiple of the three. This problem normally occurs at the transet level, but could also be occuring at the transunit. The errors for transet could mask the similar error for transunit; therefore, upon resolving one, you may still encouter the other. Be sure to check!
2. Shut down the CEM Collector EM.
3. Log into the CEMDB either remotely via DBVisualizer (or similar) or locally using the pgAdmin III utility.
4. To check when the last transset data collection was made, run the following query. This will help you know just how long you've had the problem and possibly change how you resolve it.
[font=Courier New] select 'ts_st_ts_all_dly', max(ts_interval_start_time) from ts_st_ts_all_dly
union all select 'ts_st_ts_all_int', max(ts_interval_start_time) from ts_st_ts_all_int
union all select 'ts_st_ts_all_mly', max(ts_interval_start_time) from ts_st_ts_all_mly
union all select 'ts_st_ts_all_wly', max(ts_interval_start_time) from ts_st_ts_all_wly
union all select 'ts_st_ts_us_int', max(ts_interval_start_time) from ts_st_ts_us_int
union all select 'ts_st_ts_us_dly', max(ts_interval_start_time) from ts_st_ts_us_dly
union all select 'ts_st_ts_us_mly', max(ts_interval_start_time) from ts_st_ts_us_mly
union all select 'ts_st_ts_us_wly', max(ts_interval_start_time) from ts_st_ts_us_wly
union all select 'ts_st_ts_usgrp_dly', max(ts_interval_start_time) from ts_st_ts_usgrp_dly
union all select 'ts_st_ts_usgrp_int', max(ts_interval_start_time) from ts_st_ts_usgrp_int
union all select 'ts_st_ts_usgrp_mly', max(ts_interval_start_time) from ts_st_ts_usgrp_mly
union all select 'ts_st_ts_usgrp_wly', max(ts_interval_start_time) from ts_st_ts_usgrp_wly[font]
5. If the date in the error message (or in the above queries) is a recent date, perform substep A. If the date is very old, perform substep B. You could try adjusting either query set based on the findings from Step 4.
A. Edit the following SQL statement to that the “INTERVAL ‘8 hour’” string represents the correct amount of time between now and the midnight past, then execute the query.
[font=Courier New]UPDATE ts_st_ts_all_dly SET ts_last_aggregated_row = DATE_TRUNC('day',now()) - INTERVAL '8 hour';
UPDATE ts_st_ts_all_int SET ts_last_aggregated_row = DATE_TRUNC('day',now()) - INTERVAL '8 hour';
UPDATE ts_st_ts_all_mly SET ts_last_aggregated_row = DATE_TRUNC('day',now()) - INTERVAL '8 hour';
UPDATE ts_st_ts_all_wly SET ts_last_aggregated_row = DATE_TRUNC('day',now()) - INTERVAL '8 hour';
UPDATE ts_st_ts_us_dly SET ts_last_aggregated_row = DATE_TRUNC('day',now()) - INTERVAL '8 hour';
UPDATE ts_st_ts_us_mly SET ts_last_aggregated_row = DATE_TRUNC('day',now()) - INTERVAL '8 hour';
UPDATE ts_st_ts_us_wly SET ts_last_aggregated_row = DATE_TRUNC('day',now()) - INTERVAL '8 hour';
UPDATE ts_st_ts_usgrp_dly SET ts_last_aggregated_row = DATE_TRUNC('day',now()) - INTERVAL '8 hour';
UPDATE ts_st_ts_usgrp_int SET ts_last_aggregated_row = DATE_TRUNC('day',now()) - INTERVAL '8 hour';
UPDATE ts_st_ts_usgrp_mly SET ts_last_aggregated_row = DATE_TRUNC('day',now()) - INTERVAL '8 hour';
UPDATE ts_st_ts_usgrp_wly SET ts_last_aggregated_row = DATE_TRUNC('day',now()) - INTERVAL '8 hour';[font]
B. Clean out the transset data , run the following queries. This will purge the bad data entirely, ensuring a fix but removing stored data. If you don't have recent data, this might be OK.
[font=Courier New]DELETE FROM ts_st_ts_all_dly;
DELETE FROM ts_st_ts_all_int;
DELETE FROM ts_st_ts_all_mly;
DELETE FROM ts_st_ts_all_wly;
DELETE FROM ts_st_ts_us_int;
DELETE FROM ts_st_ts_us_dly;
DELETE FROM ts_st_ts_us_mly;
DELETE FROM ts_st_ts_us_wly;
DELETE FROM ts_st_ts_usgrp_dly;
DELETE FROM ts_st_ts_usgrp_int;
DELETE FROM ts_st_ts_usgrp_mly;
DELETE FROM ts_st_ts_usgrp_wly;[font]
6. Start the CEM Collector EM.