|
Michael...do I need to make a view and an EVI, or just an EVI? And making
EVIs (without making the statement dynamic) should still speed things up,
right?
On Mon, Oct 21, 2013 at 1:51 PM, Michael Schutte <mschutte369@xxxxxxxxx
wrote:
Michael, will the user be able to key an actual range to search for onone
particular field?be
If not, then consider this method...
Where :hostvar1 in (' ', field_from_table)
and :hostvar2 in (' ', field2_from_table)
It's basically a shorthanded "or" statement.
When the host variables are populated, it will try to use an index.
It wouldn't matter what option you choose, as the user changes search
criteria, the access plan will change.
Since you don't know what the user's request will be, It would probably
best to create an encoded vector index for each selection field. SQLwill
decide which ones to use. Also make the statement dynamic.would
select = 'Select * from invmstp ';
If Immdl_host <> '';
If Whereclause = '';
whereclause = 'where ';
endif;
whereclause += 'Immdl = ' + Immdl_host;
endif;
// continue building for the remaining fields.
On Mon, Oct 21, 2013 at 1:16 PM, Michael Ryan <michaelrtr@xxxxxxxxx>
wrote:
That's what I'm thinking....build the statement (dynamic). It seems Iwould
have a lot of indexes - 10 based on the 'between' criteria. Thisselection
logic is used a lot - for subfile load on a green screen and forexposing a
result set to PHP. If I was to make this dynamic (which I believe means
creating the SQL statement by building a string and executing it),
Imschutte369@xxxxxxxxx
have issues because the access plan will be rebuilt every execution ofthe
program?
Thanks...
- Michael
On Mon, Oct 21, 2013 at 1:04 PM, Michael Schutte <
obviouslydo awrote:
Yeah, probably so. However, the OP isn't doing any joins. I wasthinking
that he was. The problem that he's having is because he's trying to
yourange with all values. I tried that once and found that I was always
performing a table scan whenever my range was from loval to hival.
If you are doing that kind of range, it will never pick up the index
want it to use.range, I
I usually go one of two ways. Go dynamic or instead of using the
would use the IN clause.
By either creating a temporary table of valid values. (insert into
qtemp/table select distinct Immdl from invmstp) This would
inneed
an index over Immdl.indexes
Then
where Immdl in (select Immdl from qtemp/table).
Or if something was selected,
I would do
Where :Hostvar in (' ', Immdl ).
When hostvar was blank, all records would be returned.
Now that I think about it. I'm wondering about creating views and
over each of those views.from
create view invmstp_Immdl_view on invmstp as ( select distinct Immdl
invmstp)
create encoded vector index invmstl on invmstp (Immdl);
select * from invmstp
where Immdl in (select Immdl from invmstp_immdl_view where :hostvar
who('over-complex
', Immdl )vhamberg@xxxxxxxxxxxxxxx
I don't just a thought.
On Mon, Oct 21, 2013 at 9:27 AM, Vernon Hamberg <
wrote:
I'm generally in agreement with you, Michael - I've found
into aJOINs to perform somewhat badly at times.
Maybe the OP could use a global temporary table or 2 in his stored
procedure - could be used for a chain of SELECTs, right?
Vern
On 10/21/2013 7:35 AM, Michael Schutte wrote:
I don't doubt this. My experience though has been the opposite.vhamberg@xxxxxxxxxxxxxxx>wrote:
On Fri, Oct 18, 2013 at 2:04 PM, Vernon Hamberg <
Was just at RPG-DB2 Summit - we are encouraged to put as much
single SELECT statement as we can, by the IBMer, Tom McKinley,
intodid a
presentation on set-based thinking with SQL.
Tom specifically said to avoid multiple steps of putting data
haveJOINsseparate tables in QTEMP. instead, look for ways to combine as
andor
maybe as IN predicates against a subquery.
If the OPs stored procedure could do what the RPG program does -
it
probably can - then everything could be put into the SP. SPs
(a.otherfieldlotsRPG!
of programming capability, and some of it is easier than doing
themOthermultiple
Vern
On 10/18/2013 12:02 PM, Michael Schutte wrote:
Sometimes it's better to split the SQL statements up and return
result sets. then let the business logic take care of the rest.
times, I have selected what I've needed from each file and put
replace;into
QTEMP. Then returned the result set of the QTEMP table.
declare global temporary table tmp_mytable like mytable with
'abc';replace;declare global temporary table tmp_mytable2 like mytable2 with
insert into tmp_mytable select * from mytable where field = 1;
insert into tmp_mytable2 select * from mytable2 where field =
select * from tmp_mytable a join tmp_mytable2 b on
=procedure
doeswrote:b.otherfield);
etc.
On Thu, Oct 17, 2013 at 7:56 PM, CRPence <CRPbottle@xxxxxxxxx>
SQLRPG.program.
Could gather and review the PRTSQLINF against the SQL RPG
Could also try to get debug messages in a job that invokes the
Regards, Chuck
On 10/17/13 12:17 PM, Michael Ryan wrote:
No, it's definitely SQL. Big honking SQL statement in there.
On Thu, Oct 17, 2013 at 2:40 PM,<rob@xxxxxxxxx> wrote:
Maybe it doesn't need/use any?
You have it call an RPG program. Perhaps your RPG program
RLA instead of SQL <<SNIP>>
Michael Ryan on 10/17/2013 02:31 PM wrote:
Visual Explain/index Advisor woes. I have a stored
orthat
calls an RPG procedure that returns a result set. <<SNIP>>
How can I get more information regarding the indexes used
mailingmailingmailingmailing--needed?
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
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)
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)
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)
listlistlist
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
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
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 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.