determine oldest doc in an app group?

[jsquizz]

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.

Code Select Expand

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.

Code Select Expand

START_DT TIMESTAMP The minimum (oldest) date of documents stored in this folder, in database-native timestamp format.

[Stephen McNulty]

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.

[Justin Derrick]

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.

[nonix]

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:

Code Select Expand

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.