3 2 Some Database Guidelines

Some suggested guidelines for the definition of database files are as follows:

File definition maintenance should be assigned as a task to only one person within a project team, or to even just one person within an organization.

All files (physical and logical) should have an accurate and concise description which uses upper and lower case characters.

Avoid the use of too many logical views in applications to be deployed on the IBM i. The maintenance of logical file access paths (indexes) is probably the largest resource user in any IBM i.

For any logical view that is rarely used, consider using the RDML OPEN command with the USE_OPTION(*OPNQRYF) and KEYFLD parameters if the application is only to be deployed on an IBM i. This will achieve exactly the same result without causing the overhead of continual maintenance of another access path.

Investigate the use of the dynamic select option on logical files using the select/omit criteria. If the required file has the same key(s) as an existing logical file and selects a large percentage of the records in the file, the "dynamic select" option is a good solution.

Alternatively use the RDML OPEN command with the USE_OPTION(*OPNQRYF), QRYSLT and KEYFLD parameters if the application is only to be deployed on an IBM i. This is a viable alternative, especially if the order specified in the KEYFLD parameter is identical to that in an existing logical file.

Use as many file level validation checks as possible. There are not many situations in which a validation check cannot be performed by one of the six standard file level validation checks.

The rules can be changed without modifying or re-compiling application programs which use the file.

The rules are standardized and can be tested by the database designer before one line of RDML code is written.

Avoid the use of multi-membered files. Even though LANSA supports access to multi-membered database files, their use is not recommended as they are prone to become a maintenance problem. Either use another file altogether, or include an extra key into the file definition.

Additionally, the direct portability of multi-membered files to SQL based systems is not possible.

Investigate the use of database triggers to make the database "smart" rather than trying to make each individual function "smart".

When LANSA creates database files in IBM i, unless otherwise specified, it will automatically make the record format name exactly the same as the file name. If you intend to write RPG application programs which will access database files set up by LANSA, you have two options:

Before making the file definition operational, take the option from the File Definition Menu (on either file create or review) to change the file's record format name.