Large Tables

[Derek Giedd]

We have been having an issue with our Tablespace filling rapidly in our Dev environment.  When I looked at the tables, I see two of them KNTA_USER_SECURITY_HIST and KCST_PENDING_EV_UPDATES that are quite large.  I am not sure the value of these tables and what, if anything, can be done to reduce the size.

KNTA_USER_SECURITY_HIST seems to show the history of changes to security group members.  However, whenever I add someone to a group, it looks like it first records a row for each current member indicating delete, then adds a new row for each of these members with insert.  So, if I have a security group with a large number of members, adding a new member adds lots of rows.  I am showing around 13M rows in this table. Can this table be truncated?

 KCST_PENDING_EV_UPDATES has rows with a creation date as far back as 2012.  Since the table name indicates these records are pending some action, I am guessing something is not running properly.  We are not using any earned value calculations so I am not sure if this data is needed.  Can this table be truncated or is there something that needs to be done to clear this up?

[Natalia_R_PPM]

Hello Derek

Regarding these tables , I found the following information:

1. KNTA_USER_SECURITY_HIST

This table stores user security history. This table is used for the purpose of

Sarbanes-Oxley (SOX) compliance. If you comply with SOX, check with your

SOX auditor to see how long you need to retain the records before you truncate

the table. If you do not comply with SOX, you do not need to back up the table,

and truncating the table does not impact the application.

(PPM 9.20 Data Model Guide)

2.KCST_PENDING_EV_UPDATES

This table temporally stores the EV values that needs to be updated by the EV services

(PPM 9.20 Data Model Guide)

QCCR1L50503: KCST_PENDING_EV_UPDATES table. has almost 90K rows and Pending EV Updates Table Cleanup Service runs without error, but the rows remain.

https://softwaresupport.hp.com/group/softwaresupport/search-result/-/facetsearch/document/LID/QCCR1L50503

Pending Cost EV Update Service

Description: The service divides the earned/planned values into weekly buckets for active projects. This data is viewed only through the graph and data grid on the Project Cumulative Cost Metrics page (via the Analyze costs button on project overview). Cumulative cost metrics are stored in the table KDRV_COST_METRICS_BY_PERIOD. Cost Rollup Service adds projects to a queue (KCST_PENDING_EV_UPDATES) for this service. This service does not perform any rollup calculation.

Default interval: 1 hour

Driving table: KCST_PENDING_EV_UPDATES

Intermediate table: KCST_PENDING_EV_UPDATES_ERR

Trigger Condition: When cost rollup service runs for all active projects with a active workplan that have cost enabled, new entries are made in KCST_PENDING_EV_UPDATES.

Recommendation: If cost is not tracked for any project, this service may be disabled.

Impact of disabling service: On disabling this service, the Project Cumulative Cost Metrics are not accurate.

Hope this information be useful for you.

Thanks

Regards,

Natalia Rojas

[Derek Giedd]

Thanks for this information.  Can I safely truncate the table KCST_PENDING_EV_UPDATES? 

[Loc_Nguyen_PPM]

Hi,

This table is used by the Pending Cost EV Update Service, if you are not using this service and you do not plan to use it ever in the future, then you can backup and delete this data.

But keep in mind that if you disable this service, the Project Cumulative Cost Metrics will not be accurate.

Hope this helps,

[Derek Giedd]

I found that the Pending Cost EV Update Service somehow got turned off last year.  I have re-enabled it.  The # of records in this table has started decreasing but at a very slow rate.  After about 22 hours it has only removed 4,622 rows.  At this rate it will be about 10 years to clear this table.  Is there anything I can do to speed it up or force more records to be processed with each run of the service?  The service is currently set to run every hour.  I can reduce this but since it is a heavy load, I don't want to impact the system too much.

[Loc_Nguyen_PPM]

Hi Derek Giedd,

After discussion with my senior engineer, i would like to tell you that there is no way to speed up the process.

We recommend you to open a case to HP, we will create a new CPE incident for this case and elevate to R&D.

So R&D will decide if the old records can be deleted or not.

One thing, we don't think  that turn on the service Pending Cost EV Update Service is a good decision because it can break your PPM instance.

Regards,

[Etienne_Canaud]

Hi Derek,

Actually, there might be a way to speed this up. 

You could write a SQL script to remove all the duplicate records from this table with the same SOURCE_ENTITY_ID and SOURCE_ID values. There's no point keeping multiple rows with the same entity in there since you will recompute the same things again and again. 

I would expect PPM to filter out these duplicates under normal processing conditions, but there's no telling what happened by turning off this background service for over one year...

Thanks,

Etienne.