How are rows created in CHANGEITEM table in Jira

Question

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- · Answer

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.

Manoj Kumar_nh13i5 · Answer

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

Manoj Kumar_nh13i5 · Answer

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