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



just start with the EVI.
Then use the syntax I showed.

:hostvar in (' ', table field name)
etc.




On Mon, Oct 21, 2013 at 1:56 PM, Michael Ryan <michaelrtr@xxxxxxxxx> wrote:

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 on
one
particular field?

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
be
best to create an encoded vector index for each selection field. SQL
will
decide which ones to use. Also make the statement dynamic.


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 I
would
have a lot of indexes - 10 based on the 'between' criteria. This
selection
logic is used a lot - for subfile load on a green screen and for
exposing 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),
would
I
have issues because the access plan will be rebuilt every execution of
the
program?

Thanks...

- Michael


On Mon, Oct 21, 2013 at 1:04 PM, Michael Schutte <
mschutte369@xxxxxxxxx
wrote:

Yeah, probably so. However, the OP isn't doing any joins. I was
thinking
that he was. The problem that he's having is because he's trying to
do a
range 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
you
want it to use.

I usually go one of two ways. Go dynamic or instead of using the
range, I
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
obviously
need
an index over Immdl.
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
indexes
over each of those views.
create view invmstp_Immdl_view on invmstp as ( select distinct Immdl
from
invmstp)

create encoded vector index invmstl on invmstp (Immdl);

select * from invmstp
where Immdl in (select Immdl from invmstp_immdl_view where :hostvar
in
('
', Immdl )


I don't just a thought.





On Mon, Oct 21, 2013 at 9:27 AM, Vernon Hamberg <
vhamberg@xxxxxxxxxxxxxxx
wrote:

I'm generally in agreement with you, Michael - I've found
over-complex
JOINs 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.


On Fri, Oct 18, 2013 at 2:04 PM, Vernon Hamberg <
vhamberg@xxxxxxxxxxxxxxx>wrote:

Was just at RPG-DB2 Summit - we are encouraged to put as much
into a
single SELECT statement as we can, by the IBMer, Tom McKinley,
who
did a
presentation on set-based thinking with SQL.

Tom specifically said to avoid multiple steps of putting data
into
separate tables in QTEMP. instead, look for ways to combine as
JOINs
or
maybe as IN predicates against a subquery.

If the OPs stored procedure could do what the RPG program does -
and
it
probably can - then everything could be put into the SP. SPs
have
lots
of programming capability, and some of it is easier than doing
RPG!

Vern

On 10/18/2013 12:02 PM, Michael Schutte wrote:
Sometimes it's better to split the SQL statements up and return
multiple
result sets. then let the business logic take care of the rest.
Other
times, I have selected what I've needed from each file and put
them
into
QTEMP. Then returned the result set of the QTEMP table.

declare global temporary table tmp_mytable like mytable with
replace;
declare global temporary table tmp_mytable2 like mytable2 with
replace;

insert into tmp_mytable select * from mytable where field = 1;


insert into tmp_mytable2 select * from mytable2 where field =
'abc';


select * from tmp_mytable a join tmp_mytable2 b on
(a.otherfield
=
b.otherfield);

etc.





On Thu, Oct 17, 2013 at 7:56 PM, CRPence <CRPbottle@xxxxxxxxx>
wrote:

Could gather and review the PRTSQLINF against the SQL RPG
program.
Could also try to get debug messages in a job that invokes the
SQLRPG.

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
does
RLA instead of SQL <<SNIP>>

Michael Ryan on 10/17/2013 02:31 PM wrote:

Visual Explain/index Advisor woes. I have a stored
procedure
that
calls an RPG procedure that returns a result set. <<SNIP>>

How can I get more information regarding the indexes used
or
needed?
--
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.