Archiving
Overview
Every time an agent opens a ticket s/he uses the GUI to create an entry in the Request Schema. The entry has
attributes such as category type, sub-type, work log, solution, and current assignee that the
agent modifies. Remedy takes the schema and maps it to a variety of tables in the database.
Eventually, the tables grow so large they become cumbersome. New ticket creation and ticket searches
are slowed.
This problem is solved by archiving tickets over 60 days old.
The rest of this section describes
the archiving process.
Archiving Issues
-
What to archive?
Member Services group provides a list of fields to keep. This list changes over time.
-
What is the archiving process?
Each morning around 4 am, about one thousand tickets are archived
by an automated process called Archive. Archive begins by executing a stored
procedure called
rem_Archive, which:
-
Gets a list of all IDs (every ticket has a unique ID) whose closed date is more than 60 days ago.
-
Puts that list of IDs in a temporary table called
Archive_ID.
-
Using the IDs from the temporary table, the procedure grabs data from the
Request schema/view (as specified by
Member Services) and inserts the selected fields into
corresponding rows of Archive schema/view's backend T and H tables.
Archive returns to the Request Schema and
-
How to recover data which has been archived?
The Request Schema contains a field called Archive ID that remains null until the
ticket is archived. As with the ticket creation process
when the original ID is passed in, a ticket is reopened by passing in a non-null
archive ID.
Implementation
Remedy calls an API to generate a Request ID the first time a ticket is created.
The entry (the Request ID along with the data collected by the agent)
is placed in the Request schema.
Since Remedy manages the database for its users,
the backend T & H table handling is hidden.
Problems arise because Juno would like to use
the same Request ID as a lookup key for archived data.
Since it would be impossible to keep the Request ID unchanged
if Remedy were to handle the archive process
(Remedy would automatically generate a new Request ID while archiving the data),
Juno must manually insert the archived data
into the T and H tables - all the
while remaining careful to touch everything that Remedy would have touched.