Using WITH_KEY and WHERE with SELECT command

[jyoung]

I just realized that you can use with_key and where with the same select command as such

Code: Select all

select fields(#CLOFID #CLOD #CLOCN #CLOFN) from_file(XF114003) where('(#CLELVL = 0) AND (#CLOFD = *BLANKS) AND (#CLOD = *BLANKS)') with_key(#CLOFID #CLOFD)

I had thought that these are mutual exclusive.

The main reason I tried this was that we do not have a logical file setup with the keys in the correct order that I am interested in and I really did not want to have to create another one.

How does this work in terms of getting data from the database and applying the predicate?

Is there a benefit or consequence of doing this?

[soa]

The WITH_KEY will return all the records which match the key values as you would expect (using the index) and the WHERE clause will act as a filter as to which records are returned to your function. As long as the 'filtered' records are a small sub-set of the records returned this is usually an acceptable performance hit.

We use often when we want to, for example, load a list of active customers by region and know that 95% of customers are active.

[jyoung]

So with_key loads the records from the database and then the where predicate filters the results in memory, before handing them off to the select loop?

If you don't use with_key (only where) then the entire table is loaded into memory to apply the where predicates?

[KevinW]

As usual, it depends.

The WITH_KEY parameter(s) are always used in the OAM to request the I/O.

1. If the LANSA WHERE does not contain RDML(X) variables not in the file
1.1 if access is via SQL (windows, linux, some IBM i) then the LANSA WHERE is added on to the SQL WHERE (so added to the WITH_KEY parameter(s) to the SQL WHERE)
1.2 if access is via native I/O (some IBM i) then each record returned is tested in the OAM with the LANSA WHERE for whether it is to be returned.

2. If the LANSA WHERE does contain RDML(X) variables not in the file, then the LANSA WHERE is used to test each record returned from the OAM "under the covers" prior to its values being presented to your LANSA code.

For what it is worth, it is usually better performing to have selections in the WITH_KEY parameter.