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

[Steve Richter <stephenrichter@xxxxxxxxx>]

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




>