Changes to IBM SQL Engine affects performance in Logical Views keyed by numeric fields
| Date: | 18th August 2009 |
|---|---|
| Product/Release: | LANSA for iSeries V11 SP5 |
| Abstract: | Logical Views keyed by Numeric fields using UNSIGNED ordering will suffer performance problems at runtime on V6R1 systems and V5R4 environments with certain PTFs applied |
| Submitted By: | LANSA Technical Support |
Symptom:
Recently IBM have introduced changes to the SQL engine so that it no longer supports unsigned numeric key fields for DDS created files. Search the IBM web site www.ibm.com for APAR MA36289 for details of this behaviour and how it causes an issue when trying to use Logical Views keyed by numeric fields. In order to maintain some backwards compatibility, IBM have created a fix which creates SQL Indexes for these logical views 'on-the-fly' at runtime.
The updated SQL engine and subsequent compatibility fix is
shipped in V6R1, and it is also delivered in PTFs for V5R4:
MF44150
MF44306
How this affects LANSA applications:
It is important to note that the creation of the indexes at runtime will affect ANY iSeries application accessing these logical files using SQL. In addition, after applying the PTFs mentioned above or upgrading to V6R1, LANSA Applications making use of:
- SELECT_SQL commands
- LANSA Integrator SQL Service
- Certain LANSA Client queries (which use SQL behind the scenes)
Will have performance issues if they are used to access Logical Views containing numeric key fields with unsigned ordering.
Solution:
The fastest solution is to rebuild the logical views so that they do not use unsigned ordering on the numeric key fields. In most cases, unsigned numeric ordering was not selected intentionally, in which case, changing the ordering type to Signed may not cause significant application behavior changes. However, you should consider any impacts on your application behavior prior to making this change.
There is a Visual LANSA IDE tool available from LANSA Support that can identify which logical files need to be updated and it can be supplied to customers who have many updates to make. Future releases of the Visual LANSA IDE will also make 'Signed' as the default ordering type for newly created numeric logical keys to prevent this being selected unless specifically required.
Please contact your local support agent for more information about either of these fixes.