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 progressSorry 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