OnDemand User Group

Support Forums => MP Server => Topic started by: jsquizz on February 24, 2021, 05:24:30 PM

Title: determine oldest doc in an app group?
Post by: jsquizz on February 24, 2021, 05:24:30 PM

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]

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]

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

Title: Re: determine oldest doc in an app group?
Post by: Stephen McNulty on February 25, 2021, 06:20:03 AM

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.

Title: Re: determine oldest doc in an app group?
Post by: Justin Derrick on February 25, 2021, 07:49:20 AM

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.

Title: Re: determine oldest doc in an app group?
Post by: Norbert Novotny on March 03, 2021, 02:56:13 PM

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]

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.