I have about 2k app groups, need to figure out the oldest record per application group.
I'm looking through the tables -> arsag caught my eye, more specifically 'last_doc_dt'
I ran a simple query, and it's coming back blank.
LAST_DOC_DT
--------------------------
-
1 record(s) selected.
Is there anything on lets say, arsseg I can also try? I was also thinking I could use arsseg table.
START_DT TIMESTAMP The minimum (oldest) date of documents stored in this folder, in database-native timestamp format.
It might be possible to query the System Load tables SA%..., using the the APPLGRP_NAME and LOAD_START_DT fields.
this assumes all your loads were done after the load table was introduced.
Choose the OnDemand database segment from CMOD's arsseg table with the earliest start date, then search for the minimum value on the segment date field.
As far as I know, the start and stop date fields in the segment table aren't updated as part of expiration processing, so the date in the segment table may not be accurate.
-JD.
IMHO, there is more to the question; how you define "old" segment date field value could be back dated e.g. inserted (loaded) today with a date of 10 years back.
However, if you are after e.g. the first document (rather load) ever loaded in this ag, then I would:
a. search for an oldest segment in ARSSEG where agid = ...
b. select the smallest doc_name or better to get min(loadid) example: min(int(left(doc_name,locate(trim(translate(doc_name,'','0123456789')),doc_name)-1))) than look for min doc_off, but that's not relevant
:)
N.