Page History
...
In the following example, the KEEP_LAST parameter of the FETCH command is used in a program which reads and prints details of all general ledger transactions from a file called GLTRANS.
In this example, associated with each transaction is a company number (#COMPNO), and the actual company name (#COMPNAME, from file COMPANY) must appear on the report:
DEF_LINE NAME(#REPORTLIN)
...
FIELDS(#TRANSNUM #TRANSTYP #TRANSVAL +
#COMPNO #COMPNAME)
SELECT FIELDS(#REPORTLIN)
...
FROM_FILE(GLTRANS)
FETCH FIELDS(#COMPNAME)
...
FROM_FILE(COMPANY)
...
WITH_KEY(#COMPNO)
PRINT LINE(#REPORTLIN)
ENDSELECT
ENDPRINT
If there were 10,000 transactions in GLTRANS this program would perform exactly 20,000 database accesses.
However, if the FETCH command was modified:
FETCH FIELDS(#COMPNAME)
...
FROM_FILE(COMPANY)
...
WITH_KEY(#COMPNO)
...
+
KEEP_LAST(15)
and there were only 15 companies, then the program would now perform at most 10,015 database accesses, and thus run in about half the time required by the original version.
| Note |
|---|
| Note: If there are more than 15 companies the program will not fail, it will simply do the extra I/Os required. Refer to the FETCH command in the LANSA Technical Reference for more details of the KEEP_LAST parameter before attempting to use it. |
Another example of the KEEP_LAST parameter is the improvement of a widely used technique of database access minimization. For instance, very often programmers code logic like this:
IF COND('
...
#PRODNO *NE #PRODNOLST')
FETCH FIELDS(#PRODES)
...
FROM_FILE(PROMST)
...
WITH_KEY(#PRODNO)
CHANGE FIELD(#PRODNOLST)
...
TO(#PRODNO)
ENDIF
...
which is basically saying: "get the product description, if the product being processed is different to the last one processed". It is very effective.
However, by using the KEEP_LAST parameter the coding required to achieve the same performance benefit can be reduced to just one line - and there is no need to use a work field (like #PRODNOLST) to keep track of the last value processed.
FETCH FIELDS(#PRODES)
...
FROM_FILE(PROMST)
...
WITH_KEY(#PRODNO)
KEEP_LAST(1)