RE: SQL0466 result set access

Thanks Chuck.

But my recollection of the solution to this was real simple, like accessing a system table-function...?

I first found it via Google some months back. I thought that it was in an IBM site, but I can't be certain...which is the problem :-)

Steve Needles | IT Architect | Specialized Distribution | Northland Insurance
Travelers
385 Washington | SB03N
St. Paul, MN 55102
W: 651.310.4203







-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Saturday, October 15, 2016 5:20 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL0466 result set access

On 14-Oct-2016 22:19 -0500, CRPence wrote:

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.

Quite possibly the following is what was recalled, instead of what was mentioned above; at least if recollection of calling from within STRSQL.

I had forgot to mention another option, per [¿still?] being undocumented [thus unknown how to create a properly defined SQL routine/procedure as interface], and something I use very rarely for that reason. The following request made from within the STRSQL would be much simpler than what is shown above, for not having to deal with the extra escaping. This method rids of the indirection from muliple levels of interpreters, i.e. CL->QSH->DB2, by just a call made directly to the /stored procedure/ QZDFMDB2 in QSYS, per that being the symbolic link `/QSYS.LIB/QZDFMDB2.PGM` from the link /usr/bin/db2 that defines [the program that implements] the DB2 utility of QSH. And with program-attribute=CLE, presumably the VARCHAR input string is implicitly terminated with the null-character 0x00 by the SQL; i.e. as to why that direct call functions:

call qsys.qzdfmdb2 ('call mySchema.MyProcThatProducesResultSets()')

--
Regards, Chuck

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.
________________________________
This communication, including attachments, is confidential, may be subject to legal privileges, and is intended for the sole use of the addressee. Any use, duplication, disclosure or dissemination of this communication, other than by the addressee, is prohibited. If you have received this communication in error, please notify the sender immediately and delete or destroy this communication and all copies.

TRVDiscDefault::1201