This SQL query of the UC_SVALM table lists the sub-fields of JBA_Rest for SAP jobs, and the corresponding SAP job attributes.
-- Show JBA_Rest sub-field IDs and corresponding attribute names for SAP R3 jobs
select SVALM_RestID,SVALM_Attname
from UC_SVALM
where SVALM_Otype = 'JOBS'
AND SVALM_ATTDBFNAME = 'jba_rest'
and SVALM_HostAttrType = 'R3'
order by SVALM_RestID;
SVALM_RestID |
SVALM_Attname |
1 |
SAP_JOB_TYPE |
9 |
JOB_NAME |
9 |
JN |
A |
SAP_LANG |
B |
SAP_DST_SYSTEM |
C |
JC |
C |
JOB_CLASS |
E |
SAP_JOB_DELETE |
I |
SAP_STARTMODE |
L |
SAP_OVERRIDE_LOGIN |
X |
SAP_NOPRINT |
Y |
SAP_DELIVER |
Z |
SAP_STATUSBYMAIL |
e |
SAP_JOB_DEL_NOT_ON_ERROR |
u |
SAP_RECIPIENT |
v |
SAP_ADDRESSTYPE |
w |
SAP_EXPRESS |
x |
SAP_COPY |
y |
SAP_BLINDCOPY |
z |
SAP_NOFORWARD |
Using the data returned by the above query, one can decipher the different values of JBA_Rest of SAP R3 jobs. (JBA_Rest stores job attributes in the JBA table.) The query below displays this information for SAP R3 jobs.
-- List attributes of SAP R3 jobs
SELECT OH_Name, OH_Title, OH_Idnr, JBA_HostDst, JBA_LoginDst, JBA_Rest
FROM OH JOIN JBA ON JBA_OH_Idnr = OH_Idnr
WHERE OH_DeleteFlag = 0
AND OH_HostAttrTypeDst = 'R3'
AND JBA_HostAttrType = 'R3'
ORDER BY OH_Idnr
Building on the above understanding of the meanings of the JBA_Rest sub-fields, one can use REGEXP_SUBSTR to extract the values of these sub-fields into dedicated columns.
-- List attributes of SAP R3 jobs including sub-fields of JBA_Rest
SELECT OH_Name, OH_Title, OH_Idnr, JBA_HostDst, JBA_LoginDst,
REGEXP_SUBSTR(JBA_Rest, '(1)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_JOB_TYPE",
REGEXP_SUBSTR(JBA_Rest, '(9)=['']([^'']*)['']', 1, 1, 'c', 2) AS "JOB_NAME",
REGEXP_SUBSTR(JBA_Rest, '(A)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_LANG",
REGEXP_SUBSTR(JBA_Rest, '(B)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_DST_SYSTEM",
REGEXP_SUBSTR(JBA_Rest, '(C)=['']([^'']*)['']', 1, 1, 'c', 2) AS "JOB_CLASS",
REGEXP_SUBSTR(JBA_Rest, '(E)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_JOB_DELETE",
REGEXP_SUBSTR(JBA_Rest, '(I)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_STARTMODE",
REGEXP_SUBSTR(JBA_Rest, '(L)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_OVERRIDE_LOGIN",
REGEXP_SUBSTR(JBA_Rest, '(X)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_NOPRINT",
REGEXP_SUBSTR(JBA_Rest, '(Y)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_DELIVER",
REGEXP_SUBSTR(JBA_Rest, '(Z)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_STATUSBYMAIL",
REGEXP_SUBSTR(JBA_Rest, '(e)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_JOB_DEL_NOT_ON_ERROR",
REGEXP_SUBSTR(JBA_Rest, '(u)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_RECIPIENT",
REGEXP_SUBSTR(JBA_Rest, '(v)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_ADDRESSTYPE",
REGEXP_SUBSTR(JBA_Rest, '(w)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_EXPRESS",
REGEXP_SUBSTR(JBA_Rest, '(x)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_COPY",
REGEXP_SUBSTR(JBA_Rest, '(y)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_BLINDCOPY",
REGEXP_SUBSTR(JBA_Rest, '(z)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_NOFORWARD"
FROM OH JOIN JBA ON JBA_OH_Idnr = OH_Idnr
WHERE OH_DeleteFlag = 0
AND OH_HostAttrTypeDst = 'R3'
AND JBA_HostAttrType = 'R3'
ORDER BY OH_Idnr
This approach should also work with other job types that store attributes in JBA_Rest.
Next, I have a question. Hopefully, someone at @Broadcom Community can answer.
I'm looking for a way to distinguish between the different sub-types of SAP job: SAP_ABAP, SAP_JAVA, and SAP_PI.
The UC_SVALM entry with SVALM_RestID 1 has the SVALM_AttName SAP_JOB_TYPE, but the value of sub-field 1 in JBA_Rest looks the same ('0') for all our SAP jobs, regardless of the SAP job sub-type.
How does the AE distinguish jobs of type SAP_ABAP, SAP_JAVA, and SAP_PI? Where is this information stored in the database?