OnDemand User Group
Tips and Tricks => Tips and Tricks => Topic started by: Johan Dahlgren on May 29, 2020, 01:06:41 AM
-
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?
-
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
-
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
-
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
-
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.
-
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
-
There may be multiple System Load tables... SA2, SA3, etc. You may need to search more than just one.
-JD.
-
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
-
Not hidden. If SA2 is all you got, then that's all you have (so far).
Ed
-
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
-
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.
-
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
-
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/...
-
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.
-
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.
$ 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
-
I've switched to an AG table that will result in multiple rows being returned. I'm basically getting the same error as you, with everything I try ending in the error 'Result of SELECT more than one row.' I'll keep looking at it and see if I can find a solution.