6.3 The Database

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 the IMMED (immediate) maintenance option on a logical file with care.

• 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.

• There are many advantages in using file level validation checks, including:

• Most RDML programs can be written without any online validation.

• 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.

• The rules protect the database, even from a rogue RDML program.

• The error messages are standardized and consistent.

• 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.

• Rename the file record format within the RPG program itself.

Portability Considerations for Database Definitions

When working with files, you should be aware of the following:

• 10 character names for libraries will be truncated on the left to 8 characters. We recommend that you avoid this truncation by having library names that are 8 characters or less.

• Special characters in names (i.e. characters that are not A-Z, 0-9 or _ ) are replaced according to position. For example:

• A library named DEMPGMLIB would be renamed EMPGMLIB

• A file named DC@FILE would be renamed DC_FILE

• A file named @FILE would be renamed XFILE.

• Batch control files must be defined in the same library as the base physical file.

• Extended virtual code type Substringing using *RECORD is not supported.

• Complex logic check that calls a 3GL program is not supported.

• 3GL virtual code must be specified in ANSI C

• Select/omit criteria cannot span lines on a LANSA/AD screen panel for select/omit criteria. This means that each individual select/omit condition must be totally contained within 29 characters when using Visual LANSA. For example - COMP(LE 'XXXXXXXXXX') is valid COMP(LE 'XXXXXXXXXXXXXXXXXXXXX') is invalid.

• The following select/omit criteria keywords are supported COMP, CMP, RANGE and VALUES. The ALL keyword cannot be supported. Attempting to compile a file with the ALL keyword will result in an error in the file generation.

• The emulation facilities for IBM i select/omit criteria are only provided to support the reading of selected information from database tables. They are not provided to (or designed to) allow you to control what information is inserted or updated into database table rows. If you are using an application design that relies on the use of uniquely keyed logical files (with associated select/omit criteria) to control the content of the your database then you may have to revise this component of your design.

• Collating sequence for ASCII is different from EBCDIC. For example, in EBCDIC the numbers '0' -> '9' are collated after the letters of the English alphabet. In ASCII, they are collated before the alphabet. Your data may now appear in a different sequence.

     In sensibly designed modern applications, this should not affect the overall processing of an application, although it may affect the sequence that some individual applications present information.

• Commitment control may behave differently.