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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 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.