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



We are on 7.1 and at TR6.

After loading TR6, we got bit by the lack of a permanent index on a table where RRN processing was performed via SQL.

According to IBM, if a table will be updated according to its RRN and no index (of any sort, temp or perm) is available then the Query Engine performs a table-scan to find the appropriate row. If an index DOES exist, it does not...it "goes the faster path" through the Query Engine.

While any index would allow for the "fast" path to be chosen, the derived index key on RRN provides the fastest access. IBM recommended this index and we followed their advise. The program that uses this logic was checked and it indeed uses the new index according to the PRTSQLINF and DBMON data

Adding the index lowered run time for the process by 50%. (your results may vary!)

Index Advise wasn't provided for the table because the advisor does not generate advice for derived values (RRN) or for derived columns in your SQL's where clause.

Example:

Select * from Table
Where upper(Data_LastName) = :lastName

No index advise will be suggested for Table on Data_Lastname because the column has been modified by UPPER() and that causes it to be considered a derived (think: made up) column.

Steve Needles


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, January 08, 2014 4:25 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: Get Record Creation Date

On 07-Jan-2014 14:01 -0800, Vernon Hamberg wrote:

create index dp2test/custdatix
on dp2test/qcustcdt (rrn(qcustcdt))

The index has 12 entries, which is correct.

We are at 7.1 TR6 (IIRC).

I think this support came out in 6.1

Huh. Interesting. Actual tests using keyed access path I/O or the selection of that AccPth by the optimizer in implementing a query would be more telling than just the creation... but just the creation is good to know. Too bad I only have v5r3 to play with :-(

I recalled not being able to create an equivalent on v6r1, and seem to recall also seeing RRN explicitly referenced in the restricted function list for CREATE INDEX, though maybe instead just by implication of the error message. Looking now, I see that the RRN is\was not included in that list on v6r1:
<http://pic.dhe.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzxcindx.htm>

Given how pervasive the implication that the Query Engine would only use table-scan implementations for almost any use of RRN() [excepting what is available via an EVI], I probably just never followed-up; i.e.
due to apparent irrelevance, even if such an index existed, except for RLA. I wonder what the real story is.? Found nothing in the What's new, nor anywhere else in the InfoCenter, nor even DeveloperWorks. I did some other quick\wider web searches and came up empty; looking for any stories talking about creating such an index, and the ability for such an INDEX over the RRN() to be utilized by the SQE for implementing.
Would have been a good topic to followup on when I was still employed.

--
Regards, Chuck
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.

________________________________
This communication, including attachments, is confidential, may be subject to legal privileges, and is intended for the sole use of the addressee. Any use, duplication, disclosure or dissemination of this communication, other than by the addressee, is prohibited. If you have received this communication in error, please notify the sender immediately and delete or destroy this communication and all copies.

TRVDiscDefault::1201

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
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.