Re: Must I use OPNQRYF

[CRPence <CRPbottle@xxxxxxxxx>]

  Yet another way to generate a temporary copy of all the rows for the 
desired [product code] selection value.  Unless the optimizer is forced 
to generate a temporary result, the OPNQRYF can easily best making a 
copy.  And probably still better by OPNQRYF, even if the classic query 
engine implements using a [true] temporary.  A CPYF with row selection 
in many cases would be just as useful [and similarly undesirable] for 
making the temporary copy, as compared to a dynamic query redirected to 
an output file by either the DB2 command line or a QM Query.

  Even so, unless the DB2 command line query generates the final 
report, what benefit does the RPG program have to access a formatted 
query report as a stream file for input, as compared to having a 
described database file as input?

  And unless something has changed, the DB2 command line interface is 
still stuck doing single-row FETCH.  Hardly efficient.  I would stick 
with QMQRY in that case.  Additionally if the /append/ is not required, 
then the "output" environment variable processing can be replaced by a 
redirect either via override to standard output or via the '>' symbol. 
And FWiW, I would not prefer to ignore the failure to set the first 
environment variable, and one would hope the ignored error in the latter 
would be cause for a defect if left unmonitored yet transpired.?

  Use of the DB2 interface for the scenario by the OP, if a temporary 
copy were even desirable, would best be implemented using a "CREATE 
TABLE ... as SELECT ... WITH DATA" to avoid the cursor\FETCH activity, 
and making the data available in a TABLE instead of a stream file.  That 
method could best the CPYF too.

Regards, Chuck

On 20-Feb-2012 08:11 , Vern Hamberg wrote:
> This is maybe even easier than using QM queries - I need to look at
> it more.
>
> On 2/20/2012 9:04 AM, Jim Oberholtzer wrote:
> > While it is way better to put your SQL into the RPG program you can
> > do it directly from CL without OPNQRYF. You would put your SQL
> > statement into a variable and call a utility in QSHELL called DB2.
> > So it looks like:
> >
> > ADDENVVAR ENVVAR(QIBM_QSH_CMD_OUTPUT) +
> >   VALUE('FILEAPPEND=/tmp/shell.log')
> > MONMSG CPFA980 /* Error adding value */
> > ADDENVVAR ENVVAR(QIBM_QSH_CMD_ESCAPE_MSG) +
> >   REPLACE(*YES) VALUE(Y)
> > MONMSG CPFA980
> > CHGVAR &Command VALUE('db2 "'select * from whatever...etc'"')
> >
> > QSH cmd(&Command)
> >
> > Works like a charm, and really, it's too easy. The hardest part is
> > getting the quote ticks to work right.