OV_INDEXED_SPACE

LANSA Technical

OV_INDEXED_SPACE

Þ Note: Built-In Function Rules

Allows you to define and manipulate an indexed space.

Warning: This function does not support RDMLX fields.

Note: The user of this Built-In Function is responsible for any impact it has on any application. No warranty of any kind is expressed or implied. Refer to full Disclaimer.

Function No:

989

DLL Required:

U_BIF989.DLL

 

For use with

Visual LANSA for Windows

YES

Visual LANSA for Linux

NO

LANSA for i

NO

 

 

Arguments

No

Type

Req/ Opt

Description

Min Len

Max Len

Min Dec

Max Dec

1

A

Req

Type of indexed space operation to be performed.

Pass as one of:
CREATE  Create a new indexed space.

INSERT  Unconditionally insert a new entry into an indexed space.

PUT  Insert a new or update an existing entry in an indexed space.

GET  Get an entry from an indexed space.

FIRST  Get the first entry in an indexed space.

NEXT  Get the next entry in an indexed space.

DESTROY  Destroy an indexed space and free associated system resources.

Note that this Built-In Function only validates and acts upon the first character of the requested index space operation (i.e. C,I,P,G,F,N,D) but to maximize RDML function readability it is recommended that you use the full words CREATE, INSERT, PUT, GET, FIRST, NEXT and DESTROY.

1

50

 

 

2

A

Req

Definition string or indexed space, identifier (or handle). An identifier (or handle) is the value returned to you in argument 2 when you create a new indexed space that uniquely identifies the indexed space.

When Arg 1 is
      Pass this argument as:

CREATE  The indexed space definition string. See the following information for details of definition strings.

any other  The identifier (or handle) of the indexed space that is to be used by the requested operation (the identifier or handle is the value returned in return value 2 when a new index space is created. It uniquely identifies the indexed space which you wish to use).

1

256

 

 

3

A

Opt

Definition string continuation.

Only valid for CREATE operations, ignored for other operations.

1

256

 

 

4

A

Opt

Definition string continuation.

Only valid for CREATE operations, ignored for other operations.

1

256

 

 

5

A

Opt

Definition string continuation.

Only valid for CREATE operations, ignored for other operations.

1

256

 

 

6

A

Opt

Definition string continuation.

Only valid for CREATE operations, ignored for other operations.

1

256

 

 

7

A

Opt

Definition string continuation.

Only valid for CREATE operations, ignored for other operations.

1

256

 

 

8

A

Opt

Definition string continuation.

Only valid for CREATE operations, ignored for other operations.

1

256

 

 

9

A

Opt

Definition string continuation.

Only valid for CREATE operations, ignored for other operations.

1

256

 

 

10

A

Opt

Definition string continuation.

Only valid for CREATE operations, ignored for other operations.

1

256

 

 

11

A

Opt

Definition string continuation.

Only valid for CREATE operations, ignored for other operations.

1

256

 

 

 

 

Return Values

No

Type

Req/ Opt

Description

Min Len

Max Len

Min Dec

Max Dec

1

A

Req

Standard Return Code
OK = Completed normally
NR = No record found
ER = Error  occurred.

2

2

 

 

2

A

Opt

Returned indexed space identifier or handle.
This return value is mandatory when argument 1 is passed  as CREATE because it returns the identifier (or handle) of the indexed space that was created.

10

10

 

 

 

 

Technical Notes

Indexed Spaces and Indexed Space Definition Strings

When an index space is being created (i.e. CREATE is used in argument 1) then arguments 2 through 11 must specify a definition string for the indexed space.

Arguments 2 through 11 are concatenated (trailing blanks in each separate argument are ignored) to form a single definition string that must be formatted thus :

name keyword(value), name keyword(value), name keyword(value), ....... name keyword(value)

 

where:

  • "name" is the name of a valid RDML field that is defined in, or referenced by, the    RDML function that is creating the list.
  • "keyword" specifies the use of the field specified in "name". It may be one of KEY,     DATA, AVG, MAX, MIN, COUNT and SUM. If a keyword is not specified then DATA is    assumed as a default keyword.
  • "value" specifies the name of a valid RDML field that is defined in, or referenced by,   the RDML function defining the list. It is used to specify, for certain keyword values only,   the field upon which the keyword activity should take place. Thus ".., A SUM(B), .." defines   that indexed space field A is to contain the SUM (or total) of field B. Likewise, the   string ".., X AVG(Y), .." defines that indexed space field X is to contain the average   of field Y.

The "keywords" AVG, MAX, MIN and SUM require associated "values" and thus must be formatted as AVG(value), MAX(value), MIN(value) and SUM(value) where value is the name of a field defined in the invoking RDML function.

The "keywords" KEY, DATA and COUNT do not require associated "values" and they should only be specified as KEY(), DATA() and COUNT().

An indexed space is best visualized as a table or grid.

The definition string defines what the columns in the indexed space are to be and how they should be used. The following examples illustrate this concept:

Definition String: deptment key(), deptdes

Can be visualized as :

Department
(DEPTMENT)

Department Description
(DEPTDESC)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

where DEPTMENT is the single key to each table or grid entry. Note that "deptdesc" adopts the default keyword "data()" in this example.

Definition String : deptment key(), section key(), secdesc

Can be visualized as

Department
(DEPTMENT)

Section
(SECTION)

Section Description
(SECDESC)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

where DEPTMENT and SECTION form an aggregate to each table or grid entry. Note that "secdesc" adopts the default keyword "data()" in this example.

Definition String: deptment key(), empasal avg(salary), empxsal max(salary), empmsal min(salary)

Can be visualized as

Department
(DEPTMENT)

Average Salary
(EMPASAL)

Maximum Salary
(EMPXSAL)

Minimum Salary
(EMPMSAL)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

   where DEPTMENT is the single key to each table or grid entry.

Notes / Rules / Guidelines for use of OV_INDEXED_SPACE

  • Indexed spaces have been primarily designed to support "batch" style functions that process large volumes of information. They have been designed to provide:
  • An optimized way of accumulating aggregate multi-level summary information.
  • An optimized way of randomly and repeatedly accessing large lists of information without the overhead of a database row access (i.e. you can load the required information into an indexed space at the start of your function and then repeatedly (re)access the indexed space more efficiently than you can by accessing the DBMS directly).

For examples of these types of usage please refer to the following examples.

  • An indexed space must have at least one key field/column defined.
  • An indexed space can have at most 20 key fields/columns defined.
  • An indexed space must have at least one non-key field/column defined.
  • An index space can have at most 100 non-key fields/columns defined.
  • The aggregate byte length of all key fields/columns in an indexed space definition cannot exceed 16K. Note that if you are coming even remotely close to this limit then you should consult your product vendor about application design.
  • The aggregate byte length of all non-key fields/columns in an indexed space definition entry cannot exceed 16K. Note that if you are coming even remotely close to this limit then you should consult your product vendor about application design.
  • The significance of defined keys decreases with the order of their definition. Thus the definition string "aaa key(), bbb key(), ccc key(), xxx data(), yyy data()"  defines "aaa" as the most significant key and "ccc" as the least significant key.
  • Key definitions can take place at any point in the definition string, but it is customary to place them at the beginning.
  • There is no effective limit to how many entries can be place into an indexed space, but you must REMEMBER AT ALL TIMES that indexed spaces use allocated system memory. The more entries that exist in an index space the more overhead you are placing on the consumption of a system resource. References in this section to "no effective limit" actually mean that you are effectively constrained by how much memory your system can viably allocate and use.
  • The operations FIRST and NEXT support sequential key order access, however they can only be used with indexed spaces where there are less than 63KB / 6 (for Windows 3.1) or 32MB / 6  (other environments) entries in the indexed space. This is because the storage of the indexed entry's key always takes 6 bytes (regardless of the actual aggregate length of the key fields).
    If you try to use FIRST/NEXT sequential processing on an indexed space that is too large to support it you will receive a specific error message and your application will be aborted. GET, PUT and INSERT operations are not subjected to this limitation and can be used with no effective limit on the number of entries in the indexed space.
  • An indexed space can only be used by the function that creates it. Although you can easily pass the identifier (or handle) of an indexed space to another function, any attempt by the other function to access the index space may lead to application failure and or unpredictable results. Do not attempt to do this. It will not work because when you CREATE an indexed space, a unique access plan to data fields stored in the creating function is formed. This access plan is unique to the creating function and cannot be effectively used by any other RDML function.
  • You should use the DESTROY operation in your functions. However, all indexed spaces created by an RDML function are automatically destroyed when it terminates.
  • If you are using multiple definition strings with a CREATE operation please remember that trailing blanks are ignored. Therefore:

USE OV_INDEXED_SPACE (CREATE 'A KEY(), B KEY(), C ' 'KEY(), D DATA(), E DATA()')

 

      will cause a run time syntax error because the strings will be concatenated to form the definition string:

A KEY(), B KEY(), CKEY(), D DATA(), E DATA()

 

  • Indexed space key fields are treated (and sorted) as pure binary data. The indexed space has no sense of the definition of a field as alpha, packed, signed, DBCS, etc and will not account for this in any operation.
  • The PUT and INSERT operations are significantly different. A PUT operation checks whether an entry in the index exists with the specified key already. If it does, it is aggregated and updated as appropriate. If it does not exist, then a new entry is created. An INSERT operation does not check. It unconditionally creates a new entry.
    This means that if you wish to fill an indexed space from a DBMS table when you know you are creating unique entries, then using INSERT is substantially more efficient than using PUT.
  • You can put duplicate keys into an indexed space (i.e., 2 or more entries that have exactly the same key values) by using the INSERT operation. However, the effects of doing this and/or the order in which the duplicates are processed is unspecified and may vary from platform  to platform.
  • The aggregation operations AVG(), MAX(), MIN(), SUM() and COUNT() are all performed with a maximum of 15 significant digits of precision.

You must not use arrays or array indices in indexed spaces.

Examples

The following sample RDML function (which can be copy and pasted into the L4W free form function editor) is designed to illustrate the relative efficiency of indexed spaces for random access. You can use it to create a simple indexed space of up to 100,000 entries, and then cause it to lookup each entry individually:

FUNCTION OPTIONS(*LIGHTUSAGE *DIRECT);

********** COMMENT(Define the index space columns);
DEFINE FIELD(#OV_KEY01) TYPE(*DEC) LENGTH(7) DECIMALS(0) EDIT_CODE(4);
DEFINE FIELD(#OV_KEY02) TYPE(*DEC) LENGTH(7) DECIMALS(0) EDIT_CODE(4);
DEFINE FIELD(#OV_KEY03) TYPE(*DEC) LENGTH(7) DECIMALS(0) EDIT_CODE(4);
DEFINE FIELD(#OV_DATA01) TYPE(*DEC) LENGTH(7) DECIMALS(0);
DEFINE FIELD(#OV_DATA02) TYPE(*DEC) LENGTH(7) DECIMALS(0);
DEFINE FIELD(#OV_DATA03) TYPE(*DEC) LENGTH(7) DECIMALS(0);
DEFINE FIELD(#OV_TOTAL) TYPE(*DEC) LENGTH(7) DECIMALS(0) EDIT_CODE(4);
********** COMMENT(Define the loop test limits);
DEFINE FIELD(#OV_MKEY01) TYPE(*DEC) LENGTH(7) DECIMALS(0) LABEL('Outer Loop') EDIT_CODE(4);
DEFINE FIELD(#OV_MKEY02) TYPE(*DEC) LENGTH(7) DECIMALS(0) LABEL('Middle Loop') EDIT_CODE(4);
DEFINE FIELD(#OV_MKEY03) TYPE(*DEC) LENGTH(7) DECIMALS(0) LABEL('Inner Loop') EDIT_CODE(4);
********** COMMENT(Define other variables);
DEFINE FIELD(#OV_RC) TYPE(*CHAR) LENGTH(2);
DEFINE FIELD(#OV_HANDLE) TYPE(*CHAR) LENGTH(10);
********** COMMENT(Create the indexed space);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(CREATE 'ov_key01 key(), ov_key02 key(), ov_key03 key(),' 'ov_data01,ov_data02,ov_data03') TO_GET(#OV_RC #OV_HANDLE);
********** COMMENT(Request details of how the list is to be initialized);
REQUEST FIELDS(#OV_MKEY01 #OV_MKEY02 #OV_MKEY03);
BEGINCHECK;
CONDCHECK FIELD(#OV_MKEY01) COND('((#OV_MKEY01 * #OV_MKEY02 * #OV_MKEY03) *LE 100000)') MSGTXT('Loop values multiply to more than 100000 iterations');
ENDCHECK;
********** COMMENT(Initialize the indexed space );
CHANGE FIELD(#OV_TOTAL) TO(0);
BEGIN_LOOP USING(#OV_KEY01) TO(#OV_MKEY01);
CHANGE FIELD(#OV_DATA01) TO(#OV_KEY01);
BEGIN_LOOP USING(#OV_KEY02) TO(#OV_MKEY02);
CHANGE FIELD(#OV_DATA02) TO(#OV_KEY02);
BEGIN_LOOP USING(#OV_KEY03) TO(#OV_MKEY03);
CHANGE FIELD(#OV_DATA03) TO(#OV_KEY03);
CHANGE FIELD(#OV_TOTAL) TO('#OV_TOTAL + 1');
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(INSERT #OV_HANDLE) TO_GET(#OV_RC);
END_LOOP;
END_LOOP;
END_LOOP;
**********;
MESSAGE MSGTXT('Index area initialized. Total entry's is shown. Click OK to do lookup speed test');
DISPLAY FIELDS(#OV_TOTAL);
**********;
CHANGE FIELD(#OV_TOTAL) TO(0);
BEGIN_LOOP USING(#OV_KEY01) TO(#OV_MKEY01);
BEGIN_LOOP USING(#OV_KEY02) TO(#OV_MKEY02);
BEGIN_LOOP USING(#OV_KEY03) TO(#OV_MKEY03);
CHANGE FIELD(#OV_TOTAL) TO('#OV_TOTAL + 1');
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(GET #OV_HANDLE) TO_GET(#OV_RC);
IF COND('(#OV_RC *ne OK) *or (#OV_DATA01 *ne #OV_key01)  *or (#OV_data02 *ne #OV_key02)  *or (#OV_data03 *ne #OV_key03)');
MESSAGE MSGTXT('Lookup was error was detected for key values shown');
REQUEST FIELDS(#OV_KEY01 #OV_KEY02 #OV_KEY03 #OV_RC);
ENDIF;
END_LOOP;
END_LOOP;
END_LOOP;
MESSAGE MSGTXT('Test completed. Total number of lookup tests is shown');
DISPLAY FIELDS(#OV_TOTAL);

 

The following sample RDML function uses an indexed space to aggregate details of employee salary information. It uses the standard LANSA demonstration file PSLMST as the basis of employee salary information:

FUNCTION OPTIONS(*LIGHTUSAGE *DIRECT);
********** COMMENT(Departmental Summary definitions);
DEFINE FIELD(#OVTDEPSAL) TYPE(*DEC) LENGTH(15) DECIMALS(2) COLHDG('Total' 'Salary' 'Expenditure') EDIT_CODE(3);
DEFINE FIELD(#OVXDEPSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Maximum' 'Salary') EDIT_CODE(3);
DEFINE FIELD(#OVNDEPSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Minimum' 'Salary') EDIT_CODE(3);
DEFINE FIELD(#OVADEPSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Average' 'Salary') EDIT_CODE(3);
DEFINE FIELD(#OVCDEPSAL) TYPE(*DEC) LENGTH(7) DECIMALS(0) COLHDG('Total' 'Employees') EDIT_CODE(3);
DEFINE FIELD(#OVFDEPSAL) TYPE(*CHAR) LENGTH(256) DECIMALS(0) COLHDG('Indexed' 'Space' 'Definition');
CHANGE FIELD(#OVFDEPSAL) TO('deptment key(), ovtdepsal sum(salary), ovxdepsal max(salary), ovndepsal min(salary), ovadepsal avg(salary), ovcdepsal count()');
DEFINE FIELD(#OVHDEPSAL) TYPE(*CHAR) LENGTH(10) LABEL('Space Handle');
DEF_LIST NAME(#OVSDEPSAL) FIELDS(#DEPTMENT #OVTDEPSAL #OVCDEPSAL #OVXDEPSAL #OVNDEPSAL #OVADEPSAL);
**********;
DEFINE FIELD(#OV_RC) TYPE(*CHAR) LENGTH(2) LABEL('Return Code');
********** COMMENT(Create the indexed space);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(CREATE #OVFDEPSAL) TO_GET(#OV_RC #OVHDEPSAL);
********** COMMENT((Pass over the data and update the summary details');
SELECT FIELDS(#DEPTMENT #SALARY) FROM_FILE(PSLMST);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(PUT #OVHDEPSAL) TO_GET(#OV_RC);
ENDSELECT;
********** COMMENT(Now load/show a browse list with the results);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(FIRST #OVHDEPSAL) TO_GET(#OV_RC);
DOWHILE COND('#OV_RC = OK');
ADD_ENTRY TO_LIST(#OVSDEPSAL);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(NEXT #OVHDEPSAL) TO_GET(#OV_RC);
ENDWHILE;
DISPLAY BROWSELIST(#OVSDEPSAL);
********** COMMENT(Destroy the indexed space);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(DESTROY #OVHDEPSAL) TO_GET(#OV_RC);

 

The following sample RDML function is identical to the previous one except that it uses a second indexed space to aggregate information for all departments and then adds the "grand" aggregates to the end of the aggregate list that is displayed to the user. This demonstrates how indexed spaces can be used to perform multiple level totaling by using multiple indexed spaces :

 

FUNCTION OPTIONS(*LIGHTUSAGE *DIRECT);
********** COMMENT(Departmental Summary definitions);
DEFINE FIELD(#OVTDEPSAL) TYPE(*DEC) LENGTH(15) DECIMALS(2) COLHDG('Total' 'Salary' 'Expenditure') EDIT_CODE(3);
DEFINE FIELD(#OVXDEPSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Maximum' 'Salary') EDIT_CODE(3);
DEFINE FIELD(#OVNDEPSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Minimum' 'Salary') EDIT_CODE(3);
DEFINE FIELD(#OVADEPSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Average' 'Salary') EDIT_CODE(3);
DEFINE FIELD(#OVCDEPSAL) TYPE(*DEC) LENGTH(7) DECIMALS(0) COLHDG('Total' 'Employees') EDIT_CODE(3);
DEFINE FIELD(#OVFDEPSAL) TYPE(*CHAR) LENGTH(256) DECIMALS(0) COLHDG('Indexed' 'Space' 'Definition');
CHANGE FIELD(#OVFDEPSAL) TO('deptment key(), ovtdepsal sum(salary), ovxdepsal max(salary), ovndepsal min(salary), ovadepsal avg(salary), ovcdepsal count()');
DEFINE FIELD(#OVHDEPSAL) TYPE(*CHAR) LENGTH(10) LABEL('Space Handle');
DEF_LIST NAME(#OVSDEPSAL) FIELDS(#DEPTMENT #OVTDEPSAL #OVCDEPSAL #OVXDEPSAL #OVNDEPSAL #OVADEPSAL);
**********;
DEFINE FIELD(#OVFGRAND) TYPE(*CHAR) LENGTH(256) DECIMALS(0) COLHDG('Indexed' 'Space' 'Definition');
CHANGE FIELD(#OVFGRAND) TO('ovkgrand key(), ovtdepsal sum(salary), ovxdepsal max(salary), ovndepsal min(salary), ovadepsal avg(salary), ovcdepsal count()');
DEFINE FIELD(#OVHGRAND) TYPE(*CHAR) LENGTH(10) LABEL('Space Handle');
DEFINE FIELD(#OVKGRAND) REFFLD(#DEPTMENT) LABEL('Invariant Key') DEFAULT('''*ALL''');
**********;
DEFINE FIELD(#OV_RC) TYPE(*CHAR) LENGTH(2) LABEL('Return Code');
********** COMMENT(Create the indexed spaces);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(CREATE #OVFDEPSAL) TO_GET(#OV_RC #OVHDEPSAL);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(CREATE #OVFGRAND) TO_GET(#OV_RC #OVHGRAND);
********** COMMENT((Pass over the data and create the summary indexes');
SELECT FIELDS(#DEPTMENT #SALARY) FROM_FILE(PSLMST);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(PUT #OVHDEPSAL) TO_GET(#OV_RC);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(PUT #OVHGRAND) TO_GET(#OV_RC);
ENDSELECT;
********** COMMENT(Now load/show a browse list with the results);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(FIRST #OVHDEPSAL) TO_GET(#OV_RC);
DOWHILE COND('#OV_RC = OK');
ADD_ENTRY TO_LIST(#OVSDEPSAL);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(NEXT #OVHDEPSAL) TO_GET(#OV_RC);
ENDWHILE;
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(GET #OVHGRAND) TO_GET(#OV_RC);
CHANGE FIELD(#DEPTMENT) TO(#OVKGRAND);
ADD_ENTRY TO_LIST(#OVSDEPSAL);
********** COMMENT(Display the results);
DISPLAY BROWSELIST(#OVSDEPSAL);
********** COMMENT(Destroy the indexed space);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(DESTROY #OVHDEPSAL) TO_GET(#OV_RC);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(DESTROY #OVHGRAND) TO_GET(#OV_RC);

 

The following example RDML function is again very similar to the previous two, except that it produces two additional aggregation lists by using two additional indexed spaces. The second is by department/section and the third is by salary (i.e., a distribution of how many employees earn a particular salary value) :

FUNCTION OPTIONS(*LIGHTUSAGE *DIRECT);
********** COMMENT(Departmental Summary definitions);
DEFINE FIELD(#OVTDEPSAL) TYPE(*DEC) LENGTH(15) DECIMALS(2) COLHDG('Total' 'Salary' 'Expenditure') EDIT_CODE(3);
DEFINE FIELD(#OVXDEPSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Maximum' 'Salary') EDIT_CODE(3);
DEFINE FIELD(#OVNDEPSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Minimum' 'Salary') EDIT_CODE(3);
DEFINE FIELD(#OVADEPSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Average' 'Salary') EDIT_CODE(3);
DEFINE FIELD(#OVCDEPSAL) TYPE(*DEC) LENGTH(7) DECIMALS(0) COLHDG('Total' 'Employees') EDIT_CODE(3);
DEFINE FIELD(#OVFDEPSAL) TYPE(*CHAR) LENGTH(256) DECIMALS(0) COLHDG('Indexed' 'Space' 'Definition');
CHANGE FIELD(#OVFDEPSAL) TO('deptment key(), ovtdepsal sum(salary), ovxdepsal max(salary), ovndepsal min(salary), ovadepsal avg(salary), ovcdepsal count()');
DEFINE FIELD(#OVHDEPSAL) TYPE(*CHAR) LENGTH(10) LABEL('Space Handle');
DEF_LIST NAME(#OVSDEPSAL) FIELDS(#DEPTMENT #OVTDEPSAL #OVCDEPSAL #OVXDEPSAL #OVNDEPSAL #OVADEPSAL);
********** COMMENT(Section Summary definitions);
DEFINE FIELD(#OVTSECSAL) TYPE(*DEC) LENGTH(15) DECIMALS(2) COLHDG('Total' 'Salary' 'Expenditure') EDIT_CODE(3);
DEFINE FIELD(#OVXSECSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Maximum' 'Salary') EDIT_CODE(3);
DEFINE FIELD(#OVNSECSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Minimum' 'Salary') EDIT_CODE(3);
DEFINE FIELD(#OVASECSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Average' 'Salary') EDIT_CODE(3);
DEFINE FIELD(#OVCSECSAL) TYPE(*DEC) LENGTH(7) DECIMALS(0) COLHDG('Total' 'Employees') EDIT_CODE(3);
DEFINE FIELD(#OVFSECSAL) TYPE(*CHAR) LENGTH(256) DECIMALS(0) COLHDG('Indexed' 'Space' 'Definition');
CHANGE FIELD(#OVFSECSAL) TO('deptment key(), section key(), ovtdepsal sum(salary), ovxdepsal max(salary), ovndepsal min(salary), ovadepsal avg(salary), ovcdepsal count()');
DEFINE FIELD(#OVHSECSAL) TYPE(*CHAR) LENGTH(10) LABEL('Space Handle');
DEF_LIST NAME(#OVSSECSAL) FIELDS(#DEPTMENT #SECTION #OVTDEPSAL #OVCDEPSAL #OVXDEPSAL #OVNDEPSAL #OVADEPSAL);
********** COMMENT(Salary Distribution Definitions);
DEFINE FIELD(#OVCSALSAL) TYPE(*DEC) LENGTH(7) DECIMALS(0) COLHDG('Total' 'Employees') EDIT_CODE(3);
DEFINE FIELD(#OVFSALSAL) TYPE(*CHAR) LENGTH(256) DECIMALS(0) COLHDG('Indexed' 'Space' 'Definition');
CHANGE FIELD(#OVFSALSAL) TO('salary key(),  ovcsalsal count()');
DEFINE FIELD(#OVHSALSAL) TYPE(*CHAR) LENGTH(10) LABEL('Space Handle');
DEF_LIST NAME(#OVSSALSAL) FIELDS(#SALARY #OVCSALSAL);
**********;
DEFINE FIELD(#OV_RC) TYPE(*CHAR) LENGTH(2) LABEL('Return Code');
********** COMMENT(Create the indexed space);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(CREATE #OVFDEPSAL) TO_GET(#OV_RC #OVHDEPSAL);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(CREATE #OVFSECSAL) TO_GET(#OV_RC #OVHSECSAL);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(CREATE #OVFSALSAL) TO_GET(#OV_RC #OVHSALSAL);
********** COMMENT((Pass over the data and create the summary indexes');
SELECT FIELDS(#DEPTMENT #SECTION #SALARY) FROM_FILE(PSLMST);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(PUT #OVHDEPSAL) TO_GET(#OV_RC);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(PUT #OVHSECSAL) TO_GET(#OV_RC);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(PUT #OVHSALSAL) TO_GET(#OV_RC);
ENDSELECT;
********** COMMENT(Now load/show a browse list with the results);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(FIRST #OVHDEPSAL) TO_GET(#OV_RC);
DOWHILE COND('#OV_RC = OK');
ADD_ENTRY TO_LIST(#OVSDEPSAL);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(NEXT #OVHDEPSAL) TO_GET(#OV_RC);
ENDWHILE;
DISPLAY BROWSELIST(#OVSDEPSAL);
********** COMMENT(Now load/show a browse list with the results);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(FIRST #OVHSECSAL) TO_GET(#OV_RC);
DOWHILE COND('#OV_RC = OK');
ADD_ENTRY TO_LIST(#OVSSECSAL);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(NEXT #OVHSECSAL) TO_GET(#OV_RC);
ENDWHILE;
DISPLAY BROWSELIST(#OVSSECSAL);
********** COMMENT(Now load/show a browse list with the results);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(FIRST #OVHSALSAL) TO_GET(#OV_RC);
DOWHILE COND('#OV_RC = OK');
ADD_ENTRY TO_LIST(#OVSSALSAL);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(NEXT #OVHSALSAL) TO_GET(#OV_RC);
ENDWHILE;
DISPLAY BROWSELIST(#OVSSALSAL);
********** COMMENT(Destroy the indexed spaces);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(DESTROY #OVHDEPSAL) TO_GET(#OV_RC);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(DESTROY #OVHSECSAL) TO_GET(#OV_RC);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(DESTROY #OVHSALSAL) TO_GET(#OV_RC);

 

The following sample RDML function is designed to demonstrate how indexed lists can be used to improve application performance in "batch" style jobs processing large amounts of information.

Logically, the "engine" loop of this function is like this :

   BEGIN_LOOP TO(#OV_ITER);
      SELECT FIELDS(#EMPNO #SURNAME #GIVENAME #DEPTMENT #SECTION) FROM_FILE(PSLMST);
         FETCH FIELDS(#DEPTDESC) FROM_FILE(DEPTAB) WITH_KEY(#DEPTMENT);
         FETCH FIELDS(#SECDESC) FROM_FILE(SECTAB) WITH_KEY(#DEPTMENT #SECTION);
      ENDSELECT;
   END_LOOP;

 

This loop selects all the employees in the standard shipped demonstration table PSLMST (repeated for a specified number of iterations). It does this to attempt to emulate the processing of a large number of records typically found in a "batch" job.

For each row selected it fetches in the associated department and section description from the DEPATB and SECTAB tables.

However, the "engine" loop has actually been coded as:

   BEGIN_LOOP TO(#OV_ITER);
      SELECT FIELDS(#EMPNO #SURNAME #GIVENAME #DEPTMENT #SECTION) FROM_FILE(PSLMST);
         IF COND(*USEINDEX);
            USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(GET #OV_DEPTAB) TO_GET(#OV_RC);
            USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(GET #OV_SECTAB) TO_GET(#OV_RC);
         ELSE;
            FETCH FIELDS(#DEPTDESC) FROM_FILE(DEPTAB) WITH_KEY(#DEPTMENT);
            FETCH FIELDS(#SECDESC) FROM_FILE(SECTAB) WITH_KEY(#DEPTMENT #SECTION);
         ENDIF;
      ENDSELECT;
   END_LOOP;

 

which allows you to run the engine loop using either the DBMS (i.e., the FETCH commands) or an indexed space (i.e., the USE OV_INDEXED_SPACE commands) to get the department and section descriptions. By doing this you will be able to see the speed advantage that an indexed space provides over performing a full DBMS access to find information.

The full sample RDML function follows.

Notice that you can run the "engine" loop through 1 -> 20 iterations using either the DBMS (specify D) or an indexed space (specify I) to fetch the department and section description details. All department and section descriptions are loaded into their associated indexed spaces at the beginning of the function:

FUNCTION OPTIONS(*LIGHTUSAGE *DIRECT);
********** COMMENT(Define and load the department indexed space);
DEFINE FIELD(#OV_DEPDEF) TYPE(*CHAR) LENGTH(50) LABEL('Dept Index');
CHANGE FIELD(#OV_DEPDEF) TO('deptment key(), deptdesc');
DEFINE FIELD(#OV_DEPTAB) TYPE(*CHAR) LENGTH(10) LABEL('Index Handle');
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(CREATE #OV_DEPDEF) TO_GET(#OV_RC #OV_DEPTAB);
SELECT FIELDS(#DEPTMENT #DEPTDESC) FROM_FILE(DEPTAB);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(INSERT #OV_DEPTAB) TO_GET(#OV_RC);
ENDSELECT;
********** COMMENT(Define and load the section indexed space);
DEFINE FIELD(#OV_SECDEF) TYPE(*CHAR) LENGTH(50) LABEL(' Section Index');
CHANGE FIELD(#OV_SECDEF) TO('deptment key(), section key(), secdesc');
DEFINE FIELD(#OV_SECTAB) TYPE(*CHAR) LENGTH(10) LABEL('Index Handle');
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(CREATE #OV_SECDEF) TO_GET(#OV_RC #OV_SECTAB);
SELECT FIELDS(#DEPTMENT #SECTION #SECDESC) FROM_FILE(SECTAB);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(INSERT #OV_SECTAB) TO_GET(#OV_RC);
ENDSELECT;
********** COMMENT(Define other variables);
OVERRIDE FIELD(#GIVENAME) LENGTH(10);
DEFINE FIELD(#OV_RC) TYPE(*CHAR) LENGTH(2) LABEL('Return Code');
DEFINE FIELD(#OV_MODE) TYPE(*CHAR) LENGTH(1) LABEL('Mode (D/I)') DEFAULT(D);
DEFINE FIELD(#OV_ITER) TYPE(*DEC) LENGTH(3) DECIMALS(0) LABEL('Iterations') EDIT_CODE(3) DEFAULT(5);
DEFINE FIELD(#OV_TOTAL) TYPE(*DEC) LENGTH(7) DECIMALS(0) LABEL('PSLMST Accesses') EDIT_CODE(3);
DEF_COND NAME(*USEINDEX) COND('#OV_MODE = I');
DEF_LIST NAME(#OV_LIST) FIELDS(#SURNAME #GIVENAME #DEPTDESC #SECDESC) COUNTER(#OV_TOTAL);
********** COMMENT(Repeat testing until cancelled);
BEGIN_LOOP;
********** COMMENT(Request and validate testing details);
POP_UP FIELDS((#OV_MODE *IN) (#OV_ITER *IN)) EXIT_KEY(*NO) PROMPT_KEY(*NO);
BEGINCHECK;
VALUECHECK FIELD(#OV_MODE) WITH_LIST(D I) MSGTXT('Mode must be D (use DBMS) or I (use indexed space)');
RANGECHECK FIELD(#OV_ITER) RANGE((1 20)) MSGTXT(('Number of iterations must be in range 1 to 20'));
ENDCHECK;
********** COMMENT(Repeat the test for the number of iterations);
CLR_LIST NAMED(#OV_LIST);
BEGIN_LOOP TO(#OV_ITER);
SELECT FIELDS(#EMPNO #SURNAME #GIVENAME #DEPTMENT #SECTION) FROM_FILE(PSLMST);
IF COND(*USEINDEX);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(GET #OV_DEPTAB) TO_GET(#OV_RC);
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(GET #OV_SECTAB) TO_GET(#OV_RC);
ELSE;
FETCH FIELDS(#DEPTDESC) FROM_FILE(DEPTAB) WITH_KEY(#DEPTMENT);
FETCH FIELDS(#SECDESC) FROM_FILE(SECTAB) WITH_KEY(#DEPTMENT #SECTION);
ENDIF;
ADD_ENTRY TO_LIST(#OV_LIST);
ENDSELECT;
END_LOOP;
********** COMMENT(display the results);
DISPLAY FIELDS(#OV_TOTAL) BROWSELIST(#OV_LIST) EXIT_KEY(*NO) MENU_KEY(*NO) PROMPT_KEY(*NO);
END_LOOP;