A followup...
The correct index for my example would be:
create index lib.New_Index on lib.Table (upper(Data_LastName))
Which is not the same as...
create index lib.New_Index on lib.Table (Data_LastName)
Steve Needles
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Needles,Stephen J
Sent: Thursday, January 09, 2014 12:07 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: RE: Get Record Creation Date
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
--
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.
midrange.com
