11.3 Using Working Lists and the KEEP_LAST Parameter
LANSA provides two main facilities which can be used to reduce the number of program database accesses. These are the data construct called a "working list" and the KEEP_LAST parameter of the FETCH command.
The use of working lists for high performance summary reporting has already been discussed in previous sections. However, this is not their only use. Working lists can also be used to minimize database I/Os as they can be "looked up" much faster than a database I/O can be performed. Examples of this latter use of working files can be found in the LANSA Technical Reference.
In situations where complex look up criteria or multiple files are involved, the working list is a viable solution for database access reduction. HOWEVER, in most simple situations, the KEEP_LAST parameter of the FETCH command can produce the same results with much less effort.
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: 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 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)