× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. September 2013

AW: have View need Stored Proc

A stored procedure or an UDTF make sense if multiple steps (statements) must
be performed within the Stored Procedure (or UDTF).
Neither a stored procedure nor an UDTF makes sense if only a single result
set is returned based on a single select statement.

A stored procedure is nothing else than a program written in either a HLL or
SQL.
Result Sets are returned by declaring a cursors, opening them and not
closing them before the end of the procedure. That's all.

Here is a very simple example of a stored procedure returning 2 result sets:
CREATE PROCEDURE HSCOMMON05/HSMULTRS (ParOrdNo)
DYNAMIC RESULT SETS 2
LANGUAGE SQL
READS SQL DATA
CALLED ON NULL INPUT
BEGIN NOT ATOMIC
DECLARE PRVSQLCODE SMALLINT ;
DECLARE CURSORNOTOPEN CONDITION FOR '24501' ;

DECLARE CSRC1 CURSOR FOR SELECT * FROM ORDHDR WHERE ORDNO =
ParOrdNo;
DECLARE CSRC2 CURSOR FOR SELECT * FROM ORDDET WHERE ORDNO = ParOrdNo
;

DECLARE CONTINUE HANDLER FOR CURSORNOTOPEN
SET PRVSQLCODE = SQLCODE ;

CLOSE CSRC1 ;
CLOSE CSRC2 ;
OPEN CSRC1 ;
OPEN CSRC2 ;
END ;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Hoteltravelfundotcom
Gesendet: Wednesday, 18.9 2013 14:18
An: Midrange Systems Technical Discussion
Betreff: Re: have View need Stored Proc

Thanks, I was interested in seeing any stored procedures examples for IBM i.
I have for example some cases in the reporting tool where they make small
SQL Query of the database for some purpose over several reports. I think a
stored procedure would be good in these cases. But I have not seen any
example of making Stored procedure on IBM i.


On Tue, Sep 17, 2013 at 11:40 PM, Birgitta Hauser
<Hauser@xxxxxxxxxxxxxxx>wrote:

All advised indexes are stored in the SYSIXADV table located in the
QSYS2 schema.
You may also run your SQL statement in the IBM i Navigators - Run an
SQL scripting tool and analyze it with visual explain. Visual explain
will show you whether any indexes are advices and which ones and it
even allows you to directly create those indexes.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them and keeping them!"


-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Hoteltravelfundotcom
Gesendet: Tuesday, 17.9 2013 23:43
An: Midrange Systems Technical Discussion
Betreff: Re: have View need Stored Proc

where is this index advisor? is it in 5.4?


On Tue, Sep 17, 2013 at 12:45 PM, Hoteltravelfundotcom <
hoteltravelfun@xxxxxxxxx> wrote:

i have just started this. Each of the logicals were indexed by
company number at the least. GOod idea to look at the advisor I did
not know
this.


On Tue, Sep 17, 2013 at 11:22 AM, Gary Thompson
<gthompson@xxxxxxxxxxx>wrote:

I wonder if you have reviewed the available indexes/logical files
for the data in question ?
Views and queries will benefit if you have the required queries.
Index Advisor is available on System i Navigator to suggest indexes

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Hoteltravelfundotcom
Sent: Tuesday, September 17, 2013 7:46 AM
To: Midrange Systems Technical Discussion
Subject: Re: have View need Stored Proc

the issue is, there is a massive amount of data. I asked the user
and she said most of the time they run the report for periods over
the past 2 years but sometimes it can be more past dates.
So I was thinking of creating one view for past 2 years and another
view for previous years.

Wouldn't it be faster that way? Currently, it takes about 30+
minutes to run the report. All the Links are being done in the
Reporting tool, it queries 5 Logical files on the IBM i.

Since I now have 1 View for all these, I also want to see if other
formulas the tool is doing might be better to be done in the View.
But still I anticipate this as time taking.


On Tue, Sep 17, 2013 at 8:51 AM, Hoteltravelfundotcom <
hoteltravelfun@xxxxxxxxx> wrote:

I see, this is good. But I was thinking of the time taking to read.
I thought it would be faster as a stored procedure.


On Mon, Sep 16, 2013 at 7:00 PM, Birgitta Hauser <
Hauser@xxxxxxxxxxxxxxx>wrote:

Why not simply adding a WHERE clause from within the reporting
tool that selects the records with the desired dates from the view?

Select * from View
Where IDDOCD between FirstDate and LastDate;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek
Bok) "What is worse than training your staff and losing them?
Not training them and keeping them!"


-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Hoteltravelfundotcom
Gesendet: Monday, 16.9 2013 22:21
An: Midrange Systems Technical Discussion
Betreff: have View need Stored Proc

I did finally get that view to work. What I would like to do,
and I am not sure how, is to make a stored procedure so that
this view will accept dates from and to params on the column
IDDOCD. Then, if I am correct, the reporting tool can accept
this stored procedure via dates entered by the user. I have not
done SP in IBM i, so so far am bombing out. is there any one can
set me on the right
path here?



CREATE VIEW astccdta.acsusage
AS (
SELECT
IARCC9, IDDOCD, IDCOM#, ADSFX#, IDPRT#, IDGRC#, IDENT#, IDSFX#,
IDPRLC, IDNTU$, IDSHP#, ASTDTA.ADRESSAD.ADFNM,
ASTDTA.ADRESSAD.ADLNM, ASTTRN.OEINHDIH.IHVIAC,
ASTDTA.ADRESSAD.ADSTTC, IDINV# FROM ASTDTA.OEINDLID INNER JOIN
ASTDTA.ICPRTMIA ON ASTDTA.OEINDLID.IDPRT# =
ASTDTA.ICPRTMIA.IAPRT# INNER JOIN ASTTRN.OEINHDIH ON
ASTDTA.OEINDLID.IDIDC#=ASTTRN.OEINHDIH.IHIDC#
INNER JOIN ASTDTA.ADRESSAD ON
ASTDTA.ADRESSAD.ADENT#=ASTTRN.OEINHDIH.IHENT#
WHERE ASTDTA.ICPRTMIA.IARCC9='ACS'
AND ASTDTA.OEINDLID.IDCOM#='001' AND ASTDTA.OEINDLID.IDPRT# LIKE
'ACS%')
)
--
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.


--
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.



--
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.



--
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.



--
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.


--
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.


--
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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.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 on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.