RE: DB2 SQL View Resources

Hi Matt,

Actually, no, I didn't get a chance to try out your solution yet.  I'll
do that right now and see what type of performance gain I can get.

Thanks!

Brian.

-----Original Message-----
From: Tyler, Matt [mailto:mattt@xxxxxxxxxxxxxx] 
Sent: Wednesday, July 20, 2005 12:01 PM
To: 'RPG programming on the AS400 / iSeries'
Subject: RE: DB2 SQL View Resources

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

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