3.14.4 Detailed Access Route Maintenance

This screen is displayed when:

• Reviewing an existing access route in detail

• Specifying the details of a new access route

DC@P200902             Create Access Route                                                                                                      File : XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX (XXXX )                                                                          Access route name . . . . . . . .  __________                                                                                                   Access route description  . . . .   _____________________________                                                                          File to be accessed via this route . . . __________                                                                                             Maximum number of records expected . . . ____        Keep Last ____     PJF before/after Virtual Fld derivation  __                             Action to take if no records found . . . __________                                                                                             Key fields/values used for access  . . : ____________________                                                    ____________________                                                    ____________________                                                    ____________________                                                                                                                                                           Fnn=Help  Fnn=Exit  Fnn=Cancel  Fnn=Messages

Note: The Change and Delete Function keys are only enabled when reviewing an existing access route.

Working from this screen you can:

• Review the full details of an existing access route.

• Change an existing access route. Use the Change function key to indicate that the access route should be altered. The screen will be re-displayed and all fields available for change. Validation of the access path will follow the same rules as the Add/Create option.
  
  Note: A message will be displayed on the change screen if Predetermined Join Fields are defined on the access route, warning that these fields will be deleted if the file to be accessed or the relationship (number of records) is changed.

• Delete an existing access route. Use the Delete function key to indicate that the access route should be deleted. On the resulting display enter YES to confirm that the access route should be deleted. If YES is not entered the access route will not be deleted.
  
  Note: A message will be displayed if Predetermined Join Fields are defined on the access route, warning that these fields will be deleted along with the access route.

• Specify details of a new access route that is to be created.

Input Options

These input options apply when defining a new access route in a file definition:

Access Route Name

Specifies the name that is to be assigned to the access route. Every access route must have a name. Maximum length is 10 characters. Used for identification purposes only.

Name specified must be unique within the current file definition. It is suggested that a naming convention be developed for access route names. For example first 3 letters may indicate current file definition, next 3 indicate the name of the file being accessed, etc

Access Route Description

Specifies the description of this access route. A description must be provided as it is the easiest way users have to identify what information can be obtained from the access route. For example:

Current File Definition File Accessed Via Access Route Access Route Description Order header Order lines Order lines associated with order Order header Cust master Full details of order customer Cust master Order header Orders associated with this customer Order lines Order header Order associated with this order line

File to be Accessed via This Route

Specifies the name of the physical or logical file that is to be accessed via this access route. Physical or logical file specified must have been already defined to the LANSA system. File name can be entered in full, partially or left blank. If required a list of available files will be displayed and the required file chosen.

Maximum Number of Records Expected

Specifies the maximum number of records that are expected to be found in the "file to be accessed" that have a key matching the "key fields/value used for access" specified. The value entered must be in the range 1 to 9999.

In the current version of LANSA the actual value entered is only significant in that it is 1 or greater than 1. If the value is 1 then a "1:1" relationship between the files is established. If the value is greater than 1 a "1:many" relationship is established. The relationship between the files affects the method by which screen formats are designed.

It is recommended that a value as "realistic" as possible be entered into this field. For instance, if the access route is from an order header to an order lines file and an order can have a maximum of 99 lines, enter a value of 99.

If the number of records expected exceeds 9999 enter a value of 9999.

Keep Last

The Keep Last value (0 to 999) applies to Predetermined Join Fields defined on the access route when the relationship is one to one. Each value retrieved from the accessed file is stored in memory up to the keep last value. If more than the keep last value are retrieved, current values are overwritten starting from the first value retrieved. This is a very useful feature when using Predetermined Join Fields to retrieve values from small frequently used code fields to reduce I/Os. It is ignored if the access route relationship is one to many.

PJF Before/After Virtual Fld Derivation

This attribute nominates when the Predetermined Join Fields defined on this access route will be retrieved, i.e. before (B) or after (A) virtual fields which are derived After input from file. It allows a Predetermined Join Field to be used in deriving a virtual field or a virtual field to be used as a key to a file accessed for Predetermined Join Fields.

Action to Take If No Records Found

Indicates what action should be taken if no records can be found in the "file to be accessed" with the "key fields/values" specified below. Valid entries in this field are:

ABORT	The function attempting to access the file specified should abort (fail) with an error message indicating the cause of the problem. This option can be used to continually verify database integrity. For instance an access route from an order lines file to an order header file should always find a record. An order line without an associated order header probably indicates database corruption.
IGNORE	The function attempting to access the file specified should ignore the no records situation and continue to process. This option may be valid in the reverse access path of the case above. It is perfectly valid for an order header record to have no associated order lines.
DUMMY	The function attempting to access the file specified should create a "dummy" record when no "real" record(s) can be found. The dummy record created will have blanks in all alphanumeric fields and zero (0) in all numeric fields. Only one dummy record will be created.
N/AVAIL	The function attempting to access the file specified should create a "dummy" record when no "real" record(s) can be found. The dummy record created will have zero (0) in all numeric fields, blanks in all alphanumeric fields less than 3 characters long, and as much of the string 'N/AVAIL' as will fit in all other alphanumeric fields. Only one "dummy" record will be created. This option is useful in situations where the no record(s) situation arises occasionally. For instance an access route from an archived invoices file to a customer master file may use this option. When the name of a customer associated with an archived invoice cannot be found (presumably because it has been deleted) then it will be displayed as 'N/AVAIL', rather than causing an error.

Key Fields/Values Used for Access

Specifies the fields or values that should be used to form the key that will be used to access the record(s) in the "file to access". If field names are used then they must be defined in this file (i.e. the file definition that the access route is being added to - not the "file to access").

At least one key field or value is required. Up to 20 key fields or values can be specified. Use the ROLL UP key to enter more.

Entries made can be:

• An alphanumeric literal (in quotes) such as 'NSW', 'BALMAIN'

• A numeric literal such as 1, 14.23, -1.141217.

• Another field name such as CUSTNO, INVNUM, etc. As mentioned the field must be defined in the current file definition. (A "?" will display the single field selection screen. The list of fields will contain all real and virtual fields defined in the current file. Refer to section 3.10.1 Select Fields When Working from File Definition Menu for more details.)

• A system variable name such as *BLANKS, *ZERO, *DATE or any other system variable defined at your installation.

Key values are checked for type and length compatibility. The entire key list supplied is checked for compatibility with the actual key(s) of the "file to access". The key list specified can be a full or partial key to the file.

In cases where a "1:many" relationship is being defined the key list specified would almost always be partial key to the file.

A warning is issued if a partial key list is specified. In 1:many cases this is normal and should be ignored. In 1:1 relationship it may be valid to only supply a partial key. However, if the warning message is issued check what has just been defined.

Note: The access route facility is provided as an aid to user traversal of database structures. It does not restrict database access to pre-defined access routes. The RDML component of LANSA makes use of Predetermined Join Fields which are defined on access routes, in the same manner as it does virtual fields.