On 14-Oct-2016 12:19 -0500, Needles,Stephen J wrote:
I know that I once found a way to display the result set of a stored
procedure call run via green screen STRSQL.
Perhaps just the Command Entry vs actually within the SQL session?
I know that I can see the result set via iNav, but I have my reasons
for looking for it at the green screen level.
Recollection perhaps, that the data was presented as Standard Output?
Does anyone have insight? Google failed me on this one.
The DB2 utility in QSH is written with the SQL CLI [SRVPGM QSQCLI]
which can access the SP result-set, so perhaps that is what you recall.?
I do not much like the use of STDOUT for reporting, nor dealing with
overriding to a printer file or redirecting output etc.; too comfortable
at the CL command entry and the 5250 paradigm I guess ;-)
Anyhow, consider what iNav Run SQL scripting environment does, IIRC
by default, to produce a report from the result set of the invoked SP
such as for this request:
call mySchema.MyProcThatProducesResultSets()
The similar effect can be had from the DB2 command line scripting
environment within the QSHell; e.g. the following request made from the
command-line can effect similar reporting by default [the output even
starts with **** CLI ERROR ***** SQLSTATE: 0100C NATIVE ERROR CODE: 466
"&n result sets are available from procedure &p in &l."; i.e. the msg
SQL0466 in the Subject of this topic]
db2 "call mySchema.MyProcThatProducesResultSets()"
Yet from within the Command Entry, the request must get passed into
the QSHell as an apostrophe-delimited string; a bit messier, esp. if
there are character-string parameters:
QSH CMD('db2 "call mySchema.MyProcThatProducesResultSets()"')
But from within the Start Interactive SQL Session statement
processing environment, the statement-entry area for STRSQL, the request
would become even messier; thus why I was thinking maybe the
recollection of access from with STRSQL was incorrect, or there might be
something completely different being recalled, such as a generic report
writer written similar to how the DB2 utility functions, with the SQL CLI?:
call qsys2.qcmdexc
('qsh cmd(''db2 "call mySchema.MyProcThatProducesResultSets()"'')')
FWiW: Whenever I needed something like that, rather than depending on
the db2 utility and the shell, or writing an effective generic report
writer to process the result sets, or worse writing code specific to the
particular result sets, I would just code the Stored Procedure (SP) to
offer a parameter, for which a choice was given to place the results in
a Global Temporary Table (GTT) [or effective equivalent; i.e. I
typically just wrote to a file in QTEMP before there ever was or that I
ever knew of the GTT]. Then I would just defer presentation to the SQL
report-writer after I issued the CALL in STRSQL; or a /default query/
might be used, as provided by the Run Query (RUNQRY) using the
specification QRY(*NONE), when running the request from elsewhere, such
as from the Run SQL Statement (RUNSQLSTM) processor.
midrange.com
