The Determine logic command variants read records into memory direcly from a database or by using a profile. The Determine Actual logic command variant reads an extract file or serially reads a database table.
The logic command variants determine how records are read into memory and how many are read. Once a record is in memory, it can be manipulated by logic.
An End or EndExit logic statement must terminate the loop created by all variants except Determine Last and Determine Total. The logic statements within the loop are executed for each record that is read.
For MCP-based systems, you cannot specify a double byte text string for a single byte primary key or a single byte text string for a double byte primary key.
Profiles
Profiles are used to retrieve records from a database, and the keys of a profile determine which records are retrieved. The Ascending or Descending key property determines the order in which the records are retrieved.
While using keys:
Specify all keys.
Do not use mismatching data types to read a database.
Specify keys in the same order as declared in the profile. Keys do not have to be referred to by the same name.
Do not use a variable brought into memory by using a Determine logic statement as a key over its own structure.
For example, for a profile of a Cust ispec with the Name key, Cust.Name cannot be specified as the key. Instead, move the value to a variable and specify that variable as the key.
The order of duplicate keys cannot be determined. Hence, add another variable to a profile to render it unique.
Logically Deleted Records
The Maint built-in ispec attribute is ignored by the Determine logic command when retrieving records from a database. Even if Maint is set to the Glb.Del built-in segment attribute, the record is still retrieved.
However, a profile used to retrieve records can include conditions to exclude such records. Include logic or profile conditions to exclude such records as appropriate.
Nesting
SQL script variants of the Determine Actual logic command that specify the same SQL script should not be nested, as this can result in inconsistent behavior.
Determine commands might be nested up to:
Twenty levels deep on Systems that use the Windows Operating Systems
Unlimited depth on MCP-based systems
Database accesses are handled differently by the various host types:
For MCP-based systems, database access by the Determine commands depends on the positioning of the Profile by nested Determine commands. To avoid unexpected side effects, do not access the same Profile with nested Determine commands as these can alter the positioning of the Profiles required by outer Determine commands, and unexpected records can be read.
The use of an additional class instance within the nested Determine commands provides independence between the Determine commands.
For Windows-based systems, each Determine command is independent of the previous position.
For Windows-based and MCP-based systems, the database server does not provide read consistency throughout a query, so results dynamically change to reflect subsequent updates.
This means that database updates resulting from an inner Determine command will affect the results returned by the outer Determine command.
Handling of Duplicate Keys
For Windows systems, the order in which records with duplicate key values are retrieved cannot be determined. You need to add another attribute to a Profile to make it unique.
For an MCP-based system, the order of duplicate keys can be handled directly by DMSII. The order in which records are retrieved depends upon the setting of the Duplicates First/Last Profile DASDL option, as shown in the following table.
Duplicates | DT Command | Order of Duplicates |
|---|---|---|
| FIRST | FROM | Reverse order to that of entry (newest first) |
| FIRST | BACK | Order of entry (oldest first) |
| LAST | FROM | Order of entry (oldest first) |
| LAST | BACK | Reverse order to that of entry (newest first) |
| Blank | FROM/BACK | Physical order of records in database (note that this might change during a reorganization) |
A database reorganization or garbage collect might alter the order of Duplicates First/Last entries. Refer to the Data and Structure Definition Language (DASDL) Programming Reference Manual for more information.
For Developer, the order in which records with duplicate key values are retrieved is always the order of the entry. That is, for Determine From, the oldest record is retrieved first, and for Determine Back the newest record is retrieved first.
While reading database records, mismatching data types cannot be used as values for profile keys. For example, use of a string instead of a numeric (including signed numeric as well as date) and vice versa is not allowed.
The following rules apply for mismatching types:
Only Strings are allowed as values for keys defined as strings.
Dates and numeric can be used interchangeably for keys defined as numeric or date.
Only Boolean values are allowed as values for keys defined as Boolean.
Only NationalString values are allowed for keys defined as NationalString (except for Glb.Spaces, Glb.High, LB.HIGH, Glb.Low that are allowed as values for NationalString keys).
Failure Behavior
If no records are retrieved (no record satisfies the specified criteria), the Glb.Status built-in segment attribute is set to "*****". If a record is retrieved, the Glb.Status built-in segment attribute is not set by the Determine logic command. The Glb.Status built-in segment attribute is not set when end-of-file is detected.
The Glb.Status built-in segment attribute should always be verified after a Determine logic statement or, if a loop is created, after the End or EndExit logic statement for that loop. The Glb.Status built-in segment attribute is set to spaces before the Determine logic statement is executed.
Use the GS logic command option to direct the Glb.Status built-in segment attribute value to a variable. This is useful in instances where many logic commands might set the Glb.Status built-in segment attribute (for example, within nested Determine logic statements).