It's all about the answers!

Ask a question

How to audit CCM DB?


Jinjin Tran (123) | asked Apr 10 '15, 10:21 a.m.

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



permanent link
Glenn Bardwell (58621527) | answered Apr 10 '15, 5:14 p.m.
JAZZ DEVELOPER
A start...

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.




permanent link
Jinjin Tran (123) | answered Apr 14 '15, 5:12 p.m.

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


permanent link
Glenn Bardwell (58621527) | answered Apr 15 '15, 4:40 p.m.
JAZZ DEVELOPER
edited Apr 15 '15, 4:41 p.m.

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
Jinjin Tran commented Apr 20 '15, 1:01 p.m.

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


permanent link
Jinjin Tran (123) | answered Apr 16 '15, 11:17 a.m.

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.


permanent link
Jinjin Tran (123) | answered Apr 16 '15, 11:19 a.m.

Correction,

My ITEM_VALUE of REPOSITORY_ITEM_STATES is shown as (BLOB)

(not ITEM_UUID)


permanent link
Jinjin Tran (123) | answered Apr 16 '15, 11:19 a.m.

Correction,

My ITEM_VALUE of REPOSITORY_ITEM_STATES is shown as (BLOB)

(not ITEM_UUID)


permanent link
Glenn Bardwell (58621527) | answered Apr 22 '15, 2:08 p.m.
JAZZ DEVELOPER

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.

Your answer


Register or to post your answer.


Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.