Delete / Archive Help

[Chris Kelleher]

Hi All,

Still trying to get some feedback.

We have been reviewing our Delete / Archive procedures, or non use of the
exisitng programs, in order to determine what strategies to employ in
setting up automated delete / archives.

Currently we have the following large tables, consisting of data that in
some cases is over two years old.

# ---Record Size--- ---Fragments--- Scatter
#Table Records Bytes Min Max Mean Count Factor Factor
qad_wkfl 121093 10529364 70 227 86 121093 1.0 3.5
wr_route 132245 34410173 217 364 260 132463 1.0 5.6
ps_mstr 174750 18737162 97 162 107 174750 1.0 3.3
iro_det 264017 21244328 57 117 80 264017 1.0 3.2
schd_det 285714 22756646 66 116 79 285714 1.0 3.1
sct_det 408126 22488365 44 116 55 408126 1.0 3.0
opgl_det 608718 46179884 54 94 75 608718 1.0 2.8
gltw_wkfl 827990 75793537 74 112 91 827990 1.0 2.1
grig_mtx 1124546 51200110 25 60 45 1124569 1.0 3.6
spt_det 2040650 95995962 36 69 47 2040650 1.0 2.3
tr_hist 2405342 571027256 193 332 237 2405344 1.0 2.3
trgl_det 2819681 204386948 38 93 72 2819681 1.0 2.1
op_hist 3740308 728556425 158 251 194 3740308 1.0 1.8
gltr_hist 6386949 808121109 83 159 126 6386949 1.0 1.8
# ------------------------------------------------------------
#Totals: 23163698 2856224k 10 1147 126 23164769 1.0 3.9

The databse is:
HP/UX 10.20
Progress 8.2b
MFG/Pro 8.5g

What we are trying to determine is:
What do other MFG/Pro users do with regard to doing delete/archives on the
GL history detail?
How often are the archives done?
How many months are kept un-archived?
How are the archives stored?
I have suggested keeping them in gzipped format and having a script
available to allow data to be extracted automatically, by unzipping the
files, grepping for the data and extracting into a temp file which would be
emailed to the user. Then zipping the files again (brief overview).
How easily is it to get at archived data?
Is data archived in monthly groups or more / less?

What about the other files, specifically op_hist and tr_hist.
What rules are applied to these (above questions)?

Any other strategies of information would be apprreciated. We are trying to
build a case for doing mothly archives and keeping at most 13 months worth
of gltr_hist + summarised data. The op_hist and tr_hist I would like to cut
down to less than a year, perhaps only 6 months.

TIA

Robert Cohen
Fisher Gauge Limited
Peg # 1999081601

 

[Chris Kelleher]

Robert,

Lookng at these records I noticed a large number of records in the
gltw_wkfl. This is not normal as this is a temporary table containing "in
progress" GL Transactions (Invoice Post for example). These records are
normally cleaned up after the process is finished. The fact that you have
nearly 1 million records in there indicates that there is something not
quite right in your system.

The rest of the tables look normal.

Delete/Archive is a process to be done every once in a while. Why do you
want to automate this?

regards,

Martin

+++++++++
Martin 't Hart - Project Leader QAD Service Line
Origin International Brussels
*:martin.hart@origin-it.com
*: +32 2 7123615 Mobile: +32 75 488706 Fax: +32 2 7123667 http://www.origin-it.com/qad

 

[Chris Kelleher]

Martin,

> Lookng at these records I noticed a large number of records in the
> gltw_wkfl. This is not normal as this is a temporary table containing "in
> progress" GL Transactions (Invoice Post for example). These records are
> normally cleaned up after the process is finished. The fact that you have
> nearly 1 million records in there indicates that there is something not
> quite right in your system.
>

I Will look into this, could be customized invoicing programs (before my
time) that may be missing somthing. Thank for this heads up.

> The rest of the tables look normal.

But we have over 2 years worth of detail and are growing at around 10 meg a
day. We would like to start archiving data from the larger table and are
looking for strategies and suggestion that other companies have implemented.

>
> Delete/Archive is a process to be done every once in a while.
> Why do you want to automate this?

The automation is easy, and than can be set to run monthly (or what ever)
without having to worry about excessive db growth.

The hard part is determining how much to archive and how much to keep?

 

[Chris Kelleher]

Robert,

Unfortunately, only you can answer most of your questions.

How long do you wish to keep data before you archive it? Well, that
depends on your business situation and what your end users are comfortable
with.

How often should you archive? I'm a firm believer in setting a regular
schedule and adhering to it, so *I'll* probably say you're doing some
archiving every week; the question is what you archive each week, and how
much you keep in the production database.

How is data stored? In ASCII files, which can be stored anywhere and used
as you see fit. On file, on a CD, on a COLD array -- your choice. To see
samples of the data layout, run a delete/archive from one of the demo
databases that we provide.

How should you make it retrievable? If you use a regular archive routine,
you can reload the data into your production database from any archive file
within a couple of hours, satisfy the end-user-customer's needs, and delete
that data again when you're done with it. The approach that you're
describing is rather complex and would probably work well; I'd suggest that
archiving data in monthly buckets and saving them makes life a lot easier.

It's a natural fact of life that your production database will eventually
be composed of a lot of history and a little data. When you first started,
it was a lot of data and a little history. If I read between the lines, it
looks like you have a 2.8GB database of which 2.5GB is history (in the 10
files you listed). There's nothing either good or bad about this --
they're simply facts. In fact, they may be part of the facts that justify
purchasing additional disk drives to keep the data, if your end users
determine that they need to have it.

I've usually used business cycles as my benchmark for how long to retain
data. For example, financial data (IMHO) should be kept around for no less
than a month after that month is closed out. It can be kept longer if the
finance group deems it necessary. I've kept sales information (for
schedules) until the end of the obsolescence claim processing period for
the contract. In other words, it all depends, and if you can show to your
end users how easy it can be to get the information that they'd like to
have back, it can become a non-issue. Note: when asked how much history
they wish to keep, your end users will tell you that yes, they wish to keep
their history.

Good luck.

Scott
===============================================================
Scott M. Dulecki /* 1998061901 */ +1 616 975 6322
Product Manager scott_dulecki@qad.com
QAD, Inc. http://www.qad.com
1188 East Paris SE Grand Rapids, MI 49546 USA

Next Michigan Progress Users Group: 19 January 2000

All opinions are my own, and don't necessarily reflect those of
any other living being.
===============================================================

 

[Chris Kelleher]

Martin, Scott,

Scott, thanks for the input, this info will go a long way to helping us make
a case for using the delete / archive functions. As you said, if you ask
the user they always need the complete detail for as long as possible. How
long is up to us, but ideas and input from other help make the case. Thanks

Scott, a while back (before you short leave - how is the new addition
doing?) I remember a message from you to someone on this topic regarding
switching from detail to summary posting.
Therefore, if we do not do this are we saving the same data in both places
(in the AR file and the GL files - both keeping all detail)?
If we start posting only summary data is the data still available for
reporting in the original area (i.e. AR)?

Martin,

I looked into what source programs (we don't have all source) use the
gltw_wkfl file and narrowed it down to two reports that have been
customised. It looked at the programs and they seem to be fine. So I ran
one, 3.21.16 Transactions Accounting Report (ictrrp03.p) for a broad range
within a monthly range. I then checked my MFG/Pro userid (sip930) and in
another session read the gltw_wkfl for this userid. There were lost of
records being created, yet the report did not display results --- wait 5
minutes --- still no results. So in typical user fashion I pressed CTRL C,
what they would do. The records remained in the file.

In looking at the file a lot of these records are rather old. Do you or
anyone know if I can just remove these, when running in single user mode, or
what should I do to clean them up. Is there any utility program that can be
used?


> Looking at these records I noticed a large number of records in the
> gltw_wkfl. This is not normal as this is a temporary table containing "in
> progress" GL Transactions (Invoice Post for example). These records are
> normally cleaned up after the process is finished. The fact
> that you have nearly 1 million records in there indicates that there is
> something not quite right in your system.
>

HP/UX 10.20
Progress 8.2b
MFG/Pro 8.5g

Robert Cohen
Fisher Gauge Limited
Peg # 1999081601

 

[Chris Kelleher]

Robert,

Last mail for today - it is getting late in Europe...

I would be very, very carefull in removing these records. I have had a case
like this at one of my customer sites (however there were only 63 stranded
records) and we nearly went through them one by one deleting them. Perhaps
QAD support could give you the full guarantee on being able to delete them
all.

regards,

Martin

+++++++++
Martin 't Hart - Project Leader QAD Service Line
Origin International Brussels
*:martin.hart@origin-it.com
*: +32 2 7123615 Mobile: +32 75 488706 Fax: +32 2 7123667 http://www.origin-it.com/qad

 

[Chris Kelleher]

Robert,

You are correct. If you are posting in detail, all the details are kept
both in the GL module as well as the source module (AP, AR, IC, etc.). If
you post in summary, the detail is still available in the source module
until you address that through a D/A.

Most folks see a TREMENDOUS reduction in GL records once they start posting
in summary, and after you've been using the system for about three or four
months, your people should be comfortable enough with the GL process that
they no longer need the security blanket of a detail GL. Once you've hit
that point, I would also consolidate the detail GL records that are
currently sitting out there, but remember not to do it all at once --
monthly chunks are usually a safe bet.

One of the key things I would suggest to you is to show your end users just
how easily you can get that archived data back for them. When they see
that it can be available in just a couple of hours, that should reduce a
lot of their fears.

And the new addition is doing fine, thank you very much. He just turned
one month old yesterday, and has developed a nasty habit -- being wide
awake from about 9 to 11 at night, just when dad likes to do his own R&R...

Good luck.

Scott
===============================================================
Scott M. Dulecki /* 1998061901 */ +1 616 975 6322
Product Manager scott_dulecki@qad.com
QAD, Inc. http://www.qad.com
1188 East Paris SE Grand Rapids, MI 49546 USA

Next Michigan Progress Users Group: 19 January 2000

All opinions are my own, and don't necessarily reflect those of
any other living being.
===============================================================

 

[Chris Kelleher]

One question I have always had regarding Delete/Archive is :

If you archive the data in Version 7.X of QAD in a ASCII file few years back(say
1996)
and have a need to restore that information NOW. (i.e. Current Year 1999) with a
new version of QAD (say 9.0).
What strategy should be considered. As database schema changes are more than
likely to occur in between Versions.

and the data dumped in Version 7.X might not load properly in currect QAD
version(9.0) because of some additionnal fields added to different archived
tables. If the fields were added always at the end of the tables then I think
there should not be a problem loading the OLD data.


Any thoughts or suggestions.

thanks


Praveen

 

[Chris Kelleher]

As a rule, during every upgrade, we load the archive in an MFG empty Old
Version. Convert this to the newer version & Archive it with the newer
version. This add's a task to the conversion task-list, but it save's a lot
of head-aches for us.

My 2 cents,

Sanju

 

[Chris Kelleher]

Praveen,

You identify a potential issue. The simplest (i.e. least possibility of
error) approach is to reload the archived data to another database, convert
the database, and run the archive again.

The issue with schema changes is not so much in the addition of fields as
it is in the removal of fields. Progress uses a counter internally to keep
track of the fields in a table. If I have five fields (tracked as 1, 2, 3,
4, 5) and I add a sixth field, that field is placed at the end. If I then
remove field 3 and add field 7, the order will now be 1, 2, 7, 4, 5, 6 -- a
real problem if I try to load data from back when it was 1, 2, 3, 4, 5. By
reloading and converting, the data would now be archived according to the
new layout -- problem solved.

You could, of course, examine the _file and _field records for each data
file that you've archived, checking to see if this kind of problem exists,
and modify the archive file appropriately so that it would load, but I
offer that it's a lot less work (and more likely to be successful) if you
simply reload, convert, and rearchive.

As for the practices that QAD takes, we try not to remove fields from the
database, but occasionally we do. We do add fields, and provided there
isn't a gap from a previously removed field, the new field is added at the
end of the record. However, neither I nor anyone else at QAD can
*guarantee* you that a new field will be added at the end...

Good luck.

Scott
===============================================================
Scott M. Dulecki /* 1998061901 */ +1 616 975 6322
Product Manager scott_dulecki@qad.com
QAD, Inc. http://www.qad.com
1188 East Paris SE Grand Rapids, MI 49546 USA

Next Michigan Progress Users Group: 19 January 2000

All opinions are my own, and don't necessarily reflect those of
any other living being.
===============================================================

 

[Chris Kelleher]

Hi Scott:

My understaning was if QAD decides to stop using the field then they put in
make pt_mtl_ll to be pt_xmtl_ll to preserve the Progress Internal Order. But
as you mentioned the following >>>

>As for the practices that QAD takes, we try not to remove fields from the
>database, but occasionally we do. We do add fields, and provided there
>isn't a gap from a previously removed field, the new field is added at the
>end of the record. However, neither I nor anyone else at QAD can
>*guarantee* you that a new field will be added at the end...

Than The only choice is to keep the Archived QAD Version CD on hand as well as
access to the Archived Database.

The steps will be:

A. Make a copy of the Orignal Archived DB to a dummy DB
A. Reload the archived data from ASCII file into the dummy Database
B. Convert the Dummy DB to the most recent QAD version.
C. Archive the same data again from this dummy DB
D. Load the archived data from step C into the Most recent QAD DB


I know this is off the topic but I just wanted to clarify things in my mind.

thanks for all the help.

praveen