MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » February 2013

Re: does SQL have access to any system info



fixed

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>






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact