Question

Changing Expiration Date in Report Archive

  • 27 July 2021
  • 8 replies
  • 626 views
I
Rank
Userlevel 2
Badge +5
  • ig_
  • Do Gooder (Customer)
  • 18 replies

Hello Community,

 

we are currently using IFS9 UPD16 and are planning to use the Report Archive to store documents for several years to meet government requirements.

 

We are aware of the option with the default lifetime. With that we could set the reports up for 10 years, but that would save all reports, even the ones that we do not need to store for such a long time (e.g. preliminary invoices or test prints etc.).

That is why we have created different SQL rules depending on the report to change the Expiration Date in the Database directly. To be more precise, we update the field expire_date in the table archive_distribution_tab. These jobs would then be executed as scheduled jobs on the DB.

Several tests of ours were successful. Means after changing this date, the reports were available after the original expiration date.

But as this is a very sensitive topic we need to make sure this is really enough to store those documents for years or if we are missing something. Can we expect to have the option ‘Show PDF’ years later, by only changing this single field? So it’s not only about keeping the record, but the actual PDF as the original document!

 

This is one of the SQL queries to get a better understanding:

update archive_distribution_tab t
set t.expire_date = t.expire_date+100
--set t.expire_date = t.arrival_time+4020
where exists (select t2.result_key
             from archive_tab t2  
             where t2.result_key=t.result_key and t2.report_id in ('CUSTOMER_ORDER_IVC_REP')
             and t2.sender=t.user_name
             and t2.notes not like '%: PR%')
      and trunc(t.expire_date) < to_date('31.08.2021','DD.MM.YYYY')

 

Another question would be if there is any impact to be expected on the performance?

Currently we have around 140k entries in the archive_distribution_tab. I expect that to grow into millions over the years.

Of course we need to consider the storage space, but might there be an impact of the system performance of IFS?

 

As last a general question - how do you manage the task of storing the original documents in your system(s)? Have you set up IFS with the default lifetime of 10 years or are you saving those documents in another system? I would be glad to get an idea on how you do it.

 

This topic has been closed for comments

8 replies

Thushitha Chandrasiri
Rank
Userlevel 7
Badge +20

Is it not better to use the “Set Default Life Time” option in the report definition window for each report. So, you can set deferent life time for different reports. Don’t know whether you have tried this already.

 

Thanks,

Thushitha Chandrasiri - Follow my LinkedIn profile and contact me via - https://www.linkedin.com/in/thushitha-a-chandrasiri-32a2b2134/
I
Rank
Userlevel 2
Badge +5

Hi Thushitha,

 

as mentioned in my first post we are aware of this option. But this would save many documents, that we do not need to be saved.

M
Rank
Userlevel 5
Badge +10

In the past what I’ve done with a customer is to set up a report rule with conditions to export them to an FTP and the file naming represents the order_no and date. In that case, the burden is not in the Application / Database, but outside in an FTP folder. This may be an alternative to look at.

S
Rank
Userlevel 6
Badge +12

Hi Thushitha,

 

as mentioned in my first post we are aware of this option. But this would save many documents, that we do not need to be saved.

 

This raises the question (for me, anyway) of how many documents “do not need to be saved”. You mention preliminary invoices and test prints in your original post… Test prints should mostly occur in your TEST environment, but I am not sure about “preliminary invoices”. Do you have a feel for what proportion of documents are of this unneeded (to be archived) variety?

I recently developed a client-side printing mechanism that generates Purchase Orders every time someone wants to generate a PO and attach it to an email. This is going to potentially generate hundreds (or even thousands) of extra documents over time, but each one is only around 50-60K. An extra thousand documents is only 50-60 MB or so.

In other words, are you sure that setting the expiration by report type isn’t close enough? Do the extra documents really hurt anything? I like your solution, and the archive area is relatively simply (in terms of the data model), but like you, I would worry that something might go amiss down the line.

 

Thanks,

Joe Kaufman

Just because something is part of a plan doesn't make it right.
I
Rank
Userlevel 2
Badge +5

Hi Thushitha,

 

as mentioned in my first post we are aware of this option. But this would save many documents, that we do not need to be saved.

 

This raises the question (for me, anyway) of how many documents “do not need to be saved”. You mention preliminary invoices and test prints in your original post… Test prints should mostly occur in your TEST environment, but I am not sure about “preliminary invoices”. Do you have a feel for what proportion of documents are of this unneeded (to be archived) variety?

I recently developed a client-side printing mechanism that generates Purchase Orders every time someone wants to generate a PO and attach it to an email. This is going to potentially generate hundreds (or even thousands) of extra documents over time, but each one is only around 50-60K. An extra thousand documents is only 50-60 MB or so.

In other words, are you sure that setting the expiration by report type isn’t close enough? Do the extra documents really hurt anything? I like your solution, and the archive area is relatively simply (in terms of the data model), but like you, I would worry that something might go amiss down the line.

 

Thanks,

Joe Kaufman


Hi Joe,

 

take this example:

This is the print out of one single invoice report. Due to Report Distribution Groups the report has 27 entries in the archive. Yes, it’s only one result key, hence one report. But for the archiving purpose we only need one single entry (t2.sender=t.user_name is my SQL query in first post).

 

Another example are the test print outs. I didn’t mean that we are testing development changes on PROD environment. Like you said, that’s what we use TEST for. It’s more like users are checking the data on the documents before actually printing it. In these cases, we want only the documents that have a check mark in the "Printed" field.’ field tagged. And from those also only the latest print out.

Here is the SQL code for the Delivery note:

update archive_distribution_tab t
set t.expire_date = t.expire_date+100
where exists (select *
             from archive_tab t2
             where
             t2.result_key in (select max(t3.result_key)
                              from archive_tab t3
                              where t3.report_id in ('SHIPMENT_DELIVERY_NOTE_REP')
                                and t3.sender=t.user_name
                              group by t3.notes)
             and t2.result_key=t.result_key)
      and trunc(t.expire_date) < to_date('31.08.2021','DD.MM.YYYY')
      and t.printed = 1

 

So yes, setting all reports to max lifetime is possible. We are just trying to minimize storage space consumption with data, that is not really needed.

S
Rank
Userlevel 6
Badge +12

Hi Thushitha,

 

as mentioned in my first post we are aware of this option. But this would save many documents, that we do not need to be saved.

 

This raises the question (for me, anyway) of how many documents “do not need to be saved”. You mention preliminary invoices and test prints in your original post… Test prints should mostly occur in your TEST environment, but I am not sure about “preliminary invoices”. Do you have a feel for what proportion of documents are of this unneeded (to be archived) variety?

I recently developed a client-side printing mechanism that generates Purchase Orders every time someone wants to generate a PO and attach it to an email. This is going to potentially generate hundreds (or even thousands) of extra documents over time, but each one is only around 50-60K. An extra thousand documents is only 50-60 MB or so.

In other words, are you sure that setting the expiration by report type isn’t close enough? Do the extra documents really hurt anything? I like your solution, and the archive area is relatively simply (in terms of the data model), but like you, I would worry that something might go amiss down the line.

 

Thanks,

Joe Kaufman


Hi Joe,

 

take this example:

This is the print out of one single invoice report. Due to Report Distribution Groups the report has 27 entries in the archive. Yes, it’s only one result key, hence one report. But for the archiving purpose we only need one single entry (t2.sender=t.user_name is my SQL query in first post).

 

Another example are the test print outs. I didn’t mean that we are testing development changes on PROD environment. Like you said, that’s what we use TEST for. It’s more like users are checking the data on the documents before actually printing it. In these cases, we want only the documents that have a check mark in the "Printed" field.’ field tagged. And from those also only the latest print out.

Here is the SQL code for the Delivery note:

update archive_distribution_tab t
set t.expire_date = t.expire_date+100
where exists (select *
             from archive_tab t2
             where
             t2.result_key in (select max(t3.result_key)
                              from archive_tab t3
                              where t3.report_id in ('SHIPMENT_DELIVERY_NOTE_REP')
                                and t3.sender=t.user_name
                              group by t3.notes)
             and t2.result_key=t.result_key)
      and trunc(t.expire_date) < to_date('31.08.2021','DD.MM.YYYY')
      and t.printed = 1

 

So yes, setting all reports to max lifetime is possible. We are just trying to minimize storage space consumption with data, that is not really needed.

 

Am I right in saying, though, that one result key means only one actual file store as a LOB? Extra data records, while cluttering, should not take up much space (we could calculate the bytes taken per records, I suppose).

But I know what you mean about various printouts being preliminary or parts of “test” processes, even in PROD, and those are definitely going to take up space. At the end of the day, I do not see what could go wrong with your process, since that expiration date only resides in one spot as far as I can tell, and the data model is relatively simple. If it works for cleanup, it should keep working on into the future.

One thing I might suggest is that you may want to use API methods instead of a direct hit against the table. I tend to go that way so that code is more IFS-native (we are in Apps10). For example, the Archive_Distribution_API has a method Set_Expire_Date() that performs an update like you list above. Yeah, it is one update at a time and you would need to pull the data you want and increment the expire date to use, but when it comes to writes I try to always use IFS API methods in case they are doing something else under the hood. In looking at the code for Set_Expire_Date() I do not see anything very interesting (which is why your update code is probably safe), but you may way to consider it anyway.

 

Thanks,

Joe Kaufman

Just because something is part of a plan doesn't make it right.
I
Rank
Userlevel 2
Badge +5

Am I right in saying, though, that one result key means only one actual file store as a LOB? Extra data records, while cluttering, should not take up much space (we could calculate the bytes taken per records, I suppose).

But I know what you mean about various printouts being preliminary or parts of “test” processes, even in PROD, and those are definitely going to take up space. At the end of the day, I do not see what could go wrong with your process, since that expiration date only resides in one spot as far as I can tell, and the data model is relatively simple. If it works for cleanup, it should keep working on into the future.

One thing I might suggest is that you may want to use API methods instead of a direct hit against the table. I tend to go that way so that code is more IFS-native (we are in Apps10). For example, the Archive_Distribution_API has a method Set_Expire_Date() that performs an update like you list above. Yeah, it is one update at a time and you would need to pull the data you want and increment the expire date to use, but when it comes to writes I try to always use IFS API methods in case they are doing something else under the hood. In looking at the code for Set_Expire_Date() I do not see anything very interesting (which is why your update code is probably safe), but you may way to consider it anyway.

 

Thanks,

Joe Kaufman

 

Hi Joe,

 

appreciate your input.

 

My guess is that one result key is one stored file , yes. In this case it’s true that storage space can be ignored. Here it’s more about having a better overall overview in the report archive looking for reports connected to a specific order.

 

Everything you say about using the APIs is absolutely true. That’s also our normal approach. But just like you did, I analyzed the method and saw it was really basic and decided to go for directly for the change of the table. With thousands of reports to change a week, that’s way faster.

S
Rank
Userlevel 6
Badge +12

All great points! Be sure to let us know if the manual change of expiration dates ever bites you in the but down the road.  *smile*  I like this idea and will keep it in my back pocket in case we decide we need to do the same sort of thing based on some criteria.

Have a great weekend!

 

Thanks,

Joe Kaufman

Just because something is part of a plan doesn't make it right.
Terms & ConditionsCookie settings