DB View over Work Center Messages

[DaveWagoner]

WOW that is good stuff you came up with. I'm going to have to come back to it when I have a few mins and some brain cells remaining

 

[AttilaP]

I now received feedback from the DBA team on how to retrieve the full subject (thanks Jörg!).
Turned out the same ZMZMPI is the key in F9203 (which was actually in the same library as the F00165)
Updated & extended SQL is:

CREATE OR REPLACE VARIABLE <ACTUALLIB>.V_LANGCODE VARCHAR(1) DEFAULT '';

SELECT DATE(CHAR(F.ZZUPMJ+1900000)) AS PROCESS_DATE,
       SUBSTR(LPAD(F.ZZUPMT,6,'0'),1,2) ||':'||SUBSTR(LPAD(F.ZZUPMT,6,'0'),3,2) ||':'||RIGHT(F.ZZUPMT,2) AS PROCESS_TIME,
       SUBSTR(F.ZZTSV,1,INSTR(F.ZZTSV,'|')-1) AS JOB_NAME,
       SUBSTR(F.ZZTSV,INSTR(F.ZZTSV,'|')+1,INSTR(F.ZZTSV,'|',INSTR(F.ZZTSV,'|')+1)-INSTR(F.ZZTSV,'|')-1) AS JOB_VERSION,
       S.FRDSCA AS SUBJECT,
       A.ABALPH AS USER,
       D.GDTXFT AS ERROR_DETAILS
FROM F01131M AS M
     LEFT JOIN F01131 AS F ON M.ZMPPSRK = F.ZZSERK
     LEFT JOIN <YOURLIB>.F00165 AS D ON D.GDOBNM='GT92002' AND D.GDTXKY= TRIM(M.ZMTMPI)||'|'||<ACTUALLIB>.V_LANGCODE||'||'
     LEFT JOIN <YOURLIB>.F9203 AS S ON S.FRDTAI = TRIM(M.ZMTMPI) AND S.FRLNGP = <ACTUALLIB>.V_LANGCODE
     LEFT JOIN <SYSLIB>.F0092 AS U ON U.ULUSER = F.ZZUSER
     LEFT JOIN F0101 AS A ON A.ABAN8 = U.ULAN8
WHERE M.ZMPPSRK IN (SELECT MAX(ZMPSRK) FROM F01131M WHERE ZMTSV='<YOUR_EDOC_GOES_HERE>');

DROP VARIABLE <ACTUALLIB>.V_LANGCODE;

ACTUALLIB is where F01131 is - I have to run this query on an old AS/400 iSeries. You can set the preferred language via DEFAULT - e.g. DEFAULT 'F' for french (client dependant!)
YOURLIB is where you found the F00165 and F9203 with matching rows to ZZTMPI, see my previous post.
SYSLIB is where you have the user table, you can also use the query I provided in the last post to search for a table in all libraries

Result

PROCESS_DATE	PROCESS_TIME	JOB_NAME	JOB_VERSION	SUBJECT	USER	ERROR_DETAILS
2023-01-03	09:57:37	R47011	ABC47011	Order &1 Contains Errors	John Doe	Cause . . . . . Order &1 contains errors. Resolution. . Review the detail errors for Order &1 .
2023-01-03	09:57:37	R47011	ABC47011	ERROR - Customer Inhibited from Batch	John Doe	Cause . . . . . The customer is inhibited from batch processing. Resolution. . . Go to Billing Instructions and change the Batch Processing Mode for this customer, or use a different customer.

 

[ybuffetaud]

Hi I usually find intel with this SQL on oracle :
select B.JCUSER as "Utilisateur",A.JCPID as "Programme",A.JCVERS as "Version",A.JCSTDTIM as "Heure_Debut",A.JCETDTIM as "Heure_Fin",B.JCJOBQUE as "File",B.JCJOBSTS as "Statut",B.JCJOBNBR as "Numero_Job",C.ZZSERK as "Index_Maitre",C.ZZMSGP as "Message_Maitre",D.ZMPSRK as "Message_Pere",D.ZMSERK as "Message_Fils",D.ZMLV as "Niveau",D.ZMTSV as "Champ",D.ZMTMPI as "DD_Erreur",D.ZMMSGP as "Texte_message_1",UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(GDTXFT)) as "Texte_message_2",D.ZMEAP as "Ecran_Erreur",D.ZMFMNM as "SS_ECRAN_ERREUR",D.ZMFCTNM as "BSFN_Erreur",D.ZMLNBR as "Ligne_BSFN"
from svm920.f986110 B,svm920.f986114 A,proddta.f01131 C,proddta.f01131m D,dd920.F00165
where B.JCJOBNBR=3031114
and A.JCJOBNBR=B.JCJOBNBR
and concat(trim(A.jcpid),concat('|',concat(trim(A.jcvers),'|0|')))= C.ZZTSV and C.ZZUSER=B.JCUSER AND C.ZZUPMJ=B.JCSBMDATE and C.ZZUPMT>=B.JCSBMTIME and C.ZZUPMT<=B.JCACTTIME
and D.ZMPPSRK=C.ZZSERK
and gdobnm='GT92002' and GDTXKY =trim(D.zmtmpi)||'|F||' --change your desired language for translations
order by C.ZZSERK,D.ZMSERK,D.ZMLV;

 

[AttilaP]

Hi I usually find intel with this SQL on oracle :
select B.JCUSER as "Utilisateur",A.JCPID as "Programme",A.JCVERS as "Version",A.JCSTDTIM as "Heure_Debut",A.JCETDTIM as "Heure_Fin",B.JCJOBQUE as "File",B.JCJOBSTS as "Statut",B.JCJOBNBR as "Numero_Job",C.ZZSERK as "Index_Maitre",C.ZZMSGP as "Message_Maitre",D.ZMPSRK as "Message_Pere",D.ZMSERK as "Message_Fils",D.ZMLV as "Niveau",D.ZMTSV as "Champ",D.ZMTMPI as "DD_Erreur",D.ZMMSGP as "Texte_message_1",UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(GDTXFT)) as "Texte_message_2",D.ZMEAP as "Ecran_Erreur",D.ZMFMNM as "SS_ECRAN_ERREUR",D.ZMFCTNM as "BSFN_Erreur",D.ZMLNBR as "Ligne_BSFN"
from svm920.f986110 B,svm920.f986114 A,proddta.f01131 C,proddta.f01131m D,dd920.F00165
where B.JCJOBNBR=3031114
and A.JCJOBNBR=B.JCJOBNBR
and concat(trim(A.jcpid),concat('|',concat(trim(A.jcvers),'|0|')))= C.ZZTSV and C.ZZUSER=B.JCUSER AND C.ZZUPMJ=B.JCSBMDATE and C.ZZUPMT>=B.JCSBMTIME and C.ZZUPMT<=B.JCACTTIME
and D.ZMPPSRK=C.ZZSERK
and gdobnm='GT92002' and GDTXKY =trim(D.zmtmpi)||'|F||' --change your desired language for translations
order by C.ZZSERK,D.ZMSERK,D.ZMLV;

It is fine when you know the job number. I had to search by the EDOC number.
Previous post had a bug - now I use a recursive CTE to retrieve all lines of F01131M:

WITH MSG(ZMPSRK,ZMSERK) AS (SELECT ZMPSRK,ZMSERK FROM F01131M WHERE F01131M.ZMPPSRK=(SELECT MAX(ZMPSRK) FROM F01131M WHERE ZMTSV='<YOUR_EDOC>') AND F01131M.ZMTSV='<YOUR_EDOC>'
UNION ALL
SELECT M.ZMPSRK,M.ZMSERK FROM MSG,F01131M AS M WHERE M.ZMPSRK = MSG.ZMSERK)

SELECT ZMLV AS INDENT,
    DATE(CHAR(F.ZZUPMJ+1900000)) AS PROCESS_DATE,
    SUBSTR(LPAD(F.ZZUPMT,6,'0'),1,2) ||':'||SUBSTR(LPAD(F.ZZUPMT,6,'0'),3,2) ||':'||RIGHT(F.ZZUPMT,2) AS PROCESS_TIME,
    SUBSTR(F.ZZTSV,1,INSTR(F.ZZTSV,'|')-1) AS JOB_NAME,
    SUBSTR(F.ZZTSV,INSTR(F.ZZTSV,'|')+1,INSTR(F.ZZTSV,'|',INSTR(F.ZZTSV,'|')+1)-INSTR(F.ZZTSV,'|')-1) AS JOB_VERSION,
    S.FRDSCA AS SUBJECT,
    A.ABALPH AS USERNAME,
    D.GDTXFT AS ERROR_DETAILS,
    M.ZMTSV AS VARIABLES
FROM F01131M AS M
    LEFT JOIN F01131 AS F ON M.ZMPPSRK = F.ZZSERK
    LEFT JOIN <YOURLIB>.F00165 AS D ON D.GDOBNM='GT92002' AND D.GDTXKY= TRIM(M.ZMTMPI)||'|||'
    LEFT JOIN <YOURLIB>.F9203 AS S ON S.FRDTAI = TRIM(M.ZMTMPI) AND S.FRLNGP = ''
    LEFT JOIN <SYSLIB>.F0092 AS U ON U.ULUSER = F.ZZUSER
    LEFT JOIN F0101 AS A ON A.ABAN8 = U.ULAN8
WHERE M.ZMSERK IN (SELECT ZMSERK FROM MSG)
ORDER BY ZMSERK

 

[DaveWagoner]

Hi I usually find intel with this SQL on oracle :
select B.JCUSER as "Utilisateur",A.JCPID as "Programme",A.JCVERS as "Version",A.JCSTDTIM as "Heure_Debut",A.JCETDTIM as "Heure_Fin",B.JCJOBQUE as "File",B.JCJOBSTS as "Statut",B.JCJOBNBR as "Numero_Job",C.ZZSERK as "Index_Maitre",C.ZZMSGP as "Message_Maitre",D.ZMPSRK as "Message_Pere",D.ZMSERK as "Message_Fils",D.ZMLV as "Niveau",D.ZMTSV as "Champ",D.ZMTMPI as "DD_Erreur",D.ZMMSGP as "Texte_message_1",UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(GDTXFT)) as "Texte_message_2",D.ZMEAP as "Ecran_Erreur",D.ZMFMNM as "SS_ECRAN_ERREUR",D.ZMFCTNM as "BSFN_Erreur",D.ZMLNBR as "Ligne_BSFN"
from svm920.f986110 B,svm920.f986114 A,proddta.f01131 C,proddta.f01131m D,dd920.F00165
where B.JCJOBNBR=3031114
and A.JCJOBNBR=B.JCJOBNBR
and concat(trim(A.jcpid),concat('|',concat(trim(A.jcvers),'|0|')))= C.ZZTSV and C.ZZUSER=B.JCUSER AND C.ZZUPMJ=B.JCSBMDATE and C.ZZUPMT>=B.JCSBMTIME and C.ZZUPMT<=B.JCACTTIME
and D.ZMPPSRK=C.ZZSERK
and gdobnm='GT92002' and GDTXKY =trim(D.zmtmpi)||'|F||' --change your desired language for translations
order by C.ZZSERK,D.ZMSERK,D.ZMLV;

Thanks so much! I formatted it using poor man's t-sql for my own sake

SELECT B.JCUSER AS "Utilisateur"
    ,A.JCPID AS "Programme"
    ,A.JCVERS AS "Version"
    ,A.JCSTDTIM AS "Heure_Debut"
    ,A.JCETDTIM AS "Heure_Fin"
    ,B.JCJOBQUE AS "File"
    ,B.JCJOBSTS AS "Statut"
    ,B.JCJOBNBR AS "Numero_Job"
    ,C.ZZSERK AS "Index_Maitre"
    ,C.ZZMSGP AS "Message_Maitre"
    ,D.ZMPSRK AS "Message_Pere"
    ,D.ZMSERK AS "Message_Fils"
    ,D.ZMLV AS "Niveau"
    ,D.ZMTSV AS "Champ"
    ,D.ZMTMPI AS "DD_Erreur"
    ,D.ZMMSGP AS "Texte_message_1"
    ,UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(GDTXFT)) AS "Texte_message_2"
    ,D.ZMEAP AS "Ecran_Erreur"
    ,D.ZMFMNM AS "SS_ECRAN_ERREUR"
    ,D.ZMFCTNM AS "BSFN_Erreur"
    ,D.ZMLNBR AS "Ligne_BSFN"
FROM svm920.f986110 B
    ,svm920.f986114 A
    ,proddta.f01131 C
    ,proddta.f01131m D
    ,dd920.F00165
WHERE B.JCJOBNBR = 3031114
    AND A.JCJOBNBR = B.JCJOBNBR
    AND CONCAT (
        trim(A.jcpid)
        ,CONCAT (
            '|'
            ,CONCAT (
                trim(A.jcvers)
                ,'|0|'
                )
            )
        ) = C.ZZTSV
    AND C.ZZUSER = B.JCUSER
    AND C.ZZUPMJ = B.JCSBMDATE
    AND C.ZZUPMT >= B.JCSBMTIME
    AND C.ZZUPMT <= B.JCACTTIME
    AND D.ZMPPSRK = C.ZZSERK
    AND gdobnm = 'GT92002'
    AND GDTXKY = trim(D.zmtmpi) || '|F||' --change your desired language for translations
ORDER BY C.ZZSERK
    ,D.ZMSERK
    ,D.ZMLV