How to audit CCM DB?
Hi,
Recently I notice that the CCM Table spaces (Oracle) usage is increased quicker than normal. I also noticed the CCM schema's CONTENT_STORAGE is very large. My RTC version is 4.0.3
1. How can I find out which project in RTC contributes most data in RTC?
2. Is there away that I can control the type of file (by extension) or size that user deliver to RTC? For exemple, developer should not be able to deliver binary files (.jar, .exe ...) into RTC.
3. Is there away to clean up DB (certainly, I will need to communicate with project team)?
4. Is there away that I can see the last login date of each user? thru Command-line? This will help me to manage inventory of RTC license
Thank you in advance,
Tran
7 answers
Content Storage holds data like work item attachments, project area specifications etc. Attachment files can get quite large. If you look at the CONTENT_STORAGE table and sort by size of content bytes, you'll see the largest. These files are generally zip files. You might be able to download and look at the largest to get a sense of what the data is.
Don't modify/delete any data directly in the database. This will cause complex problems. Data files are referenced by the contents of other XML files. Even if you think you know what you're deleting, you'll miss something.
See https://jazz.net/forum/questions/25762/upload-file-size-limit for limiting the size of attachments.
Hi Glenn,
Thank you for the answer, I am able to find our the file with large size in REPOSITORY_CONTENT_STORAGE but I am not able to identify the project, How do I get the Project name from UUID below?
Thank you in advance,
UUID FILE_SIZE
_EsaFkNjoEeSOY-Ebif2jVg 72504394
_mGqR8NmwEeSjMp1RvLrVfA 72267204
Make a note of the value of the field OWNER_ITEM_ID for CONTENT_STORAGE record with the UUID above. ITEM_ID entries look like GUBDYGqZEeSr84FT-jFfjQ.
Run the query
select ITEM_VALUE rom REPOSITORY_ITEM_STATES where ITEM_UUID='owner item uuid value'
Save the ITEM_VALUE. It's a zipped file. Unzip it. The file should start with something like <workitem:WorkItem. ..>
This is the type of the entity that contains the data. There also should be a line like <projectArea itemId="_GUBDYGqZEeSr84FT-jFfjQ" />. If you see this, start the server, login and then enter,
https://localhost:9443/ccm/process/process_area/_GUBDYGqZEeSr84FT. This should show the project area that owns the entity that contains the content you're interested in.
Comments
Hi Glenn,
I am one step closer!
Following your guidances, I got ITEM_VALUE of REPOSITORY_ITEM_STATES is shown as (BLOB), so I download and unzip it.
It's an xml file. However, its content does not look similar to yours, I could not find anything with "project." or "workitem" ...therefore, I am still not able to identify the project/workitem with large attachments. I must have missed something.
Thank you in advance for helping me to the end.
Tran
Hi Glenn,
Thank you so much. I am one step closer!
my ITEM_UUID of REPOSITORY_ITEM_STATES is shown as (BLOB), so I download and unzip. It's an xml file. the content of it does not look similar to yours, I could not find anything with "project.".
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" itemId="_p5DtYCTSEeCMyPhGlTN1qw" processDomainDefinitions="">
<stateId>_w1_h48NIEeSTieMJ8B4U4g</stateId>
<immutable>true</immutable>
<contextId>_8lNyYNwSEd2pIJ5QVwgQGg</contextId>
<modified>1425567801086</modified>
<mergePredecessor xsi:nil="true"/>
... and got many lines as following...
<definitionData>
<internalId>_g4OR0ThvEeOTNsDnFKp2ow</internalId>
<key>com.ibm.team.internal.process.40.compiled.xml</key>
<value>
<internalId>_w1_h4sNIEeSTieMJ8B4U4g</internalId>
<deltaPredecessor>_grCKYThvEeOTNsDnFKp2ow</deltaPredecessor>
<contentId>_wU94ccNIEeSTieMJ8B4U4g</contentId>
<contentLength>141334</contentLength>
<characterEncoding>UTF-8</characterEncoding>
<contentType>application/xml</contentType>
<checksum>116174108</checksum>
<lineDelimiterSetting>0</lineDelimiterSetting>
</value>
</definitionData>
<descDetails xsi:nil="true"/>
<internalProcessAttachments itemId="_qJevAiTSEeCMyPhGlTN1qw" />
<internalProcessAttachments itemId="_p5nHAiTSEeCMyPhGlTN1qw" />
<internalProcessAttachments itemId="_p6X8AiTSEeCMyPhGlTN1qw" />
I also try to plug the a few itemId to my URL
https://myserver/ccm/process/process_area/_p6X8AiTSEeCMyPhGlTN1qw
But always got error page not found
I am getting to close to the end, Thank you so much for your help.
The large object is a processDomainDefinitions.
I don't see one of these in my database.in the database. Do you know how this was created? I suspect it exists outside of a project area. It looks like it's related to a process template (e.g. Scrum, Formal Project Management Process).
To get an idea of what's in a process template, in the thick client, right click over a project area and select open. Select the process configuration source. You can also export a specification. I'll see if I can determine what's in a processDomainDefinition.