Another how to question

[SteveL]

Once upon a time I asked a similar question to the one I am about to pose. Essentially a "how to" on condensing two views into one. My SQL skills are rather limited but I did get the following to work:

create view helper as select slaenb, sldccd, slcvnb. sla3cd, slrtyp, min(slacdt) as order_date, sum(sldpva) as order_value
from domsl100
group by slaenb, sldccd, slcvnb, sla3cd, slrtyp

create view orders as select slaenb, sldccd, slcvnb, sla3cd, order_date, order_value, coalesce(c6afvn, bwafvn) as user
from helper
left outer join mbc6rep
  on (slaenb = c6aenb and sldccd = c6dccd and slcvnb = c6cvnb) 
left outer join mbbwcpp
  on (slaenb = bwaenb and sldccd = bwdccd and slcvnb = bwcvnb)
where (slrtyp = 'O' or slrtyp = 'C')

"Select * from orders" produces the desired results.

I spent several frustrating hours trying to make a single statement out of these two. I know someone with more experience at this will be able to show me where I went wrong.

Steve

[arrow483]

Re: Another how to question

Well, i am not an expert in SQL, but I think you could do

with helper as select slaenb, sldccd, slcvnb. sla3cd, slrtyp, min(slacdt) as order_date, sum(sldpva) as order_value
from domsl100
group by slaenb, sldccd, slcvnb, sla3cd, slrtyp
create view orders as select slaenb, sldccd, slcvnb, sla3cd, order_date, order_value, coalesce(c6afvn, bwafvn) as user
from helper
left outer join mbc6rep
  on (slaenb = c6aenb and sldccd = c6dccd and slcvnb = c6cvnb) 
left outer join mbbwcpp
  on (slaenb = bwaenb and sldccd = bwdccd and slcvnb = bwcvnb)
where (slrtyp = 'O' or slrtyp = 'C')

But I dont' think that this buys you anything, performance-wise. I suspect the complicated part may be that you want all slrtyp's in the summary, but not the final results. Personally, I often prefer views over views in some cases, as it simplifies the coding.

[kitvb1]

Re: Another how to question

When you've combined them, test the performance. Most likely you will find that seperate views will perform much faster. I did something similar (but with case when statements) and found that the seperate views took 25% of the original bulky view#s time to execute.

Another motivating factor to split them: Birgitta will rap you over the knuckles with a ruler (as she did me)

[tomholden]

Re: Another how to question

what about creating a view over those 2 views and using that instead?

[SteveL]

Re: Another how to question

I am not familiar with starting a statement with the verb "WITH". But, I will let you know I make out.

I am certain that you recognize the system - MAPICS (or should I say Infor XA). I spent the summer and most of the fall working on an upgrade to XA 7.8. Now that it is in most of our Power Link users want to go back to the older Browser because of the speed of the newer Java based GUI. Meaning, the lack of speed.

Steve

Well, i am not an expert in SQL, but I think you could do

with helper as select slaenb, sldccd, slcvnb. sla3cd, slrtyp, min(slacdt) as order_date, sum(sldpva) as order_value
from domsl100
group by slaenb, sldccd, slcvnb, sla3cd, slrtyp
create view orders as select slaenb, sldccd, slcvnb, sla3cd, order_date, order_value, coalesce(c6afvn, bwafvn) as user
from helper
left outer join mbc6rep
  on (slaenb = c6aenb and sldccd = c6dccd and slcvnb = c6cvnb) 
left outer join mbbwcpp
  on (slaenb = bwaenb and sldccd = bwdccd and slcvnb = bwcvnb)
where (slrtyp = 'O' or slrtyp = 'C')

But I dont' think that this buys you anything, performance-wise. I suspect the complicated part may be that you want all slrtyp's in the summary, but not the final results. Personally, I often prefer views over views in some cases, as it simplifies the coding.

[SteveL]

Re: Another how to question

I will certainly test both methods. I suspect neither will be all that quick. The problem was to summarizing order value by order / warehouse but only on a subset of data in the file.

Steve

When you've combined them, test the performance. Most likely you will find that seperate views will perform much faster. I did something similar (but with case when statements) and found that the seperate views took 25% of the original bulky view#s time to execute.

Another motivating factor to split them: Birgitta will rap you over the knuckles with a ruler (as she did me)

[SteveL]

Re: Another how to question

Tom

I do not believe that it will help. This view will be slow regardless of how it is done. What would have been best would have been a statement list this:

Select order, warehouse, sum(value) from filexxx
order by order, warehouse
where (rec_type = 'O' or rec_type = 'C')

Basically, only summarize certain records in the file. But SQL does not like the preceding statement.

Steve

what about creating a view over those 2 views and using that instead?

[B.Hauser]

Re: Another how to question

There are 2 ways to tune your SQL:
1. It depends on the way you code your Select-Statement
2. You need the right access paths (either SQL indexes or DDS described keyed logical files) to get fast access to your data.

Before STRSQL execute STRDBG. Run a simple select * from View and check the joblog after. Index advices are shown in the joblog.
Even better is to use iSeries Navigator's Run an SQL Script and explain your SQL-Statement with Visual explain.

In either way you need access path over the join fields, the field specified in the where condition and the group by fields.
The easiest way is to let your Select-statement analyze.

Birgitta

Birgitta

[SteveL]

Re: Another how to question

Birgitta

I did try just that in OPS Navigator. I was offered no index suggestions. Thanks for the suggestion just in case I had not done so yet.

By the way the two SQL statements that I first showed took 3 minutes, 15 seconds to open in our resource constrained development LPAR. We typically get a pickup factor of 3 to 5 in our production LPAR. Regardless this is gonna be painful to the user.

Steve

There are 2 ways to tune your SQL:
1. It depends on the way you code your Select-Statement
2. You need the right access paths (either SQL indexes or DDS described keyed logical files) to get fast access to your data.

Before STRSQL execute STRDBG. Run a simple select * from View and check the joblog after. Index advices are shown in the joblog.
Even better is to use iSeries Navigator's Run an SQL Script and explain your SQL-Statement with Visual explain.

In either way you need access path over the join fields, the field specified in the where condition and the group by fields.
The easiest way is to let your Select-statement analyze.

Birgitta

Birgitta

[SteveL]

Re: Another how to question

Arrow

Well, I could not get your offered statement to work. First, it complained about the period after field SLCVNB. Next It wanted a "(" after "with helper as". I placed the end ")" after the group clause. Finally, it complained about the Create view statement. I did not know where to go from there.

I have spent far to much time on this at this point in time. I will live with the two views and figure it out in the future.

Steve

Well, i am not an expert in SQL, but I think you could do

with helper as select slaenb, sldccd, slcvnb. sla3cd, slrtyp, min(slacdt) as order_date, sum(sldpva) as order_value
from domsl100
group by slaenb, sldccd, slcvnb, sla3cd, slrtyp
create view orders as select slaenb, sldccd, slcvnb, sla3cd, order_date, order_value, coalesce(c6afvn, bwafvn) as user
from helper
left outer join mbc6rep
  on (slaenb = c6aenb and sldccd = c6dccd and slcvnb = c6cvnb) 
left outer join mbbwcpp
  on (slaenb = bwaenb and sldccd = bwdccd and slcvnb = bwcvnb)
where (slrtyp = 'O' or slrtyp = 'C')

But I dont' think that this buys you anything, performance-wise. I suspect the complicated part may be that you want all slrtyp's in the summary, but not the final results. Personally, I often prefer views over views in some cases, as it simplifies the coding.

[SteveL]

Re: Another how to question

Well, I just discovered a good reason for only using a single view.

We use Softlanding's Turnover for change management. That software does not understand that when you have a SQL view based on another SQL view that they need to be deleted SQL view 2 first. It attempts to delete SQL view 1 first and fails. This is going to make it very interesting if / when the views have to be replaced.

Steve

[B.Hauser]

Re: Another how to question

Try it as follows:

create view orders as
with helper as (select slaenb, sldccd, sla3cd, slrtyp, 
                       min(slacdt) as order_date, sum(sldpva) as order_value
                   from domsl100
                   group by slaenb, sldccd, slcvnb, sla3cd, slrtyp) 
select slaenb, sldccd, slcvnb, sla3cd, order_date, order_value, coalesce(c6afvn, bwafvn) as user
from helper
left outer join mbc6rep
  on (slaenb = c6aenb and sldccd = c6dccd and slcvnb = c6cvnb) 
left outer join mbbwcpp
  on (slaenb = bwaenb and sldccd = bwdccd and slcvnb = bwcvnb)
where (slrtyp = 'O' or slrtyp = 'C') 

Or

create view orders as
with helper as (rtyp) 
select slaenb, sldccd, slcvnb, sla3cd, order_date, order_value, coalesce(c6afvn, bwafvn) as user
from (select slaenb, sldccd, slcvnb.sla3cd as SLA3CD, slrtyp, 
                       min(slacdt) as order_date, sum(sldpva) as order_value
                   from domsl100
                   group by slaenb, sldccd, slcvnb, slcvnb.sla3cd, slrtyp) as Helper
left outer join mbc6rep
  on (slaenb = c6aenb and sldccd = c6dccd and slcvnb = c6cvnb) 
left outer join mbbwcpp
  on (slaenb = bwaenb and sldccd = bwdccd and slcvnb = bwcvnb)
where (slrtyp = 'O' or slrtyp = 'C') 

In either way 2 views are better (as Kit already pointed out).

Make sure that there are indexes or keyed logical files (and that they are used by the optimizer).
DOMSL100 --> All fields specified in the group by
MBC6REP --> All Join fields
MBBWCPP --> All Join fields

Also check with iSeries Navigator which query engine is used.
(Visual Explain / Final Select / End of the information at the right side).

Is DOMSL100 a DDS desrcibed logical file?
If so replace it with the physical files. All queries where a DDS described logical file is specified are rerouted to the classic query engine, which performs not as good as the SQE (SQL Query Engine).

Birgitta

[SteveL]

Re: Another how to question

Birgitta

You are the best! With one slight correction that statement got me the one view that I wanted.

DOMSL100 - the first three fields are keyed. The fifth field has a encoded vector index. The fourth filed has no help at the moment.

MBC6REP - All fields connecting are keyed
MBBWCPP - All fields connecting are keyed
DOMSL100 is an unkeyed physical with indexes

OPS Navigator indicates that CQE was used because "Derived Key or Select/Omit Index Exists"

I am working on getting it off CQE.

Steve

Try it as follows:

create view orders as
with helper as (select slaenb, sldccd, sla3cd, slrtyp, 
                       min(slacdt) as order_date, sum(sldpva) as order_value
                   from domsl100
                   group by slaenb, sldccd, slcvnb, sla3cd, slrtyp) 
select slaenb, sldccd, slcvnb, sla3cd, order_date, order_value, coalesce(c6afvn, bwafvn) as user
from helper
left outer join mbc6rep
  on (slaenb = c6aenb and sldccd = c6dccd and slcvnb = c6cvnb) 
left outer join mbbwcpp
  on (slaenb = bwaenb and sldccd = bwdccd and slcvnb = bwcvnb)
where (slrtyp = 'O' or slrtyp = 'C') 

Or

create view orders as
with helper as (rtyp) 
select slaenb, sldccd, slcvnb, sla3cd, order_date, order_value, coalesce(c6afvn, bwafvn) as user
from (select slaenb, sldccd, slcvnb.sla3cd as SLA3CD, slrtyp, 
                       min(slacdt) as order_date, sum(sldpva) as order_value
                   from domsl100
                   group by slaenb, sldccd, slcvnb, slcvnb.sla3cd, slrtyp) as Helper
left outer join mbc6rep
  on (slaenb = c6aenb and sldccd = c6dccd and slcvnb = c6cvnb) 
left outer join mbbwcpp
  on (slaenb = bwaenb and sldccd = bwdccd and slcvnb = bwcvnb)
where (slrtyp = 'O' or slrtyp = 'C') 

In either way 2 views are better (as Kit already pointed out).

Make sure that there are indexes or keyed logical files (and that they are used by the optimizer).
DOMSL100 --> All fields specified in the group by
MBC6REP --> All Join fields
MBBWCPP --> All Join fields

Also check with iSeries Navigator which query engine is used.
(Visual Explain / Final Select / End of the information at the right side).

Is DOMSL100 a DDS desrcibed logical file?
If so replace it with the physical files. All queries where a DDS described logical file is specified are rerouted to the classic query engine, which performs not as good as the SQE (SQL Query Engine).

Birgitta

[arrow483]

Re: Another how to question

MAPICS files have dozens of derived indexes. Even if the index that is derived is not used, it will still force processing back to CQE. You should edit file QUSRSYS/QAQQINI and set value IGNORE_DERIVED_INDEX to *YES. Also in the AMFLIBx libraries whereever this file exists. When I did this, SQL over Mapics files in my system ran 4x faster.

[B.Hauser]

Re: Another how to question

... but keep in mind if you change IGNORE_DERIVED_INDEX to *YES, the SQE optimizer cannot use any indexes in DDS described files with SELECT/OMIT-Clauses. That means instead of using these access paths a table scan or table probe will be performed (which may be much slower than using the CQE).
In this way you need to check index advice and generate the necessary indexes. If there are logical files with select/omit clauses with the same key fields. First delete these logical files and recreate them after having created the SQL indexes. In this way the logical file can share access path with the SQL index. If you create an SQL index without recreating the DDS desrcibed logical files 2 access paths are created and must maintained.

To test IGNORE_DERIVED_INDEX, copy the QAQQINI file into your test library/schema and modify it. After assign the QAQQINI in your test library/schema with the CL-Command CHGQRYA to your current job. In this way you can test without affecting other jobs (which will work as usual and use the CQE). BTW it also works with iSeries Navigators Run an SQL Script. Just add a CL: in front of your CL-Command.

CL: CHGQRYA QRYOPTLIB(MYSCHEMA) 

Birgitta