You are here: LANSA for i User Guide > Appendix C. Technical Notes > Using *DBOPTIMIZE / *DBOPTIMIZE_Batch

Using *DBOPTIMIZE / *DBOPTIMIZE_Batch

If you write an RDML program like this:

REQUEST  FIELDS(#PRONUM)

FETCH    FIELDS(#PANEL01) FROM_FILE(PRODMST) WITH_KEY(#PRONUM)

FETCH    FIELDS(#PANEL01) FROM_FILE(PRODCAT) WITH_KEY(#PROCAT)

 

then compile and execute it, the objects being used would look something like this:

 __________            _____________            ____________

|          |          |             |    to    |            |

|          |  calls   | I/O Module  |  access  |  PRODMST   |

|          | -------> | for PRODMST | <------> | data file  |

| RDML     |          |_____________|          |____________|

| Function |                                                 

|          |  calls    _____________            ____________

|          |          |             |    to    |            |  

|          | -------> | I/O Module  |  access  |  PRODCAT   |

 ----------           | for PRODCAT | <------> | data file  |

                      |_____________|          |____________|

 

This is a typical LANSA application, using I/O module calls to control all access to all database files.

Using this approach has several advantages, the chief of which is the ability to alter the definition of a database file and/or its validation rules without ever having to recompile the RDML functions that access it.

Only the I/O module usually needs to be recompiled.

However, there is a disadvantage as well. Using this approach involves a performance overhead in the call to the I/O module.

Usually this overhead is small and acceptable, given the advantages it brings, however in some situations the call overhead becomes magnified by other factors.

The factors can range from accessing a file with a very large number of records to trying to run a large and complex application system on a small or undersized machine.

To alleviate this call overhead, a facility called *DBOPTIMIZE (or *DBOPTIMISE) can be used.

If the program above was changed to be like this:

FUNCTION OPTIONS(*DBOPTIMIZE) or OPTIONS(*DBOPTIMISE)

REQUEST  FIELDS(#PRONUM)

FETCH    FIELDS(#PANEL01) FROM_FILE(PRODMST) WITH_KEY(#PRONUM)

FETCH    FIELDS(#PANEL01) FROM_FILE(PRODCAT) WITH_KEY(#PROCAT)

 

then (re)compiled and executed, the objects being used would look something like this:

     ________________                     ___________

    |                |      access       |           |

    |                | <---------------> | PRODMST   |

    | RDML Function  |                   | data file |

    |                |                   |___________|

    |                |                                

    |                |                   ____________

    |                |      access       |           |

    |                | <---------------> | PRODCAT   |

    |                |                   | data file |

    |________________|                   |___________|

                                    

 

This alleviates the I/O module call overhead imposed by the IBM i operating systems.

This technique also produces efficient code, because only the code required for the specific database access method(s) required by the RDML function are actually brought into the RDML function.

It can produce significant performance benefits in some situations.

However, it also has several disadvantages that you should be aware of:

 

Some other things to think about when using *DBOPTIMIZE are:

To understand how *DBOPTIMIZE_BATCH changes the way file records are processed, you must first understand how records are processed by I/O modules, or by normal *DBOPTIMIZE logic.

Consider the following RDML function:

    FUNCTION OPTIONS(*DBOPTIMISE)

-- SELECT    FIELDS(#FIELD01) FROM_FILE(TEST)

|     CHANGE  FIELD(#FIELD01) TO('#FIELD01 * 1.1')

|     UPDATE  FIELDS(#FIELD01) IN_FILE(TEST)

-- ENDSELECT

 

This RDML logic is translated into I/O requests like this:

SELECT   - Read next record from file TEST (in keyed order)

         | and save its associated relative record number

         | (RRN). The record is not locked because the open

         | data path being used is only open for input, not

         | for update.

         |

UPDATE   | Reread the record from file TEST via an alternate

         | open data path using its RRN. This RRN access is  

         | much faster than access by key. The record is 

         | locked because this open data path is open for

         | update.

         | Check for any changes to the record since it was

         | originally read via the input open data path. 

         |

         | Update the record via the update open data path

         | and release the record lock.

         |

ENDSELECT - Go back to the SELECT command

 

This logic produces very strong program logic, does not leave records locked for a long period of time, and may actually work faster than an *DBOPTIMIZE_BATCH version of the program when low percentages of the records read by the SELECT loop are updated. This is because the input open data path being used to read records does not have to lock every record it reads.

However, when large volumes of the records are being updated (or deleted), the extra open data path and the extra RRN I/O performed on it, may impose an unnecessary overhead.

To remove this overhead, you can make the following change to the function:

FUNCTION OPTIONS(*DBOPTIMIZE_BATCH)

 

This changes the translated I/O requests to be like this ...

 

SELECT   - Read next record from file TEST (in keyed order).

         |  The record is locked because the open data path

         |  being used is open for update.

         | 

UPDATE   |  Update the file record. No check for changes

         |  since the record was read is required because the

         |  file record has been locked since it was read.

         |  Use the same open data path as the read operation.

         |

ENDSELECT - Go back to the SELECT command

 

which will probably execute faster than the *DBOPTIMIZE version.

Some other things to note about *DBOPTIMIZE_BATCH include: