Author Topic: determine oldest doc in an app group?  (Read 1343 times)

jsquizz

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 572
    • View Profile
determine oldest doc in an app group?
« 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.
« Last Edit: February 24, 2021, 07:24:40 PM by jsquizz »
#CMOD #DB2 #AFP2PDF #TSM #AIX #RHEL #AWS #AZURE #GCP #EVERYTHING

Stephen McNulty

  • Jr. Member
  • **
  • Posts: 57
    • View Profile
Re: determine oldest doc in an app group?
« Reply #1 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.
#ISERIES #ODWEK #XML

Justin Derrick

  • IBM Content Manager OnDemand Consultant
  • Administrator
  • Hero Member
  • *****
  • Posts: 2228
  • CMOD Guru for hire...
    • View Profile
    • Tenacious Consulting
Re: determine oldest doc in an app group?
« Reply #2 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.

IBM CMOD Professional Services: http://TenaciousConsulting.com
Call:  +1-866-533-7742  or  eMail:  jd@justinderrick.com
IBM CMOD Wiki:  https://CMOD.wiki/
FREE IBM CMOD Education & Webinars:  https://CMOD.Training/

Interests: #AIX #Linux #Multiplatforms #DB2 #TSM #SP #Performance #Security #Audits #Customizing #Availability #HA #DR

Norbert Novotny

  • Jr. Member
  • **
  • Posts: 46
    • View Profile
Re: determine oldest doc in an app group?
« Reply #3 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.
Norbert Novotny
Legal archiving - Swisscom AG

Mobile:  +41-On-request

Dev: #SQL, #Perl, #Java, #C

Interests: #CMOD, #Multiplatforms, #DB2, #Oracle, #TSM, #ERM, #Performance