How are rows created in CHANGEITEM table in Jira

[Manoj Kumar]

Hello team,

                I have a requirement where I need to take copy of CHANGEITEM table every day. At present it is taking lot of hours to pull this data as there are around 25 million records and table consists of CLOB data types.

Can you someone please answer below questions for me ?

1. Will there any change to the existing rows in CHANGEITEM table or is it just new rows gets added every time we add a comment/ change in UI.

2. Does old rows gets deleted when we do any thing in Jira application ?

3. I want to develop a script where I will just append new rows to the existing data rather than deleting and copying whole data . Will this work ??

Thanks in Advance

Regards,

Manoj  

[Nic Brough -Adaptavist-]

Changeitem is at the heart of Jira because it is an issue tracker.  Changeitem is tracking changes to the issues.

So

1. No, because a change to an issue should be recorded as it happens.  If that changes later, you are destroying the tracking function
2. Mostly no, because it is an issue tracker.  Issue deletion is the only thing that destroys your issue data, including the history
3. It might, but as Jira is already "appending" rows, it's probably utterly pointless, and almost certainly will corrupt your database

Instead of looking at the database, could you explain what you are trying to do and why?  What are your end users trying to do?  Why are you copying the history when you almost certainly don't need to?

[Manoj Kumar]

Hi @Nic Brough -Adaptavist- 

            Thanks for your time on this. 

We are not making changes to source db .. we just want to take copy of source table to our database. We are copying data because we don't want to cause performance issues as there are lot of users who will be using our report.

We are using this data to create reports to see how a team is performing when a story/bug is created. Something like this :

1,How fast they are responding to the user ? .

2. How frequent they are updating the status of the Bug/Story.

So I will proceed to create a script which will just copy new rows instead of whole data.

Regards,

Manoj

[Manoj Kumar]

Hi @Nic Brough -Adaptavist- 

Can you also help with below ?

1. is there any way I can pull data for the past two years rather than whole data as we CHANGEITEM table has data for past 15-20 years which is not useful to us.

2. How can I join CHANGEITEM table with date ?

Regards,

Manoj 

[Nic Brough -Adaptavist-]

Have you tried any of the "time to response" reporting built into Jira?  (Or by apps) That will be easier and can be done without leaving Jira.