Re: Looking for view containing printer IP addresses

On 15-Jul-2016 13:58 -0500, Troy Hyde wrote:

We have a configuration table in our package that has a row for
each printer. It contains information specific for our workflows:
headings, lines to advance, next check number, print logo, etc, etc.
All of the information that she has been asked to put on the screen
is contained in this table except the I.P. address of the printer.
That one piece of information is not specific to our package but
specific to the *DEVD. We could put it in the table (and we probably
will,) but that would mean that if someone changes the device
description _without_ changing the table, the information is
incorrect.

I see how dynamic reflection is probably better than trying to store
and maintain the value; avoiding the need to ensure consistency between
the actual device attribute and the representation of that attribute in
row data. Note however, that there is the capability to intercept the
[return from] the [Create and\or the] Change Device Description
{Printer} (CHGDEVPRT) command(s); that could be used as a means to
enable maintaining accuracy between the device and the row data -- refer
to the /command/ exit features.

I do have a program that calls the necessary API to obtain the
address (among other things) and I can make it available as a UDF
without too much trouble. I am just hoping that IBM has already done
the work like they have with so many other things.

Hope [for what IBM will deliver] springs eternal ;-)

Possibly more trouble than value, to either wait [for something
unlikely to be had, from IBM; as I'd noted, I see nothing on their
DeveloperWorks pages] or even to try to fit an existing program into the
mix. There would seem little reason to do anything more than obtain
that single value via what presumably would be a rather trivial HLL
program or procedure that is specific to that task [esp. one with
return-value capability], that then could be made available via the SQL
with the CREATE FUNCTION [External Scalar]; possibly all effected with
little effort, from /new source/ that likely would be culled from
existing examples.

For example, with CLP source from an existing scalar UDF made from a
CLP [one of many, originally for another purpose], I combined that with
a CLP that had been coded for use of the Retrieve Device Description
(QDCRDEVD) API [originally coded to obtain the /Last Activity Date/].
[http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/apis/QDCRDEVD.htm]
Similar to the Q&D example I gave in
[https://groups.google.com/d/msg/comp.sys.ibm.as400.misc/iMRqK9PKUT0/ey9SSVf20VQJ],
I modified the latter of those sources to obtain the IP Address via the
Remote Location Name (RMTLOCNAME) data at offset 1064 of format
DEVD1100, for a printer device of Type=*LAN and Location-Type=*IP.
Perhaps however, that interpretation of what is required for an IP
address is too limited, and the logic of the currently coded program for
its use of QDCRDEVD is more appropriate. Regardless, I offer the
following as an example:


pgm (&p_devd &rtnval &p_ind1 +
&rtnind &sqlste &funcnm &specnm &diagmg)
/* Parameters; per Parameter Style SQL specification */
dcl &p_devd *char 10 /* arg1 - *DEVD (DEVPRT) */
dcl &rtnval *char 255
dcl &p_ind1 *int 2
dcl &rtnind *int 2
dcl &sqlste *char 5
dcl &funcnm *char 141
dcl &funcnl *int 2 stg(*defined) defvar(&funcnm 1)
dcl &funcns *char 139 stg(*defined) defvar(&funcnm 3)
dcl &specnm *char 130
dcl &specnl *int 2 stg(*defined) defvar(&specnm 1)
dcl &specns *char 128 stg(*defined) defvar(&specnm 3)
dcl &diagmg *char 72
dcl &diagml *int 2 stg(*defined) defvar(&diagmg 1)
dcl &diagms *char 70 stg(*defined) defvar(&diagmg 3)
/* Program variables */
/* QDCRDEVD interface format DEVD1100 */
dcl &dcrRtnVar *char 2000
dcl &dcrRtnLen *int 4 2000
dcl &dcrFmtNam *char 8 DEVD1100
dcl &dcrDevNam *char 10
dcl &dcrErrCde *char 8 x'0000000000000000'

/*dcl &dcrRtnVar *char 2000 -- overlay variables */
dcl &o_bytRtn *int 4 stg(*defined) defvar(&dcrRtnVar 1)
dcl &o_devCtgy *char 10 stg(*defined) defvar(&dcrRtnVar 32)
dcl &o_devCls *char 10 stg(*defined) defvar(&dcrRtnVar 153)
dcl &o_rmtLoc *char 255 stg(*defined) defvar(&dcrRtnVar 1065)
dcl &o_rmtLocT *char 10 stg(*defined) defvar(&dcrRtnVar 1331)

/* Global Monitor */
monmsg cpf0000 exec(goto BadThing)

/* Mainline program */
Mainline:
chgvar &dcrDevNam &p_devd
call qsys/qdcrdevd /* Retrieve Device Desc (PRT) */+
( &dcrRtnVar &dcrRtnLen &dcrFmtNam &dcrDevNam &dcrErrCde )
if (&o_bytRtn *ge 1419) then(do)
if ( (&o_devCls *eq '*LAN') +
*and (&o_rmtLocT *eq '*IP' ) ) then(do)
/* (&o_devCtgy *eq '*PRT') implied per no error CPF26A7 */
chgvar &rtnval &o_rmtLoc
enddo
else do
chgvar &sqlste '01HIP' /* pre-defined class-code: 01H -> Warning*/
chgvar &diagml 70
chgvar &diagms ('An IP Addr for DEVD(' +
*cat &p_devd *tcat ') could not be determined')
chgvar &rtnval ('IPADDR(*UNKNOWN) DEVCLS(' +
*cat &o_devCls *tcat ')' )
enddo
enddo
else do
chgvar &diagms ('Check QDCRDEVD output in FUNCTION' +
*bcat &funcns )
sndpgmmsg *n cpf9897 qcpfmsg &diagms msgtype(*diag) +
tomsgq(*topgmq) topgmq(*prv)
BadThing:
chgvar &sqlste '38IP1' /* per-defined class-code: 38 -> Error */
chgvar &diagml 70
chgvar &diagms ('Rtv IP Addr for DEVD(' +
*cat &p_devd *tcat ') failed; see prior msgs in joblog' )
sndpgmmsg *n cpf9898 qcpfmsg &diagms tomsgq(*topgmq) topgmq(*prv) +
msgtype(*diag)
enddo
Mainend:
return

endpgm

The interface can be defined using the following; NB the chosen
PARAMETER STYLE is a match to, correlates to, the above CLP PARM() list:

create function PrtDevIP
( devd char(10)
) returns varchar(255) cast from char(255)
language CL not deterministic
no SQL returns null on null input
disallow parallel not fenced
no external action parameter style SQL
specific PrtDevIP
external name PrtDevIP

An invocation example [assumes the char(10) printer device name column
is DEVICENAME and that the configuration table name is
configuration_table]:

select ct.*
, PrtDevIP(ct.DeviceName) as IP_ADDR
from configuration_table as ct

<<SNIP>when I get back someone will have said, "Oh, yeah. That is
table or view xxxxxx in QSYS2." However, what will probably happen
is, I'll create the function for her and then somebody will say, "Oh,
yeah. That is table xxxxxx in QSYS2." Just like when you can't find
that tool in the garage until you've bought a replacement.

Again, I am doubtful.

Although I have long imagined the OS moving in such a direction, to
actively maintain attributes in the database [or make them more directly
capable of being queried without run-time retrieval], that such data
would ever be maintained as physical row data within a TABLE, within
this decade, is highly improbable. Like most of the DB2 for i
/services/, the data likely would be generated, dynamically upon an
invocation, per having been made available as a system-supplied User
Defined Table Function (UDTF). Such a UDTF most likely would be coded
effectively to invoke a list-API to obtain all *DEVD and any required
retrieve-API(s) [e.g. QUSRDEVD] to obtain everything that the table
function has defined as columns; being both dynamic and generating a
complete list, likely the feature would be far from lightweight. Of
course, one could *hope* that such a feature would provide for filters
on each of device-category [e.g. PRT|DSP], device-class [e.g. RMT|LCL]
and by name [e.g. generic*], as a means to limit the amount of data
generated [and thus minimize work performed] prior to the actual query
row selection; or possibly even separate UDTFs per device category.

<<SNIP>>
When replying, please edit your Subject line so it is more specific
than "Re: Contents of MIDRANGE-L digest..."

or "Re: MIDRANGE-L Digest, Vol 15, Issue 1128"

Although no relevant content from the replied-to message remained, the
above text was left in the reply. Really. Why?