Re: IBM table function to break down fully qualified job name?

I find table functions kind of complicated. here are 3 sql functions.
job_jobName(), job_jobUser( ), job_jobNbr( ). my guess is that 3 sql
functions run faster than 1 table function.


select job_jobName( ) jobName, job_jobUser() jobUser,
job_jobNbr( ) jobnbr
from sysibm/sysdummy1 a

CREATE OR REPLACE FUNCTION job_jobName ( )
RETURNS char(10)
language cl
external name qgpl/core0033n1
parameter style SQL

/* core0033n1 - job_jobName sql function. */

PGM (&jobName &rtnNull &SqlState +
&function &specific &errmsg)

dcl &jobName *char 10
dcl &rtnNull *int 2
dcl &sqlstate *char 5
dcl &function *char 517
dcl &specific *char 130
dcl &errmsg *char 72

RTVJOBA JOB(&JOBNAME)

endpgm

CREATE OR REPLACE FUNCTION job_jobUser ( )
RETURNS char(10)
language cl
external name qgpl/core0033n3
parameter style SQL

/* core0033n3 - job_jobUser sql function. */

PGM (&jobUser &rtnNull &SqlState +
&function &specific &errmsg)

dcl &jobUser *char 10
dcl &rtnNull *int 2
dcl &sqlstate *char 5
dcl &function *char 517
dcl &specific *char 130
dcl &errmsg *char 72

RTVJOBA USER(&JOBUSER)

endpgm

CREATE OR REPLACE FUNCTION job_jobNbr ( )
RETURNS char(6)
language cl
external name qgpl/core0033n5
parameter style SQL

/* core0033n5 - job_jobNbr sql function. */

PGM (&jobNbr &rtnNull &SqlState +
&function &specific &errmsg)

dcl &jobNbr *char 6
dcl &rtnNull *int 2
dcl &sqlstate *char 5
dcl &function *char 517
dcl &specific *char 130
dcl &errmsg *char 72

RTVJOBA NBR(&JOBNBR)

endpgm



On Wed, May 20, 2020 at 11:06 AM Rob Berendt <rob@xxxxxxxxx> wrote:

Is there an IBM supplied table function to break down the fully qualified
job name, even if the job is completed? For example

Select
Job_number,
Job_user,
Job_name
From table(breakdown_qualified_job_name('*')) A
;

Or
Select
Job_number,
Job_user,
Job_name
From table(breakdown_qualified_job_name('123456/jobuser/jobname')) A