I'm afraid it's above my pay grade to force #2 or 3. Thank you
-----Original Message-----
From: Birgitta Hauser [mailto:Hauser@xxxxxxxxxxxxxxx]
Sent: Wednesday, November 06, 2019 10:50 PM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Alt index to SQL keyed view
You have 3 choices:
1. You continue to use an DDS described logical file (which is way not as powerful as an SQL index).
You can use it with SQL ... but you should not! The logical file is treated by SQL like any view, i.e. SQL decides whether or if an index is used. There is no guarantee, that the access path stored in your logical file is used. If you need a specific sequence, you have to add an order by to your SELECT statement.
2. You create a view and replace your native I/O access to embedded SQL.
Again, it is the query optimizer which decides if and which index is used.
You also need to add an ORDER BY clause.
3. You create an index AND a view. The index can be used with native I/O and the view can be used with SQL. If the syntax is an exact match, the Query Optimizer may even use the index (and also profit from the enhanced indexing
technologies)
Assumed I have the following index:
Create Index ADDRESSX_I05 For System Name ADDRI05
on ADDRESSX
(COUNTRY,
Lower(Trim(City)) as CITY_LOWERCASE For Column LOWCITY,
Lower(Trim(CustName1)) as CUSTNAME1_LOWERCASE For Column LOWNAME1,
Lower(Trim(CustName2)) as CUSTNAME2_LOWERCASE For Column LOWNAME2,
Lower(Trim(Street)) as STREET_LOWERCASE For Column LOWSTREET)
WHERE COUNTRY in ('D', 'A', 'CH')
and Lower(Trim(CUSTNAME1) concat
' ' concat
Trim(CUSTNAME2)) like '%gmbh%'
RcdFmt ADDRESSXF
Add All Columns;
And the following view
Create or Replace View ADDRESSX_V05 For System Name ADDRV05
as (Select a.* ,
Lower(Trim(City)) as LowCity,
Lower(Trim(CustName1)) as LowName1,
Lower(Trim(CustName2)) as LowName2,
Lower(Trim(Street)) as LowStreet
From ADDRESSX A
Where Country in ('D', 'A', 'CH')
and Lower(Trim(CustName1) concat
' ' concat
Trim(CustName2)) like '%gmbh%'
You can use the index with native I/O and the view with SQL ... and because the Definition of the KeyFields and the WHERE conditions are an exact match, the query optimizer can also use the index.
Try to do this with DDS!
Much more programming effort in composition with native I/O and even with SQL, because you either have to code the WHERE conditions and Key Fields directly in your SELECT statement or ... CREATE a VIEW to mask the compelity.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to." (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Justin Taylor
Sent: Mittwoch, 6. November 2019 22:07
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Alt index to SQL keyed view
I can't use different objects. It must be a single object that works for everything. That is why I think I'll need to go with a DDS LF.
Thanks
-----Original Message-----
From: Birgitta Hauser [mailto:Hauser@xxxxxxxxxxxxxxx]
Sent: Wednesday, November 06, 2019 1:08 PM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Alt index to SQL keyed view
I need a keyed LF that can be used in RPG RLA, QRY/400, and SQL. If I
use
a SQL view, I'm afraid I'll lose my keyed sequence.
Does that mean I have to use a DDS LF?
NO!
Just use the view and add an ORDER BY to your SQL-Statement or specify the sequence in your Query/400.
For Native I/O you can use the index (if you use the view with native I/O you may use the sequence, because a view is always unkeyed)
... I'm quite sure I described this in my article.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to." (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Justin Taylor
Sent: Mittwoch, 6. November 2019 18:52
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Alt index to SQL keyed view
I need a keyed LF that can be used in RPG RLA, QRY/400, and SQL. If I use a SQL view, I'm afraid I'll lose my keyed sequence.
Does that mean I have to use a DDS LF?
Thanks
-----Original Message-----
From: Birgitta Hauser [mailto:Hauser@xxxxxxxxxxxxxxx]
Sent: Wednesday, November 06, 2019 11:42 AM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Alt index to SQL keyed view
You cannot access an SQL Index with SQL. You only can access views. It it the query optimizer which decides if or whether and which indexes are used.
If you created an index with enhanced indexing technologies (such as additionally generated key fields or where conditions) and you want to access these data, you either have to build the additional fields you need in the SELECT statement and/or add the where conditions to the SELECT statement.
Much better however is to create a view with the appropriate information (additional key fields and where conditions) and access the view.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to." (Richard Branson)
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
