Sometimes, I've found it very beneficial for SQL performance if new indexes are created with the Record Id as the first key field.
As an example, imagine there are 100000 records in ECH of which 90% are inactive.
Most native I/O uses ECHL01 (Key HORD, S/O HID='CH') and performance is good. But SQL could be better.
However, when this is converted to SQL, you end with something like DECLARE C CURSOR FOR SELECT * FROM ECH WHERE HID='CH' ORDER BY HORD. But the Select/Omit causes issues with SQL and may cause less than expected performance.
Creating a new index on HID, HORD helps and 'fits' with SQL.
Sean
________________________________
From: bpcs-l-bounces+sean.mcgovern=covidien.com@xxxxxxxxxxxx on behalf of Genyphyr Novak
Sent: Fri 15/05/2009 19:37
To: bpcs-l@xxxxxxxxxxxx
Subject: Re: [BPCS-L] QCCSID & 65535
Hi Rob,
In fact, it was IBM that changed the default in their 6.1 release for how the SQL optimizer behaved. Prior releases of the operating system used Select/Omit logicals automatically and this would send the query to the CQE (classic optimizer). In a move designed to get more and more code using the new SQE optimizer, they did this change.
However, in extensive testing of various BPCS releases the effect on BPCS is overall not a good one. The Select/Omit logicals that BPCS contains are known as 'sparse logicals' and as it turns out it is much faster for the database to grab the entire contents of a Select/Omit logical to get records that exactly match the query, rather than to parse through (even by key) a much larger logical using the same keys. Until IBM makes improvements to SQE to get rid of this bias, this is the overall best setting for BPCS. We've even had some IBM consultants at BPCS client sites try to re-work the delivered database, by creating new logicals mimicking every S/O logical in BPCS (so that there is a matching by key logical available) and the majority of queries still ran slower in SQE with the new logicals than they did in CQE with the Select/Omits ... this is especially true if the file contains many 'soft deleted' records and the query is searching for active
records only. Obviously as IBM changes their optimizer this could improve or change in future, and if it does and if Infor re-tests the performance of BPCS they would (hopefully) change that recommended setting.
As regards 65535, I believe the historical reason for this was Client/Server which was doing code page translations itself based on tables internal to the Client/Server interface. When this interface was first invented, they found some issues with IBM supplied tables and so created their own (we are talking pre-history now... about 1996!). There were issues found when the system was set up otherwise, especially in Fat Client and someone found that this solved the issue. Whether that is the only way to set up the QCCSID or not is definitely an open question in 8.3 but the setting does work for BPCS so it is not technically incorrect.
If anyone decides to change this setting from the recommendation just be sure to test all your interfaces into and out of BPCS with data other than A-z and 0-9 characters (ie data which is variant in code pages such as the dollar sign, the hash sign, the curly braces are some easy ones to test with) to ensure no undesired change has occurred, because this is one of the things that support will ask you to change back if you start to have code page translation issues. They understandably need to start from a 'known quantity' which is the set up used internally at Infor where the system is created and tested.
Thanks,
Genyphyr Novak
iSeries technical consultant
Chamonix France
+33 662 15 02 02
message: 2
date: Wed, 13 May 2009 16:47:40 -0400
from: rob@xxxxxxxxx
subject: Re: [BPCS-L] QCCSID & 65535
You will find that Infor often has interesting ideas about changing
anything with the status quo. Like QAQQINI at
http://www-01.ibm.com/support/docview.wss?uid=nas22ed784e875e77a4586257490003c6d39
But at least they've gotten better in explaining their decisions:
"Infor recommends the following QAQQINI file setting to be activated for
support with R610:
IGNORE_DERIVED_INDEX *NO
The BPCS and ERPLX database contains large numbers of Select/Omit logical
files and if this option is not set, these logical files will be ignored
when the SQL Optimzer selects logical files to run a given query. This can
cause poor performance."
Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com <
http://www.dekko.com/>
--
This is the BPCS ERP System (BPCS-L) mailing list
To post a message email: BPCS-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/bpcs-l
or email: BPCS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/bpcs-l.
Delivered-To: sean.mcgovern@xxxxxxxxxxxx
midrange.com
