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



Brian,
        Consider a SQL view as a compiled SQL statement without an index (as
you would run a SQL statement in interactive SQL).  So take your last
statement 
        c+ select sum(ivpqty) into :IRGLINV
      c+    from sst37t2
      c+    where ivptno= :ihptno
      c+      and trim(ivptcl)= :ihptcl

You would need to create the view as "Select sum(ivpqty) from sst37t2"
That's it!  You cannot have a where clause that is variable.  So it's a view
if probably not going to be worth the effort. 

I have used views in several SQLRPGLE programs and I now want to remove them
and revert to using explicit joining in the program SQL statement and
defining the result set host structure in another manner.  Views cause all
sorts of havoc with out CMS product (Aldon).  If I change any file
referenced in the view and create/promote to an environment that's not
production, our CMS creates the underlying PF's in that environment (empty).
NO other logical/views are created, so if I am not on top of it, testing can
get screwed up (the tested programs and sub programs can also use LF's as
native I/O).  I end up having to create all logicals/views in the
environment and copying all underlying PF's data to the environment.  The
real problem is when the view (that was changed) is promoted to production,
our CMS leaves all the other PF's and logicals/views in the promote "from"
environment.  

You cannot use a view in the F-specs and expect to perform random keyed
access to the view.  It only record number access.  

It's my suggestion that if you need to create a SQL statement in RPG that
joins files, specify the in the RPG SQL source.  If your result set is a
combination of all joined files, you can create a work PF that combines all
the joined files formats in the PF source.  This prevents the view issue I
mentioned about with some CMS's 

A    R RECFMT           FORMAT(RECFMT)
A                               FORMAT(RECFMT1)
A                               FORMAT(RECFMT2)
A                               ... 
A                               FORMAT(RECFMTn)


Back to your issue yesterday, did you try my solution?
http://archive.midrange.com/rpg400-l/200507/msg00626.html
I am not saying that this is the absolute solution, but it's worth a try.
Since I do not know the fields in sst37t2/sswihi and what the indexes are I
can only guess that this solution is worth trying.  

Also, what are the sizes of sst37t2 verses sswihi (it not clear from value
given below).  

Thank you,
Matt Tyler
WinCo Foods, LLC
mattt@xxxxxxxxxxxxxx


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Brian Piotrowski
Sent: Wednesday, July 20, 2005 7:37 AM
To: RPG programming on the AS400 / iSeries
Subject: DB2 SQL View Resources

Hi All,

 

Can someone point me to some resources that explain on how to create
views in DB2 and how to call them from RPG?

 

I'm still wrestling with this problem I had yesterday about retrieving
info.  Currently, the table is sitting at 2.9 Million records.  However,
within the records, there are only 218,000 that I need (I only need
records with an IVCHK status of 3 or 5).  I tried creating a LF only
using these records, but it was still slow.

 

Would creating a view based on a set of criteria speed up my process?

 

Thank you!

 

-=-=-=-=-=-=-=-=-=-=-=-=-=-

Brian Piotrowski

Specialist - I.T.

Simcoe Parts Service, Inc.

Ph: 705-435-7814 x343

Fx: 705-435-6746

bpiotrowski@xxxxxxxxxxxxxxx

-=-=-=-=-=-=-=-=-=-=-=-=-=-

 


As an Amazon Associate we earn from qualifying purchases.

This thread ...


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.