Re: does SQL have access to any system info

[CRPence <CRPbottle@xxxxxxxxx>]

On 01 Feb 2013 07:47, Stone, Joel wrote:
> For example job # ?

  A simple CLP doing a RTVJOBA can effect that, using a parameter list 
declared to match the PARAMETER STYLE SQL for the CREATE FUNCTION of a 
scalar external UDF.  However sometimes a UDTF to get many different 
attributes in their own field is more valuable than making several 
different scalar functions.  There are articles that show such a [one 
row] UDTF; a web search on RTVJOBA and TABLE FUNCTION should yield examples.

  As Rob alluded the CLIENT special registers could be an option, but 
that would require that some prior SQL [or the client] had established 
those settings; e.g. the CLIENT ACCTNG could have been set to the job 
number previously, for that job\session.

  I suppose a system-supplied scalar UDF or UDTF might already exist to 
provide that information; poring over the documentation and\or searching 
the SYSPARMS and SYSFUNCS might assist to find.  But for that specific 
detail it is easy enough to just create your own; e.g.:

<code>

/* create function jobnbr() returns char(6)            */
/* language CL specific jobnbr  deterministic          */
/* no sql  returns null on null input                  */
/* disallow parallel  not fenced no external action    */
/* parameter style SQL                                 */
/* external name jobnbr                                */
pgm (&rtnval &rtnind &sqlste &udfnam &specnm &diagmg)
dcl &rtnval *char   6
dcl &rtnind *int    2
dcl &sqlste *char   5
dcl &udfnam *char 141
dcl &specnm *char 130
dcl &diagmg *char  72
main: /* no error handling; any error is unexpected  */
/* */ chgvar &rtnind 0
/* */ rtvjoba nbr(&rtnval)
mainend: /* any error in above would be catastrophic */
/* */ return
endpgm

</code>