System load table, is that the one called SA in the database? in our case it is named SA2, not sure why there is no SA1 but could be it has been deleted since it is set to expire after 3650 days.
I got a bit of help from our Database Admin with an SQL to get a decent number calculated, here is an example of one wouch SQL query:
select x.name, x.agid, x.agid_name , AVG(BIGINT(x.decomp_size + x.DOC_LEN)) as AVG from (
select AG.NAME, AG.AGID, AG.AGID_NAME, RES.DECOMP_SIZE, DOC.DOC_LEN
from ARSRES as RES ,
ARSAG as AG ,(
select DOC_LEN,RESOURCE from QLA1 UNION ALL
select DOC_LEN,RESOURCE from QLA2 UNION ALL
select DOC_LEN,RESOURCE from QLA3 UNION ALL
select DOC_LEN,RESOURCE from QLA4 UNION ALL
select DOC_LEN,RESOURCE from QLA5
) as DOC
where AG.AGID=RES.AGID
and RES.RID=DOC.RESOURCE and AG.AGID_NAME='QLA'
) X where decomp_size is not null
group by x.NAME, x.AGID, x.AGID_NAME
It works well enough for us we think. This combined with a select count(*) gives us the aproximate size of the application group.
I thing i can try to describe an overview of our conversion process. Unfortunately no details since the data is sensetive.
Basically we have CM OnDemand that has been running for many years, think it was early 2000. Anyway it was old when i started 11 years ago.
It has been recieving documents of various kinds over these years, slowly adding and changing with no regard for GDPR or that the data eventually had to be removed och preserved for future statistics.
Start of our project, we were tasked with making a new document storage as well as moving and converting the old one.
What we are doing to migrate the documents.
1. We segment a folder search, usually one year worth of documents/folder, depending on the size of the folder (number of documents).
2. Write the AFP files (so far we only worked with the AFP format but we will have to migrate some TIF aswell in the future) to disk along with the metadata about each document. We also give each document a unique name for when we put it in the new storage. We also include all the available resources, because they will be needen later in the conversion to PDF-A/1b.
3. We now have all the information we can get we run two processes
A. Convert the file, this is done with AFP2PDF converter, it takes a bit of fiddeling with resources souch as font mapping and replacing missing peices of information with other resources. But we
got it to work fairly well.
B. Update the medatata about the document, this part is a bit sensetive but basically we update dates that are wrong or missing, customer personal id with internal customer ids instead and we
apply rules for when document should be erased/preserved.
4. Now most of the work is done and we package the updated metadata about the documents with the new PDF files and send it to the new server that inserts it all in to its database and storage.
That is the basic process, if you have any questions about some part of it I'll try to answer if I can.
EDIT: Unfortunately it seems that our SA table does not contain loads for all our application groups.
db2 "select count(*) from SA2 where LOAD_AGID=5863"
1
-----------
0
1 record(s) selected.And i have resources in ARSRES that have the AGID 5863 but alas no DECOMP_SIZE.
Show Posts