Author Topic: Is there a way to find out the load date for a specific document in ondmeand?  (Read 5599 times)

Johan Dahlgren

  • Jr. Member
  • **
  • Posts: 12
    • View Profile
Hello,

I have a problem with dates in our ondemand. We have documents that we need to apply rules to according to their age, unfortunately some of the documents are missing this information. The date value is set to "-1568" for about 30 000 of the documents. My question is if there is a way to find out when these documents were loaded into Ondemand and use that date instead?

I have the following example.

Our application group from the table ARSAG
NAME                       AGID  AGID_NAME
Application group 1   6045   UGA

DB2 describe of table UGA1
Column name     Data type schema    Column Data type name   Length  Scale   Nulls
VALUE_1         SYSIBM              CHARACTER               12      0       No
DATE            SYSIBM              SMALLINT                2       0       No
VALUE_2         SYSIBM              CHARACTER               4       0       No
DOC_NAME        SYSIBM              VARCHAR                 11      0       No
DOC_OFF         SYSIBM              INTEGER                 4       0       No
DOC_LEN         SYSIBM              INTEGER                 4       0       No
COMP_OFF        SYSIBM              INTEGER                 4       0       No
COMP_LEN        SYSIBM              INTEGER                 4       0       No
ANNOT           SYSIBM              CHARACTER               1       0       No
COMP_TYPE       SYSIBM              CHARACTER               1       0       No
RESOURCE        SYSIBM              INTEGER                 4       0       No
PRI_NID         SYSIBM              SMALLINT                2       0       No
SEC_NID         SYSIBM              SMALLINT                2       0       No

I was hoping there was a way to connect the DOC_NAME to the NAME in ARSLOAD and get the date from there but I cant seem to find the correct NAME and AGID to correlate with the DOC_NAME in my table UGA1.

So in short, is there  a way to find out when a specific document in my UGA1 table was loaded?

Mehmet S Yersel

  • Jr. Member
  • **
  • Posts: 56
    • View Profile
    • LinkedIn Profile
System Load has that information and it retrieves it from ARSLOAD table. You can probably build a join between ARSLOAD table and your segment table.

Link to ARSLOAD table details: https://www.ibm.com/support/knowledgecenter/en/SSEPCD_10.5.0/com.ibm.ondemand.administeringmp.doc/dodsc018.htm

I hope this helps,
Mehmet
#zOS #Multiplatforms
#DB2 #OAM
#AFP #RiCOH AFP2PDF #SnowBound
#Finance #Telecom #Airlines
#ICN #IHS #WAS ND #Cert and Key Management
#Migrations #Data Modeling #RACF-2-CMOD Synch
#FileTek AMMO #ABI #RMDS #RADAR

jsquizz

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 573
    • View Profile
what about using the system log? Can you view the document in the thick client?

Right click, take the partial load ID and search for it in the message field in the system log folder, along with msg num 88
#CMOD #DB2 #AFP2PDF #TSM #AIX #RHEL #AWS #AZURE #GCP #EVERYTHING

Johan Dahlgren

  • Jr. Member
  • **
  • Posts: 12
    • View Profile
System Load has that information and it retrieves it from ARSLOAD table. You can probably build a join between ARSLOAD table and your segment table.

Link to ARSLOAD table details: https://www.ibm.com/support/knowledgecenter/en/SSEPCD_10.5.0/com.ibm.ondemand.administeringmp.doc/dodsc018.htm

I hope this helps,
Mehmet

Hello Mehmet

I have been trying to get my head around how to join the tables as to give me the load date for the document but cant seem to find the correct correlation. Any help here would be appriciated.

I have tried to connect the tables using the DOC_NAME from my document storage table, in the example below UGA1. With the NAME in the ARSLOAD table but doing that i find many many results so I have to narrow it down a bit. I was then thinkig I could use the AGID from ARSAG to find the load corresponding to the document loaded in my UGA1 table but then I get no results.

Can i be that the ARSLOAD is incomplete?

what about using the system log? Can you view the document in the thick client?

Right click, take the partial load ID and search for it in the message field in the system log folder, along with msg num 88

Hello Jsquizz,

I can try it to see if I can find the load date for one document. Unfortunately I need to do this for thousands of documents missing the date so I really need a less manual way of doing it.

//Johan

Darrell Bryant

  • Full Member
  • ***
  • Posts: 104
  • Sed fugit interea fugit inreparabile tempus-Virgil
    • View Profile
Here's an SQL that works for me. I'm sure some adaptation will be needed for your circumstances. Because you know the AGID Name, UGA, you can look up the AGID from the ARSAG table. In my example, my AGID Name is LNJD and my AGID is 93322. My instance name is ONDENU. The DOC_NAME column in the AG table requires the last character to be removed so that doc name 1FAAA becomes 1FAA, which is the correct name for the load ID.

SELECT LOAD_TIME_DT, LOAD_ID FROM ONDENU.SA2 WHERE LOAD_ID LIKE
(SELECT DISTINCT '93322-' || PRI_NID || '-'|| SEC_NID || '-' ||
LEFT(DOC_NAME, LENGTH(TRIM(DOC_NAME))-1) || '%'               
FROM ONDENU.LNJD1  WHERE RPT_DATE = 0)                         

The output in my example is:
LOAD_TIME_DT                LOAD_ID                                           
2020-03-31-13.48.48.087472  93322-2-0-1FAA-19940110000000-19940110000000-93347


I hope this helps.
#IBMi #iSeries #PDF #XML #400 Indexer #ASM

Johan Dahlgren

  • Jr. Member
  • **
  • Posts: 12
    • View Profile
Here's an SQL that works for me. I'm sure some adaptation will be needed for your circumstances. Because you know the AGID Name, UGA, you can look up the AGID from the ARSAG table. In my example, my AGID Name is LNJD and my AGID is 93322. My instance name is ONDENU. The DOC_NAME column in the AG table requires the last character to be removed so that doc name 1FAAA becomes 1FAA, which is the correct name for the load ID.

SELECT LOAD_TIME_DT, LOAD_ID FROM ONDENU.SA2 WHERE LOAD_ID LIKE
(SELECT DISTINCT '93322-' || PRI_NID || '-'|| SEC_NID || '-' ||
LEFT(DOC_NAME, LENGTH(TRIM(DOC_NAME))-1) || '%'               
FROM ONDENU.LNJD1  WHERE RPT_DATE = 0)                         

The output in my example is:
LOAD_TIME_DT                LOAD_ID                                           
2020-03-31-13.48.48.087472  93322-2-0-1FAA-19940110000000-19940110000000-93347


I hope this helps.

Great information, I've tried it in my OnDemand setup but it complains about my version of the SQL. Not sure if I missunderstan some things or if we have different setups?

This is the SQL i ran, changed to fit our names for things

SELECT LOAD_TIME_DT, LOAD_ID FROM ARSAG WHERE LOAD_ID LIKE (SELECT DISTINCT '6045-' || PRI_NID || '-'|| SEC_NID || '-' || LEFT(DOC_NAME, LENGTH(TRIM(DOC_NAME))-1) || '%' FROM UGA1  WHERE BESLUTSDATUM < 1)
SQL0206N  "LOAD_TIME_DT" is not valid in the context where it is used.
SQLSTATE=42703

i am not sure if your ONDENU.SA2 is ARSAG table or if I missunderstood here cause i have no "LOAD_TIME_DT" in either ARSLOAD or ARSAG. 6045 is my AGID.

Nevermind, just there is an actual table called SA2 that I have not seen before.

Will continue trying to figure this out, will return to report on progress

Sorry for spamming the changes here, finally figured it out but unfortunately there is no hits when I run the SQL. If i separate the SQLs they seem to find what I am looking for from the UGA1 table but it does not match anything in the SA2 table. Has it been removed or am i still doing something wrong?

my runs an their results:
$ db2 "select NAME, AGID, AGID_NAME from ARSAG where NAME like 'BB-%'"

NAME                                                         AGID        AGID_NAME
------------------------------------------------------------ ----------- ---------
BB-BESLUT                                                           6045 UGA

  1 record(s) selected.

$ db2 "select distinct '6045-' || PRI_NID||'-' || SEC_NID || '-' || LEFT(DOC_NAME,LENGTH(TRIM(DOC_NAME))-1) ||'%' FROM UGA1 where BESLUTSDATUM < 1"

1
-------------------------------
6045-19-0-4066FAA%

  1 record(s) selected.

$ db2 "select LOAD_ID from SA2 where LOAD_ID like '6045-19-0-4066FAA%'"

LOAD_ID
----------------------------------------------------------------------------------------------------

  0 record(s) selected.




//Johan D
« Last Edit: June 04, 2020, 05:50:42 AM by Johan Dahlgren »

Justin Derrick

  • IBM Content Manager OnDemand Consultant
  • Administrator
  • Hero Member
  • *****
  • Posts: 2228
  • CMOD Guru for hire...
    • View Profile
    • Tenacious Consulting
There may be multiple System Load tables...  SA2, SA3, etc.  You may need to search more than just one. 

-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

Johan Dahlgren

  • Jr. Member
  • **
  • Posts: 12
    • View Profile
There may be multiple System Load tables...  SA2, SA3, etc.  You may need to search more than just one. 

-JD.

Only seem to have one table named SA2 or are they hidden somehow

$ db2 list tables |grep SA
ARSAG                           ARCHIVE         T     2017-05-27-16.47.33.550405
ARSAG2FOL                       ARCHIVE         T     2017-05-27-16.47.33.653442
ARSAGFLD                        ARCHIVE         T     2017-05-27-16.47.33.733617
ARSAGFLDALIAS                   ARCHIVE         T     2017-05-27-16.47.33.815872
ARSAGINDEX                      ARCHIVE         T     2017-05-27-16.47.33.899852
ARSAGPERMS                      ARCHIVE         T     2017-05-27-16.47.33.986818
ARSANN                          ARCHIVE         T     2017-05-27-16.47.34.147018
ARSAPP                          ARCHIVE         T     2017-05-27-16.47.34.727632
ARSAPPUSR                       ARCHIVE         T     2017-05-27-16.47.35.053065
SA2                             ARCHIVE         T     2017-05-29-10.24.16.826301


//Johan D

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Not hidden.  If SA2 is all you got, then that's all you have (so far).

Ed
#zOS #ODF

Johan Dahlgren

  • Jr. Member
  • **
  • Posts: 12
    • View Profile
I've concluded that there are two scenarios for what I am doing.
a) I am doing somethings wrong since I cant find the load information about a document.
b) the load records are incomplete or have been deleted some time over the years

Is it possible that the AS2 table only stores the load information for a set time before removing the information? Some of the documents are very old (10+ years old)

//Johan D

Darrell Bryant

  • Full Member
  • ***
  • Posts: 104
  • Sed fugit interea fugit inreparabile tempus-Virgil
    • View Profile
The system load facility was introduced in 2010 at server level 8.4.1. However, on MP and z/OS, it was optional. Also optional was using timestamps. The SA2 table also contains a column, LOAD_TIME, which I assume is used if you don't turn on timestamps. My example is from OnDemand for i, where use of the system load facility, and the use of timestamps, was automatic, the administrator did not have to make a decision to turn them on.
#IBMi #iSeries #PDF #XML #400 Indexer #ASM

Johan Dahlgren

  • Jr. Member
  • **
  • Posts: 12
    • View Profile
Ohhhh that explains it Darrel. Thank you so mouch all of you for taking the time to look at my problem.

Our SA2 table only contains load data for the applications group of my documents from 2017 at the oldest. The document that are missing their dates are from 2006, the ones I have manually searched for and opened to read atleast.

Thank you all again for helping me sort this problem out. I can now conclude that this is not a viable option for us to find dates for the documents with missing dates.

// Johan D

Darrell Bryant

  • Full Member
  • ***
  • Posts: 104
  • Sed fugit interea fugit inreparabile tempus-Virgil
    • View Profile
You can also find that information from the system log, although that will probably be a lot more work. You probably have multiple system log tables, SL2, SL3, etc, and maybe made a change from the old 'time stamp' to the new timestamp data type at some point. The old 'time stamp' column is TIME_STAMP and the new timestamp column is TIME_STAMP_DT. Note the leading wildcard % on the SELECT DISTINCT statement as the load id is embedded in the message text.

The SQL I used with the SL2 table and TIME_STAMP_DT is:

SELECT TIME_STAMP_DT, MSG_TEXT FROM ONDENU.SL2 WHERE MSG_TEXT LIKE
(SELECT DISTINCT '%93322-' || PRI_NID || '-'|| SEC_NID || '-' || 
LEFT(DOC_NAME, LENGTH(TRIM(DOC_NAME))-1) || '%'                   
FROM ONDENU.LNJD1  WHERE RPT_DATE = 0) AND MSG_NUM = 87           

And the output is:

TIME_STAMP_DT               MSG_TEXT                                                                                               
2020-03-31-13.48.48.087472  Application Group Load: Name(TEST-LOAN) LoadId(93322-2-0-1FAA-19940110000000-19940110000000-93347) File(/tmp/...
« Last Edit: June 04, 2020, 07:34:47 AM by Darrell Bryant »
#IBMi #iSeries #PDF #XML #400 Indexer #ASM

Justin Derrick

  • IBM Content Manager OnDemand Consultant
  • Administrator
  • Hero Member
  • *****
  • Posts: 2228
  • CMOD Guru for hire...
    • View Profile
    • Tenacious Consulting
And for some insight into the old CMOD Date & Time formats:

https://cmod.wiki/index.php?title=Date_and_Time_formats_in_Content_Manager_OnDemand

-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

Johan Dahlgren

  • Jr. Member
  • **
  • Posts: 12
    • View Profile
My SQL knowledge is not enough for this problem it seems.

The query works fine if I only find one result in the sub-select. but for some document types (other tables) i get over 700 unique DOC_NAMES for documents that are missing dates.

If i run the SQL query with multiple results from the subselect i get an error.

Code: [Select]
$ db2 "SELECT TIME_STAMP, MSG_TEXT FROM SL2 WHERE MSG_TEXT LIKE
(SELECT DISTINCT '%6414-' || PRI_NID || '-'|| SEC_NID || '-' ||
LEFT(DOC_NAME, LENGTH(TRIM(DOC_NAME))-1) || '%'
FROM MLA1  WHERE UTBDATUM < '1') AND MSG_NUM = 87"

TIME_STAMP  MSG_TEXT                                                                                                                                                                             
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL0811N  The result of a scalar fullselect, SELECT INTO statement, or VALUES
INTO statement is more than one row.  SQLSTATE=21000

This is just with one of my 30+ MLAX tables and one of my about 100 SL tables...

//Johan D