IBM i - Parse out the required data from a fully qualified job name

Parse Job Details.sql

--
-- Description: In many cases, IBM functions (or data that we have collected as customers/users) will provide
--              a fully qualified job identifier consisting of the job number, user profile, ad job name
--              formatted like '123456/USERNAME/JOBNAME'. Sometimes it is necessary to break this information
--              out into just the job number, or user, or job name.  
--              This "PARSE_JOB_DETAILS()" scalar function will do this.  Usage examples are provided further below.
--              The function accepts 2 parameters:
--                  1. The fully-qualified job
--                  2. The desired component (*JOBNUMBER, *JOBUSER, or *JOBNAME)
--              The function returns only the one requested component.
--

--Set the desired schema for creation of the function in your environment
SET SCHEMA MYLIB;

--Create the PARSE_JOB_DETAILS() function
CREATE OR REPLACE FUNCTION PARSE_JOB_DETAILS (
            FULLY_QUALIFIED_JOB CHAR(28), --this is a fully qualified job (i.e. 123456/USERNAME/JOBNAME)
            REQUESTED_DETAIL    CHAR(10)  --this is the requested value
    )
    RETURNS VARCHAR(10)
    LANGUAGE SQL
    NO EXTERNAL ACTION
    DETERMINISTIC
    BEGIN
        DECLARE SUBSTR_START INTEGER;
        DECLARE SUBSTR_LEN INTEGER;
        DECLARE RESPONSE_VALUE VARCHAR(10);
        CASE REQUESTED_DETAIL
            WHEN '*JOBNUMBER' THEN 
                SET SUBSTR_START = 1;
                SET SUBSTR_LEN = 6;
            WHEN '*JOBUSER' THEN 
                SET SUBSTR_START = 8;
                SET SUBSTR_LEN = LOCATE_IN_STRING(FULLY_QUALIFIED_JOB, '/', -1) - 8;
            WHEN '*JOBNAME' THEN 
                SET SUBSTR_START = LOCATE_IN_STRING(FULLY_QUALIFIED_JOB, '/', -1) + 1;
                SET SUBSTR_LEN = 10;
            ELSE RETURN 'BAD_PARM';
        END CASE;
        RETURN SUBSTR(FULLY_QUALIFIED_JOB, SUBSTR_START, SUBSTR_LEN);
    END;

VALUES MYLIB.PARSE_JOB_DETAILS('123456/QSECOFR/BATCHJOB', '*TRASH');     --returns BAD_PARM
VALUES MYLIB.PARSE_JOB_DETAILS('123456/QSECOFR/BATCHJOB', '*JOBNUMBER'); --returns 123456
VALUES MYLIB.PARSE_JOB_DETAILS('123456/QSECOFR/BATCHJOB', '*JOBUSER');   --returns QSECOFR
VALUES MYLIB.PARSE_JOB_DETAILS('123456/QSECOFR/BATCHJOB', '*JOBNAME');   --returns BATCHJOB

--This scalar function can also be used over data that you have collected in a table
--For this example, the field "QUALIFIED_JOB" in the table "MONITORJOB" contains a fully-qualified job identifier
SELECT QUALIFIED_JOB, 
       MYLIB.PARSE_JOB_DETAILS(QUALIFIED_JOB, '*JOBNUMBER') AS JOB_NUMBER
    FROM MONITORJOB;
--Resulting data:
-- QUALIFIED_JOB        	JOB_NUMBER
-- 847090/QSECOFR/MONITOR	847090
-- 881596/QSECOFR/MONITOR	881596
-- 838840/QSECOFR/MONITOR	838840
-- 830921/QSECOFR/MONITOR	830921
-- 825705/QSECOFR/MONITOR	825705